Outputs & Troubleshooting

The outputs that matter

A model earns its keep through a few headline outputs, not a wall of numbers.

After nine units of plumbing, the model finally has to say something. The temptation is to present everything — every line of every statement for every year. Resist it. A decision-maker needs a small set of outputs: where revenue, EPS, and free cash flow are heading; how profitable and capital-efficient the business is over the forecast; and a single, defensible valuation conclusion. Everything else is supporting evidence.

The four output families

• Trajectory: the revenue, EPS, and free-cash-flow path across the forecast — the shape of the story, not just the endpoint.
• Returns: ROE, ROA, and margins over time, decomposed so you can see what is driving them (DuPont).
• Valuation: a per-share conclusion with the method and discount rate stated, so a reader can challenge it.
• Risk: how much the conclusion moves when the key assumptions move — the subject of the next lesson.

FCFF = EBIT x (1 - tax rate) + D&A - CapEx - ΔNet working capitalFCFF is the cash the business throws off before financing choices. It is the raw material of an intrinsic valuation, and the trajectory of FCFF is usually the single most important output.

Lead with the conclusion On a desk, the first line of any output is the answer: 'We value the shares at $X, implying Y% upside.' The supporting detail comes after. If a reader has to dig for the conclusion, the model has failed as a communication tool.

In the workspace these outputs live on dedicated tabs. The Income Statement, Cash Flow, and Balance Sheet tabs hold the full statements; the Ratios tab carries margins, returns, and the DuPont decomposition; the Capital Returns tab shows dividends and buybacks; and the Valuation tab pulls it together into a per-share conclusion. You read the story across these tabs rather than rebuilding a summary by hand.

Sensitivity analysis

A valuation is a range, not a point — sensitivity shows how the answer breathes.

No single valuation is correct, because no set of assumptions is certain. Sensitivity analysis is how you turn one number into an honest range: you flex the two or three assumptions that matter most and watch the conclusion move. It answers the question every reader actually has — 'how wrong could this be, and what would have to be true for it to be wrong?'

The classic format is a two-way data table: one key driver on the rows, another on the columns, the valuation in the cells. The most common pairing for an intrinsic valuation is the discount rate against the terminal (long-run) growth rate, because the conclusion is usually most sensitive to those two. For an operating story you might instead flex revenue growth against operating margin.

Sensitivity vs. scenario A sensitivity flexes one or two assumptions in isolation to map the slope of the answer. A scenario (Base / Bull / Bear, from Unit 2) flexes a whole coherent set of assumptions at once. You need both: scenarios tell a story, sensitivities tell you which assumption to argue about.

1. Pick the two assumptions the conclusion is most exposed to — often discount rate and terminal growth.
2. Choose a sensible range around each (e.g. ±1% on the discount rate, ±0.5% on terminal growth).
3. Read the grid for the spread between the corners — that spread is your honest valuation range.
4. If a small, plausible change flips the recommendation, say so explicitly; that fragility is itself a finding.

Live, not stale The Sensitivity tab recomputes the whole grid from the live engine whenever an assumption changes, so it never drifts out of sync with the model — unlike an Excel data table you build once and forget to refresh.

The bugs that break almost every model

Five classic errors account for the overwhelming majority of broken models.

Most modelling errors are not exotic. A handful of recurring mistakes account for the vast majority of broken models, and learning to recognise their fingerprints saves hours. The worst of them is the balance sheet that won't balance — which is precisely the bug this tool makes impossible.

Circularity is the sneaky one Interest expense depends on the debt balance, which depends on the cash position, which depends on... interest expense. This loop is real finance, not a mistake — but handled carelessly in Excel it forces iterative calculation that can silently diverge or zero out. A revolver that funds shortfalls and sweeps surpluses is the textbook trigger.

The hardest Excel bug can't happen here Because the engine links the three statements automatically and uses a balance-sheet plug and a cash-flow plug, assets always equal liabilities plus equity by construction. The capital-allocation engine's revolver resolves the interest/cash/debt loop deterministically, so the out-of-balance balance sheet and the runaway circular reference — the two bugs that consume the most analyst time in Excel — are designed out.

A troubleshooting checklist

Isolate a bug to one statement, one period, and one line — then fix it.

When something looks wrong, don't stare at the whole model. Debugging is a search problem: each step should cut the space of possible causes roughly in half. Start broad and narrow until the bug has nowhere to hide — a statement, a period, a line.

1. Start at the balance check: if assets minus liabilities and equity is non-zero, a cash-flow or linking item is missing — fix this before anything else.
2. Find the first period that breaks. A model that is fine in year 1 and wrong in year 2 usually has a roll-forward or link error, not an input error.
3. Walk down the broken statement line by line until a number stops making sense; that line is your suspect.
4. Check the suspect formula for the usual suspects: a hard-code, a wrong sign, or a reference to the wrong cell or year.
5. Sanity-check the magnitude: an error roughly double the size of a line item is almost always a sign error on that item.
6. Re-run the trajectory and the sensitivity grid; if both look reasonable and the balance check is zero, the fix held.

Balance check = Total assets - (Total liabilities + Shareholders' equity)This should be exactly zero in every period. The first non-zero period is the single most valuable clue in any debugging session. In this tool it is always zero by construction.

Reverse solvers: debugging in the other direction Sometimes the question isn't 'what does this assumption produce?' but 'what assumption would produce this result?'. The ROE and ROA reverse solvers work backwards from a target return to the implied driver, which is also a powerful sanity check: if hitting a plausible ROE requires an implausible margin or turnover, the story doesn't hold together.

Finish by writing the executive summary: lead with the valuation conclusion and the case it came from, show the two or three assumptions the answer is most sensitive to, and flag any fragility the sensitivity grid revealed. A reader should be able to challenge your number without opening a single statement tab.