Why formulas automate calculations in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a simple sales report that automatically calculates total sales and average sales per day.

📊 Data: You have daily sales data for 10 days, including the date and the amount sold each day.

🎯 Deliverable: Create a spreadsheet that uses formulas to automatically calculate the total sales and the average sales per day.

Sample Data

Date	Sales Amount
2024-06-01	150
2024-06-02	200
2024-06-03	180
2024-06-04	220
2024-06-05	170
2024-06-06	210
2024-06-07	190
2024-06-08	230
2024-06-09	160
2024-06-10	200

1

Step 1: Enter the daily sales data into two columns: 'Date' in column A and 'Sales Amount' in column B, starting from row 2.

Expected Result

Data is organized with dates in A2:A11 and sales amounts in B2:B11.

2

Step 2: In cell B12, calculate the total sales using a formula that adds all sales amounts.

=SUM(B2:B11)

Expected Result

Total sales amount is 1910.

3

Step 3: In cell B13, calculate the average sales per day using a formula that finds the average of the sales amounts.

=AVERAGE(B2:B11)

Expected Result

Average sales per day is 191.

4

Step 4: Label cells A12 as 'Total Sales' and A13 as 'Average Sales per Day' for clarity.

Expected Result

Cells A12 and A13 show descriptive labels.

5

Step 5: Test the formulas by changing one sales amount in the data and observe that total and average update automatically.

Change B2 from 150 to 160

Expected Result

Total sales updates to 1920 and average sales updates to 192.

Final Result

Date       | Sales Amount
-------------------------
2024-06-01 | 150
2024-06-02 | 200
2024-06-03 | 180
2024-06-04 | 220
2024-06-05 | 170
2024-06-06 | 210
2024-06-07 | 190
2024-06-08 | 230
2024-06-09 | 160
2024-06-10 | 200
-------------------------
Total Sales          1910
Average Sales per Day 191

✓Formulas like SUM and AVERAGE automatically update results when data changes.

✓This automation saves time and reduces errors compared to manual calculations.

✓Using formulas helps keep reports accurate and up-to-date easily.

Bonus Challenge

Add a formula to calculate the highest sales day and display the date and amount.

Show Hint

Use MAX to find the highest sales amount and MATCH or INDEX to find the corresponding date.