Business Context
Adventure Works is a fictionalised manufacturing and retail company whose multi-year transactional data is widely used in BI training. The challenge here was not just to build charts, it was to model six relational tables in Power Pivot, write custom DAX measures for KPIs, automate dashboard interactivity with Excel macros, and surface actionable insights for four distinct stakeholder groups: executive leadership, marketing, finance, and product teams.
Time Series Analysis
Revenue, profit and transaction trends across years, quarters, and days of the week, with YoY comparisons and interactive year slicer
Sales Analysis
Product colour, top customers, gender split, and age demographics, drilling into where profit is actually concentrated across 606 products
The Dashboard
Built entirely in Excel with Power Pivot data modelling, DAX measures, VBA macros, and interactive slicers. The dark theme, donut chart, and KPI tiles with YoY change indicators were all built from scratch.
What the Data Revealed
$307.09M total revenue, $126.29M profit at a 41.1% margin across 2005–2008. Revenue and profit both on a consistent upward trajectory over the four years.
Q2 was the strongest quarter, contributing 31% of total profit ($39.30M). Q3 was the weakest at only 19%, indicating a significant mid-year seasonal dip.
2007 delivered the highest single-year profit. 2007 and 2008 tied for highest revenue at $102M each, while 2008 recorded the most individual transactions, indicating a growing but slightly lower-margin customer base.
Wednesday–Friday drove 43.8% of total profit, the prime selling window. Weekdays combined accounted for 79% of total profit vs 21% for weekends.
The top 4 products contributed 20% of total profit across a 606-product catalogue, a classic long-tail distribution where a tiny fraction of products carries disproportionate weight.
Black, red, and silver products combined drove 75% of total profit. Black was the single highest-performing colour. Colour is a meaningful proxy for product tier and customer preference.
Top 5 customers contributed only 0.28% of total profit, revenue is broadly distributed across the customer base. No single customer concentration risk exists.
Customers aged 50+ contributed 39.4% of total profit, the single most valuable demographic segment. Gender split was near-equal (50.4% female, 49.6% male).
4 Charts. 4 Business Questions.
Excel Techniques Used
Business Recommendations
Capitalise on Q2 with increased marketing investment
Q2 contributes 31% of annual profit, nearly 1.6× the weakest quarter. Marketing and promotional budgets should be front-loaded into Q1/Q2 to maximise returns during the natural peak. Spring campaigns, early-bird promotions, and partner co-marketing should all be timed to the Q2 buying window.
→ Marketing & Executive LeadershipDevelop targeted Q3 promotions to close the seasonal gap
The 12-percentage-point gap between Q2 (31%) and Q3 (19%) represents the largest single revenue optimisation opportunity. A structured mid-year promotion, flash sales, bundle offers, or loyalty reward activations, targeting the high-value 50+ segment specifically in July and August could meaningfully narrow this gap.
→ Marketing & FinanceSchedule campaign launches and product drops on Wednesday–Friday
Wednesday–Friday drives 43.8% of total profit. This is not coincidence, it reflects mid-to-late-week purchase decision behaviour. Product launches, email campaigns, paid media pushes, and time-limited promotions should all be timed to land on Wednesday or Thursday to capture the peak engagement window.
→ Marketing & ProductPrioritise black, red, and silver product lines in supply and development
These three colours drive 75% of total profit from a 606-product catalogue. This concentration is a signal about which product categories and price tiers are resonating. New product development should be evaluated against the traits of these high-performing lines, and supply chain allocation should reflect their disproportionate revenue contribution.
→ Product & Supply ChainBuild a dedicated loyalty programme for the 50+ age segment
Customers aged 50+ generate 39.4% of profit from what is likely a smaller share of total customer count, making them the highest lifetime-value cohort. A tiered loyalty programme with premium product access, early release offers, and personalised service would formalise this relationship and reduce churn in the most profitable demographic segment.
→ CRM & Marketing