Excel Capstones — the Analyst's Stack

Learning objectives

By the end you can:

1. Explain what the five capstone builds collectively prove about an analyst, and why "knowing functions" and "owning the stack" are different competencies.
2. Rebuild a 3-statement model (income statement → balance sheet → cash flow statement) to professional standard, keyboard-only, with genuine IS→BS→CFS linkage and a balance check that equals zero by construction rather than by a plug.
3. Roll retained earnings forward correctly (opening RE + net income − dividends) and build the indirect-method cash flow statement that reconciles net income to the change in cash.
4. Build a DCF with an explicit forecast plus a Gordon-growth terminal value, drive it with a two-variable data table over WACC × terminal growth, and assemble the results into a football field alongside a comparable-companies range.
5. State and defend the consistency rule for a football field: every method must value the same cash flows, capital structure, and date, or the bars are not comparable.
6. Build a Power Query portfolio tracker that references broker columns by header, never by position, so a re-ordered or re-named export cannot silently corrupt the model, and compute market value, P&L, and weights on refresh.
7. Design a DAX macro-dashboard: a star schema with a proper Date table, and KPI measures (not calculated columns) such as YoY change and a policy-rate spread, using CALCULATE and SAMEPERIODLASTYEAR.
8. Write a VBA one-click refresh-and-print routine that refreshes queries and pivots, forces a full recalculation, sets the print area and fit-to-page, and exports a dated PDF.
9. Diagnose the five signature failures of analyst workbooks, the plugged balance sheet, the inconsistent football field, the position-indexed import, the Date-table-less DAX model, and the print macro that forgets its print area, and build so none of them can occur.

Prerequisites & connections

Builds on, essentially the entire track. EX0.02 (references, names, LET) and EX1.02 (lookups) are the wiring of every model here; EX1.03 (dynamic arrays and LAMBDA) gives you the spilling report blocks the dashboards lean on; EX2.02 (What-If & Optimization) is where you first met the two-variable Data Table that Capstone 2 turns into a football field; EX3.01–3.02 (PivotTables, charts, dashboard craft) supply the presentation layer; EX4.01–4.02 (Power Query) are the whole engine of Capstone 3; EX5.01–5.02 (Power Pivot & DAX) are Capstone 4; EX6.01–6.04 (VBA) are Capstone 5; and EX7.01 (Professional Modeling Standards) is the grammar every build is graded against, the inputs/calcs/outputs separation, the colour code (blue inputs, black formulas, green cross-sheet links), the checks-and-flags row, keyboard-only speed, and circularity discipline. You cannot pass this node without EX7.01 reflexes; this is where they are examined.

Ties the analyst path together. These five builds are the Excel-native rebuilds of analyst-track deliverables: Capstone 1 rebuilds M3.08's three-statement model; Capstone 2 is the Excel realisation of M3.09's DCF and football field; Capstone 4 renders M7.07's macro-indicator dashboard as DAX measures. When a finance module says "and now build it in Excel," this is the module that taught you how.

Feeds forward. Nothing in the Excel track comes after this, it is the finale, the Black Belt. What it feeds is your working life: the RegionMatrix-and-tiers pricing spine, the DCF sensitivity grid, the refreshable tracker, the measure library, and the one-click macro become templates you carry into every real engagement. On the analyst path they underwrite the case labs and the capstone deliverables, where the grader assumes you can produce a balanced model and a defensible valuation without hand-holding.

The Analyst's Stack: what the five builds prove

Think of an analyst's output as a pipeline: raw data → structured model → valuation/insight → a refreshable, printable deliverable a decision-maker reads. Each of the five capstones is a section of that pipeline, and each exercises a distinct competency that a real job will test independently.