Financial Forecasting, Analysis, and Modelling A Framework for Long-Term Forecasting

1. 1Build the deterministic base case first, with the structure the source prescribes: separate assumptions, workings and output sheets, and no hard-coded values in the workings. Uncertainty tools can only flex inputs that sit in one place on the assumptions sheet. If a number is typed inside a formula, none of the three methods can reach it.
2. 2Add a dedicated uncertainty output sheet. The source notes there may be more than one output sheet when the problem requires handling uncertainty. Read it directly from the workings and make it show the results you will compare: for example free cash flow, enterprise value, profit after tax, funding need.
3. 3Run one-dimensional sensitivity first. Change one forecast driver at a time (for SteelCo, sales drivers such as market share, cost drivers, working-capital days) and record the output each time. Rank the drivers by how far the output moves. This is the screening step that tells you where the model is fragile.
4. 4Run two-dimensional sensitivity only on the top two drivers from step 3. Cross them in a grid so you can see combined effects. Do not grid minor drivers, because the grid then reports noise.
5. 5Build named scenarios, with the good, basic and bad cases the source describes. Each scenario is a coherent set of driver values moved together, not one variable at a time. Store them with Excel's Scenario Manager or an alternative selector, and have the control panel switch between them and show the resulting outputs side by side. Make the bad case severe enough to test viability, because lenders and suppliers ask for exactly that proof.
6. 6Move to Monte Carlo simulation for the few drivers where you can state a range and a likelihood. Replace each fixed input with a random draw from a probability distribution that you define. Re-run the model many times and collect the output each time. Keep every other input on the assumptions sheet as is.
7. 7Read the simulation results as a frequency distribution chart and a cumulative probability table. Report the chance that the output falls below your decision threshold (for example, that funding needs exceed available facilities) instead of a single mean. State this alongside the base case.
8. 8Document each range and its rationale next to the assumption. The source says macroeconomic forecasts tend to be 'little better than intelligent guesswork', so tie ranges to historical volatility and to the 2011–2013 statements rather than to confident point predictions.

SteelCo's four-year plan starts from historic statements for 2011–2013 and forecasts 2014 sales as steel market size multiplied by a market share, which the source sets at 10%. Treat that 10% as the first sensitivity variable: hold everything else fixed, move market share down and up around 10%, and record the effect on profit and funding need. The market size itself can come from a macroeconomic equation (market = f(prior-year GDP, industrial production, home sales) plus an error term). The source warns such forecasts are little better than guesswork and that the 2007 crisis was missed entirely, so market size is a prime variable to flex. Next, pair market share with market size in a two-dimensional table to see the joint effect. Then package a good, basic and bad scenario in Scenario Manager, each moving market size, share and cost drivers together, and show all three on the output sheet and control panel. Finally, give market share and market size probability distributions, run the Monte Carlo simulation, and report from the cumulative probability the likelihood that SteelCo needs more funding than it can access. That is a stronger case for a lender than one forecast line.

• If you have not yet screened drivers, then run one-dimensional sensitivity before building scenarios or simulation, so effort goes to the drivers that move the output.
• If two drivers dominate and plausibly move together, then use a two-dimensional table or a joint scenario. A one-at-a-time test hides their interaction.
• If the audience is a lender, supplier or auditor asking whether the business stays viable, then deliver explicit good, basic and bad scenarios, with the bad case tested against viability.
• If you can defend a range and likelihood for a driver, then simulate it. If you can only name a plausible low and high, then keep it as a scenario or sensitivity input.
• If the decision turns on the chance of breaching a threshold (cash shortfall, covenant), then report the cumulative probability from the simulation, not the average outcome.

• Hard-coded values in the workings sheet, so scenario or simulation changes do not flow through. The tell: you change an assumption and part of the output does not move.
• Flexing every variable equally, with no screening. The tell: a long sensitivity table in which no driver stands out and no decision follows.
• A bad case that is only a mild haircut to base. The tell: even the bad scenario shows comfortable viability, so the lender learns nothing.
• Treating simulation output as a prediction rather than a range. The tell: the report quotes one mean result and drops the distribution, which reintroduces the single-point forecast.
• Assuming drivers are independent when they move together, for example sales and working capital in a downturn. The tell: simulated outcomes look better than any scenario you would have built by hand.

• ▸Decision/output tested: name the output metric (for example free cash flow, enterprise value, funding need) and the threshold that would change the decision.
• ▸Base-case driver list: list each forecast driver on the assumptions sheet with its base value, so nothing is hard-coded.
• ▸Sensitivity ranking: record the output swing from moving each driver alone, and rank them to pick the top two.
• ▸Two-way grid pair: name the two drivers crossed and the low-to-high range tested for each.
• ▸Scenario definitions: for good, basic and bad cases, list every driver value moved together and a one-line story for each.
• ▸Bad-case viability check: state whether the bad case stays above the threshold and what funding it requires.
• ▸Simulated drivers and distributions: list the drivers given probability distributions, with the range and the reason for it.
• ▸Simulation readout: record the number of runs, the frequency distribution shape, and the cumulative probability of breaching the threshold.
• ▸Reporting note: state how base case, scenarios and probability will be shown together, and the main caveat.

1. 1Fix the four-step structure before touching numbers: (1) forecast free cash flows for an explicit period, (2) calculate WACC, (3) estimate terminal value from the last projected free cash flow, (4) discount the explicit flows and the terminal value at the step-2 WACC. Accuracy fails at whichever step you skip or rush.
2. 2Choose the explicit period by market type. In a fast-changing market, cap it at about 5 years, because anything beyond is 'mostly speculation' and the terminal value inherits that. In a mature market where forecasting past 5 years is possible, extend it as far as you can defend, because that shrinks the terminal value's weight.
3. 3Build the FCF forecast from historical drivers, not from round guesses. Derive each driver from the reported past years: constant effective tax rate when profits exist, depreciation as a percentage of gross fixed assets, a cost line held at the average of the last 3 years when it varies little.
4. 4Reconcile the units of every driver. Revenue excludes VAT while accounts receivable include it, so adjust DSO for VAT before forecasting receivables. A mismatch here misstates working capital and therefore free cash flow in every forecast year.
5. 5Estimate WACC with CAPM for the equity cost: risk-free rate, beta (the company's risk profile relative to the equity market) and the equity risk premium of the relevant market. Document the source and date of each input so the rate is auditable.
6. 6Compute the terminal value from the year-5 free cash flow, then calculate its share of total value. The passage's normal range is 60% to 80%. Treat a share at or above the top of that range as a warning that the explicit forecast is doing too little work.
7. 7Discount both the explicit FCFs and the terminal value with the same step-2 WACC, then apply the enterprise value adjustments to reach the final value.
8. 8Stress the result before you trust it. Run one- and two-dimensional sensitivity analysis on the drivers that move FCF, WACC and the terminal value, and use scenarios or Monte Carlo to express the value as a range rather than a point.

SteelCo's valuation follows the four steps: forecast FCF for an explicit 5 years, calculate WACC, estimate the terminal value from the year-5 free cash flow, and discount everything at that WACC to reach fair market value. Accuracy depends on how the drivers feeding step 1 are set. The FP&A analyst sees that one cost percentage moves only from 0.4% of turnover in 2011 to 0.6% in 2012, so he holds it at 0.5%, the 3-year average, for the whole forecast. He keeps the effective tax rate constant at 33% when there are profits to tax, and he sets depreciation as a percentage of gross fixed assets. For receivables he divides DSO by the VAT factor, because income-statement revenue excludes VAT while accounts receivable include it. Each choice ties a forecast line to a documented historical pattern. After discounting, check the terminal value share against the 60-80% norm. A share near 80% means about four-fifths of SteelCo's value rests on the year-5 cash flow, so test that cash flow and the WACC hardest.

• If the market changes fast, then cap the explicit period at about 5 years and treat the terminal value as the main source of risk.
• If the market is mature and cash flows can be forecast reliably beyond 5 years, then extend the explicit period to cut the valuation's sensitivity to terminal-value error.
• If the terminal value exceeds roughly 80% of total value, then lengthen the explicit forecast or re-test the year-5 cash flow before presenting the number.
• If a historical ratio varies little across years, then hold it at the multi-year average; if it swings widely, then find the cause before projecting it.
• If the valuation will drive a decision, then present a sensitivity, scenario or Monte Carlo range alongside the point estimate, since projections are not guarantees.

• Extending the explicit forecast past what the market allows, so the later years are speculation. The tell: year 6 or later growth and margins cannot be tied to any historical driver.
• Letting the terminal value carry the valuation unnoticed. The tell: you never calculated its percentage of total value, or it sits above the 60-80% norm.
• Computing WACC from undocumented CAPM inputs. The tell: nobody can state the source of the risk-free rate, beta or equity risk premium.
• Mixing VAT-inclusive and VAT-exclusive figures in working capital drivers. The tell: DSO is computed straight from revenue and receivables without adjustment, and receivables look off against sales.
• Quoting a single value with no sensitivity testing. The tell: no table shows how value moves when sales growth, WACC or terminal assumptions change.

• ▸Market type (fast-changing / mature): state which, since it justifies the explicit period.
• ▸Explicit forecast period (years): enter the length and the reason it is defensible.
• ▸Key forecast drivers and historical basis: list each driver (tax rate, depreciation %, cost % of turnover, DSO) with the years it is based on.
• ▸VAT/unit consistency check: confirm revenue and receivables are on the same VAT basis.
• ▸WACC inputs: risk-free rate, beta, equity risk premium, and the source of each.
• ▸Year-5 free cash flow: enter the figure used as the terminal value base.
• ▸Terminal value as % of total value: calculate it and compare with the 60-80% range.
• ▸Sensitivity variables tested: list the drivers, WACC and terminal assumptions you flexed.
• ▸Valuation range and conclusion: record the low-to-high range and what drives it.

1. 1Name the metrics that need a range before you build anything: net debt, covenant ratios, profit and free cash flow. Give each one a single output cell on a control panel (the book's 'dashboard-like control panel'). A range you cannot see in one place is not visible.
2. 2Run one-dimensional sensitivity first. Move one driver at a time (for example sales growth +2%) and record the change in free cash flow. Rank drivers by output swing so you know which ones deserve a distribution later.
3. 3Run 2-dimensional sensitivity on the pairs that move together, as the book does: sales growth plus supplier payment terms shortened by 5 days, or a 35% turnover increase plus a 10-day cut in client credit period. Show the joint result on liquidity. Two drivers acting together can produce a hit that neither shows alone.
4. 4Build good, basic and bad scenarios, and show the covenant path under each. Hold the scenario definitions in one switchable block (Scenario Manager or an alternative selector) so every statement and covenant line recalculates from the same inputs.
5. 5For the drivers with the largest swing, replace the point estimate with a distribution and run a Monte Carlo simulation. Put uncertainty directly into the modelling process. Output the probabilities, the cumulative probabilities and a frequency distribution chart for net debt and free cash flow.
6. 6Report the range with its confidence level attached, in the form the book uses: 'net debt range at a 95% confidence level based on selected assumptions'. State the assumptions beside the number. A range with no stated confidence level or assumption list cannot be checked.
7. 7Cross-check every driver forecast for reasonableness with the business owner before it enters the range. Put the cross-check result and any method change on the same sheet as the output.
8. 8Show the cash conversion cycle (DSO + DIO − DPO) next to the liquidity range. Working-capital drivers are what move the range, and the quick ratio ignores the timing of receipts and payments, so it cannot stand in for that view.

The book's Steelco case shows how the choice of method alone creates a range. Applying Excel's linear trend to long-product volume gives 48,000 metric tons in 2017, against 150,000 in 2013. The Commercial Director calls this extremely low, so the FP&A forecasts the rate of change instead: −11.3% (2014), −9.5% (2015), −7.7% (2016) and −6% (2017). Total 2013 volume is 200,000 tons (flat is 50/200 = 25%, long is 75%). Chaining the rates gives about 177,400 (2014), 160,500 (2015), 148,200 (2016) and 139,300 (2017); this arithmetic is derived from the passage figures. Holding the 75% long share, 2017 long products come to about 104,500 tons, against 48,000 from the trend line. Neither is the answer. The visible gap between 48,000 and about 104,500 is the first sign of how wide the volume range is. Both numbers, and the reason one was rejected, belong in the model. They then feed the sensitivity, scenario and Monte Carlo layers, so free cash flow and net debt carry that spread.

• If one driver moves an output more than the others in one-dimensional sensitivity, give it a distribution in the Monte Carlo run. Hold the rest at scenario values.
• If two drivers act on the same line, such as sales growth and payment terms on working capital, test them jointly in a 2-dimensional table. Do not add two separate one-dimensional results.
• If a covenant is on the list, show its path under the good, basic and bad scenarios. Do not show it only in the basic case.
• If a forecast fails the reasonableness check with the business owner (the 48,000-ton trend result), change the method or driver and keep both outputs visible. Do not overwrite the rejected one.
• If a range is quoted, state its confidence level (such as 95%) and the assumptions behind it. If you cannot state them, quote the scenario values only.

• Reporting a single basic-case number for net debt or free cash flow. The tell: the output page has no low, high or probability beside the metric.
• Testing drivers one at a time when they move together in practice (sales up while credit terms shorten). The tell: the liquidity result looks mild, yet the combined test (+35% turnover, −10 days of credit) was never run.
• Accepting a mechanical extrapolation without a cross-check. The tell: a trend line produces a figure such as 48,000 tons against 150,000 in the base year, and nobody has asked the commercial owner.
• Quoting a confidence level without the assumptions. The tell: '95%' appears with no list of the distributions or inputs behind it.
• Treating liquidity ratios as range evidence. The tell: the current or quick ratio is the only liquidity view, even though both ignore timing of receipts and payments.

• ▸Metric and period: net debt, covenant ratio, profit or free cash flow, and the year.
• ▸Basic-case value: the output under the basic scenario.
• ▸Good / bad scenario values: the output under each, with the scenario inputs listed.
• ▸Top sensitivity drivers: the drivers ranked by output swing from one-dimensional tests.
• ▸Joint stress tested: the driver pair (for example turnover +35% with credit period −10 days) and the resulting output.
• ▸Distribution assumptions: the distribution used for each Monte Carlo driver.
• ▸Confidence-level range: the low and high output at the stated confidence level (such as 95%).
• ▸Covenant threshold and breach probability: the limit, the scenario where it breaks, and the cumulative probability of breaching it.
• ▸Reasonableness check: who reviewed the drivers, the date and any method change, with the rejected figure kept.

1. 1Write the decision as a question before opening Excel. The source says there is always a problem that needs to be solved, and that problem is why the model exists. A well-formed question names the outputs and the inputs that move together. Example: 'What happens to liquidity if turnover rises 35% and the client credit period falls by 10 days?'
2. 2Pick output variables that answer that question and nothing else. The SteelCo set is annual turnover, EBITDA, working capital, net debt, profit before tax and free cash flow. Working capital shows operating liquidity, net debt shows financial soundness, and free cash flow is the best measure of shareholder value creation.
3. 3Build the projection from reported historical financials. Derive key performance drivers from those records and project the income statement, balance sheet and cash flow statement. Then read the ratios (profitability, liquidity, solvency) as proxies for future performance, while remembering they describe the past.
4. 4Check the model before you read any result. Test that assets equal equity plus liabilities. Test that opening cash equals the prior period's closing cash. Test that closing cash equals the balance sheet cash account. Add model-specific checks as well. About 5% of spreadsheet formulae contain errors, so an unchecked output is not decision-grade.
5. 5Test whether a good-looking number survives the chain: revenue growth, then margin trend, then ROE against alternative investments, then cost of funds against ROE growth, then cash conversion of earnings. Stop at the first link that breaks. A company can be profitable and still be short of cash.
6. 6Run sensitivity analysis to find which inputs move your chosen outputs, changing one or several inputs at once. The source describes this analysis as often the real reason the model was built.
7. 7Build worst, base and best scenarios. Keep the base case values already set in the projection and assign worst and best values to each input variable. Direction matters: if the base case already shows losses, the worst-case sales volume must be lower still.
8. 8Use Monte Carlo simulation when the decision needs a range rather than three points, for example the range of net debt at a 95% confidence level under stated assumptions. State the decision against that range and say plainly that the projection is not a guarantee.

The source poses a two-input liquidity question: what is the combined impact on liquidity of a 35% rise in turnover and a 10-day cut in the credit period given to clients? Answering it in order starts with the output, liquidity, read through working capital, net debt and free cash flow. Both inputs change in the same model run, because separate runs would miss their interaction. Growth in turnover and faster client collections pull cash in opposite directions, so the two-way view is the point. Before reading any result you confirm the balance sheet balances and closing cash ties to the balance sheet. You then set the SteelCo-style scenarios. The base case values come from the existing 4-year plan, and in that plan base-case sales volume already produces losses, so the worst case must use an even lower volume. Finally, you ask Monte Carlo for the range of net debt at a 95% confidence level, so management decides against a range instead of one forecast number. That is the sense in which the decision is proactive rather than reactive.

• If the balance sheet or cash checks do not reconcile, then do not present or act on any output until the error is found.
• If revenue or profit is growing but cash is not, then treat the position as weak: the source warns a company can be profitable yet 'continually short of cash' and fail.
• If the base case already shows losses, then set the worst case below it on the driving variable (for SteelCo, sales volume) and test what that does to net debt and free cash flow.
• If the question involves two or more simultaneous changes (volume and credit terms), then flex them together rather than one at a time.
• If you are comparing ratios across firms or years, then first ask whether accounting practices are compatible, whether the ratios give consistent readings, and whether the figure is reasonable for the industry.

• Building the model without a stated decision. The tell is a 40-sheet workbook whose outputs nobody can link to a choice management must make.
• Trusting unchecked formulas. The tell is that the check totals were never built, or are ignored, even though roughly 5% of formulae carry errors.
• Reading profit as safety. The tell is a decision memo that cites revenue, margin or ROE growth with no cash conversion or free cash flow figure beside it.
• Taking ratios at face value when earnings can be managed. The tell is a year-end cash balance and creditor days that look unusually high because payments to trade creditors were delayed.
• Treating a single forecast as a guarantee. The tell is that one number is quoted with no worst/best case, no range and no confidence level, although the projections are meant only to inform.

• ▸Decision question: write the problem in one sentence, naming each input that changes together.
• ▸Output variables: list the outputs the decision depends on (turnover, EBITDA, working capital, net debt, profit before tax, free cash flow).
• ▸Model integrity checks: record pass/fail for assets = equity + liabilities, opening cash = prior closing cash, and closing cash = balance sheet cash.
• ▸Base case inputs: record the values taken from the existing projection plan.
• ▸Worst case inputs: assign each input a value worse than base, and confirm the direction is correct if base already shows losses.
• ▸Best case inputs: assign each input a value better than base, with a one-line rationale for each.
• ▸Cash conversion check: note whether profit growth is matched by cash growth, and if not, why.
• ▸Range result: state the output range at the chosen confidence level (e.g. net debt at 95%) and the assumptions behind it.
• ▸Decision and trigger: record the action chosen and the output level that would make you act early.

1. 1Embed integrity checks directly in each statement as you build: for the balance sheet, add a cell computing sum of assets minus sum of equity and liabilities and flag any non-zero; for cash flow, verify closing cash of one period equals opening cash of the next and equals the balance sheet cash account of that period.
2. 2Assume roughly 5% of all formulae contain errors regardless of author skill, so run reasonableness tests: feed known inputs into a formula meant to sum or average, and confirm the output matches the hand-calculated expected value.
3. 3Isolate every input assumption in dedicated worksheets (Assumptions, Control panel, Sensitivity Analysis) and route all of them through the Assumptions sheet into Workings so there is one audit path for tracing values.
4. 4Annotate any Assumptions cell that reads from another worksheet with an Excel comment (Review tab > New Comment) naming the source sheet, so the red-triangle marker warns you and third parties where values originate.
5. 5Separate model outputs from workings: put the three financial statements on one workings sheet and the covenant results/summary on a distinct page so conclusions are not tangled with the calculation mechanics.
6. 6Handle non-cash accounting entries deliberately rather than by raw balance differencing — where a provision or similar distorts a period-over-period difference, add back the non-cash amount and remove it from the liability difference.
7. 7Run sensitivity analysis to rank inputs by their contribution to output variability, then use scenarios and Monte Carlo to stress conclusions before decisions are made.
8. 8Adopt spreadsheet best-practice controls (clear objectives, defined calculations, good design, testing, documented presentation) per guides like EuSpRIG and IBM's Spreadsheet Modelling Best Practice, and apply them consistently as regulators (BCBS, FSA) now require.

SteelCo takes a €500k provision in 2013 for doubtful clients. The entry debits an income statement expense (raising it) and credits a balance sheet provision liability (raising it). If the cash flow statement is built by simply subtracting last year's liability balance from this year's, the model records a €500k cash inflow — which is false, because no cash moved. The correct treatment adds the €500k bad-debt provision back to operating activities (it is a non-cash expense) and removes it from the difference of the liability accounts. The bottom line is unchanged, but cash flows by source are now realistic. This is precisely the kind of quietly wrong number that passes every glance yet corrupts every downstream valuation.

• If a balance sheet or cash flow check cell returns a non-zero difference, then stop and locate the error before trusting any downstream output.
• If a percent-of-sales driver produces an account balance, then cross-check it for reasonableness — this technique is simple but prone to false conclusions.
• If an account is affected by a non-cash entry (provision, depreciation), then adjust the cash flow rather than take the raw balance difference.
• If an input cell reads from another worksheet, then attach a comment naming the source so the value's origin is traceable.
• If a decision depends on the model, then run sensitivity analysis first and treat scenarios as qualitative broadening exercises, not single-point answers.

• Trusting a clean-looking output because the author knows Excel — the tell: no check cells exist, so the ~5% formula-error rate goes undetected (JP Morgan's multibillion-dollar spreadsheet loss is the extreme case).
• Building the cash flow by differencing consecutive balance sheets without stripping non-cash items — the tell: provisions or write-offs show up as phantom cash inflows.
• Averaging the wrong data down the line — the tell: Excel dutifully returns a plausible number with no protection flag, so wrong ranges propagate silently.
• Scattering input assumptions across sheets with no routing or comments — the tell: no one, including you months later, can find where a value came from, breeding future errors.
• Treating scenario analysis as if one scenario is the answer instead of a qualitative exercise — the tell: management adjusts inputs to force the expected-value case and never tests downside/upside strategies.

• ▸Balance check: cell computing sum of assets minus equity+liabilities; must equal zero
• ▸Cash continuity check: opening cash = prior period closing cash = balance sheet cash account
• ▸Reasonableness test log: input fed, expected output, actual output, pass/fail
• ▸Formula error scan note: sampled formulae reviewed against the ~5% error assumption
• ▸Input location register: worksheet and cell for each assumption (Assumptions/Control panel/Sensitivity)
• ▸Source comment flag: confirm every cross-sheet cell carries a comment naming its source
• ▸Non-cash adjustment list: provisions/depreciation added back and removed from balance differences
• ▸Output separation confirm: results/covenants on a page distinct from workings
• ▸Best-practice control sign-off: objectives clear, calculations defined, design/testing/presentation reviewed

1. 1Write the problem as one decision question before opening Excel. The source's rule is that there is always a problem to be solved, and the model exists to answer it. Example form: 'What funding does a 2/10 net 30 credit policy require on a given turnover?' If you cannot state the decision the output will inform, stop and ask the requester.
2. 2Specify the model on paper first. List the outputs the decision needs, then the inputs that drive them (for the credit-policy model: turnover, the discount percentage and days, the net credit days, plus your estimates of customer behaviour). Mark each input as a hard estimate or an assumption to be flexed later. This is the 'defining inputs and outputs' step of Chapter 1, done before any formula exists.
3. 3Decide the structure before you build. Create three separated zones: an inputs area (every assumption typed once, nothing calculated), a workings area (every formula, referencing only inputs or earlier workings), and an outputs area (the summary the decision-maker reads, containing no hard-coded numbers). For larger models, make these separate blocks or sheets, e.g. a dashboard-like control panel for drivers.
4. 4Build in dependency order. For a full model that is historical data, then forecast drivers, then income statement, balance sheet, cash flow statement, then valuation. Each stage reads only from the stage before it. Type any assumption exactly once and link to it everywhere else.
5. 5Test the logic against the FAST Standard (Flexible, Appropriate, Structured, Transparent). Flexible: can the inputs change without rewriting formulae? Appropriate: is the model no more complex than the problem needs? Structured: is the layout consistent? Transparent: can another person trace any output back to its inputs? Fix any 'no' before you check numbers.
6. 6Check the outputs for errors, not just the formulae. Reproduce at least one output by hand from the inputs. For three-statement models, confirm the statements articulate (the balance sheet balances and cash flow ties to the balance sheet cash). Then run a deliberately extreme input and see whether the output moves in the direction and size you expect.
7. 7Stress the finished model with the kind of question it exists to answer, such as 'what happens to free cash flow if sales growth rises 2% and supplier payment terms shorten by 5 days?' This is a change to the inputs only. If answering it requires editing workings, the separation of inputs and workings failed, so go back to step 3.
8. 8Record the problem statement, the assumption list and the checks you performed alongside the file. Financial modelling is unregulated and lacks generally accepted practice guidelines, so this documentation is the only assurance a reviewer has against model risk.

Take the book's opening model: the funding needs of a 2/10 net 30 credit policy on a certain turnover. Step 1, the problem: how much funding does this policy tie up, so the business can decide whether to offer it? Step 2, specification: the output is the funding need. The inputs are turnover, the discount percentage, the discount window of 10 days, the net period of 30 days, and your estimate of how customers will behave. Step 3, design: put turnover and the credit terms in one input block, the receivables and funding calculations in a workings block that references only those cells, and the single funding figure in an output block. Step 4, check: choose a round turnover figure you can multiply by hand, work the funding need on paper, and confirm the spreadsheet agrees. Then change the terms (say, shorten the net period) and confirm the funding need moves the right way. Because inputs are typed once and everything else links to them, you can later pose the book's two-factor question, a 35% rise in turnover together with a 10-day cut in the credit period given to clients, by editing two input cells.

• If you cannot phrase the decision the model supports in one sentence, then do not start building. Go back to the requester, because without a problem there is no way to tell which outputs matter.
• If a number appears inside a formula or in the outputs area, then move it to the inputs area and link to it. The model is no longer flexible or transparent until you do.
• If a reviewer cannot trace an output to its inputs without help, then restructure it. The FAST Standard's 'Transparent' and 'Structured' tests have failed, whatever the numbers say.
• If the balance sheet does not balance or cash flow does not tie to balance sheet cash, then stop all further building and debug. Do not run scenarios, sensitivities or a valuation on a model that fails articulation.
• If the question is a what-if on drivers (sales growth, credit days, payment terms), then answer it by changing inputs only. If you have to edit workings, treat that as a design defect to fix first.

• Building before defining the problem. The tell is a large, well-formatted model whose outputs do not answer the requester's actual question, and nobody can say which output is 'the answer'.
• Mixing inputs, workings and outputs. The tell is the same assumption retyped in several cells, or constants embedded in formulae, so one change needs several edits and results then conflict.
• Checking only that formulae calculate, not that outputs are right. The tell is a model with no error-free proof such as a hand recalculation, a balanced balance sheet or a cash tie-out, and a first reviewer who finds the error instead of you.
• Over-building beyond the problem. The tell is complexity no one asked for that makes the model harder to trace, which breaks the 'Appropriate' and 'Transparent' tests.
• Treating the absence of formal rules as permission to skip discipline. The tell is no documentation or standard followed. The source says modelling is unregulated, so model risk falls entirely on you.

• ▸Decision question: write the one-sentence problem the model must answer and who will use it.
• ▸Required outputs: list each output figure the decision needs, and nothing else.
• ▸Input register: list every assumption once, with its source or basis, and mark each as fixed or to be flexed.
• ▸Structure plan: name where the inputs, workings and outputs zones or sheets will sit and in what calculation order.
• ▸FAST self-check: record yes/no and a note for Flexible, Appropriate, Structured and Transparent.
• ▸Hand-check: record the output you reproduced independently, your hand figure and the model figure.
• ▸Integrity checks: record the balance sheet balance and cash-flow tie-out results, or write 'n/a' for single-purpose models.
• ▸Extreme-input test: record the input you pushed to an extreme, the expected direction of the output and what happened.
• ▸Open assumptions and limits: list what remains uncertain, for the reviewer.

1. 1Build the driver list from the statements you intend to forecast. The passages name these drivers: revenue growth (the rate of change of historical revenues), operating expenses as a percentage of revenue, working-capital parameters for receivables, inventory and payables, plus depreciation and tax. Write one row per driver before touching any projection.
2. 2Collect the historical statements and group accounts to the level of detail the problem needs. SteelCo uses 2011, 2012 and 2013. You do not need every income statement, balance sheet and cash flow account, so merge the ones the question does not require. Derive each driver from those historical records rather than typing a forecast number first.
3. 3Put every driver on its own assumptions sheet, separate from the workings and output sheets. Use a distinct colour for assumption cells versus formulas and outputs. Hard-code nothing inside workings formulas, and split complex formulas into simple steps. A driver you cannot trace to a single input cell is not auditable.
4. 4Run a realism test on each historical driver before extending it. Ask the passage's three questions: do the firms you compare against have compatible accounting practices, do the ratios give consistent readings, and does the figure look reasonable for the industry? Check also whether one year's balance is abnormal. A higher-than-normal payables balance, for example, inflates creditor days.
5. 5Set the forecast path explicitly for each year to the end of the horizon (2017 in the SteelCo case) and state why it departs from, or repeats, history. SteelCo had been severely hit by the economic downturn, so extending the 2011–2013 trend unexamined is an assumption you must defend.
6. 6Forecast the income statement from the drivers first, excluding interest. Then drive receivables, inventory and payables from the working-capital parameters. Forecast the remaining balance sheet accounts, balance with debt as the plug, and only then calculate interest through the circular reference.
7. 7Run the built-in checks: assets equal equity plus liabilities, opening cash equals the prior period's closing cash, and closing cash equals the balance sheet cash account. A failed check tells you a driver or formula is wrong, because about 5% of spreadsheet formulas contain errors.
8. 8Stress the drivers with a joint change rather than a single one. The passages model questions like 'increase sales growth by an extra 2% while cutting supplier payment terms by 5 days', or '+35% turnover with a 10-day shorter customer credit period'. A driver that makes the output swing implausibly under such a change needs a tighter justification or a range.

SteelCo is a long-established steel company hit by the economic downturn. The model uses 2011, 2012 and 2013 history to forecast to 2017. The key drivers are the change rate of historical revenues, operating expenses as a percentage of revenue, and the working-capital parameters for receivables, inventory and payables. The passages give no driver values, so none are shown here. Step one derives each driver from the three historical years and places it on the assumptions sheet. Step two tests each against the passage's questions. For payables, if the year-end balance is higher than normal, the creditor-days figure is overstated, and carrying it forward would overstate the cash that suppliers finance. Step three drives the income statement from the revenue growth and operating-expense ratio drivers, and the balance sheet from the working-capital drivers. Debt acts as the plug and interest comes from the circular reference. Step four applies the passage's stress question: what happens to free cash flow if sales growth rises 2% and supplier payment terms shorten by 5 days? A driver set is sound when this shift moves liquidity in the direction and rough size the business logic predicts, and when the balance sheet and cash checks still hold.

• If a historical driver depends on one unusual year (an abnormal receivables or payables balance), then normalise it or use a multi-year view before extending it, because the ratio will otherwise carry the distortion into every forecast year.
• If a comparison firm's accounting practices differ from yours or its ratios give inconsistent readings, then do not use its ratios as a benchmark for your driver.
• If a driver departs from its historical level, then record the reason on the assumptions sheet. If you cannot state one, revert to the historical figure.
• If the balance sheet or cash checks fail after a driver change, then fix the driver or formula before reading any output, because the model is not ready until it produces what it was designed to.
• If a driver materially moves the model's key outputs (EBITDA, interest coverage, free cash flow), then run it through sensitivity analysis alongside a related driver instead of defending a single point estimate.

• Extrapolating history from a disrupted period without comment. The tell is a forecast that reads as a smooth continuation of 2011–2013 with no stated view on the downturn.
• Using a distorted year-end balance to set working-capital days. The tell is creditor days (or similar) that sit well off the other years or off the industry.
• Benchmarking against firms with incompatible accounting. The tell is a driver that matches a peer on paper but cannot be reconciled to your own statements.
• Burying numbers inside workings formulas. The tell is a driver value you cannot find on the assumptions sheet, so a change does not flow through.
• Testing drivers one at a time only. The tell is that combined movements, such as growth plus shorter credit terms, produce a liquidity result nobody anticipated.

• ▸Driver name: e.g. revenue growth, operating expenses % of revenue, receivables, inventory or payables days, depreciation, tax.
• ▸Historical values (each year, e.g. 2011–2013): the figure derived from the historical statements.
• ▸Abnormal-year flag: note any year whose balance or ratio is unusually high or low, and why.
• ▸Peer/industry comparability: do the compared firms share accounting practices, do the ratios read consistently, is the figure reasonable for the industry?
• ▸Forecast value by year: the figure on the assumptions sheet for each year to the end of the horizon.
• ▸Rationale for change vs history: one line explaining any departure, or 'unchanged from history'.
• ▸Input cell location: sheet and cell reference confirming the value is not hard-coded in workings.
• ▸Linked-driver sensitivity test: the paired change tested (e.g. +2% growth with 5 days shorter supplier terms) and the output effect observed.
• ▸Check status: balance sheet balances, opening/closing cash reconcile, cash equals balance sheet cash (pass/fail).

1. 1Write down what the model's outputs need before you collect anything. The passages say a good model has clearly identifiable outputs based on clearly defined inputs, with a logical audit trail between them. List each input, the output it feeds, and the period it covers. That list sets how much detail you collect.
2. 2Start from reported financials for past years. They are the basis for most projection models, and the forecast drivers are derived from them. Pull the income statement, balance sheet and cash flow statement for the same set of years, so every driver comes from one consistent record.
3. 3Check that the historical statements are internally sound before using them. The balance sheet must balance (assets = equity + liabilities). Opening cash must equal the prior period's closing cash. Closing cash on the cash flow statement must equal the balance sheet cash account. A history that fails these checks is not a usable base.
4. 4Confirm the accounting basis. Meaningful comparison needs statements prepared under the same accounting principles (GAAP or the statutory basis). Note any change in accounting policies across the years. The passages treat frequent policy changes as a warning sign.
5. 5Normalize with common-size statements. Express the income statement as a percentage of sales and the balance sheet as a percentage of total assets. Raw numbers hide information that percentages show, and cost and margin trends are the most useful result.
6. 6Run trend analysis across the historical years and flag the ratios that move. Only then derive the key performance drivers from the history. Where the history is unstable, document why before using a driver.
7. 7Benchmark cross-sectionally only against comparable firms: similar business type, revenue size, product mix, diversification, asset size, capital structure, markets, location and accounting methods. Compare the firm's ratios to its own past, its competitors and its industry norms.
8. 8Label every estimated input (for example the expected 30% discount take-up in the credit-policy model) as an assumption. Give each a source and a date. Keep these inputs separate from reported historical data, because 'garbage in, garbage out' applies to both.

The passages' credit-policy model is a wholesale company testing a '2/10 net 30' policy: 2% discount if paid by day 10, full price due by day 30 (20 additional days). The inputs are turnover, the 2% discount, the 10-day and 30-day terms, and an expected 30% of buyers taking the discount. Only turnover is a historical figure, so pull it from reported sales and check that the year's statements balance and agree on cash. The 30% take-up is a forecast assumption. Mark it as unverified and record where it came from, such as past customer payment behaviour if you have it. Trade credit is a loan to the customer, so the funding need depends directly on these inputs. If turnover is stale or the 30% is a guess, the output looks precise but is wrong. The model is only as sound as these two inputs, and any sensitivity analysis should start with them.

• If the historical balance sheet does not balance or cash does not reconcile across statements, fix or re-source the data before you derive any drivers.
• If a peer is not reasonably comparable (business type, size, accounting methods), drop it from the cross-sectional benchmark or flag the comparison as weak.
• If accounting policies or auditors changed across the historical years, restate or annotate the affected years before computing trends.
• If an input is an estimate, not a reported figure (such as the 30% discount take-up), label it, source it, and make it a sensitivity variable.
• If common-size ratios swing sharply between years, investigate the cause before extrapolating them as drivers.

• Deriving drivers from unreconciled statements. The tell: the balance sheet check or the cash-to-balance-sheet check fails, or is absent.
• Mixing accounting bases across years or peers. The tell: margins jump in the year a policy changed, with no operational explanation.
• Benchmarking against non-comparable companies. The tell: peer set differs in size, product mix, capital structure or geography from the target.
• Treating assumptions as facts. The tell: an estimated input such as a take-up rate sits in the model with no source or date and is never flexed.
• Working only from raw numbers. The tell: no common-size or trend view exists, so cost and margin shifts go unnoticed until the forecast looks odd.

• ▸Input / driver name: the model input and the output it feeds.
• ▸Source: reported statement or other source, with the period covered.
• ▸Type: reported historical fact or forecast assumption.
• ▸Accounting basis: GAAP/statutory, plus any policy change in the period.
• ▸Reconciliation checks: balance sheet balances / opening cash = prior closing / closing cash = balance sheet cash (pass or fail).
• ▸Common-size value: % of sales or % of total assets for each historical year.
• ▸Trend flag: stable, improving or deteriorating, with a one-line reason.
• ▸Comparability of benchmark: peer or industry norm used, and whether it matches on type, size, mix, structure and accounting.
• ▸Sensitivity plan: whether this input will be flexed, and over what range.

1. 1Set the control standard before you build. The source says Excel is a tool whose use 'should be underpinned by proper controls', and that spreadsheets should be built 'in a methodical, well-documented way', like programming. Write down the rules for layout, inputs, checks and documentation first, and consult the ICAEW 'Twenty principles for good spreadsheet practice' (cited in the chapter references) as the external benchmark.
2. 2Separate the model into three layers: an assumptions sheet holding every input, a workings sheet holding the calculations, and one or more output sheets. The output sheet 'should read directly from the workings sheet'. Nothing is typed over a formula, and no input hides inside a calculation.
3. 3Put the control panel, if the model has one, in the output layer. It is a one-page summary where the key assumptions are changed and the effect on the chosen KPIs is visible at once. If the specification does not call for one, leave it out; the chapter's test is that the model be 'easy to read, easy to understand, easy to change, and simply easy to use'.
4. 4Build integrity checks into the model itself, because Excel gives no protection against bad data: it 'will dutifully average the wrong data right down the line'. In a three-statement model the first check is that the balance sheet balances. Add a check that the cash flow statement ties to the balance sheet cash line, and show the results in a visible place on the output sheet.
5. 5Treat circular references (interest on average debt) as a controlled exception. Handle them deliberately, as covered in the interest section (3.6), so they are never an accident that stays hidden in the workbook.
6. 6Keep complexity in the model proportional to the problem. The source separates the complexity of the transaction from the complexity of the model, which are 'two different things'. Fewer moving parts means fewer places for errors, and a simple model is easier for a reviewer to check.
7. 7Stress the model with extreme inputs before relying on it. The preface frames questions such as a simultaneous 35% rise in turnover and a 10-day cut in client credit period. Run inputs like these, and confirm the checks still hold and the outputs move in the expected direction.
8. 8Record the origin and limits of the data and assumptions: the historical period used, the 'anchoring' bias risk of extrapolating 2–3 years, and the external-analyst versus insider data basis. A reviewer can then see what the model rests on.

The SteelCo four-year business plan shows the structure. The problem is whether the company stays within three covenants: quick ratio, leverage and interest cover. The specification step fixes the scope: three years of SteelCo history, key forecast drivers, an income statement, a balance sheet that is modelled 'and balanced', and interest expense. It states that neither sensitivity analysis nor a control panel is required for now. That choice keeps the model small. The inputs sit on an assumptions sheet and the calculations on a workings sheet. The covenant results go to a separate output sheet that reads directly from the workings. The balance sheet balancing is the built-in integrity check. The document also notes that using 2–3 years of history carries 'anchoring' bias, so the assumption is stated explicitly rather than hidden. This answers the JP Morgan, CFO.com and Parmenter warnings about spreadsheet error. It also meets the regulators' expectation (BCBS, FSA) that spreadsheets relied on for key data flows have 'effective controls... consistently applied'.

• If a number is an estimate or driver, then it goes on the assumptions sheet; if it is a calculation, then it goes on workings and is never overwritten by a typed value.
• If the model feeds a decision, regulator, lender or covenant report, then apply formal controls consistent with the BCBS and FSA expectation: documented, consistently applied and reviewable.
• If the scope does not require a control panel or sensitivity analysis, then leave them out, as the SteelCo specification does, and add them only when the problem needs them.
• If the balance sheet or cash reconciliation does not tie, then stop and fix it before producing any output or ratio.
• If the model has grown so large and tangled that it is hard to read or change, then simplify or restructure it, since Parmenter's warning concerns large spreadsheets and their susceptibility to error.

• Trusting the arithmetic because Excel computes it: the CFO.com warning is that Excel will average wrong data down the line without complaint. The tell is outputs that look precise but have no reconciliation check behind them.
• Mixing inputs, calculations and outputs on one sheet. The tell is hardcoded numbers inside formulas and an output page that cannot be traced to a single workings source.
• Over-building: adding scenarios, panels and detail the problem does not need. The tell is a model that no one besides its builder can read or change easily.
• Leaving the model undocumented. The tell is that a reviewer cannot say which historical period, data source or assumptions were used, or whether the view is an external analyst's or an insider's.
• Treating spreadsheet risk as an IT matter rather than a business one. The tell is that no one owns the checks, even though the JP Morgan loss of several billion dollars from a spreadsheet error shows the exposure.

• ▸Model purpose and decision supported: state the problem the model solves in one line.
• ▸Sheet map: list the assumptions, workings and output sheets, with the control panel if used.
• ▸Input register: every assumption, with source and date, and where it sits on the assumptions sheet.
• ▸Integrity checks: list each check (balance sheet balances, cash ties) and where its result is displayed.
• ▸Circular references: note any present, where they sit, and how they are handled.
• ▸Data basis and bias: historical period used, whether the view is external or insider, and the anchoring risk.
• ▸Stress tests run: inputs changed, expected direction, and actual result.
• ▸Reviewer and date: who checked the model and when, and any open issues.
• ▸Standards benchmark: which ICAEW principles or internal rules the model was checked against.

1. 1Start from the accounting equation and double entry. Every transaction is listed in two places, so every modelled line must have a counter-entry somewhere. Before building, list each forecast driver and name the two accounts it hits (for example, a credit sale hits sales and accounts receivable).
2. 2Build the income statement first. Then route every income statement line that has a balance sheet side into the balance sheet. The source is explicit: you cannot record a sale or an expense without affecting the balance sheet. Credit sales create receivables, product sales reduce inventory, and purchases on credit create payables.
3. 3Link the income statement to the balance sheet through retained earnings. Opening retained earnings plus net income minus amounts given back to shareholders gives closing retained earnings. The source describes this as the connection (Exhibit 2.13), so build it as a formula, never a typed number.
4. 4Derive the cash flow statement from the two most recent balance sheets using the indirect method. ΔCash equals the movements in the non-cash balance sheet items plus net income. Do not forecast cash independently. The cash flow statement is the only source of the closing cash balance on the balance sheet.
5. 5Link closing cash from the cash flow statement back to the balance sheet. Then add a balance check row (total assets minus total liabilities and owner's equity) for every forecast year. A non-zero value in any year means a link is broken.
6. 6Treat interest and debt as a separate module (Chapter 3.6, 'Modelling interest and circular references'). Debt is the balancing item, so it depends on cash needs, and interest depends on debt. Isolate this loop in one place, so that a break in it is easy to find, and run the balance check after every change to it.
7. 7Cross-check the cash flow statement against the direct method. The source shows that direct and indirect statements give the same operating cash result (Exhibits 2.11 and 2.12). A difference points to a missed balance sheet movement.
8. 8Stress-test articulation with scenarios. Change several drivers at once (the preface's example is turnover up 35% and the credit period cut by 10 days) and confirm that the check row stays at zero in every case.

The source's own double-entry cases make a usable test. Buy an asset for €100 with cash: assets rise €100 and cash falls €100, so total assets are unchanged and the check stays at zero. Now borrow from a bank: assets and liabilities rise by the same amount, so the check stays at zero. (I use €100 for illustration; the source gives no figure for the loan.) Now make a credit sale: the income statement records sales, and the balance sheet records accounts receivable, not cash. Net income flows into retained earnings, so owner's equity and assets both rise. In the cash flow statement, the receivable increase is deducted from net income, so cash does not move until collection. If your model shows cash rising on the credit sale, the cash flow statement is not reading the receivables line from the balance sheet. That is a typical articulation break, and the check row shows it as a non-zero difference.

• If the balance check is non-zero in any forecast year, then stop and fix it before changing assumptions or valuing the business. A model that does not balance cannot support valuation or ratios.
• If a transaction touches the income statement, then confirm it has a balance sheet counter-entry. If a line has no counter-entry, it is a hard-code or an omission.
• If closing cash on the balance sheet is typed or computed on the balance sheet itself, then replace it with a link to the cash flow statement.
• If the interest and debt loop is circular, then keep it in one isolated block and rerun the balance check after every change. Do not spread circular logic across the three statements.
• If the direct and indirect cash flow results differ, then look for a balance sheet movement missing from the indirect reconciliation.

• Cash forecast independently of the cash flow statement. Tell: balance sheet cash does not equal the closing cash on the cash flow statement.
• Income statement items with no balance sheet side, such as credit sales that never create receivables. Tell: the check row drifts by the amount of that item each period.
• Retained earnings rolled forward without net income or shareholder distributions. Tell: equity diverges from net income cumulatively.
• A balance forced with a plug that hides an error, so the check always shows zero. Tell: the plug line changes sign or size unpredictably when drivers change. Debt should absorb real funding needs only, not unexplained differences.
• Circular interest references scattered through the model, or never tested under scenarios. Tell: the model balances in the base case but breaks when several drivers move at once.

• ▸Driver / transaction: name the forecast item (e.g. credit sales).
• ▸Income statement line: record the account affected on the income statement.
• ▸Balance sheet counter-entry: record the account on the other side (e.g. accounts receivable).
• ▸Retained earnings link: confirm opening + net income − distributions = closing, as a formula.
• ▸Cash flow reconciliation: confirm the indirect-method line picks up this balance sheet movement.
• ▸Closing cash link: confirm balance sheet cash equals cash flow statement closing cash.
• ▸Debt/interest block: record where the debt balancing item and interest calculation sit and whether they are circular.
• ▸Balance check by year: record total assets minus (liabilities + owner's equity) for each forecast year; it must be zero.
• ▸Scenario retest: record the multi-driver scenario run and whether the check held.