Why statistical functions summarize data in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick summary of monthly sales data to understand overall performance and trends.

📊 Data: You have a table with monthly sales figures for different products over six months.

🎯 Deliverable: Create a summary report using statistical functions to show total sales, average sales, highest and lowest sales, and the number of months with sales data.

Sample Data

Month	Product A Sales	Product B Sales	Product C Sales
January	1200	850	900
February	1350	900	950
March	1100	870	920
April	1400	920	980
May	1250	880	940
June	1300	910	970

1

Step 1: Calculate the total sales for Product A over six months.

=SUM(B2:B7)

Expected Result

7600

2

Step 2: Calculate the average sales for Product B over six months.

=AVERAGE(C2:C7)

Expected Result

888.33

3

Step 3: Find the highest sales value for Product C.

=MAX(D2:D7)

Expected Result

980

4

Step 4: Find the lowest sales value for Product A.

=MIN(B2:B7)

Expected Result

1100

5

Step 5: Count the number of months with sales data for Product B.

=COUNTA(C2:C7)

Expected Result

6

Final Result

Monthly Sales Summary Report
----------------------------
Product A Total Sales: 7600
Product B Average Sales: 888.33
Product C Highest Sales: 980
Product A Lowest Sales: 1100
Months with Product B Sales Data: 6

✓Product A sold a total of 7600 units over six months.

✓Product B's average monthly sales were about 888 units.

✓Product C had its highest sales of 980 units in April.

✓Product A's lowest sales month was March with 1100 units.

✓Sales data is complete for all six months for Product B.

Bonus Challenge

Create a new column that calculates the total sales for all products each month and find the month with the highest total sales.

Show Hint

Use SUM across the product sales columns for each row, then use MAX to find the highest total.