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 sellers drive revenue and margin — and how is the business trending month over month and 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: Olist Brazilian E-Commerce (Kaggle) — ~100k real orders across multiple related tables (orders, order items, products, customers, sellers). A Superstore dataset is a drop-in alternative when native profit/cost columns are preferred.
• Format & storage: source CSVs (or a PostgreSQL database) ingested via Power Query; the analytical model lives inside the Excel workbook as an in-memory Power Pivot model.
• Grain: one row per order line item in the fact table.
• Known limitations: Olist ships no cost field, so gross margin uses a documented commission / freight-based cost assumption — or switch to Superstore for native cost and profit. The dataset is historical and fixed: no live refresh.

Methodology

The build follows CRISP-DM:

1. Business Understanding — frame the revenue / margin / trend questions above.
2. Data Understanding — profile the Olist tables, keys, and quality issues.
3. Data Preparation — clean and shape with Power Query (M).
4. Modeling — star schema in Power Pivot plus DAX measures.
5. Evaluation — validate measures against manual checks; sanity-check totals.
6. Deployment — interactive dashboard plus executive summary.

1. Power Query — ETL

Repeatable, refreshable ETL in M: type coercion, deduplication, null handling, key normalization, category-name translation, and merging the Olist tables into clean fact and dimension queries. Every step is captured in the query and re-runs on Refresh All — no manual copy-paste.

2. Power Pivot — data model

The star schema shown under Architecture above: one fact table at line-item grain, conformed dimensions, explicit one-to-many relationships, and a dedicated date dimension for time intelligence.

3. DAX — measures

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

Revenue        = SUMX ( fact_order_items, fact_order_items[price] * fact_order_items[quantity] )
Orders         = DISTINCTCOUNT ( fact_order_items[order_id] )
Average Ticket = DIVIDE ( [Revenue], [Orders] )
Gross Margin % = DIVIDE ( [Revenue] - [Cost], [Revenue] )

Revenue PM     = CALCULATE ( [Revenue], DATEADD ( dim_date[date], -1, MONTH ) )
Revenue MoM %  = DIVIDE ( [Revenue] - [Revenue PM], [Revenue PM] )

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

4. Interactive dashboard

A single-page dashboard driven by slicers (category, region, seller) and a timeline for date filtering, with KPI cards, a trend line, and category/region breakdowns. One click re-slices the whole page.

5. What-if / scenario analysis

Scenario analysis with Excel Data Tables and Scenario Manager plus what-if parameters: model how a change in discount rate, freight, or commission moves revenue and margin — so leadership can pressure-test a decision before making it.

AI assist

The executive summary is drafted with an LLM from the validated measures and findings, then reviewed, corrected, and approved by the analyst before it ships. AI accelerates the writing; the human owns the numbers and the conclusions. AI assists, human validates.

Challenges

These are the problems the build has to solve — named up front so the finished project can be judged on how it handled them.

• Defining the margin assumption without a cost column. Olist ships no cost field, so Gross Margin % is only as credible as the commission- and freight-based cost proxy behind it. The assumption has to be documented, exposed as a what-if parameter rather than buried in a hard-coded formula, and labeled everywhere a margin figure appears — otherwise the workbook produces a precise-looking number backed by nothing. Switching to Superstore trades realism for native cost and profit; that trade-off is itself part of the write-up.
• Holding a single fact grain against fan-out. The fact table is one row per order line item, but freight, payments, and reviews live at different grains in the Olist schema. Relating them carelessly silently multiplies revenue — the totals still look plausible, which is what makes it dangerous. Relationship design and the measure library both have to respect the declared grain.
• Time intelligence needs a real date dimension. DATEADD and SAMEPERIODLASTYEAR return wrong or blank results against a fact date column with gaps, so a contiguous, marked date table is a prerequisite rather than a nicety. Olist's thin early months compound this: MoM and YoY over partial periods produce dramatic percentages that mean nothing, and need to be excluded or flagged.
• Keeping the workbook reproducible and reviewable. Refresh All has to re-run the entire ETL with zero manual steps, or the project is a spreadsheet again. A binary .xlsx also does not diff in Git, so the change history has to be carried another way — Git LFS, or an exported PDF snapshot alongside the file.

Results

Planned insight lines, to be replaced with real figures:

• Which category concentrates margin versus which one drives volume.
• Regions trending down despite stable volume — discount or freight pressure.
• The highest-revenue segment versus the most profitable one.

The provisional recommendations in the repository README — reallocating spend toward high-margin, under-growing categories; revisiting discount and freight policy in declining regions; prioritizing the most profitable segments rather than only the highest-volume ones — are framing, and will be finalized from the analysis.

Tech Stack