Introduction
In the world of data science, we often get excited about Python, SQL, and Machine Learning. But the truth is, the world runs on spreadsheets. Microsoft Excel remains the most widely used tool for business intelligence and rapid prototyping.
For my latest project at the Lux Tech Academy, I was challenged to take a raw, messy sales dataset and transform it into a "Mastery Dashboard." The goal? To answer complex business questions like "Which sales reps are giving away too much discount?" and "What happens to our profit if inflation hits 10%?"
Here is how I built it,from data cleaning to advanced scenario modeling.
Step 1: The Clean Up (Data Preparation)
Real-world data is never clean. My dataset had 600+ rows of transactional data, but it was full of errors.
Missing Data: I found blank entries for cities and salespersons. I used Excel's Go To Special > Blanks feature to identify and fill these gaps efficiently.
Negative Prices: Some unit prices were negative (a common data entry error). I fixed this using the =ABS() function to convert them to positive values.
Lead Time: To measure shipping efficiency, I created a new column LeadTimeDays using the formula =RequiredDate - OrderDate.
Step 2: The Logic (Advanced Formulas)
To get real insights, raw data isn't enough. I needed to calculate profitability. I used structural formulas to derive:
Gross Revenue: Price × Quantity × (1 - Discount)
Margin %: Gross Profit / Gross Revenue
This allowed me to see not just how much we were selling, but how much money we were actually making.
Step 3: The "What-If" Machine (Scenario Modeling)
This was the most powerful part of the project. I didn't want a static report; I wanted a tool that could predict the future.
I built a Scenario Control Panel that allows a user to type in an "Inflation Rate" or a "Discount Cap." By linking my data columns to these input cells using absolute references (e.g., $AF$2), the entire dataset updates instantly.
Scenario A: If inflation rises by 5%, our costs adjust automatically.
Scenario B: If we cap discounts at 20%, we can see exactly how much revenue we save.
Step 4: Visualizing the Story (The Dashboard)
Finally, I brought it all together in an interactive dashboard.
Slicers: I added buttons to filter data by Channel (Retail vs. Online) and Product Category.
KPI Cards: Big, bold numbers at the top show Total Revenue, Margin %, and On-Time Delivery rates.
Outlier Detection: I built a chart specifically to catch Sales Reps who were giving discounts higher than 20%, helping management spot revenue leakage.
Conclusion
This project reinforced that Excel is more than just rows and columns—it is a powerful decision-support system. By combining data cleaning, logic formulas, and interactive dashboarding, I was able to turn a CSV file into a business solution.
Top comments (0)