Excel for Data Analytics • Final Project
Real-World Excel Data Analytics Project
Work like a real data analyst. Start with raw sales data, calculate business metrics, investigate patterns, identify unusual results and communicate your findings.
YOUR ROLE
You Are the Data Analyst
An electronics company has given you a sales dataset. Management wants to understand revenue performance across products, regions and salespeople.
Your task is to turn the raw data into useful business information.
DATASET
Sales Transactions
Assume this data is stored in an Excel worksheet named SalesData.
| Order ID | Region | Product | Salesperson | Units | Price | Cost/Unit | Revenue |
|---|---|---|---|---|---|---|---|
| O1001 | North | Laptop | Amit | 5 | 50000 | 40000 | 250000 |
| O1002 | South | Monitor | Neha | 8 | 15000 | 11000 | 120000 |
| O1003 | East | Laptop | Rahul | 3 | 50000 | 40000 | 150000 |
| O1004 | West | Keyboard | Priya | 20 | 2000 | 1200 | 40000 |
| O1005 | North | Monitor | Karan | 10 | 15000 | 11000 | 150000 |
| O1006 | South | Laptop | Sonia | 4 | 50000 | 40000 | 200000 |
| O1007 | East | Mouse | Vikas | 25 | 1000 | 600 | 25000 |
| O1008 | West | Laptop | Riya | 2 | 50000 | 40000 | 100000 |
TASK 1 • BUILD A METRIC
Calculate Revenue Yourself
The Revenue column should be calculated from Units multiplied by Price.
Assume:
Units = C2
Price = D2
Type the formula you would enter for Revenue.
FORMULA CHALLENGE
What Excel formula calculates Revenue from Units and Price?
TASK 2 • TOTAL REVENUE
Calculate the Company Total
Revenue is stored in cells H2:H9.
FORMULA CHALLENGE
Write the Excel formula that calculates total revenue for all eight transactions.
CHECK YOUR RESULT
₹1,035,000
TASK 3 • PROFIT ANALYSIS
Calculate Profit
Profit is the amount remaining after product cost is subtracted from revenue.
If Revenue is in H2 and Cost/Unit is in G2, while Units are in E2, first calculate total cost as Units × Cost/Unit.
FORMULA CHALLENGE
What formula calculates Profit when Revenue is H2, Units is E2 and Cost/Unit is G2?
Example
Laptop transaction: ₹250,000 revenue − (5 × ₹40,000 cost) = ₹50,000 profit.
TASK 4 • PROFIT MARGIN
Measure Profitability
Revenue alone does not tell us how profitable a transaction is.
FORMULA CHALLENGE
If Profit is in I2 and Revenue is in H2, what formula calculates Profit Margin?
TASK 5 • INTERPRET THE DATA
Revenue vs Units
Look carefully at these two transactions.
Transaction O1001
5 units → ₹250,000 revenue
Transaction O1007
25 units → ₹25,000 revenue
CHECK YOUR UNDERSTANDING
What is the most reasonable analytical explanation for this difference?
TASK 6 • FILTER THE DATA
Investigate High-Value Transactions
Management wants to investigate transactions where Revenue is greater than ₹100,000.
Apply this filter:
CHECK YOUR UNDERSTANDING
Which transactions meet this condition?
TASK 7 • SORT
Find the Highest-Value Transaction
Sort the Revenue column from largest to smallest.
First record after descending sort
O1001 — ₹250,000
Salesperson: Amit • Product: Laptop • Region: North
CHECK YOUR UNDERSTANDING
What should you investigate after finding an unusually high transaction?
TASK 8 • PIVOTTABLE THINKING
Summarize Revenue by Region
Imagine you create a PivotTable with:
Rows
Region
Values
Sum of Revenue
| Region | Revenue |
|---|---|
| North | ₹400,000 |
| South | ₹320,000 |
| East | ₹175,000 |
| West | ₹140,000 |
CHECK YOUR UNDERSTANDING
Which region should management investigate first if the goal is to understand the strongest revenue contribution?
TASK 9 • PRODUCT ANALYSIS
Find the Revenue Driver
A PivotTable grouped by Product produces:
Laptop
₹700,000
Monitor
₹270,000
Keyboard
₹40,000
Mouse
₹25,000
Analyst observation
Laptop sales represent the largest revenue contribution in this dataset.
TASK 10 • DEBUGGING
Find the Formula Error
An analyst wants to calculate profit but writes:
This formula is actually mathematically valid because Excel follows multiplication before subtraction. However, the analyst may make the calculation easier to understand by using parentheses.
Clearer version
=H2-(E2*G2)
FORMULA CHALLENGE
Write the clearer profit formula using parentheses.
FINAL ANALYST TASK
Build the Analysis Workflow
You now have raw data, calculated metrics and summarized results. Think about the correct order an analyst should follow.
FINAL BUSINESS CASE
Present Your Findings
Imagine you are presenting the analysis to the sales manager. Which statement represents an evidence-based analytical finding?
CHECK YOUR UNDERSTANDING
Choose the strongest statement.
Excel Data Analytics Course Complete
You have completed the 10-lesson Excel Data Analytics course. You have worked with formulas, functions, logical analysis, data preparation, lookups, exploratory analysis, visualization and a complete business case.