Calculated fields in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report showing total sales, total cost, and profit for each product category.

📊 Data: You have sales data including Product Category, Units Sold, Unit Price, and Unit Cost.

🎯 Deliverable: Create a table with calculated fields for Total Sales, Total Cost, and Profit for each product category.

Sample Data

Product Category	Units Sold	Unit Price	Unit Cost
Electronics	100	50	30
Clothing	200	20	10
Electronics	150	50	30
Furniture	50	100	70
Clothing	100	20	10
Furniture	30	100	70

1

Step 1: Create a pivot table from the sales data.

Insert > PivotTable > Select the entire data range including headers > Place pivot table in a new worksheet.

Expected Result

A blank pivot table ready for configuration.

2

Step 2: Add 'Product Category' to the Rows area of the pivot table.

Drag 'Product Category' field to Rows area.

Expected Result

Pivot table rows show Electronics, Clothing, Furniture.

3

Step 3: Add a calculated field for Total Sales: Units Sold * Unit Price.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Total Sales > Formula: ='Units Sold' * 'Unit Price' > Add.

Expected Result

Calculated field 'Total Sales' appears in the pivot table.

4

Step 4: Add a calculated field for Total Cost: Units Sold * Unit Cost.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Total Cost > Formula: ='Units Sold' * 'Unit Cost' > Add.

Expected Result

Calculated field 'Total Cost' appears in the pivot table.

5

Step 5: Add a calculated field for Profit: Total Sales - Total Cost.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Profit > Formula: ='Total Sales' - 'Total Cost' > Add.

Expected Result

Calculated field 'Profit' appears in the pivot table.

6

Step 6: Format the calculated fields as currency for better readability.

Select the calculated fields in the pivot table > Right-click > Number Format > Currency > OK.

Expected Result

Total Sales, Total Cost, and Profit values show with currency formatting.

Final Result

Product Category | Total Sales | Total Cost | Profit
---------------------------------------------------
Electronics      | $12,500     | $7,500     | $5,000
Clothing         | $6,000      | $3,000     | $3,000
Furniture        | $8,000      | $5,600     | $2,400

✓Electronics category has the highest total sales and profit.

✓Clothing has moderate sales and profit with lower unit prices.

✓Furniture has fewer units sold but higher unit prices, resulting in good profit.

Bonus Challenge

Add a calculated field to show Profit Margin as a percentage (Profit divided by Total Sales).

Show Hint

Create a calculated field with formula = Profit / Total Sales and format it as Percentage.