BAI329 Lab application

Required information

[The following information applies to the questions displayed below. Note: This lab is only compatible with Excel for Windows (and not with Excel for Mac – Apple computers). Contact your instructor for guidance if needed.]

Lab Insight: Apply the same steps in Lab 6.1 to the Alt Lab 6.1 Data.xlsx dataset. While Lab 6.1 uses data for transactions with customers, the Alt Lab 6.1 dataset contains data regarding purchasing transactions with vendors/suppliers for raw materials, which will be converted into the beer that Slinte will sell to customers. A new data dictionary is presented in the Obtain the Data section.

Required:

  1. Create a pivot table to show the quantity of each type of raw material purchased per month.
  2. Add conditional formatting for the grand totals.
  3. Add sparklines for each product. If necessary, adjust your sparklines so that you can see a continuous line to represent zero values.
  4. Create a stacked bar chart for the pivot table results.

Data:

Specify the Question: What quantity of raw materials is purchased each month?

Obtain the Data: This lab uses the data in Alt Lab 6.1 Data.xlsx, which contains supplier/vendor transaction data for Slinte from January through June of 2026. The data dictionary for this file appears on the following page. The data include three worksheets/tables: Purchase_Orders, Suppliers, and Raw_Materials. In the Purchase_Orders worksheet/table, the data include an observation/row for each purchase transaction and type of raw material purchased. So, if a transaction included the purchase of two different raw materials, the dataset will have one unique Purchase_Order_ID with two rows (that is, one for each raw material type).

Analyze the Data: Refer to lab 6.1 in your text for additional instructions and steps.

Questions:

1. Which raw material did Slainte purchase in the largest quantity over the six months?

2. In which month did Slainte purchase the largest of Hops?

3. In April. which raw material did Slainte purchase in the smallest quantity?

4. In June, which raw material did Slainte purchase in the smallest quantity?

5. What is the total quantity of all raw materials purchased in January?