Excel Analytics

Context

Commercial teams live in Excel, yet most sales workbooks stop at a pivot table over a single flat sheet. This project treats Excel as a full analytics platform: a governed ETL layer, a dimensional data model, a reusable DAX measure library, and an interactive dashboard a decision-maker can actually explore.

Business Problem

Which product categories, regions, and sub-categories drive revenue and profit, and how is the business trending year over year?

Leadership needs a single, self-service view to read performance, spot outliers, and test scenarios before committing budget.

Architecture

The model is a star schema built inside Excel: a central fact table surrounded by conformed dimensions, joined by explicit one-to-many relationships. A dedicated date dimension unlocks time intelligence. This is the line between a spreadsheet and a model.

Data Source

• Source: Sample Superstore, the classic US retail dataset (9,994 order-line rows, 2015-2018), fetched from a public GitHub mirror with zero authentication.
• Why Superstore over the originally-planned Olist dataset: Olist requires a Kaggle account to download (an auth wall this project's "how to reproduce" shouldn't depend on) and it ships with no cost field, so margin would need a documented commission/freight-based assumption. Superstore has a native Profit column per line item, so Gross Margin is a measured fact, not an assumption: a more defensible base for a DAX measure library. Documented as a build decision, not a silent substitution.
• Format & storage: raw CSV cleaned via Power Query (M scripts, paste-ready), mirrored in Python for headless reproducibility; the analytical model is a star schema, native Excel Tables today, importable into Power Pivot as the Data Model.
• Grain: one row per order line item in the fact table, the source's native grain, verified unique.
• Known data quirks, found and handled: 1 null postal code (Burlington, VT, imputed as its real ZIP, 05401); 32 of 1,862 Product IDs map to more than one Product Name (resolved: most-frequent name wins, ties broken by most recent order date).

Methodology

The build follows CRISP-DM:

1. Business Understanding: frame the revenue / margin / trend questions above.
2. Data Understanding: profile the raw export: keys, nulls, duplicates, category consistency.
3. Data Preparation: clean and shape (Python today, mirrored 1:1 in Power Query M for the pending Windows pass).
4. Modeling: star schema plus a measure library (Excel-native formulas today, DAX text ready to paste).
5. Evaluation: every KPI computed two independent ways and cross-checked; every workbook formula re-evaluated headless and matched against that same evidence file.
6. Deployment: interactive dashboard and what-if model live today; Power Pivot/DAX/Tableau publish pending.

1. Power Query: ETL

Repeatable, refreshable ETL in M: type coercion, a Postal Code text-fix (Excel otherwise infers it as a number and silently drops leading zeros), text trims, and a documented dedup rule for the 32 inconsistent product names. Every step is written once as a decision in the Python build (which runs and is the source of every published number) and again as real M code (paste-ready, not yet executed, no Excel available to run it): a 1:1 port, not a rewrite.

2. Power Pivot: data model

The star schema shown under Architecture above, 5 tables, built today as native Excel Tables, ready for Add to Data Model once opened in real Excel. The relationships, a marked Date Table, and hidden FK columns are the exact remaining checklist for the Windows session.

3. DAX: measures

A reusable measure library instead of one-off cell formulas, covering revenue, gross margin, average ticket, and MoM / YoY growth (11 measures total):

Revenue        = SUM ( fact_order_items[sales] )
Profit         = SUM ( fact_order_items[profit] )
Orders         = DISTINCTCOUNT ( fact_order_items[order_id] )
Average Ticket = DIVIDE ( [Revenue], [Orders] )
Gross Margin % = DIVIDE ( [Profit], [Revenue] )

Revenue PY     = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( dim_date[date] ) )
Revenue YoY %  = DIVIDE ( [Revenue] - [Revenue PY], [Revenue PY] )

Because no Power Pivot model can exist in this build environment, the workbook also carries a live, working equivalent in plain Excel formulas (SUMIFS/SUMPRODUCT against a denormalized table), so it calculates correctly today, not only after the DAX is pasted in. Both were cross-checked to agree.

4. Interactive dashboard

Region and Category dropdown filters (Excel data validation) drive live KPI cards today, using a wildcard-aware distinct-count formula for Orders, a functional stand-in for slicers. Category/region breakdown charts and a 48-month revenue trend chart sit alongside. Real slicers and a timeline, connected to PivotCharts built off the DAX measures, are part of the pending Power Pivot pass.

5. What-if / scenario analysis

A 3-lever sensitivity model (% of deep-discount (>40%) lines "fixed", commission %, cost inflation %) comparing Base / Optimistic / Conservative / Custom scenarios. The discount-fix lever isn't an arbitrary slider: it's grounded in the measured relationship between discount depth and profit in this exact dataset. Real Scenario Manager cases, mirroring these same 3 named scenarios, are part of the pending Windows pass.

AI assist

The executive summary is AI-drafted from the validated measures and findings, explicitly marked pending the analyst's review, correction, and sign-off before it ships. Every number in the draft traces to the evidence file; the AI drafted prose and framing, not numbers. AI assists, human validates: the review step is part of the pending handoff, not skipped.

Challenges

These are the problems the build had to solve, named so the finished project can be judged on how it handled them.

• Power Pivot and DAX genuinely cannot be faked headless. They run on Microsoft's proprietary xVelocity engine, which only exists inside real Excel desktop: no library, no LibreOffice feature, writes that data model. Rather than build a fake version and call it "Power Pivot," the build was split honestly: everything automatable (ETL, star schema, a fully working Excel-formula measure/dashboard/what-if layer) was built and verified now; the genuinely Excel-desktop-only steps became a precise, spec'd handoff instead of a silent gap.
• Proving the Excel-formula layer actually works, without Excel to open it in. A SUMPRODUCT-based distinct-order-count formula, wildcard-aware for the dropdown filters' "All" state, was validated on a small synthetic dataset for both the "All" and specific-filter cases before scaling to the full 9,994 rows, then every formula in the finished workbook was re-evaluated with a headless formula engine and cross-checked against the same evidence file used for every published number: 40 of 40 checks passed.
• A real layout bug the verification pass caught. The dashboard's three charts were originally anchored to overlap in the same column, a bug that would only have surfaced by opening the file in real Excel. Re-anchoring them and re-running the full verification pass (still 40/40) is the kind of check that doesn't happen if "the script ran without errors" is treated as good enough.
• Grounding the what-if model in the data, not a guess. The discount-fix lever's target isn't invented: it's the dataset's own measured average profit for lightly-discounted lines, so "what if we capped deep discounts" is a projection off a real relationship, not an arbitrary slider.

Results

• $2,297,201 revenue, $286,397 profit, 12.47% margin, 5,009 orders, $458.61 average ticket.
• Furniture is a margin problem, not a revenue problem. Furniture ($741,999) and Technology ($836,154) generate similar revenue, but Furniture returns 2.49% margin vs. Technology's 17.40% and Office Supplies' 17.04%.
• Discounting past 20% turns profit negative. Average profit per line falls from +$66.90 (0% discount) to +$26.50 (1-20%), then negative at every band beyond that. Deep discounting (>40%, 933 lines, 9.3% of all lines) erased -$99,559 in profit, close to a third of total company profit, from under a tenth of its line items.
• Only 3 of 17 sub-categories lose money overall: Tables (-$17,725), Bookcases (-$3,473), Supplies (-$1,189). But Binders and Machines are quietly profitable overall only because their low/no-discount sales outweigh -$38,510 and -$30,119 respectively lost on their own deep-discount lines.
• West leads on margin (14.94%); Central lags on both revenue and margin (7.92%, the weakest region on both axes at once).
• Revenue grew every year 2015→2018: a 51.4% increase over the period, though 2016 was essentially flat (-2.8%) before two years of ~20%+ growth.

Recommendations from the analysis: cap discounts near 20% by default; re-examine Furniture's pricing/cost structure, not its volume; audit Binders' and Machines' discount practices specifically; and prioritize root-causing Central's underperformance before allocating it more spend. Full detail in the repository README.

Tech Stack