Why formulas automate calculations in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a sales assistant at a small retail store.

📋 Request: Your manager wants you to create a simple sales summary that automatically calculates total sales and average sales per product.

📊 Data: You have a list of products, the number of units sold, and the price per unit.

🎯 Deliverable: Create a sheet that uses formulas to calculate total sales per product and the average sales across all products.

Sample Data

Product	Units Sold	Price per Unit
Apples	10	0.5
Bananas	15	0.3
Cherries	7	1.2
Dates	5	1.5
Elderberries	3	2.0

1

Step 1: In a new column next to Price per Unit, calculate Total Sales for each product by multiplying Units Sold by Price per Unit.

=B2*C2

Expected Result

For Apples, Total Sales = 10 * 0.5 = 5.0

2

Step 2: Copy the formula down for all products to calculate their Total Sales.

Copy =B2*C2 down to rows 3 to 6

Expected Result

Bananas: 4.5, Cherries: 8.4, Dates: 7.5, Elderberries: 6.0

3

Step 3: Below the Total Sales column, calculate the Average Sales using the AVERAGE formula on the Total Sales values.

=AVERAGE(D2:D6)

Expected Result

Average Sales = (5.0 + 4.5 + 8.4 + 7.5 + 6.0) / 5 = 6.28

4

Step 4: Explain that these formulas automatically update if Units Sold or Price per Unit change, so calculations are always current.

No formula needed; explanation only.

Expected Result

If you change Units Sold for Apples to 12, Total Sales updates to 6.0 automatically.

Final Result

Product     Units Sold  Price per Unit  Total Sales
--------------------------------------------------
Apples      10          0.5             5.0
Bananas     15          0.3             4.5
Cherries    7           1.2             8.4
Dates       5           1.5             7.5
Elderberries3           2.0             6.0

Average Sales: 6.28

✓Formulas multiply units sold by price to get total sales automatically.

✓Average sales formula calculates the mean of total sales.

✓Changing input data updates all related calculations instantly.

✓Formulas save time and reduce errors compared to manual calculations.

Bonus Challenge

Add a new column that calculates a 10% sales tax on Total Sales for each product.

Show Hint

Use a formula multiplying Total Sales by 0.10, e.g., =D2*0.10