Data Analytics Question

Required:

Part 1

  1. Create a pivot table.
  2. Add conditional formatting for the grand totals.
  3. Add sparklines for each type of beer.
  4. Create a stacked bar chart from the pivot table results.

Data: .

Specify the Question: How many cases of each beer are sold each month and in total from January through June?

Obtain the Data: This lab uses the data in Lab 6.1 Data.xlsx, which contains sales transaction data for Slinte from January through June of 2026. The data dictionary for this file is on the following page. The data include three worksheets/tables: Sales_Transactions, Customers, and Products. In the Sales_Transactions worksheet/table, the data include an observation/row for each sales transaction and product sold. So, if a transaction included the purchase of two products, the dataset has one unique Sales_Order_ID with two rows (that is, one for each product).

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

Upload the Word or PDF document containing your Lab screenshots using the button below.

Note: Please submit your file attachment response in one of the approved file formats; Word file (.doc or .docx), Excel file (.xls or .xlsx), or PDF. To submit photo or image files please paste them into Word or PDF. Consult with your instructor as needed on their preferred file attachment format.

Take a screenshot of your pivot table (with the conditional formatting and sparklines), paste it into a Word document named Lab 6.1 Excel Submission.docx, and label the screenshot Submission 1. Take a screenshot of the completed stacked bar chart, paste it into the same Word document, and label the screenshot Submission 2.

PART 2

1. Which beer sold the most cases over the six months?

2. Which beer sold the most cases in January?

3. Which beer sold more cases in June than January?

4. Which Excel option shows the sales trend over time?

5. Which Excel option compares the total sales for different products?

Part 3: Note: Submit your answers for the following Analysis Questions in the text entry field below.

  1. On the stacked bar chart, use filters to show IPA and Imperial IPA sales in May. When would filters be helpful to use?
  2. Which do you think provides the most helpful information to management: conditional formatting, sparklines, or the stacked bar chart? Explain.