Scenario 1:
You are a data analyst for a retail company. You have been given the daily sales figures (in
USD) for a specific store over a 15-day period.
Dataset: 250, 310, 275, 250, 400, 380, 290, 310, 250, 420, 360, 280, 310, 330, 295.
Questions:
Enter the dataset into an Excel column starting from A1 with the header "Daily Sales".
Format the numbers as Currency.
1. Central Tendency
• Calculate the Mean (Average) of the sales.
• Calculate the Median of the sales.
• Calculate the Mode of the sales.
2. Dispersion
• Calculate the Range of the sales.
• Calculate the Sample Variance.
• Calculate the Sample Standard Deviation.
3. Data Visualization
• Create a Histogram to visualize the distribution of the daily sales data. Ensure
your chart has an appropriate title and axis labels.
Scenario 2:
A human resources manager is analyzing the commute times (in minutes) of 20 employees to
evaluate a new remote work policy.
Dataset: 15, 45, 32, 50, 12, 60, 25, 30, 45, 55, 20, 35, 40, 90, 22, 28, 45, 38, 50, 18.
Questions:
Enter the data into column A with the header "Commute Time (Mins)".
1. Data Analysis
• Use the Data Analysis ToolPak to generate a comprehensive "Descriptive
Statistics" summary for the commute times.
2. Data Interpretation
• Based entirely on the ToolPak output table, type the following answers:
• What is the Standard Error?
• What is the Kurtosis?
3. Data Visualization
• Create a Box and Whisker Plot using the dataset to easily identify the spread
of the data and any outliers. Give the chart a descriptive title.
Scenario 3: Academic Performance & Attendance Analysis
You are an Academic Advisor analyzing the mid-semester performance of a 30-student
cohort. The administration wants to understand how the students are performing on their
Final Exams and whether their attendance shows any significant patterns. You need to
provide a statistical breakdown and visual evidence to support your findings.
Student Performance Dataset
Student ID Midterm Score (Out of 50) Final Exam (Out of 100) Attendance (%)
STU-001 42 85 92
STU-002 38 76 88
STU-003 15 45 60
STU-004 45 91 95
STU-005 40 82 90
STU-006 28 65 75
STU-007 48 95 98
STU-008 35 70 82
STU-009 36 74 85
STU-010 41 88 91
STU-011 22 55 65
STU-012 46 92 96
STU-013 39 79 89
STU-014 33 68 80
STU-015 44 89 94
STU-016 47 96 97
STU-017 29 62 72
STU-018 37 77 86
STU-019 43 84 93
STU-020 12 35 50
STU-021 49 98 99
STU-022 34 71 81
STU-023 31 66 78
STU-024 40 81 87
Student ID Midterm Score (Out of 50) Final Exam (Out of 100) Attendance (%)
STU-025 25 58 68
STU-026 45 90 94
STU-027 36 73 84
STU-028 42 86 91
STU-029 30 64 76
STU-030 50 100 100
Part 1: Manual Calculation of Central Tendency & Dispersion
1. Central Tendency: Using Excel formulas, calculate the Mean, Median, and Mode of
the Final Exam scores.
2. Dispersion: Using Excel formulas, calculate the Range, Sample Variance, and
Sample Standard Deviation of the Final Exam scores.
Part 2: Automated Analysis using the Data Analysis ToolPak
1. Use the Data Analysis ToolPak to run "Descriptive Statistics" on the Attendance
data.
2. Ensure you check "Summary statistics" and output the results to a new worksheet
named "Attendance Stats".
Part 3: Data Visualization
1. Histogram: Create a Histogram for the "Final Exam" scores to show the grade
distribution. Give it a proper title and axis labels.
2. Box Plot: Create a Box and Whisker Plot for the "Final Exam" scores to identify the
spread and pinpoint any specific outliers (students who scored unusually high or low).
Part 4: Interpretation and Summary (Written Outcome)
Based on your calculations and visualizations, write a brief, 3-4 sentence summary of the
class's performance on the Final Exam. Specifically, mention what the standard deviation and
the box plot tell you about the spread of the grades.
Project 1: Real Estate Market Analysis (Handling Outliers)
Scenario: A junior real estate agent wants to understand the housing market in a specific
neighbourhood. They have collected the recent sale prices of 20 homes. Students will
discover how a single luxury home (an outlier) affects the mean versus the median.
The Dataset (Home Prices in $1,000s)
Home ID Sale Price (in $1,000s)
H-01 250
Home ID Sale Price (in $1,000s)
H-02 265
H-03 270
H-04 280
H-05 295
H-06 300
H-07 310
H-08 315
H-09 320
H-10 325
H-11 330
H-12 340
H-13 350
H-14 360
H-15 375
H-16 380
H-17 390
H-18 410
H-19 450
H-20 1200
1. Calculate Mean, Median, Mode, Range, Variance, and Standard Deviation using
manual Excel formulas (=AVERAGE, =MEDIAN, etc.).
2. Create a Box and Whisker Plot to visualize the distribution and spot any outliers.
3. Interpretation: Write two sentences explaining whether the Mean or the Median is a
better representation of a "typical" house price in this neighbourhood and why.
Project 2: Quality Control (Consistency & Variance)
Scenario: A coffee roastery guarantees that their standard bags contain 250 grams of coffee
beans. The Quality Control manager has randomly sampled 20 bags from the production line.
Students will focus on Variance and Standard Deviation to see how consistent the machines
are.
The Dataset (Coffee Bag Weights in grams)
Bag ID Weight (g)
B-01 248
B-02 251
B-03 249
B-04 252
B-05 250
B-06 247
B-07 253
B-08 250
B-09 249
B-10 251
B-11 248
B-12 250
B-13 254
B-14 246
B-15 250
B-16 251
B-17 249
B-18 252
B-19 250
Bag ID Weight (g)
B-20 248
Project 3: Customer Service (Call Wait Times)
Scenario: A tech support call center wants to analyze how long customers wait on hold
before speaking to an agent. Students will use visual tools to figure out the distribution of
hold times.
The Dataset (Hold Time in minutes)
Caller ID Hold Time (Mins)
C-01 2
C-02 3
C-03 5
C-04 4
C-05 7
C-06 1
C-07 2
C-08 4
C-09 3
C-10 8
C-11 5
C-12 2
C-13 3
C-14 6
C-15 4
C-16 1
Caller ID Hold Time (Mins)
C-17 9
C-18 3
C-19 2
C-20 4
1. Calculate Mean, Median, Mode, Range, Variance, and Standard Deviation. (Students
can choose to use manual formulas OR the ToolPak).
2. Create both a Histogram and a Box Plot for the wait times.
3. Interpretation: Summarize the customer experience. Are most wait times short or
long?
Project 4: Supplier Selection (Comparative Variance)
Scenario: An automotive manufacturer needs to buy steel bolts. The bolts must be exactly
100 mm long. They are testing samples from two potential suppliers: Supplier A (cheaper)
and Supplier B (more expensive). The students must use descriptive statistics to recommend
which supplier to sign a contract with, focusing heavily on standard deviation.
The Dataset (Bolt Lengths in mm)
Sample ID Supplier A (mm) Supplier B (mm)
1 98.1 99.8
2 102.5 100.1
3 97.0 99.9
4 103.2 100.2
5 99.5 100.0
6 101.8 99.7
7 96.5 100.3
8 104.1 99.8
9 98.8 100.1
10 100.2 99.9
11 97.4 100.2
12 102.9 100.0
13 99.1 99.8
14 101.4 100.1
15 96.8 99.9
1. Use the Data Analysis ToolPak to run "Descriptive Statistics" on both Supplier A and
Supplier B.
2. Create a Box and Whisker Plot that includes both suppliers side-by-side to visually
compare their distributions.
3. Interpretation: Write a recommendation to the purchasing manager. Which supplier
should they choose and why? You must justify your answer using the Mean and the
Standard Deviation.
Project 5: Pre vs. Post Intervention (Healthcare)
Scenario: A corporate wellness program introduced a 12-week diet and exercise plan to help
employees lower their Systolic Blood Pressure. Students must analyze paired data (the same
people before and after) to determine if the program actually reduced blood pressure and
improved the group's overall health consistency.
The Dataset (Systolic Blood Pressure)
Employee BP (Before Program) BP (After Program)
E-01 145 130
E-02 150 135
E-03 138 132
E-04 160 140
E-05 142 128
E-06 155 138
E-07 135 130
E-08 148 134
E-09 152 136
E-10 165 142
E-11 130 125
E-12 140 133
1. Run the Data Analysis ToolPak to get the descriptive statistics for all three columns
(Before, After, and Change).
2. Create a Box Plot showing both "Before" and "After" data.
3. Interpretation: Did the program work? Look at the Mean BP before and after. Also,
look at the Standard Deviation—did the group become more similar (tighter variance)
or more varied after the program?