01 — Forecast structure
Structure the forecast around its drivers
An operating model forecasts a business from the handful of operating drivers that management actually manages, not from line items grown at a percentage.
Lay the model out as inputs, driver schedules, financial statements and outputs, in that order, with every forecast line traceable to a named driver and every driver traceable to a historical figure or a stated assumption.
Quick rules
- Historical periods to the left, forecast periods to the right, one column per period across every sheet, with the last actual period marked.
- Drivers are the quantities the business thinks in: volume, price, headcount, utilisation, capacity, take rate. Financial lines are calculated from them.
- One assumption block holds every input; driver schedules reference it and never retype it.
- Show the historical value of each driver next to its forecast so the assumption has a visible base.
- Outputs (revenue, EBITDA, margins, cash flow, returns on capital) are calculated last, on their own sheet or block, from the statements.
How to fix it
- Write the driver tree on paper first: for each revenue and cost line, name the one or two quantities it depends on. Each quantity becomes a row in a driver schedule.
- Move every growth rate, margin and ratio that is typed inside a formula into the assumption block and reference it.
- Add a period header row (actual or forecast, period end date, months in the period) at the top of every sheet and link the statements to it.
- Worked example (invented): a manufacturer forecasts units sold, price per unit and cost per unit. Revenue, cost of sales and gross margin are calculated from those three rows, so a reviewer changing the price assumption sees every dependent line move.
02 — Revenue build
Build revenue from volume and price
Revenue is a driver calculation: how much is sold, at what price, in each segment. Growing last year's revenue at a percentage hides the assumption that matters.
Forecast revenue as volume × price (or its equivalent for the business: customers × average revenue per customer, capacity × utilisation × rate) per segment, and let total revenue be the sum of the segments.
Quick rules
- Split revenue into the segments management reports and forecast each one; do not forecast the total and allocate down.
- Volume and price move for different reasons and are forecast separately, each with a stated growth or step change.
- Derived rates (revenue per unit, per customer, per site) are calculated from the history in the model, not typed in from a source document.
- New capacity, new customers or new products enter the forecast on the date they start, with a ramp if the business needs one, not as a full-year figure in the year they open.
- Total revenue growth is an output. Read it back against the history and the market before accepting the driver assumptions.
How to fix it
- Worked example (invented): units 10,000 growing 4% a year and price 50 growing 2% a year give revenue of 500,000 in the base year and 530,400 the next: 10,400 units × 51.00. The implied revenue growth of 6.1% is a check, not an input.
- For a ramping asset, forecast the start month and the months active in the period, then scale the annual run rate by months active ÷ 12.
- Reconcile the sum of the segments to reported total revenue in every historical period before forecasting.
- Put a growth line under each driver so the reviewer can see the assumption without opening the formula.
03 — Costs and margins
Cost structure and margin progression
Costs are variable, semi-variable or fixed, and the split decides how margins move when revenue moves.
Classify each cost line by its driver, forecast variable costs as a rate on their volume or revenue driver and fixed costs as an amount with inflation, and let margins fall out as outputs that you then read back against history.
Quick rules
- Variable costs (materials, freight, commissions, payment fees) follow volume or revenue at a rate per unit or a percentage.
- Fixed costs (rent, salaried staff, insurance, licences) are amounts that step with capacity or headcount and grow with inflation.
- Staff costs are headcount × average cost per head, with hiring and pay inflation as separate assumptions.
- Operating leverage: when fixed costs are a large share of the base, margin expands faster than revenue in growth and contracts faster in decline. The model should show this without help.
- Gross margin, EBITDA margin and EBIT margin are calculated lines. A margin typed in as an assumption should be the exception, and labelled as one.
How to fix it
- Worked example (invented): revenue 1,000, variable costs 40% of revenue, fixed costs 450. EBITDA = 1,000 − 400 − 450 = 150, a 15.0% margin. With revenue up 10% and fixed costs unchanged, EBITDA = 1,100 − 440 − 450 = 210, a 19.1% margin: operating leverage in one line.
- Calculate the historical cost ratios in the model and forecast from them; a forecast ratio that jumps away from history needs a written reason.
- Keep cost lines in the same order and grouping as the reported income statement so the historical reconciliation is one row of differences.
- Read the forecast margin path against the history: a straight line to a record margin is an assumption to defend, not a result.
04 — Capex and depreciation
Link capex to depreciation
Capital expenditure creates assets that depreciate over their lives. The two forecasts must be one schedule, not two assumptions.
Forecast maintenance and growth capex from their drivers, depreciate each year's additions over a stated life alongside the depreciation of the existing asset base, and roll net PP&E forward so the income statement, cash flow and balance sheet all read from the same schedule.
Quick rules
- Maintenance capex holds the asset base steady and is usually a percentage of revenue or of the existing base; growth capex is tied to the capacity or sites the revenue build assumes.
- Depreciation of existing assets runs off from the opening balance; depreciation of new assets is each year's additions ÷ useful life, starting in the year of purchase or the year after, as stated.
- Closing net PP&E = opening + capex − depreciation. The balance sheet links to this line; the cash flow statement links to the capex line; the income statement links to the depreciation line.
- Over a long horizon depreciation should converge toward maintenance capex; a growing gap between them in a steady-state year is a warning.
- Intangibles and right-of-use assets follow the same roll-forward with their own lives; do not fold them into PP&E.
How to fix it
- Worked example (invented): opening net PP&E 400 depreciating at 40 a year, capex of 60 with a five-year life depreciated from the year of purchase. Year-one depreciation = 40 + 60 ÷ 5 = 52. Closing net PP&E = 400 + 60 − 52 = 408.
- Build a depreciation waterfall: one row per capex year, each running down over its life, with the total row feeding the income statement.
- Trace the depreciation line in the income statement back to the schedule with the formula bar; if it references an assumption instead, the linkage is broken.
- Compare capex to depreciation in the final forecast year and state whether the business is investing ahead of, in line with, or below its run-off.
05 — Working capital
Working capital drivers and the cash effect
In an operating model, working capital is where profit and cash diverge. Each balance follows the line that drives it, and the movement is a cash flow.
Forecast receivables from revenue, inventory and payables from cost of sales, and other operating balances from the line each one accrues against, using days or percentages anchored to history, and carry the movement in the net balance to the cash flow statement.
Quick rules
- Receivables = revenue × days sales outstanding ÷ days in the period; inventory and payables use cost of sales and their own days ratios.
- Deferred revenue, prepayments and accruals are forecast as a percentage of the income statement line they relate to, not grown at a rate.
- The cash flow effect is the negative of the change in the net operating balance: a bigger net asset is a use of cash.
- Seasonal businesses have balance-sheet dates that do not represent the average; forecast the period-end balance, and say so.
- Working-capital days are outputs of history before they are inputs to the forecast: calculate them in the model from reported balances.
How to fix it
- Worked example (invented): cost of sales 600 and days payable outstanding 40 give payables of 600 × 40 ÷ 365 = 66. If opening payables were 60, the increase of 6 is a source of cash in the period.
- Put each balance on the working-capital schedule with its driver, its days or percentage assumption and its historical value side by side.
- Test the sign: raise days sales outstanding and confirm operating cash flow falls; raise days payable outstanding and confirm it rises.
- Reconcile the sum of the schedule's movements to the working-capital line in the cash flow statement in every period.
06 — Scenarios
Scenario and case switches
A scenario switch lets one model run a base, upside and downside case from one set of formulas, with the live case chosen in a single cell.
Store each scenario's assumptions in parallel rows, select the live scenario with one input cell, and pull the active assumption into the driver schedule with a lookup (CHOOSE, INDEX or OFFSET) so the rest of the model never knows which case is running.
Quick rules
- One selector cell, clearly labelled and formatted as an input, drives every scenario lookup in the model.
- Scenario rows hold only the assumptions that differ between cases; everything else stays in the single assumption block.
- The live-case row is a formula: =CHOOSE(selector, base, upside, downside) or an INDEX into the scenario rows. The driver schedule references the live-case row only.
- Keep scenario names in a list next to the selector so a reviewer can see which number means which case.
- Outputs for all scenarios at once come from a data table over the selector cell, not from copies of the model.
How to fix it
- Worked example (invented): revenue growth of 5%, 8% and 2% in the base, upside and downside rows. With the selector at 1 the live-case row reads 5%; changing it to 3 flows 2% through every revenue line without any other edit.
- Search the model for hardcoded scenario values inside formulas (a growth rate typed into a driver) and replace them with references to the live-case row.
- Add a check that the selector is within the valid range and that the live-case row is never blank.
- Run each scenario once and record the headline outputs in a summary block so the effect of the switch is visible without flipping it.
07 — Review
Common operating-model errors and how to catch them
Most operating-model errors are drivers that do not drive: a line grown at a rate while its driver sits unused, or a schedule the statements never read.
Before handing over, trace every forecast line in the statements back to a driver schedule, every driver back to history or a stated assumption, and every schedule forward to the statements it should feed.
Quick rules
- Revenue grown at a percentage while a volume and price schedule exists on another sheet and feeds nothing.
- Costs forecast as a flat percentage of revenue so margins cannot move, or fixed costs grown with revenue so operating leverage disappears.
- Depreciation forecast as a percentage of revenue, disconnected from the capex schedule and the asset base.
- Working-capital balances grown at a rate instead of following their drivers, so the cash flow statement carries no working-capital effect.
- A scenario switch that only some formulas respect, so the upside case is partly the base case.
- Historical periods that do not reconcile to the source, so every forecast ratio is anchored to a wrong base.
How to fix it
- Add a checks block: each historical statement reconciles to the source; revenue equals the sum of the segments; net PP&E rolls forward; the working-capital movement in the cash flow equals the schedule; the scenario selector is in range.
- Flex one driver at a time by a round amount and confirm only the lines that should move do move, by the amount expected.
- Read the forecast ratios (growth, margins, days, capex to revenue) in one summary block against their history before signing off.
Final pass
Checks before handover
- 01Inputs, driver schedules, statements and outputs sit in that order on one consistent timeline.
- 02Every revenue line is volume × price (or its equivalent) per segment and reconciles to reported history.
- 03Costs are classified by driver so margins are outputs and operating leverage shows.
- 04Capex and depreciation are one schedule that the three statements read from.
- 05Working-capital balances follow their drivers and their movement reaches the cash flow statement.
- 06One selector cell drives every scenario lookup and the checks confirm it is in range.
- 07A checks block flags the disconnected drivers and unreconciled history above before delivery.
StreetCred practice cases assess submitted workbooks and return cell-level feedback.
Start the free diagnostic →