Why advanced formulas solve complex problems in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a detailed report that shows total sales, average sales per product category, and identifies the top-selling product in each category for the last quarter.

📊 Data: You have sales data including Date, Product Name, Category, Units Sold, and Unit Price for the last three months.

🎯 Deliverable: Create a summary report using advanced formulas that calculates total sales per category, average sales per product, and highlights the top-selling product in each category.

Sample Data

Date	Product Name	Category	Units Sold	Unit Price
2024-03-01	Notebook	Stationery	120	2.5
2024-03-05	Pen	Stationery	200	1.2
2024-03-10	Stapler	Stationery	75	5.0
2024-04-02	Chair	Furniture	15	45.0
2024-04-15	Desk	Furniture	10	120.0
2024-04-20	Table Lamp	Furniture	20	30.0
2024-05-01	Coffee Mug	Kitchenware	150	3.5
2024-05-10	Plate Set	Kitchenware	40	25.0
2024-05-15	Knife Set	Kitchenware	30	45.0

1

Step 1: Add a new column 'Total Sales' to calculate sales per row by multiplying Units Sold by Unit Price.

=D2*E2

Expected Result

For row 2 (Notebook), Total Sales = 120 * 2.5 = 300

2

Step 2: Calculate total sales per category using SUMIF formula.

=SUMIF(C:C, "Stationery", F:F)

Expected Result

Total sales for Stationery = 300 + 240 + 375 = 915

3

Step 3: Calculate average sales per product category using AVERAGEIF formula.

=AVERAGEIF(C:C, "Furniture", F:F)

Expected Result

Average sales for Furniture = (675 + 1200 + 600) / 3 = 825

4

Step 4: Identify the top-selling product in each category using MAXIFS and INDEX-MATCH formulas.

Top sales in Stationery: =MAXIFS(F:F, C:C, "Stationery") Top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Stationery"), F:F, 0))

Expected Result

Top-selling product in Stationery is Stapler with sales 375

5

Step 5: Repeat step 4 for Furniture and Kitchenware categories to find their top-selling products.

Furniture top sales: =MAXIFS(F:F, C:C, "Furniture") Furniture top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Furniture"), F:F, 0)) Kitchenware top sales: =MAXIFS(F:F, C:C, "Kitchenware") Kitchenware top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Kitchenware"), F:F, 0))

Expected Result

Furniture top product: Desk with sales 1200 Kitchenware top product: Knife Set with sales 1350

6

Step 6: Create a summary table listing each category, total sales, average sales, and top-selling product.

Use the results from previous steps to fill the summary table manually or with cell references.

Expected Result

Summary table shows Stationery: Total 915, Average 305, Top product Stapler Furniture: Total 2475, Average 825, Top product Desk Kitchenware: Total 2875, Average 958.33, Top product Knife Set

Final Result

Category    | Total Sales | Average Sales | Top-Selling Product
-------------------------------------------------------------
Stationery  | 915         | 305           | Stapler
Furniture   | 2475        | 825           | Desk
Kitchenware | 2875        | 958.33        | Knife Set

✓Kitchenware category has the highest total sales and average sales per product.

✓Stapler is the top-selling product in Stationery despite lower units sold because of higher unit price.

✓Knife Set leads Kitchenware sales due to high unit price despite lower units sold.

Bonus Challenge

Create a dynamic dropdown to select a category and display its total sales, average sales, and top product automatically.

Show Hint

Use Data Validation for dropdown and formulas with cell references to update summary based on selection.