Excel Capstone Project

Table 1: Sales_Raw (fact table — 72 rows, 3 years × 4 regions × 6 categories)
  Columns: OrderID, OrderDate, Region, Category, Product,
           UnitsSold, UnitPrice, UnitCost, TargetUnits, TargetRevenue

  Sample rows:
  OrderID  OrderDate    Region  Category     Product          UnitsSold  UnitPrice  UnitCost  TargetUnits  TargetRevenue
  1001     01/01/2023   North   Electronics  Smart Speaker        42       £89.99    £44.00       40          £3,600
  1002     01/01/2023   South   Clothing     Winter Jacket        28       £64.99    £22.00       30          £1,950
  1003     01/01/2023   East    Homeware     Coffee Maker         35       £49.99    £18.50       35          £1,750
  1004     01/01/2023   West    Grocery      Organic Bundle       110      £18.99     £9.20      100          £1,900
  1005     01/02/2023   North   Beauty       Skincare Set         22       £34.99    £12.00       25          £875
  1006     01/02/2023   South   Sports       Yoga Mat Set         18       £44.99    £16.50       20          £900
  ...continues for 3 years (2023, 2024, 2025)

Table 2: DimDate (date dimension — same structure as Lessons 47–49)
  Columns: Date, Year, Month, MonthNum, Quarter, MonthYear, FY_Quarter
  FY_Quarter: NorthBridge FY runs Apr–Mar. Q1 = Apr–Jun, Q2 = Jul–Sep, etc.

Table 3: DimProduct (product lookup)
  Columns: Product, Category, SubCategory, LaunchYear, IsActive, SupplierID

Table 4: Targets (monthly targets by region and category)
  Columns: Year, Month, Region, Category, TargetRevenue, TargetUnits, TargetMargin

-- Sales_Raw query — required steps --

1. Load from Excel Table (or CSV if practising with external files)

2. Set data types explicitly:
   OrderDate  → Date
   UnitsSold  → Whole Number
   UnitPrice  → Currency (£)
   UnitCost   → Currency (£)

3. Add calculated columns in Power Query (NOT in Power Pivot):
   Revenue = UnitsSold × UnitPrice
   Cost    = UnitsSold × UnitCost
   -- Keep unit-level columns for DAX aggregation

4. Add a Year column: Date.Year([OrderDate])
   Add a MonthNum column: Date.Month([OrderDate])
   -- Used for relationship to DimDate

5. Filter: remove any rows where UnitsSold = 0 or null
   -- These are cancelled orders — exclude from analysis

6. Rename columns to PascalCase for consistency:
   "OrderID", "OrderDate", "Region", "Category", "Product",
   "UnitsSold", "UnitPrice", "UnitCost", "Revenue", "Cost",
   "TargetUnits", "TargetRevenue", "Year", "MonthNum"

-- Targets query — required steps --

1. Unpivot if targets arrive as a wide table (one column per month)
   Select identifier columns → Transform → Unpivot Other Columns
   Rename "Attribute" → "Month", "Value" → varies by metric

2. Add MonthNum: look up month number from a reference table
   or use: = List.PositionOf({"January","February",...}, [Month]) + 1

3. Merge with DimDate on Year + MonthNum to add a proper Date column
   -- Required for time intelligence measures to work correctly

-- === CORE SALES MEASURES === --

Total Revenue =
  SUM(Sales_Raw[Revenue])

Total Cost =
  SUM(Sales_Raw[Cost])

Gross Profit =
  [Total Revenue] - [Total Cost]

Gross Margin % =
  DIVIDE([Gross Profit], [Total Revenue], 0)

Units Sold =
  SUM(Sales_Raw[UnitsSold])

Avg Order Value =
  DIVIDE([Total Revenue], DISTINCTCOUNT(Sales_Raw[OrderID]), 0)


-- === TARGET & VARIANCE MEASURES === --

Target Revenue =
  SUM(Targets[TargetRevenue])

Revenue vs Target =
  [Total Revenue] - [Target Revenue]

Revenue vs Target % =
  DIVIDE([Revenue vs Target], [Target Revenue], 0)

Target Units =
  SUM(Targets[TargetUnits])

Units vs Target =
  [Units Sold] - [Target Units]

Target Margin =
  SUM(Targets[TargetMargin])

Margin vs Target =
  [Gross Margin %] - [Target Margin]


-- === TIME INTELLIGENCE MEASURES === --

YTD Revenue =
  CALCULATE(
    [Total Revenue],
    DATESYTD(DimDate[Date])
  )

Prior Year Revenue =
  CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DimDate[Date])
  )

YoY Growth % =
  DIVIDE(
    [Total Revenue] - [Prior Year Revenue],
    [Prior Year Revenue],
    0
  )

YTD Prior Year =
  CALCULATE(
    [Total Revenue],
    DATESYTD(SAMEPERIODLASTYEAR(DimDate[Date]))
  )

Rolling 3M Revenue =
  CALCULATE(
    [Total Revenue],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -3, MONTH)
  )


-- === INVENTORY & EFFICIENCY MEASURES === --

Revenue per Unit =
  DIVIDE([Total Revenue], [Units Sold], 0)

Cost per Unit =
  DIVIDE([Total Cost], [Units Sold], 0)

Contribution Margin =
  [Revenue per Unit] - [Cost per Unit]

Category Share of Revenue =
  DIVIDE(
    [Total Revenue],
    CALCULATE([Total Revenue], ALL(Sales_Raw[Category])),
    0
  )

Region Share of Revenue =
  DIVIDE(
    [Total Revenue],
    CALCULATE([Total Revenue], ALL(Sales_Raw[Region])),
    0
  )

Regional Scorecard — one row per region, six KPI columns

PivotTable setup:
  Rows:   Region (from Sales_Raw)
  Values: Total Revenue · Revenue vs Target % · Gross Margin % ·
          YoY Growth % · Units Sold · Units vs Target

Layout: Tabular · subtotals off · grand total row at bottom

Conditional formatting rules (apply column by column):
  Revenue vs Target %  → 3-colour scale: red (below 0%) / yellow (0%) / green (above 5%)
  Gross Margin %       → Icon set: green circle (≥40%) / yellow triangle (35–40%) / red diamond (<35%)
  YoY Growth %         → Data bars, green, midpoint at 0%
  Units vs Target      → Red if negative (below target), green if positive

Sort: Total Revenue descending so best-performing region appears first

GETPIVOTDATA for Board summary card (separate cell block above the scorecard):
  Top region:          =GETPIVOTDATA("Total Revenue", PT_Scorecard, "Region", INDEX(regions,MATCH(MAX(revenues),revenues,0)))
  Fastest growing:     =GETPIVOTDATA("YoY Growth %",  PT_Scorecard, "Region", [region with max YoY])
  Below target count:  =COUNTIF(variance_range, "<0")   -- number of regions missing revenue target

Sheet order (tab colour coded):
  [Green]  Raw_Sales        -- Power Query output, do not edit
  [Green]  Raw_Targets      -- Power Query output, do not edit
  [Green]  DimDate          -- date dimension, do not edit
  [Green]  DimProduct       -- product lookup, do not edit
  [Grey]   PT_Helper        -- all PivotTables, hidden before sharing
  [Blue]   Dashboard_Sales  -- Dashboard 1
  [Blue]   Dashboard_Margin -- Dashboard 2
  [Blue]   Dashboard_Score  -- Dashboard 3
  [Yellow] Notes            -- data dictionary, refresh instructions

Pre-delivery checklist:
  ☐ Power Query loads without errors on a clean open (Data → Refresh All)
  ☐ All slicers connected to all relevant PivotTables via Report Connections
  ☐ Conditional formatting applies correctly for each sign convention
       Revenue vs Target: positive = green, negative = red
       Cost vs Target:    positive = red,   negative = green
       Margin vs Target:  positive = green, negative = red
  ☐ KPI cards use GETPIVOTDATA (not hardcoded values)
  ☐ No formula errors (#REF!, #VALUE!, #DIV/0!) visible anywhere
  ☐ PT_Helper sheet hidden (right-click tab → Hide)
  ☐ Raw sheets protected (Review → Protect Sheet, allow no changes)
  ☐ Dashboard sheets protected (allow only slicer interaction)
  ☐ File saved as .xlsm if macros used, .xlsx if not
  ☐ File size under 5MB (check: File → Info → bottom of panel)
  ☐ Open on a colleague's machine and confirm slicers respond correctly