DATA SUMMERIZATION QUESTIONS
❗ Important
These are QUESTIONS ONLY (no solutions).
Each dataset has 30+ rows.
Focus is on HOW to analyze and WHAT the statistics mean in business.
All scenarios are real-world analytics sectors.
QUESTION 1: Retail Sales Performance Analysis
- Sector: Retail / Business Analytics
- Dataset: Monthly Store Transactions (35 rows)
You are a data analyst working for a retail company operating multiple stores in Tanzania.
Management wants to understand sales performance, variability, patterns, and anomalies to
improve decision-making.
QUESTIONS
🧠 TASKS (DO NOT SKIP INTERPRETATION)
A. Data Aggregation (Basic Summarization)
1. Calculate:
o Total Revenue
o Average Revenue per transaction
o Maximum and Minimum Revenue
2. Explain in words:
o What does total revenue indicate about overall business performance?
o Why is average revenue more useful than total revenue when comparing
stores?
B. Conditional Aggregation (Critical Analyst Skill)
3. Using Excel formulas:
o Total revenue from Electronics
o Average revenue for Dar Mall
o Total units sold where Discount > 0
o Average revenue where Units Sold ≥ 5
4. Explain:
o What business questions each calculation answers.
o Why conditional aggregation is essential in real-world analytics.
C. Descriptive Analysis (CORE FOCUS – MEANING)
5. Calculate for Revenue:
o Mean
o Median
o Mode
o Range
o Variance
o Standard Deviation
6. Interpret (this is more important than calculations):
o What does the mean revenue represent in daily sales?
o Why might median revenue be more reliable than mean here?
o What does a high standard deviation say about sales stability?
o What does the range tell management about sales extremes?
D. Patterns & Anomalies (Analytical Thinking)
7. Identify:
o Transactions with unusually high revenue.
o Transactions with unusually low revenue.
8. Explain:
o Possible real-world reasons for these anomalies.
o Whether these are positive or negative from a business perspective.
E. Data Grouping with Pivot Tables & Slicers
9. Create Pivot Tables to show:
o Total revenue by Store
o Average revenue by Product Category
o Total units sold by Store and Category
10. Add slicers for:
Store
Product Category
11. Explain:
How Pivot Tables help summarize large datasets.
How slicers improve interactive data analysis for managers.
QUESTION 2: Employee Salary & Workforce Analysis
Sector:HumanResources/WorkforceAnalytics
Tool: Microsoft Excel
You are a data analyst working in the HR department of a mid-size organization.
Management wants to understand salary distribution, workforce structure, variability,
patterns, and anomalies to support fair pay, retention, and budgeting decisions.
🧠 TASKS (READ CAREFULLY)
A. Data Aggregation (Workforce Overview)
1. Calculate:
o Total salary cost
o Average salary
o Minimum and maximum salary
o Average performance score
2. Explain:
o What total salary cost tells management.
o Why average salary alone can be misleading in HR analysis.
B. Conditional Aggregation (Targeted Insights)
3. Using Excel formulas:
o Average salary for IT department
o Total salary cost for Sales department
o Average performance score where Experience Years > 5
o Average salary where PerformanceScore = 5
4. Explain:
o What business question each calculation answers.
o Why conditional aggregation is essential for HR decisions.
C. Descriptive Analysis (CORE MEANING)
5. Calculate descriptive statistics for Salary:
o Mean
o Median
o Mode
o Range
o Variance
o Standard Deviation
6. Interpret (do NOT skip meaning):
o What the mean salary represents in this company.
o Why the median salary may better represent a “typical” employee.
o What high or low salary spread indicates about pay structure.
o What right-skewed or left-skewed salary distribution means.
D. Patterns & Anomalies (Analytical Thinking)
7. Identify:
o Employees whose salary is unusually high relative to experience.
o Employees with high performance but comparatively low salary.
o Departments with wide salary variability.
8. Explain:
o Risks these anomalies present to the organization.
o How HR could use this analysis for retention and fairness.
E. Data Grouping with Pivot Tables & Slicers
9. Create Pivot Tables showing:
o Average salary by Department
o Average performance score by Role
o Total salary cost by Department
10. Add slicers for:
Department
Performance Score
Experience group (0–2, 3–5, 6–9, 10+)
11. Explain:
How Pivot Tables simplify workforce analysis.
How slicers support strategic HR decision-making.