Why automation saves hours in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants to understand how automating sales report calculations can save time compared to manual work.

📊 Data: You have daily sales data for 10 products over 7 days, including units sold and time spent manually calculating totals each day.

🎯 Deliverable: Create a spreadsheet that calculates total sales automatically and compares time spent manually versus automated, showing hours saved.

Sample Data

Product	Day 1 Units	Day 2 Units	Day 3 Units	Day 4 Units	Day 5 Units	Day 6 Units	Day 7 Units	Manual Time (minutes)
Product A	5	7	6	8	7	5	6	15
Product B	3	4	5	4	6	7	5	12
Product C	8	9	7	6	8	9	7	18
Product D	2	3	4	3	2	3	4	10
Product E	6	7	8	7	6	7	8	16
Product F	4	5	6	5	4	5	6	14
Product G	7	8	7	8	7	8	7	17
Product H	3	2	3	2	3	2	3	11
Product I	5	6	5	6	5	6	5	13
Product J	9	8	9	8	9	8	9	20

1

Step 1: Calculate total units sold for each product over 7 days.

In a new column 'Total Units', enter formula in row 2: =SUM(B2:H2) and copy down for all products.

Expected Result

Product A total units = 44, Product B total units = 34, Product C total units = 54, etc.

2

Step 2: Calculate total manual time spent for all products.

Use formula: =SUM(I2:I11)

Expected Result

Total manual time = 146 minutes

3

Step 3: Calculate automated time assuming automation takes 2 minutes per product.

In a new cell, enter: =COUNTA(A2:A11)*2

Expected Result

Automated time = 20 minutes

4

Step 4: Calculate time saved by automation in minutes.

In a new cell, enter: =SUM(I2:I11) - (COUNTA(A2:A11)*2)

Expected Result

Time saved = 126 minutes

5

Step 5: Convert time saved from minutes to hours.

In a new cell, enter: =(SUM(I2:I11) - (COUNTA(A2:A11)*2))/60

Expected Result

Time saved = 2.1 hours

Final Result

Product | Total Units | Manual Time (min)
-----------------------------------------
A       | 44          | 15
B       | 34          | 12
C       | 54          | 18
D       | 21          | 10
E       | 49          | 16
F       | 35          | 14
G       | 52          | 17
H       | 18          | 11
I       | 38          | 13
J       | 60          | 20

Total Manual Time: 146 minutes
Automated Time: 20 minutes
Time Saved: 126 minutes (2.1 hours)

✓Automation reduces manual work time from 146 minutes to 20 minutes.

✓This saves 126 minutes or about 2.1 hours.

✓Automating repetitive calculations saves significant time and effort.

Bonus Challenge

Create a chart comparing manual time and automated time per product to visualize time savings.

Show Hint

Use a bar chart with products on the x-axis and time on the y-axis, showing two bars per product for manual and automated times.