Why Excel is essential for data work - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to show why Excel is essential for handling and analyzing sales data.

📊 Data: You have monthly sales data for different products including units sold, price per unit, and total revenue.

🎯 Deliverable: Create a simple Excel report that calculates total sales, average price, and highlights best-selling products to demonstrate Excel's usefulness.

Sample Data

Product	Month	Units Sold	Price per Unit	Total Revenue
Apples	January	120	0.5	=C2*D2
Bananas	January	150	0.3	=C3*D3
Apples	February	100	0.5	=C4*D4
Bananas	February	130	0.3	=C5*D5
Cherries	January	200	1.2	=C6*D6
Cherries	February	180	1.2	=C7*D7

1

Step 1: Calculate Total Revenue for each row by multiplying Units Sold by Price per Unit.

In cell E2, enter =C2*D2 and copy down to E7.

Expected Result

Total Revenue values calculated for each product and month.

2

Step 2: Calculate Total Units Sold for each product.

Use SUMIF: =SUMIF(A$2:A$7, "Apples", C$2:C$7) for Apples, similarly for other products.

Expected Result

Apples: 220, Bananas: 280, Cherries: 380

3

Step 3: Calculate Average Price per Unit for each product.

Use AVERAGEIF: =AVERAGEIF(A$2:A$7, "Apples", D$2:D$7) for Apples, similarly for others.

Expected Result

Apples: 0.5, Bananas: 0.3, Cherries: 1.2

4

Step 4: Calculate Total Revenue per product.

Use SUMIF: =SUMIF(A$2:A$7, "Apples", E$2:E$7) for Apples, similarly for others.

Expected Result

Apples: 110, Bananas: 84, Cherries: 456

5

Step 5: Highlight the best-selling product by total revenue using conditional formatting.

Select total revenue cells, apply conditional formatting with rule: Format cells equal to MAX of total revenue range.

Expected Result

Cherries total revenue cell is highlighted as highest.

6

Step 6: Create a summary table showing Product, Total Units Sold, Average Price, and Total Revenue.

Organize results from steps 2, 3, and 4 into a clear table.

Expected Result

Summary table with correct totals and averages for each product.

Final Result

Product   | Total Units Sold | Average Price | Total Revenue
-----------------------------------------------------------
Apples    | 220              | 0.5           | 110
Bananas   | 280              | 0.3           | 84
Cherries  | 380              | 1.2           | 456  <-- Highlighted

✓Excel quickly calculates totals and averages from raw data.

✓Conditional formatting helps spot the best-selling product easily.

✓Excel organizes data clearly for quick business decisions.

Bonus Challenge

Create a chart that shows total revenue per product to visualize sales performance.

Show Hint

Use Insert > Chart, select the summary table's Product and Total Revenue columns.