Excel Capstones: the Analyst's Stack

Excel Mastery Track · ⬛ Black belt · CROWN capstone. Excel Black Belt (90% gate) · ~10–14 hours · Microsoft 365 on Windows assumed

Every module before this one taught a single organ. This one asks you to build the body. Across the track you learned to enter data without lying to yourself (EX0.01), to make a formula's references bend correctly under a copy (EX0.02), to fetch a fact by key instead of typing it twice (EX1.02), to spill a whole report from one formula (EX1.03), to shape and validate inputs (EX2.01), to run a two-variable sensitivity (EX2.02), to summarise a hundred thousand rows in a PivotTable (EX3.01), to import and clean a messy CSV without touching it by hand (EX4.01–4.02), to measure over a data model in DAX (EX5.01–5.02), and to automate the boring parts in VBA (EX6.01–6.04). Each was a rung. This node is the wall you climb with all of them at once.

An analyst is not someone who knows Excel functions. An analyst is someone who can take a raw pile of numbers and a question a decision-maker actually has, what is this company worth, is my portfolio winning, is inflation eating my returns, does this model even balance, and return a defensible answer that another professional can open, audit, and trust. That capability is not one skill; it is a stack, and this module makes you prove you own the whole thing by building five integrated deliverables that together constitute the working kit of a modern financial analyst. Passing this node is the Excel Black Belt. There is no thirteenth function to memorise here, only the discipline to make twelve modules' worth of skill converge into workbooks that are correct, fast to build, and honest under pressure.

We build five things, and each one is a capstone in its own right: a 3-statement model rebuilt to professional standard, keyboard-only, with a balance check that reads exactly zero; a DCF with a two-variable data table feeding a football field of value; a Power Query portfolio tracker that survives your broker reordering their export columns; a DAX macro-dashboard of KPI measures over a real data model with a Date table; and a VBA one-click refresh-and-print that turns a morning of clicking into a single button. First principles first, then the build, then the two worked examples you will recompute by hand, then a practice set and a capstone mini-project. Everything assumes Microsoft 365 on Windows, because that is where the full stack (dynamic arrays, Power Query, Power Pivot/DAX, and real VBA) actually lives.

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.

Core teaching content

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.

• The 3-statement model proves you understand accounting as a closed system, that the three statements are not three reports but three views of one set of transactions, wired so tightly that if the balance sheet does not balance, you have a real error, not a formatting nuisance.
• The DCF and football field prove you can turn a model into a valuation with honest uncertainty, not a single false-precision number but a defended range, and the humility to show how sensitive that range is to two assumptions everyone argues about (the discount rate and the growth rate).
• The Power Query tracker proves you can build a pipeline that survives contact with the real world (messy exports, changing column orders, new rows every month) without a human re-doing the work each time.
• The DAX dashboard proves you can model relationships and time, a star schema and a Date table let you ask "versus last year" and "year to date" of any measure without rewriting formulas.
• The VBA macro proves you can remove yourself from the loop, that a report which used to take twenty clicks now takes one, reliably, the same way every time.

The through-line, and the thing the Black Belt actually certifies, is trust. A workbook another professional can open and believe is worth more than a cleverer one they cannot. Every discipline below (the zero balance check, the consistent football field, the by-header import, the Date table, the print-area macro) exists to make your work trustable by someone who was not in the room when you built it. That is the whole game.

Capstone 1: the 3-statement model to pro standard

First principles. The income statement, balance sheet, and cash flow statement describe the same business through one accounting period from three angles: profitability (IS), position (BS), and liquidity (CFS). They are joined by iron links. Net income from the IS flows into retained earnings on the BS and is the top line of the indirect CFS. Depreciation reduces IS profit but is added back on the CFS because it moved no cash, and it reduces net PP&E on the BS. Working-capital movements (receivables, inventory, payables) are cash effects on the CFS and balances on the BS. Capex is a CFS investing outflow and a BS asset addition. Dividends and financing are CFS outflows/inflows and BS equity/debt changes. The closing cash the CFS computes is the same cash that sits on the BS. Wire all of this correctly and the balance sheet balances because the accounting is right, not because you forced it.

The professional layout (EX7.01, examined here). Inputs in blue, isolated on their own block or sheet; calculations in black; cross-sheet links in green; no hardcoded number ever buried inside a formula. A checks row runs across the model with a single headline cell (the balance check) defined as:

Balance check  =  Total assets − Total liabilities − Total equity

This cell must read 0 in every period. It is not decoration; it is the smoke alarm. The cardinal sin of amateur modeling is to make the sheet "balance" by typing a plug (a hand-entered number, or =Total assets − known liabilities shoved into equity) so the check reads zero while hiding an error. A real model balances because cash is derived by the cash flow statement and flows to the balance sheet; the check then confirms the wiring, and any non-zero value is a bug you must hunt, never paper over.

The indirect cash flow statement is the reconciling bridge, and building it is the heart of the capstone:

Cash from operations (CFO)
   Net income
 + Depreciation & amortisation        (non-cash, added back)
 − Increase in receivables            (cash tied up)
 − Increase in inventory              (cash tied up)
 + Increase in payables               (cash deferred)
Cash from investing (CFI)
 − Capital expenditure
Cash from financing (CFF)
 ± Change in debt
 − Dividends paid
Net change in cash = CFO + CFI + CFF
Closing cash = Opening cash + Net change in cash   → posts to the BS

Keyboard-only. A Black-Belt modeller barely touches the mouse: F2 to edit, F4 to cycle reference locking, Ctrl+[ to trace a precedent and F5+Enter to jump back, Alt+= for a SUM, Ctrl+Shift+↓ to select a column to its end, Ctrl+D/Ctrl+R to fill down/right, Ctrl+'? no, you build the first period's column of formulas once, then fill right across the forecast so every year is identical logic. Worked example 1 constructs a complete one-year linkage and proves the balance check is exactly zero.

Capstone 2: DCF, data tables, and the football field

First principles. A discounted-cash-flow valuation says a business is worth the present value of the cash it will generate. Build it in two pieces: an explicit forecast of unlevered free cash flow for a handful of years, and a terminal value capturing everything after, collapsed to a single figure by the Gordon growth formula. Discount both back at the weighted average cost of capital (WACC):

EV  =  Σ  FCFₜ / (1+WACC)ᵗ      +      TV / (1+WACC)ᴺ
        t=1..N                          (present value of the terminal value)

where   TV  =  FCF_N × (1 + g) / (WACC − g)          (Gordon growth, g = perpetual growth)

The result is an enterprise value (EV). To reach equity value, bridge by net debt: Equity value = EV − net debt. Two things about this formula terrify honest analysts, and both are assumptions, not facts: WACC (a small change moves the answer a lot, because you divide by WACC − g) and g (the perpetual growth rate). So a professional never reports one number, they report how the number moves as those two assumptions move.

The two-variable data table (EX2.02, applied). Excel's Data Table (Data ▸ What-If Analysis ▸ Data Table) recomputes one output cell across a grid of two input values. Put the EV formula's cell in the top-left corner, list candidate WACC values across the top row, list candidate terminal-growth values down the left column, point Row input cell at the WACC assumption and Column input cell at the growth assumption, and Excel fills the whole matrix (one EV per (WACC, g) pair) in a single stroke. This is the exact row/column machinery of a 2-D lookup (EX1.02), now automated over a model.

The football field. Take the lowest and highest EV from that grid (the low corner is high WACC + low growth, the high corner is low WACC + high growth) and you have the DCF's value band. A football field chart stacks that band against ranges from other methods (comparable-companies multiples (EV/EBITDA, EV/Revenue) applied at low/median/high peer multiples, precedent transactions, a 52-week trading range) as horizontal bars, so a decision-maker sees at a glance where the methods agree and disagree.

The consistency rule (do not skip this). A football field is only honest if every bar values the same thing on the same basis: the same free cash flows, the same capital structure, the same valuation date, and the same level (all at enterprise value, or all at equity value, never mixed). The classic amateur error is a DCF bar built on unlevered FCF sitting next to a comps bar built on a levered P/E multiple, or a DCF at today's date next to comps at a stale one; the bars look comparable and are not. Worked example 2 builds the DCF grid and states the band, and the practice set reconciles it against a comps range.

Capstone 3: the Power Query portfolio tracker

First principles. A portfolio tracker's job is to answer, on demand, "what is my portfolio worth, what has it made, and where is the risk concentrated?" The raw material is a broker CSV (a holdings export) which is messy, formatted for humans, and, crucially, not under your control: the broker can rename Symbol to Tradingsymbol, add a column, or reorder them in the next app update. A tracker built by hand, or by referencing "column D," breaks silently the first time that happens. Power Query (EX4.01–4.02) is the fix: an ingestion pipeline that cleans the export into a tidy holdings table and refreshes on a button.

The one rule that makes it robust: reference columns by header, never by position. Every Power Query step should name the columns it touches: Table.SelectColumns(Source, {"Symbol", "Qty", "AvgCost", "LTP"}), Table.TransformColumnTypes(..., {{"Qty", Int64.Type}, {"LTP", type number}}), so that if the broker reorders columns, the query still finds LTP by its name. The moment you rely on Column4 or a hardcoded index, you have built the position-dependent fragility Power Query exists to eliminate. (If the broker renames a header, you fix it in exactly one place, a Table.RenameColumns step at the top, and everything downstream keeps working.)

The pipeline, in shape:

let
    Source        = Csv.Document(File.Contents("broker_holdings.csv"),
                                  [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Promoted      = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Kept          = Table.SelectColumns(Promoted, {"Symbol","Qty","AvgCost","LTP"}),  // by NAME
    Typed         = Table.TransformColumnTypes(Kept,
                        {{"Qty", Int64.Type},{"AvgCost", type number},{"LTP", type number}}),
    Invested      = Table.AddColumn(Typed, "Invested", each [Qty]*[AvgCost], type number),
    MarketValue   = Table.AddColumn(Invested, "MarketValue", each [Qty]*[LTP], type number),
    PnL           = Table.AddColumn(MarketValue, "PnL", each [MarketValue]-[Invested], type number)
in
    PnL

Load that to a table, and the dashboard is built on top with the tools you already own: a total row (=SUM(MarketValue)), a weight column (=MarketValue / SUM(MarketValue)), conditional formatting for gainers vs. losers, and a PivotChart of holdings by weight. Add next month's trades to the CSV, hit Data ▸ Refresh All, and every figure updates with no hand editing. Worked and practised below on a five-stock Indian portfolio whose total market value we recompute exactly.

Capstone 4: the DAX macro-dashboard

First principles. A dashboard that must slice the same measures many ways (by month, by quarter, year-over-year, year-to-date) should not be a mesh of SUMIFS. It should be a data model: fact tables of observations, dimension tables of the things you slice by, related in a star schema, with logic written once as measures in DAX. This is Power Pivot (EX5.01–5.02). Capstone 4 renders the analyst path's macro-indicator dashboard (M7.07) (inflation, policy rates, yields, and their spreads) as a set of reusable DAX measures.

The non-negotiable: a proper Date table. Time intelligence in DAX (SAMEPERIODLASTYEAR, TOTALYTD, DATESINPERIOD) requires a dedicated, contiguous Date table marked via Table Tools ▸ Mark as Date Table, related to your fact table's date column. Without it, "versus last year" either errors or returns nonsense. Build the Date table once (a CALENDAR range covering your data, with Year/Quarter/Month columns) and every time-based measure just works.

Measures, not calculated columns. A measure is evaluated in the filter context of the cell that displays it, so [CPI YoY %] returns the right answer whether the cell is filtered to June-2025 or to all of 2025. A calculated column is computed row-by-row at refresh and cannot respond to slicers the same way. KPI logic belongs in measures. A minimal set:

Total FCF        := SUM ( Fact_Cashflow[FCF] )

CPI (latest)     := VAR d = MAX ( 'Date'[Date] )
                    RETURN CALCULATE ( AVERAGE ( Fact_Macro[CPI] ), 'Date'[Date] = d )

CPI YoY %        := VAR cur   = [CPI (latest)]
                    VAR prior = CALCULATE ( [CPI (latest)], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
                    RETURN DIVIDE ( cur - prior, prior )

Real Policy Rate := [Repo Rate] - [CPI YoY %]

CALCULATE is the engine, it modifies filter context; SAMEPERIODLASTYEAR shifts the Date table back one year; DIVIDE returns blank rather than an error on divide-by-zero. The headline KPI on the dashboard, the real policy rate (policy rate minus inflation), tells a decision-maker whether money is genuinely cheap or dear. Worked below with a CPI series where the year-over-year figure comes out to a clean 6.00% and the real policy rate to 0.50%.

Capstone 5: VBA one-click refresh-and-print

First principles. A living workbook (queries pulling fresh data, pivots to update, a dashboard to distribute) needs the same sequence of actions performed identically every time it is run. That is precisely what a computer should do and a human should not. VBA (EX6.01–6.04) automates the sequence: refresh everything, recalculate fully, set the page up so it prints as one clean page, export a dated PDF. One button, zero missed steps.

The routine, and the gotchas it guards against:

Sub RefreshAndPrint()
    Dim ws As Worksheet
    Application.ScreenUpdating = False

    ' 1) Refresh every query and pivot, then FORCE a full recalculation.
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone   ' wait for Power Query to finish
    Application.CalculateFull                    ' don't print stale numbers

    ' 2) Set a real print area and FIT — the step amateurs forget.
    Set ws = ThisWorkbook.Worksheets("Dashboard")
    With ws.PageSetup
        .PrintArea = "A1:H45"          ' never leave this to chance
        .Orientation = xlLandscape
        .Zoom = False                  ' Zoom MUST be False for FitTo to take effect
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .CenterHorizontally = True
    End With

    ' 3) Export a dated PDF next to the workbook.
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Dashboard_" & Format(Date, "yyyy-mm-dd") & ".pdf", _
        Quality:=xlQualityStandard, OpenAfterPublish:=False

    Application.ScreenUpdating = True
    MsgBox "Refreshed, recalculated, and printed to PDF.", vbInformation
End Sub

Three professional habits are baked in. Refresh before print, never distribute a PDF built on yesterday's data; RefreshAll then a forced CalculateFull guarantees the printed numbers are the current ones. Set the print area and fit explicitly, the single most common macro-print failure is a report that spills across nine ragged pages because nobody set PrintArea and FitToPagesWide; note the subtle trap that Zoom must be False or the FitTo settings are ignored. Wrap in ScreenUpdating = False so the macro runs fast and doesn't flicker. Assign the sub to a shape or a Quick Access Toolbar button and the twenty-click Monday report becomes one click.

Common mistakes & how experts think differently

1. The plugged balance sheet, the signature sin of this module. The model "balances" because someone typed a number into equity, or set cash to Total assets − everything else, forcing the check to zero while burying the actual error. The expert derives cash through the cash flow statement, posts that same closing cash to the balance sheet, and treats the balance check as an independent confirmation. A plug makes the check lie; a real linkage makes it tell the truth. If your balance sheet balances but you cannot point to the CFS cell that produced its cash, you have a plug and you have a bug.
2. A football field with inconsistent assumptions across methods. Putting an unlevered-FCF DCF bar (an enterprise value) next to a levered P/E comps bar (an equity value), or methods valued on different dates, different share counts, or different capital structures. The bars look comparable and are not, the chart becomes a persuasive lie. The expert fixes one level (all EV, then bridge to equity once at the end), one valuation date, and one set of cash flows, and only then draws bars.
3. A portfolio tracker that breaks when the broker changes column order. Referencing Column4 or a hardcoded index means the day the broker adds a column or reorders the export, LTP silently becomes AvgCost and every valuation is wrong with no error. The expert selects and types columns by header name in Power Query, and isolates any rename into a single top-of-query step, so the pipeline is immune to reordering and cheap to repair on a rename.
4. A DAX dashboard with no Date table (or a fake one). Trying to compute year-over-year off the fact table's raw date column, or off a Date table that was never marked as one, so SAMEPERIODLASTYEAR returns blanks or errors. The expert builds a dedicated, contiguous Date table, marks it as a Date table, relates it to every fact, and never writes a time-intelligence measure without it.
5. A print macro that forgets the print area and fit. The macro refreshes and exports, but nobody set PrintArea or FitToPagesWide, so the PDF is nine ragged pages, or Zoom was left non-False so the FitTo was silently ignored. The expert sets the print area explicitly, sets Zoom = False then FitToPagesWide = 1, and previews once; a deliverable that prints ugly did not get finished.
6. Calculated columns where measures belong (DAX). Writing YoY or a ratio as a calculated column freezes it at refresh time and it cannot respond to a slicer the way a measure evaluated in filter context does. The expert writes KPI logic as measures, reserving calculated columns for genuinely row-level attributes used to slice.
7. Mouse-driven modeling under time pressure. Reaching for the mouse to select ranges, sum columns, and trace precedents costs minutes per model and breaks flow. The expert models keyboard-only (F2, F4, Alt+=, Ctrl+[, Ctrl+Shift+arrow, Ctrl+D/R) building one period's logic once and filling it across, which is faster and less error-prone because every period is identical.

The unifying habit: an expert builds so that errors cannot hide. The balance check is independent, the football-field bars are forced onto one basis, the import is position-proof, the Date table is real, the print area is explicit, and the logic lives in one place that fills or refreshes rather than in a hundred hand-touched cells. Trust is engineered, not hoped for.

Worked examples

WE1: India (₹): a compact 3-statement linkage that balances to zero

The task. An Indian manufacturer closes FY0 with the balance sheet below (all figures ₹ crore). Given a set of FY1 operating assumptions, build the one-year three-statement linkage: compute net income, roll retained earnings forward, build the indirect cash flow statement, assemble the closing balance sheet, and show the balance check equals exactly zero. Every number below is recomputed and verified in Python.

Step 1, the opening balance sheet (end of FY0), ₹ crore.

It balances (550 = 550), as any opening position must.

Step 2: FY1 assumptions and the income statement. Revenue ₹800; COGS ₹480; SG&A (ex-depreciation) ₹140; depreciation ₹50; debt carries 10% interest; tax rate 25%; the firm pays out 40% of net income as dividends.

Gross profit  = 800 − 480                         = 320
EBIT          = 320 − 140 − 50                     = 130
Interest      = 10% × 200 (opening debt)           =  20
EBT           = 130 − 20                            = 110
Tax           = 25% × 110                           =  27.5
Net income    = 110 − 27.5                          =  82.5

Net income = ₹82.5 crore.

Step 3: retained earnings roll-forward. Dividends = 40% × 82.5 = ₹33.0.

Retained earnings (close) = opening RE + net income − dividends
                          = 130 + 82.5 − 33.0        = 179.5

Closing retained earnings = ₹179.5 crore.

Step 4, the indirect cash flow statement. FY1 working-capital and investing assumptions: receivables rise to 150 (Δ +30), inventory to 110 (Δ +20), payables to 85 (Δ +15); capex ₹80; depreciation ₹50; debt unchanged; no new equity.

CFO = NI + Dep − ΔAR − ΔInv + ΔAP
    = 82.5 + 50 − 30 − 20 + 15                       =  97.5
CFI = −capex = −80                                   = −80
CFF = −dividends = −33.0                              = −33.0
Net change in cash = 97.5 − 80 − 33.0                = −15.5
Closing cash = 40 + (−15.5)                           =  24.5
Net PP&E (close) = 300 + 80 − 50                      =  330

Step 5, the closing balance sheet and the check.

Balance check  =  Total assets − Total liabilities − Total equity
              = 614.5 − (85 + 200) − (150 + 179.5)
              = 614.5 − 285 − 329.5                   = 0.00   ✓

The balance check is exactly zero, and note why. We never typed a plug. Cash (24.5) came out of the cash flow statement; retained earnings (179.5) came from the roll-forward; net PP&E (330) came from the capex-less-depreciation link. The check confirms the wiring is right. Change any assumption, say, lift capex to 100, and cash, total assets, and the check all recompute; if the check ever reads non-zero, you have a genuine linkage error to hunt, not a cosmetic gap to plug. (As a bonus read, interest coverage = EBIT ÷ interest = 130 ÷ 20 = 6.5×, comfortable.)

WE2: US ($): a DCF football field from a two-variable data table

The task. A US target has forecast unlevered free cash flow (US$ million) of 120, 135, 150, 165, 180 for years 1–5. Value it by DCF with a Gordon-growth terminal value, then run a two-variable data table over WACC ∈ {9%, 10%, 11%} and terminal growth g ∈ {2%, 3%, 4%}, and state the enterprise-value football-field band (low / mid / high). The base case is WACC 10%, g 3%. Every figure is recomputed and verified in Python.

Step 1, the base-case DCF (WACC 10%, g 3%). Discount each FCF, then the terminal value.

PV of FCF₁…₅ at 10%:
  120/1.10   = 109.09
  135/1.10²  = 111.57
  150/1.10³  = 112.70
  165/1.10⁴  = 112.70
  180/1.10⁵  = 111.77
  Σ PV(FCF)                                   = 557.82

Terminal value at end of year 5:
  TV = FCF₅ × (1+g)/(WACC−g) = 180 × 1.03 / (0.10 − 0.03) = 185.4 / 0.07 = 2648.57
  PV(TV) = 2648.57 / 1.10⁵ = 2648.57 / 1.61051 = 1644.55

Enterprise value (base) = 557.82 + 1644.55        = 2202.38  ≈ $2,202m

Notice the terminal value is roughly three-quarters of the total EV, which is why the DCF is so sensitive to WACC and g, and why we never stop at this single number.

Step 2, the two-variable data table. Put the EV formula in the grid's top-left corner, WACC across the top, g down the side; Row input cell → the WACC assumption, Column input cell → the growth assumption. Excel fills all nine EVs:

Step 3, read the band. The low corner is the most conservative pair, highest WACC, lowest growth (11%, 2%): EV ≈ $1,753.5m. The high corner is the most optimistic, lowest WACC, highest growth (9%, 4%): EV ≈ $3,006.8m. The mid is the base case (10%, 3%): EV ≈ $2,202.4m.

DCF football-field band (enterprise value):
   low  ≈ $1,754m   (WACC 11%, g 2%)
   mid  ≈ $2,202m   (WACC 10%, g 3%)
   high ≈ $3,007m   (WACC 9%,  g 4%)

Step 4, a second method for the football field (comps), on a consistent basis. With trailing EBITDA of $210m and a peer set trading at EV/EBITDA of 8× / 10× / 12×, the comparable-companies EV band is 210 × {8, 10, 12} = $1,680m / $2,100m / $2,520m, deliberately at the same enterprise-value level as the DCF so the two bars are comparable. The DCF mid ($2,202m) sits just above the comps median ($2,100m): the methods broadly agree, which is exactly what a football field is meant to reveal.

Step 5, bridge to equity, once, at the end. If net debt is $300m, the base-case equity value = EV − net debt = 2202.38 − 300 = $1,902.4m. Do this bridge a single time, after the enterprise-value work is done, so every bar on the field stays on one basis until the last step. The deliverable is not "the company is worth $2,202m", it is "enterprise value is most likely ~$2.2bn, defensibly between ~$1.75bn and ~$3.0bn, and here is exactly which two assumptions move it."

Practice set

Work each problem before reading its solution. Assume professional layout (inputs isolated, no plugs) throughout. Numbers reuse the two worked examples so you can check the whole stack hangs together.

P1 (guided). Net income from the IS. Using WE1's FY1 assumptions (Revenue 800, COGS 480, SG&A 140, Depreciation 50, interest 10% on opening debt 200, tax 25%), compute EBIT, EBT, and net income (₹ crore).

Solution. EBIT = 800 − 480 − 140 − 50 = 130. Interest = 0.10 × 200 = 20, so EBT = 130 − 20 = 110. Tax = 0.25 × 110 = 27.5, so net income = 110 − 27.5 = ₹82.5 crore. The order matters: depreciation is subtracted before EBIT (it is an operating cost), interest after EBIT (it is a financing cost), and tax is charged on EBT, not on EBIT.

P2 (guided). Retained earnings roll-forward. Opening retained earnings ₹130 crore, net income ₹82.5 crore, dividend payout 40% of net income. Compute closing retained earnings.

Solution. Dividends = 0.40 × 82.5 = 33.0. Closing RE = opening RE + net income − dividends = 130 + 82.5 − 33.0 = ₹179.5 crore. This single line is the sole link from the income statement into balance-sheet equity; get it right and the equity side of the balance sheet is correct.

P3 (guided). The balance check. Using WE1's closing balances: assets: cash 24.5, receivables 150, inventory 110, net PP&E 330; liabilities: payables 85, debt 200; equity: share capital 150, retained earnings 179.5 (₹ crore), compute the balance check and state what a non-zero value would mean.

Solution. Total assets = 24.5 + 150 + 110 + 330 = 614.5. Total liabilities = 85 + 200 = 285. Total equity = 150 + 179.5 = 329.5. Balance check = 614.5 − 285 − 329.5 = 0.00. A non-zero value would mean a broken link, most often cash that was typed as a plug instead of being carried from the cash flow statement, or a working-capital change reflected on one statement but not the other. You hunt the error; you never force the cell to zero.

P4. The indirect CFO. From WE1: net income 82.5, depreciation 50, increase in receivables 30, increase in inventory 20, increase in payables 15 (₹ crore). Compute cash from operations, and say why depreciation is added back.

Solution. CFO = 82.5 + 50 − 30 − 20 + 15 = ₹97.5 crore. Depreciation is added back because it reduced net income but moved no cash, it is a bookkeeping allocation of a past cash outlay (the original capex), so the indirect method reverses it to get from accrual profit to actual operating cash. Rising receivables and inventory consume cash (subtract); rising payables defer cash (add).

P5. Closing cash. Continuing WE1: opening cash 40; CFO 97.5; capex 80; dividends 33; debt unchanged; no equity issued. Compute cash from investing, cash from financing, the net change in cash, and closing cash (₹ crore).

Solution. CFI = −capex = −80. CFF = −dividends = −33 (debt flat, no equity). Net change = 97.5 − 80 − 33 = −15.5. Closing cash = 40 − 15.5 = ₹24.5 crore, the very figure that posts to the balance sheet in P3 and makes it balance. This closing-cash link is the join that a plug destroys.

P6. Base-case DCF enterprise value. FCF (US$m) 120/135/150/165/180 for years 1–5; WACC 10%; terminal growth 3%. Compute the terminal value at year 5, its present value, and the enterprise value.

Solution. TV = 180 × 1.03 / (0.10 − 0.03) = 185.4 / 0.07 = 2648.57. PV(TV) = 2648.57 / 1.10⁵ = 2648.57 / 1.61051 = 1644.55. PV of the explicit FCF₁…₅ = 557.82 (sum of 109.09 + 111.57 + 112.70 + 112.70 + 111.77). EV = 557.82 + 1644.55 = ≈ $2,202.4m. The terminal value is ~75% of EV, the structural reason the DCF is so sensitive to WACC and g.

P7. The football-field band. Given the two-variable data-table grid from WE2, state the low, mid, and high enterprise values and the (WACC, g) corner each comes from.

Solution. Low ≈ $1,753.5m at (WACC 11%, g 2%), the conservative corner: highest discount rate, lowest growth. Mid ≈ $2,202.4m at the base case (10%, 3%). High ≈ $3,006.8m at (WACC 9%, g 4%), the optimistic corner. The band, not the point, is the deliverable: EV is most likely ~$2.2bn, defensibly ~$1.75bn–$3.0bn, and the sensitivity is driven almost entirely by the two assumptions on the grid's axes.

P8. Comps on a consistent basis. Trailing EBITDA is $210m; peers trade at EV/EBITDA of 8× / 10× / 12×. Compute the comparable-companies enterprise-value band, and explain why it must sit at the enterprise-value level next to the DCF.

Solution. Comps EV band = 210 × {8, 10, 12} = $1,680m / $2,100m / $2,520m. It must be at enterprise value because the DCF bars are enterprise values (they discount unlevered FCF, before financing); putting an equity-level bar (say a P/E-based value) beside them would compare different things and mislead. Fix one level for all bars, then bridge to equity once at the very end (Equity = EV − net debt).

P9. Portfolio tracker total. A Power Query tracker cleans this Indian holdings export: RELIANCE 50 @ LTP 2950; TCS 30 @ 3850; HDFCBANK 80 @ 1620; INFY 60 @ 1380; ITC 200 @ 465 (₹). Compute total market value, and (given total invested of ₹5,07,000) the total P&L and percentage return.

Solution. Market value = 50×2950 + 30×3850 + 80×1620 + 60×1380 + 200×465 = 147500 + 115500 + 129600 + 82800 + 93000 = ₹5,68,400. P&L = 5,68,400 − 5,07,000 = ₹61,400; return = 61,400 / 5,07,000 = 12.11%. In the workbook this is =SUM(MarketValue) over a query-loaded table, so adding next month's trades to the CSV and hitting Refresh updates the total with no re-typing, and because the query selects columns by header, a broker reordering the export does not touch the answer.

P10. DAX macro KPI. A Fact_Macro table holds a CPI index of 190.0 for Jun-2024 and 201.4 for Jun-2025; the repo rate is 6.50%. Write conceptually what the CPI YoY % and Real Policy Rate measures return, and compute both.

Solution. CPI YoY % compares the latest CPI to the same period one year earlier (via SAMEPERIODLASTYEAR on the marked Date table): (201.4 / 190.0) − 1 = 6.00%. Real Policy Rate = Repo − CPI YoY % = 6.50% − 6.00% = 0.50%, money is only marginally positive in real terms. Both are measures, so they recompute correctly whether the dashboard is filtered to a month, a quarter, or a year; writing them as calculated columns would freeze them at refresh and break under slicers.

P11 (timed: 8 min). Whole-stack sanity link. From WE1, confirm two independent facts that a grader checks: (a) interest coverage (EBIT ÷ interest), and (b) that the change in cash on the CFS equals the change in the cash line on the balance sheet.

Solution. (a) Coverage = 130 ÷ 20 = 6.5×, comfortably above the ~2–3× a lender worries below. (b) CFS net change in cash = −15.5; balance-sheet cash moves from 40 to 24.5, a change of −15.5. They match, because the CFS is the reconciliation of the two cash balances, if they ever disagree, a cash-affecting item has been recorded on one statement but not the other, which is the first thing to check when a balance check is non-zero.

P12 (timed: 8 min). Choose the right build. For each need, name the capstone tool and one reason: (a) "does this model even balance?"; (b) "what is the value range, and what drives it?"; (c) "revalue my holdings from a fresh broker export without re-typing"; (d) "show inflation vs. last year, sliceable by month"; (e) "produce the same PDF report every Monday in one click."

Solution. (a) The 3-statement model with an independent balance check = 0, a plug would hide the very error you are asking about. (b) The DCF two-variable data table → football field, a single number hides the sensitivity; the band and its axes expose it. (c) The Power Query tracker referencing columns by header, hand-editing or position indices break on the next export. (d) The DAX dashboard with a Date table and a YoY measure: SUMIFS cannot do time intelligence cleanly and a calculated column will not respond to the month slicer. (e) The VBA refresh-and-print macro with an explicit print area and fit, humans miss steps; the macro never does.

Applied mini-project

Build the Analyst's Stack, five integrated deliverables in one workbook.

Assemble a single workbook (multiple sheets) that contains all five capstones, wired to professional standard and defensible end to end. This is the Black-Belt build; treat it as a portfolio piece.

1: 3-statement model (pro standard, keyboard-only). Reproduce WE1 as a proper model: an isolated blue inputs block, a black calculations area for the IS/BS/CFS, and a checks row whose headline balance check cell reads = Total assets − Total liabilities − Total equity. Build the first period's column of formulas once, fill it right for at least three forecast years, and confirm the check reads 0 in every year. No plugs: cash must be carried from the CFS, retained earnings from the roll-forward, net PP&E from capex-less-depreciation.

2: DCF + football field. Build WE2's DCF; drive a two-variable data table over WACC × terminal growth; pull the low/mid/high enterprise values; add a comparable-companies EV band on the same enterprise-value basis; and chart both (plus, optionally, a 52-week range) as a horizontal football field. Add a one-line net-debt bridge to equity value beneath it.

3: Power Query portfolio tracker. Import a broker holdings CSV with Power Query, selecting and typing columns by header; add Invested, MarketValue, and PnL columns; load to a table; build a small dashboard with a total market value, a weight column, gainer/loser conditional formatting, and a PivotChart. Prove robustness: reorder the CSV's columns, refresh, and confirm nothing breaks.

4: DAX macro-dashboard. Load a macro fact table and a Date table into the data model, mark the Date table, relate them, and write measures for a latest value, a YoY %, and a real policy rate (policy rate − inflation). Surface them on a dashboard sliceable by month/quarter/year.

5: VBA one-click refresh-and-print. Write RefreshAndPrint: RefreshAll → wait for async queries → CalculateFull → set PrintArea, Zoom = False, FitToPagesWide = 1 → export a dated PDF. Assign it to a button on the dashboard.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep this workbook, it is the template you will clone for real engagements, and passing it is the Excel Black Belt.

Lab: open the The Analyst's Stack capstone workbook lab in this node's Practice ▸ Excel tab and pass its checks.

Reading & resources

The pro-modeling standard (read first):

• Corporate Finance Institute (CFI), its "3-statement model" and "DCF" build guides walk the exact linkages and the layout discipline this module examines. [Free/Paid] [Intermediate]
• Aswath Damodaran (NYU Stern), his free valuation spreadsheets and short lectures are the canonical dissection specimens for a DCF; open one and trace every link. [Free] [Intermediate]

Data tables, football fields, and sensitivity:

• Microsoft Learn, the official "Data Table (What-If Analysis)" page for the two-variable mechanics; and the PageSetup/ExportAsFixedFormat VBA object pages for Capstone 5. [Free] [Intermediate]
• Any reputable "football field valuation chart in Excel" tutorial (search that phrase), for the stacked-bar construction and the consistency discipline. [Free] [Intermediate]

Power Query and DAX:

• ExcelIsFun / Leila Gharani (YouTube), end-to-end "portfolio tracker in Power Query" and "star schema + DAX measures" builds are the best moving-picture treatment of Capstones 3 and 4. [Free] [Intermediate]
• SQLBI (Marco Russo & Alberto Ferrari), the authoritative source on CALCULATE, filter context, and time intelligence; their "Mark as Date Table" and SAMEPERIODLASTYEAR articles are essential. [Free/Paid] [Advanced]

VBA automation:

• The EX6.01–6.04 labs you already completed, reuse their RefreshAll and ExportAsFixedFormat patterns; this module only assembles them. [Free] [Intermediate]

Bridge: there is no next Excel node, this is the finale. The bridge is sideways, into the analyst path: your balanced model, your football field, your tracker, and your measure library are the toolkit the case labs assume you own.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Reading four structural thresholds -- size, reproducibility, versioning, and audit trail -- that mark when a workbook should stop being a spreadsheet and become a coded pipeline, without assuming either that Excel is obsolete or that Excel can do everything",
  "use": "AI will happily translate a working model to Python or pandas the moment you ask -- but it does so whether or not any threshold has actually been crossed, because it has no way to see your workbook's real row count, refresh cadence, or team size. Asked should I convert this to Python, it defaults to enthusiasm for the rewrite rather than measuring the four thresholds this addendum names.",
  "tools": ["Coding assistants -- Claude, ChatGPT, or a notebook assistant -- for a genuinely useful second opinion once you already suspect a threshold has been crossed: translating a Power Query step to a pandas equivalent, or a VBA macro to a small script", "General-purpose AI -- for drafting the migration checklist (what breaks, what needs re-testing) before a rewrite, never as the judge of whether to rewrite at all"],
  "prompt": "Here is my workbook's row count, how often it is refreshed, how many people edit it, and whether its outputs feed anything downstream. Before recommending Python, tell me which of the four thresholds -- size, reproducibility, versioning, audit trail -- this actually crosses, and say explicitly if none of them do.",
  "verify": "Never accept you should rewrite this in Python as free-standing advice -- ask which threshold triggered it, and check the number yourself. A model with a few hundred rows, one owner, and no audit requirement crosses none of the four thresholds named here, no matter how enthusiastically an assistant recommends a rewrite; the four thresholds are the check, not the assistant's opinion.",
  "diy": "The gate asks you to name, from memory and with no tool in the room, the four axes and a concrete threshold on each. You cannot outsource the judgment of when a tool change is warranted -- only the mechanics of making the change once you have decided it is."
}

Modern Data Analysis

By hand first. WE1 proves a linkage is right by deriving closing cash through the cash flow statement and letting an independent balance check read zero, never by plugging. WE2 proves a single DCF number is fragile by sweeping it into a two-variable data table and reading a football-field band instead of a point estimate. Both habits, derive, don't plug; band, don't point-estimate, are what the code below reproduces exactly, as a verification anchor.

Today's workflow. Both engines are short enough to vectorise directly and check line-for-line against this module's own worked figures.

Tools & sources (IN + US). pandas/numpy for the vectorised versions of both engines below. For grounding either build in real filings: India, a listed manufacturer's own annual report (balance sheet + cash flow statement) for a genuine WE1-style linkage, screener.in for a fast cross-check of the same figures, NSE/BSE corporate filings for the underlying financials; US, a 10-K's audited statements via SEC EDGAR (XBRL companyfacts) for a genuine WE2-style DCF input set, Damodaran's industry WACC and margin tables (pages.stern.nyu.edu/~adamodar) for a sense-check on the discount rate.

# recomputed in-session -- R3: WE1's linkage and WE2's DCF + data table, vectorised and checked
# against the module's own worked figures
cash0, ar0, inv0, ppe0 = 40, 120, 90, 300
ap0, debt0, sc0, re0 = 70, 200, 150, 130
ebit = (800-480) - 140 - 50
ebt = ebit - 0.10*debt0
ni = ebt - 0.25*ebt
div = 0.40*ni
re1 = re0 + ni - div
ar1, inv1, ap1, capex, dep = 150, 110, 85, 80, 50
cfo = ni + dep - (ar1-ar0) - (inv1-inv0) + (ap1-ap0)
cash1 = cash0 + cfo - capex - div
ppe1 = ppe0 + capex - dep
check = (cash1+ar1+inv1+ppe1) - (ap1+debt0+sc0+re1)
print(f"WE1: NI={ni:.1f} REclose={re1:.1f} CFO={cfo:.1f} cash_close={cash1:.1f} balance_check={check:.2f}")

fcf = [120,135,150,165,180]
def ev(wacc, g):
    pv = sum(fcf[t]/(1+wacc)**(t+1) for t in range(5))
    return pv + (fcf[-1]*(1+g)/(wacc-g))/(1+wacc)**5

print(f"WE2: EV base (WACC 10%, g 3%) = {ev(0.10,0.03):.2f}")
print("WE2 grid  WACC-> 9%       10%       11%")
for g in (0.02, 0.03, 0.04):
    print(f"  g={g:.0%}  " + "  ".join(f"{ev(w,g):8.1f}" for w in (0.09,0.10,0.11)))

Verify. WE1: NI 82.5, RE close 179.5, CFO 97.5, closing cash 24.5, balance check 0.00, all in ₹ crore, exactly matching the module's own Worked Example 1. WE2: EV base 2202.38, and the full 3×3 grid (low 1753.5 / mid 2202.4 / high 3006.8, all US$m) exactly matching Worked Example 2 to the cent, the code is checked against the module's own figures, not the other way round.

Quantitative lens

Excel is neither obsolete nor universal. Four axes each carry a threshold you can actually compute, and each is a point where this module's own analyst's stack eventually hands off to code.

Size. A naive, unindexed row-by-row lookup (exactly what an ungoverned VLOOKUP down a column does) scales O(N²): every new row must be compared against every existing row.

# recomputed in-session -- R3: timing a naive linear-scan lookup at increasing table sizes
import time, random
random.seed(20260731)
def naive_scan(n):
    keys = [f"K{i:07d}" for i in range(n)]; random.shuffle(keys)
    table = [(k, i*1.5) for i, k in enumerate(keys)]
    t0 = time.perf_counter()
    for qk in keys:
        for k, v in table:
            if k == qk: break
    return time.perf_counter() - t0

for n in (300, 600, 1200, 2400):
    print(f"n={n:5d}  time={1000*naive_scan(n):7.2f} ms")

Measured: 0.98 ms, 4.12 ms, 16.45 ms, 71.82 ms at n = 300 / 600 / 1,200 / 2,400, a fitted scaling exponent of 2.07, matching the O(N²) prediction closely (timing noise moves this a few hundredths run to run; the exponent stays close to 2). Extrapolating the same power law: ~1.4 seconds at 10,000 rows, ~38 seconds at 50,000, ~2.7 minutes at 100,000, a lookup pattern that is instant at demo size and genuinely painful at real-company transaction-history size. This specifically indicts the pattern (a formula that re-scans a range) not Excel as a category: this program's own EX5.01 already showed that Power Pivot's VertiPaq engine holds tens of millions of rows comfortably, precisely because it is columnar and relates tables through declared relationships instead of scanning for a match row by row. The size axis is crossed by ungoverned lookups long before it is crossed by "using Excel."

Reproducibility. Capstone 3's own by-header-not-position rule is necessary but not sufficient: it protects a Power Query refresh from a reordered export, but a human still clicks Refresh, and nothing proves two runs produced byte-identical output.

# recomputed in-session -- R3: a coded pipeline hashes identically regardless of input row order
import hashlib
rows_a = [("RELIANCE",50,2950),("TCS",30,3850),("HDFCBANK",80,1620),("INFY",60,1380),("ITC",200,465)]
rows_b = list(reversed(rows_a))                       # same holdings, broker re-exported in a different order

def pipeline_hash(rows):
    cleaned = sorted(rows, key=lambda r: r[0])         # by header/key, not position -- Capstone 3's own rule
    total = sum(qty*ltp for _, qty, ltp in cleaned)
    payload = "|".join(f"{s}:{q}:{p}" for s,q,p in cleaned) + f"|TOTAL:{total}"
    return hashlib.sha256(payload.encode()).hexdigest()[:16], total

print("order A:", pipeline_hash(rows_a))
print("order B:", pipeline_hash(rows_b))

Both orderings hash to the identical fingerprint 044e5f48f5a479d8 on a total market value of ₹5,68,400 (matching Practice Set P9 exactly) proving byte-for-byte that row order never touched the answer, a claim "I clicked Refresh and it looked right" cannot make.

Versioning. Two workbooks differing by exactly one changed assumption cell, saved with openpyxl, come to 4,932 bytes each; comparing them with a standard diff reports only Binary files ... differ and a byte-level compare (cmp -l) finds 10 bytes differ, scattered across two locations in the compressed stream, diff can say that the files differ but not what changed. Exporting the identical two states as CSV and diffing gives one clean, readable line: WACC,0.11 → WACC,0.12. The underlying change was one number; xlsx's compressed binary format makes that opaque, while a text format makes it legible.

Audit trail. A plain change-log sheet is just more cells: anyone with edit rights can rewrite an old row and nothing downstream notices.

# recomputed in-session -- R3: a hash chain makes a silently-edited past entry detectable
import hashlib
def chain(entries):
    out, prev = [], "0"*64
    for e in entries:
        prev = hashlib.sha256((prev+e).encode()).hexdigest()
        out.append(prev)
    return out

original = ["WACC set to 10%", "capex assumption raised to 80", "dividend payout set to 40%"]
tampered = ["WACC set to 10%", "capex assumption raised to 60", "dividend payout set to 40%"]  # entry 2 silently edited
print("original final hash:", chain(original)[-1][:16])
print("tampered final hash:", chain(tampered)[-1][:16])

The two final hashes differ (866c711ec6d74b7d vs 5587aa3c0bd010d0, both truncated to 16 hex characters) even though entry 3's own text never changed, editing entry 2 alone is enough to break every hash after it, which is exactly what makes tampering detectable. A spreadsheet change-log sheet offers no equivalent: an edited row looks identical to a row that was always correct.

The honest limit. None of these four thresholds is a universal trigger, and none argues Excel is finished. A 300-row weekly tracker with one owner and no audit requirement crosses none of them, and rewriting it as a coded pipeline would be pure overhead for a problem Excel already solves well. The thresholds bite specifically at the sizes and stakes where they were measured above: an ungoverned lookup pattern at tens of thousands of rows, a pipeline more than one person must trust without watching it run, a change history that must survive a dispute, a log that must survive an audit. Resist both failure modes. "Excel is obsolete" ignores that most real analyst work never crosses any of these four lines, and "Excel can do everything" ignores that, past these specific lines, it demonstrably cannot.

Where this goes next: galaxy cross-links

• G2 · CS2.04 Systems & Data-Intensive Thinking, the size axis in full: why a query that was fast in January crawls in July, and the storage/indexing trade-offs behind the O(N²) wall demonstrated above.
• G2 · QD2.04 Reproducible Research, Deployment & the Quant Stack, turns "the pipeline hashes identically" into a production discipline: pinned dependencies, fixed seeds, and a documented run sequence as standing proof a result reproduces.
• G2 · CS2.02 Version Control with Git, the versioning axis's real fix: a commit history that shows exactly what changed, line by line, instead of a binary file that only says something did.
• G2 · CS2.03 Databases & SQL for Analysts, the audit-trail axis's structural fix: every fact in one place with an explicit key, so a value cannot silently drift between two copies the way a spreadsheet's change-log sheet can.

Teach it back & journal

Feynman prompt. Explain to a smart friend who has never built a model why a three-statement model that "balances" can still be wrong, using only the idea of a plug. Tell the story of the analyst who, at 11pm, makes the balance sheet balance by typing a number into equity, and how that one keystroke turns a smoke alarm into a decoration. Then explain how a real model balances: cash comes out of the cash flow statement and is carried to the balance sheet, so when the check reads zero it is telling you the truth. Only in your last two sentences may you use the words balance check, cash flow statement, and plug, attach each to the moment in the story it belongs to.

Journal prompt. Pick the one capstone you found hardest and write half a page on why. Was it the accounting linkage of the 3-statement model, the assumption-sensitivity of the DCF, the header-discipline of Power Query, the filter-context of DAX, or the sequence of the VBA macro? Then write the single sentence you want to remember forever about what the Analyst's Stack really is, not five tools, but one capability: taking a pile of numbers and a decision-maker's question and returning an answer they can trust. Finally, name the first real workbook in your own life you will rebuild to this standard, and the one check (the balance check, the football-field basis, the by-header import, the Date table, or the print area) you will never again ship without.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.