A forecast is a decision engine, not a spreadsheet. In commercial diligence you are not trying to predict the future; you are building a model that prices a range of plausible futures, exposes the few levers that actually move revenue, margin, and cash, and ties those levers to named owners and timing. The model must be driver‑based (not account‑based), segmented (the same cuts you used throughout this playbook), and auditable (every number traceable to an assumption with a source and a confidence label). Its outputs should drop directly into valuation ranges, debt capacity and covenant headroom, term‑sheet protections, and a 90‑day plan.
This chapter shows you how to assemble that engine at diligence speed and quality. We start with a step‑by‑step build, then—later in the chapter—layer on scenarios, sensitivities, and simulation so the Investment Committee sees outcomes, not narratives.
14.1 Driver-Based Model Build – Step-by-Step Guide
Purpose and guardrails
Before typing a single formula, write one sentence that fixes scope and standards: “Monthly driver model for the next 36 months and annual thereafter; organic perimeter; constant‑currency view with FX overlay; segments = [customer/job, size band, route‑to‑market, region, product family]; margins defined as VRR → CM1 → CM2 (Ch. 13.2).” Commit to three rules: (1) no hard‑coded numbers on calculation sheets; (2) ratio‑of‑sums for all rollups; (3) every assumption has an owner, a source, and a confidence label.
Step 1 — Choose time granularity, horizon, and perimeter
Set the calendar monthly for the first 24–36 months to capture seasonality and GTM cadence, then annual beyond the hold period if needed. Freeze the consolidation perimeter (organic vs. pro forma) and the currency policy (build constant‑currency first; add an FX overlay later). Align accounting conventions that affect modeling—classification of hosting/support and outbound freight, revenue recognition quirks, capitalization policies—so margin math is consistent with Chapters 8, 10, 12, and 13.
Step 2 — Lock segmentation and model structure
Use one segmentation everywhere: product family, route‑to‑market, region, and customer tier/job‑to‑be‑done (Chapter 6). Create a simple, durable structure:
- Assumptions Hub: the only place users can type numbers; includes the driver register, scenario parameters, and sources/confidence.
- Modules: Revenue, CM1 (COGS), Cost‑to‑Serve (CM2), Opex & Capex, Working Capital, Debt & Interest, Taxes, FX, Consolidation.
- Outputs: P&L, cash flow, balance sheet, covenants and liquidity, valuation bridges, and a one‑page scenario summary.
Color‑coding is optional; separation of concerns is not. Keep assumptions, calculations, and outputs on different sheets to avoid silent errors.
Step 3 — Build the Revenue engine(s) by business model
Model revenue as functions of operational drivers, not as trend lines. Use the constructs below; keep them segmented.
- B2B SaaS / usage
- ARR roll‑forward each month:
ΔARR = New + Expansion − Contraction − Churn ± Price (re‑ratings) ± FX.
New ARR is a function of capacity (fully ramped sellers), productivity (new ARR per seller per month), and win rate (Chapter 9.1). Expansion/Contraction come from NRR cohorts (6.3). Price re‑ratings link to 8.2 fences. - Revenue recognition: start with ARR; convert to revenue via recognition rules (e.g., straight‑line for subscriptions; usage = rate × metered consumption with seasonality, floors/ceilings, and minimum commits).
- Key drivers: seller ramp curve, pipeline coverage, stage conversion, cycle length, price realization, seat/module attach, consumption per active, churn hazard modifiers (SLA credits, contact rate—Ch. 10.3).
- ARR roll‑forward each month:
- E‑commerce / DTC
- Order math: Revenue = Sessions × Conversion × Orders per buyer × AOV.
- AOV = Units per order × Net unit price; net price comes from the pricing waterfall (List − on‑invoice discounts − off‑invoice promos − take‑rates/fees − expected returns/chargebacks; Ch. 8.2, 13.1).
- Key drivers: traffic by source, paid share and CPC/CPA, promo depth, channel mix, return rate by reason, marketplace penalties, and paid placement elasticity (Ch. 9.3).
- Marketplaces / platforms
- Net revenue = GMV × Take‑rate − Incentives − Refunds/Penalties.
- GMV = Active buyers × Orders per buyer × AOV; take‑rate ladders by category/tier; penalty ladders for policy breaches (Ch. 11.4).
- Key drivers: activation rates for buyers/sellers, listing depth/fill rate, trust & safety loss rates, search rank/paid placement dependence.
- Hardware + service / B2B distribution
- Units × ASP by family and channel, with attach for spares and services.
- Backlog and book‑to‑bill to time revenue when lead times are material; include project milestones if revenue recognition is POC/milestone‑based.
- Key drivers: capacity ladders (10.2), OTIF service gates (10.3), channel rebates and compliance costs, and regulated approvals (11.1).
Whichever path you use, pin the base year to the revenue bridge (13.1). The first forecast month should reconcile to the last actual month; do not rely on plugs.
Step 4 — Model COGS and CM1 from real cost drivers
Tie costs to what you actually consume.
- Manufactured/assembled goods: BOM‑based material costs, purchase price variances vs. indices, conversion costs (labor rates × hours; overhead absorption vs. capacity), inbound freight and duties, scrap/yield. Add supplier terms (rebates, MOQs) where they change unit cost with scale (10.1, 10.2).
- Digital products: hosting/compute/storage/egress unit curves (12.1), third‑party API usage fees, and reserved vs. on‑demand coverage. Split rate effects (provider price, mix of tiers) from efficiency (engineering fixes).
- Indexation: if contracts allow, include CPI/commodity/fuel indexation ladders on both price and cost sides; they matter in stagflation scenarios (11.3).
Step 5 — Add Cost‑to‑Serve to get to CM2
CM2 reveals the real economics of growth. Build CTS as explicit functions:
- Shipping & accessorials = Orders × weight/zone profile × carrier rate cards × service level mix ± fuel/macro surcharges.
- Returns and refurb = Orders × return probability × (reverse logistics cost − recovered value); pair with drivers that reduce returns (fit/quality, packaging).
- Payment fees and fraud = Card‑processed revenue × bps by rail − recoveries; include chargeback program thresholds.
- Support & success = Accounts or orders × contact rate × minutes per contact × loaded rate; reduce via deflection/self‑serve (10.3).
- SLA credits/penalties = Incidents × credit schedule; driven by reliability (12.1).
- Platform/API = Active tenants or API calls × unit fees × on‑demand share.
Reconcile CTS totals to your CM2 bridge (13.2).
Step 6 — Opex and capex with operating logic, not percentages
Stop scaling opex with revenue unless there is a provable link.
- Sales & Marketing: capacity‑based (ramped sellers, SDRs, CSMs) and program‑based (channel spend tied to CAC/payback guardrails). Commissions follow bookings or cash collection depending on policy.
- R&D / Product / Engineering: headcount plan, contractor mix, capitalization ratio for qualifying software, and depreciation horizon; tie to the innovation pipeline gates (12.4).
- G&A: base + step‑functions (facilities, finance systems, audit/compliance), with policy‑driven increments (security/privacy attestations—12.3; regulatory filings—11.1).
- Capex: split maintenance vs. growth rungs; connect growth capex to capacity ladders (10.2).
Step 7 — Working capital and cash conversion
Cash pays the bills, not EBITDA. Build working capital mechanically, by segment and channel (13.3):
- Receivables: DSO by tier/channel (contracted terms vs. paid‑days), deductions cycle times and win rates, processor reserves/holdbacks, and chargebacks.
- Inventory: weeks of supply by family/site, slow‑moving ladders and E&O, pre‑build seasonality, consignment/VMI.
- Payables: DPO on purchases, early‑pay program usage (APR math), SCF penetration, and vendor concentration.
- Deferred revenue (for recurring): bill cadence (annual/quarterly/monthly), renewal cliffs, refunds.
Flow these into a 13‑week cash view that reconciles to the monthly model.
Step 8 — Debt, interest, and liquidity
Add a debt schedule that can withstand scenarios:
- Term debt: amortization profile, interest rate (base + spread), step‑ups, PIK toggles.
- Revolver/ABL: borrowing base (receivable eligibility, inventory advances), draws/repayments, minimum liquidity.
- Covenants: leverage, interest coverage, DSCR; compute headroom each quarter, not annually.
- Hedging: rate and FX hedges as overlays with cost.
Step 9 — Taxes and statutory tails
Model cash taxes with a simple effective‑rate approach plus loss‑utilization rules; treat NOL usage and limitations separately. Include sales tax/VAT timing where material (especially marketplaces and cross‑border). Keep statutory differences as disclosures unless they move cash inside the horizon.
Step 10 — FX and consolidation overlay
Run the base case in constant currency; apply an FX layer that translates revenue and cost lines using monthly rates and exposes transaction FX where pricing or sourcing is cross‑currency. Scenarios in Chapter 11.3 will toggle FX paths alongside macro drivers.
Step 11 — Quality gates and self‑diagnostics
Bake checks into the model so it refuses to lie:
- Balance sheet balances every month (cash bridge ties).
- Revenue first‑forecast month ties to last actual; CM1/CM2 reconcile to LTM bridges.
- No circular references; error flags for negative inventories, negative headcount, or CAC payback beyond threshold.
- Ratio‑of‑sums for all multi‑segment rollups.
- A reproduction log: a second person can rebuild a result from the assumption cell in ≤3 clicks.
Step 12 — Hook up the scenario manager
Create a Scenario Parameters section with named cases (e.g., Soft‑Landing, Recession, Stagflation, Rate‑Shock, Energy/FX Shock—Chapter 11.3). Each case sets a small set of parameters:
- End‑market proxies (PMI, housing starts, IT spend) → demand elasticities by segment.
- Price power corridors and promo guardrails; indexation on/off.
- Input costs: commodity/energy/freight bands; wage inflation.
- Reliability/penalty ladders and return/fraud rates that move CTS.
- Financing costs, FX paths, and working‑capital drifts (DSO/DIO/DPO deltas).
Press a button (or select a dropdown), and every output page should update: P&L, cash, covenants, valuation ranges, and a one‑page narrative of “what changed, why, and who owns the response.”
Step 13 — Make outputs decision‑grade
Limit the output set to what IC and operators need:
- Five‑line P&L and cash by month and year (Revenue; CM2 and %; Opex; EBITDA; Cash from ops).
- Bridge pages from the last actuals to each scenario’s Year‑1 and Year‑2 outcomes (price, volume, mix, CTS, opex, working capital).
- Covenant headroom chart with trigger months.
- Valuation lenses (multiples or DCF sensitivities) tied to scenario outputs, not to independent assumptions.
- A one‑page “Levers and Owners” for the next 90 days.
Driver Register (copy‑ready checklist)
- Demand & Mix: traffic/sessions; pipeline, stage conversion, win rate; attach/penetration; cohort survival; channel shares; geography/product mix.
- Price & Realization: list changes; on‑invoice discounts; off‑invoice promos; take‑rates; penalties/credits; price fences; re‑rating cadence.
- COGS: material indices; supplier prices; yields/scrap; labor rates and efficiency; inbound freight/duty; reserved vs. on‑demand compute.
- Cost‑to‑Serve: carriers and rate cards; fuel; return probability and cost; payment fee bps; fraud loss rate; contact rate/minutes; SLA credit schedule; API unit fees.
- Opex/Capex: headcount (hire, ramp, attrition); compensation and benefits inflation; program spends; capitalization ratio; capacity rung capex.
- Working Capital: DSO by tier; deduction win rate; processor reserve %; DIO by family/site; slow‑moving ladders; DPO on purchases; SCF/discount APRs; DCL.
- Finance & FX: base rate and spread; amortization; revolver rules; FX rates by pair; hedge costs.
- Policy/Platform: take‑rate changes; threshold ladders; certification/attestation timing and cost (11.4, 11.1, 12.3).
Each driver line should have: value, source, date, owner, confidence (H/M/L), and scenario multipliers.
Practical formulas (clear and auditable)
- SaaS ARR roll‑forward:
ARR_t+1 = ARR_t + New_t + Expansion_t − Contraction_t − Churn_t ± Price_t.
Revenue_t = ARR_t / 12 for pure subscriptions; add usage = Rate × Consumption (with floors/ceilings). - E‑commerce orders:
Orders_t = Sessions_t × Conversion_t × OrdersPerBuyer_t;
Revenue_t = Orders_t × Units/Order_t × NetPrice_t;
NetPrice_t = List × (1 − OnInvoiceDisc) × (1 − OffInvoicePromo) × (1 − TakeRate) × (1 − Returns − Chargebacks). - Marketplace net revenue:
NetRev_t = GMV_t × TakeRate_t − Incentives_t − Refunds/Penalties_t. - CM1% movement (price–cost gap):
ΔCM1% ≈ ΔRealizedPrice% − ∑(cost share_i × ΔUnitCost_i%). - CTS items:
ReturnsDrag/order ≈ ReturnProb × (ReverseLogCost − RecoveredValue) + SupportMinutes × Rate.
PaymentFeeDrag ≈ Card‑processed VRR × bps.
Cloud/API CTS ≈ UnitFee × Activity × OnDemandShare. - CAC payback (months):
Payback = CAC ÷ Monthly CM2 Contribution from the acquired cohort. - Working capital:
DSO = (Avg AR ÷ Credit Sales) × Days; DIO = (Avg Inventory ÷ COGS) × Days; DPO = (Avg AP ÷ Purchases) × Days; CCC = DSO + DIO − DPO.
For recurring, compute Net CCC = CCC − Days Contract Liability.
QA and model hygiene (use this as a build checklist)
- Base month reconciles to last actuals; LTM to GL; price waterfall aligns to discount and fee files.
- CM1/CM2 reconcile to the margin decomposition (13.2); working‑capital lines reconcile to the diagnostic (13.3).
- No hard‑codes in calc sheets; assumption cells tagged with source and date.
- Rollups use ratio‑of‑sums; FX overlay separate from constant‑currency core.
- Error flags for negative inventories, impossible payback, covenant breaches; a summary “red bar” on the outputs.
- A second person can trace any KPI to a single assumption in ≤3 clicks (write this into acceptance criteria).
Common traps—and the fix
- Trend‑line revenue with no operational logic. Fix: replace with ARR roll‑forward, order math, or GMV × take‑rate, tied to funnels and capacity.
- Treating promotions as growth. Fix: model realized price with the full waterfall; show promo dependence and price power separately.
- Unit‑cost optimism. Fix: split rate vs. efficiency; include on‑demand cloud share and third‑party API fee escalators.
- Working‑capital hand‑waving. Fix: DSO/DIO/DPO by segment; add processor reserves and deductions explicitly; wire a 13‑week cash view.
- Scenario bloat. Fix: three anchored cases plus one exposure‑specific case (rate, energy/FX) is plenty; each must move revenue, CM2, cash, and headroom.
- No owners. Fix: every red cell on the dashboard maps to a named lever and a date.
72‑hour sprint plan (from blank page to decision‑grade model)
- Day 0: Freeze scope, segmentation, horizon, definitions (VRR→CM1→CM2), perimeter, and constant‑currency policy. Create Assumptions Hub with owner/source/confidence fields.
- Day 1: Build Revenue modules by model type (SaaS, e‑comm/transactional, marketplace, hardware+service) and reconcile the first forecast month to last actuals; drop in price waterfall logic.
- Day 2: Add COGS and CTS drivers; connect to CM1/CM2; wire Opex/Capex; build working‑capital mechanics; add debt and covenant pages.
- Day 3: Stand up the Scenario Manager with soft‑landing, recession, and stagflation presets (11.3); finish outputs (P&L, cash, covenants, valuation bridges). Run a verifier pass and issue the one‑page “Levers & Owners.”
Acceptance criteria for a decision‑grade driver model
- Single source of truth; monthly for 24–36 months; organic and constant currency; segments aligned to Chapter 6.
- Revenue modules are driver‑based (ARR roll‑forward, order math, or GMV × take‑rate), reconcile to the last actual, and reflect price realization from the waterfall.
- CM1/CM2 built from cost drivers; CTS explicit (shipping, returns, payments, support, SLA credits, cloud/API).
- Opex/Capex capacity‑based; working capital mechanical; debt and covenants computed monthly; FX overlay separate.
- Scenario Manager toggles macro, price power, input costs, reliability penalties, financing, FX, and working capital; outputs update everywhere.
- QA checks in place; ratio‑of‑sums applied; no circular references; second‑person reproduction passed; every key assumption has an owner, a source, and a confidence label.
Build the model this way and you will have a compact, auditable engine that turns historical truth into a forward range, ties every dollar to an operational lever, and tells management and investors—clearly—what must be true for the plan to hold and where to act first when the world shifts.
14.2 Scenario Definition Checklist
Scenarios are disciplined “worlds you can run,” not colorful labels. Each one must (i) be externally anchored, (ii) translate macro and platform/policy changes into micro‑drivers by segment and route‑to‑market, (iii) state what management is allowed to do (with timing and cost), and (iv) output a complete path for revenue, CM2, cash, and covenant headroom. If a case cannot be parameterized, reproduced, and governed, it is not a scenario—it’s a story. Use this checklist to define three to five structurally different cases and wire them into the driver‑based model from 14.1 and the macro/policy guidance in Chapter 11.
Set the ground rules (freeze these before modeling)
- Horizon and cadence: monthly for 8–12 quarters; annual thereafter if needed.
- Perimeter and currency: organic, constant‑currency core with a separate FX overlay.
- Segmentation lens: same cuts as Chapters 6–13 (product family × route‑to‑market × region × customer tier).
- Materiality threshold: only define/maintain a scenario if it moves ≥2% revenue, ≥100 bps CM2, ≥50 bps covenant headroom, or ≥50 bps WACC.
- Governance: one owner per scenario; refresh anchors monthly; re‑score triggers at least quarterly.
Choose structurally different cases (not louder versions of the same)
Anchor three “always‑on” cases and add up to two exposure‑specific ones if the business warrants it.
- Soft‑landing / benign disinflation.
- Recession (volume shock; easing rates; wider spreads).
- Stagflation (weak growth, sticky inflation; limited monetary relief).
- Optional overlays, picked from your exposure map: rate‑shock/funding squeeze; energy/FX shock; policy/platform tightening; supply disruption or quality recall; dependency shock (critical vendor/API/payment rail); cyber/privacy incident.
Draft a Scenario Charter (copy‑ready fields, one page per case)
- Name and scope; horizon; segmentation.
- External anchors and timestamp (macro paths, policy/platform thresholds).
- Parameter set (see dictionary below) with units, lags, and low/base/high corridors.
- Allowed management actions: which levers you permit, with owner, start date, and cost (price moves, indexation, channel reweighting, capacity rungs, reserved cloud commitments, SCF, staffing/OT changes).
- Disallowed actions: moves you will not assume in‑case (e.g., “no net headcount reduction,” “no list price cuts without fence changes”).
- Model hooks: named drivers in 14.1 that these parameters change.
- Outputs to watch: revenue, CM2, cash, covenant headroom (by segment/channel); NRR; CAC/payback.
- Trigger dashboard: the two to three early‑warning indicators that flip you into this case (with thresholds and required evidence).
- Confidence label (H/M/L) and verifier initials.
Parameter dictionary (define once, then reuse across cases)
When you set a parameter, always specify the unit, timing/lag, path (monthly), and, where appropriate, a corridor (low/base/high).
- Demand & mix
- End‑market proxy path by region (e.g., PMI new orders, housing starts, IT spend).
- Elasticity by segment/route; cross‑elasticities where substitutes matter.
- Mix constraints (e.g., enterprise share capped by certification timing).
- Pricing & promo
- Realized price corridor by segment; list change cadence; discount guardrail shifts; promo depth/frequency; indexation on/off and floors/caps.
- Input costs
- Commodity baskets; energy; inbound freight/duty; third‑party API/cloud rates; supplier rebates.
- Pass‑through ability and lag by segment/contract type.
- Capacity & reliability
- Constraint utilization ceiling; surge policy and cost; implementation throughput; SLA credit ladders; return and defect rates.
- Labor & service
- Wage inflation; staffing availability; productivity drift; support contact rate and minutes per contact; field/installation crew utilization.
- Financing & FX
- Base rate + spread; refinancing windows; revolver rules; hedging bands; FX paths for major pairs.
- Working capital
- DSO by tier/channel; processor reserves/holdbacks; DIO by family/site; DPO on purchases; deferred revenue cadence (for recurring); deduction/chargeback rates and cycle times.
- Policy/platform/ESG gates
- Take‑rate/penalty thresholds; new certification/attestation milestones; packaging/EPR fees; residency/data obligations; carbon/energy price overlays.
Separate “weather” from “response” (two runs per case)
- Passive case: apply scenario weather only; no management action beyond BAU cadence.
- Managed case: add allowed actions (priced, dated, and owned).
Always show the delta and the cash cost of action. Never bake the response into the weather.
Orthogonality and conservation checks (keep the math honest)
- Do not silently improve mix or discount discipline when demand falls unless you model the operational move that causes it (e.g., shuttering low‑ROI channels).
- Apply lags realistically: price pass‑through trails cost; DSO shifts after demand shocks; returns spike with a delay.
- Constrain bookings to capacity headroom and service SLOs; cap enterprise mix if certifications are not in place.
- Use ratio‑of‑sums for rollups; state whether corridors represent independent or correlated moves.
- Reconcile Year‑1 deltas to bridges (price, volume, mix, CTS, working capital). If it doesn’t reconcile, it doesn’t stand.
Triggers and early‑warning indicators (define now, not later)
Pick two to three observable signals per case and set “flip rules.” Examples:
- Demand: PMI new orders below 48 for two consecutive months; pipeline coverage <3× next‑quarter target; orders‑to‑inventories ratio deteriorating for two months.
- Pricing/costs: competitor price index down >3% QoQ; carrier GRIs + surcharges announced; commodity index up/down by set thresholds.
- Service/returns: SLA credits >X bps of revenue for two months; OTIF below Y%; return/chargeback rate above platform threshold.
- Finance: processor reserve +100 bps; BBB spread +150 bps; DSO tail >90 days rising for two months; borrowing‑base headroom under set buffer.
- Policy/platform: take‑rate or penalty rule change notices; certification deadlines within 90 days without on‑track status.
Write the play on flip: the three moves you will execute immediately (pricing fence, channel mix shift, capacity or spend pivot).
Management actions library (only moves you can actually execute)
- Pricing and indexation (fences, guardrails, cadence, and customer comms).
- Channel reweighting (marketplace vs. direct; partner tiers; paid placement caps).
- Capacity ladders and service (shift patterns, temp labor, expedite rules, SLO tradeoffs).
- FinOps (reserved/savings commitments; hotspot fixes; third‑party API plan changes).
- Working capital (terms enforcement, SCF, early‑pay APR logic, inventory re‑tiering, deduction triage).
- Risk posture (premium SLAs with critical vendors; hedging bands; inventory buffers).
- Pipeline and spend (CAC guardrails, program cut lines; sales capacity controls).
Every action needs: owner, start date, lead time, one‑time cost, and expected monthly effect on revenue/CM2/cash.
Output package (what every scenario must produce)
- P&L and cash by month; covenant headroom by quarter.
- Bridges vs. last actuals and vs. baseline: price, volume, mix, CTS, opex, working capital.
- Segment/channel pages: revenue, CM2, NRR, CAC/payback, cash contribution.
- One‑page narrative: “what changed, why, and who does what next” with dated actions and costs.
- Term‑sheet translation: indexation clauses, inventory/service covenants, certification CPs, indemnities, earnouts tied to NRR or price realization.
Quality controls (accept no scenario without these ticks)
- Anchors and sources dated on the Scenario Charter; parameters expressed with units, lags, and corridors.
- Passive vs. managed runs both shown; management actions costed and owned.
- Deltas reconcile to driver bridges; FX overlay separate from constant‑currency core.
- Capacity and policy constraints enforced; covenant math computed monthly.
- Early‑warning triggers and playbooks documented; dashboard hooks defined.
- Verifier can reproduce each case from the Charter in ≤3 clicks.
Common traps—and the fix
- Too many cases with tiny differences → hold to three core plus at most two exposure‑specific.
- Point estimates everywhere → switch to corridors and show output fans; pick actions robust across the band.
- Hidden optimism in pricing and mix → tie price corridors to elasticity evidence and fences (Ch. 6.4, 8.2); cap mix by certification and capacity.
- Ignoring cash and headroom → every case must show 13‑week cash in the downside and quarterly covenant headroom.
- Response magic → if you cannot name the owner, cost, and start date, it’s not allowed in the managed case.
72‑hour sprint plan (from blank page to decision‑grade cases)
- Day 0: Freeze scope, segments, horizon, thresholds; draft Scenario Charter shells; import baseline and two alternates from Chapter 11; assemble exposure map by segment/channel.
- Day 1: Fill parameter dictionary with monthly paths/corridors and lags; define allowed actions with owners and costs; wire toggles into the model (14.1); run passive vs. managed for baseline and recession.
- Day 2: Add stagflation and one exposure‑specific case (e.g., rate‑shock or platform tightening); reconcile deltas to bridges; define triggers and flip rules; write one‑page narratives.
- Day 3: Publish outputs (P&L, cash, headroom, segment pages); propose term‑sheet protections; set refresh cadence; log verifier sign‑off.
Acceptance criteria (use this as your “done” checklist)
- Three to five externally anchored, structurally different cases; Scenario Charters complete; parameters expressed with units, lags, and corridors.
- Passive and managed versions of each case, with action costs and owners; deltas reconcile to bridges.
- Monthly outputs for revenue, CM2, cash, and covenant headroom; downside includes a 13‑week cash view.
- Triggers and playbooks defined and linked to the KPI dashboard; governance cadence published.
- Verifier reproduction passed; term‑sheet translations documented for residual uncertainties.
Build scenarios with this rigor and “downside” or “upside” stops being a mood. You will have a compact set of climates—each wired to micro‑drivers, explicit actions, cash math, and terms—so you can underwrite outcomes with confidence and know exactly what to do when the world shifts.
14.3 Sensitivity Analysis Template
Sensitivity analysis is the microscope for your driver‑based model. Scenarios (14.2) tell you what happens when the world changes; sensitivities tell you what happens when a lever moves—price by 1%, take‑rates by 50 bps, DSO by 10 days—holding everything else constant. Done well, it ranks the assumptions that matter, quantifies local elasticity around the base case, exposes nonlinear “cliffs” (penalty ladders, covenant breaches), and turns debate about opinions into debate about slopes. Your outputs should flow straight into the valuation range, the term sheet (indexation, covenants, earnouts), and a 90‑day action list with owners.
Design principles (freeze these before you compute)
- Segmented, not averaged. Compute and display sensitivities by product family × route‑to‑market × region × customer tier; average sensitivities hide risk.
- Comparable shocks. Use standardized step sizes (by driver type) so a tornado chart ranks drivers on a fair basis.
- Local first, then wide. Measure local slopes around your base and probe curvature to detect nonlinearities and threshold effects.
- Orthogonality. One driver at a time for sensitivities; use two‑way grids only for chosen pairs where cross‑effects are economically real.
- Auditable. Every sensitivity has a unit, a step size, a date‑stamped source, and an owner; a second person can reproduce it in ≤3 clicks.
- Economic. Always compute impact on CM2, cash, covenant headroom, and, where relevant, NRR and CAC payback—not just revenue or EBITDA.
Step‑by‑step build (from blank sheet to decision‑grade)
1) Fix the target outputs and the measurement window
Select one primary output for ranking (e.g., Year‑2 CM2 dollars or Month‑12 cash), plus the short list you will show on each card: Revenue, CM2 %, CM2 $, Cash, Covenant Headroom, CAC payback, NRR. Choose the period(s) that matter (Month‑6 and Month‑18; Year‑1 and Year‑2; LTM at exit).
2) Assemble the Sensitivity Register (shortlist the levers)
Start from the driver register (14.1) and shortlist 15–25 candidate levers that plausibly move ≥100 bps CM2 or ≥2% cash within your horizon. Classify each by controllability (high/low), lead time (≤90 days/>90 days), and confidence (H/M/L). Typical families:
- Demand & mix: conversion rates, attach/penetration, enterprise vs. SMB share.
- Pricing & realization: list change cadence, discount guardrails, promo depth, take‑rates/fees, return/chargeback rates.
- COGS & unit cost: commodity baskets, labor rates, inbound freight/duty, cloud/third‑party API rates and on‑demand share.
- Cost‑to‑Serve: shipping/accessorials, support contact rate and minutes, SLA credits.
- Working capital: DSO by tier, processor reserves, DIO (weeks of supply), DPO on purchases, deduction cycle times.
- Finance & FX: base rate + spread, FX pairs, borrowing‑base advance rates.
- Policy/platform: penalty thresholds, certification timing, packaging/EPR fees.
- Capacity/reliability: constraint utilization ceilings, implementation throughput.
3) Set standardized step sizes (your “shock library”)
Define once, then reuse so your tornado is meaningful. Adapt for the business, but start with:
- List price / realized net price: ±1 percentage point (pp) on list; ±100 bps on realized net.
- Volume / conversion / attach: ±5% relative.
- Take‑rate / platform fees: ±50 bps.
- Returns / chargebacks: ±200 bps absolute on the relevant rate.
- Commodity / inbound freight / energy: ±10% relative.
- Wage inflation: ±5% relative.
- Cloud/API unit price: ±10% relative; on‑demand share: +10 pp.
- Payment processor reserve: +100 bps of GMV.
- DSO / DIO / DPO: +10 / +7 / −5 days (purchases‑based DPO).
- Base rate: +200 bps; FX: ±10% on major pairs.
- SLA credits: +50 bps of revenue.
Document deviations where the step would breach a contractual fence or an operational bound.
4) Compute local slopes and elasticities (clean math, no plugs)
For a driver XXX with base x0x_0x0 and output YYY with base y0y_0y0:
If ∣κ∣|\kappa|∣κ∣ is large (e.g., >0.2), label the driver nonlinear and expand the range or use a two‑way grid near thresholds.
5) Rank and visualize (tornado + spider)
- Tornado: For your primary output, compute the absolute change from the standardized shock for each driver and sort descending. Produce one tornado per major segment/channel so trade‑offs are not diluted by mix.
- Spider: For the top five drivers, show a spider chart with output vs. driver change (−10% to +10% or appropriate range) to reveal asymmetry and curvature.
6) Build two‑way grids for the two or three critical pairs
Where interactions are economically real (e.g., price realization × demand elasticity; return rate × paid placement; base rate × DSO; FX × input cost), run a 5×5 grid. Shade “danger zones” (negative cash, covenant breach, NRR < 100%) and annotate the iso‑lines where those thresholds flip.
7) Compute break‑evens and cliffs (solve the “what has to be true”)
Use goal seek or a bisection routine to find the driver value where you hit a threshold.
- Price–cost break‑even: required realized price lift to hold CM2 when input costs rise.
- Liquidity break‑even: DSO (or reserve %) at which minimum liquidity buffer is breached in Month‑X.
- Coverage break‑even: base rate at which interest coverage or leverage covenant trips.
- Channel‑policy cliff: return or late‑ship rate where marketplace penalties escalate tiers.
Publish each as a Sensitivity Card with the driver level, the month of breach, and the first three mitigations.
8) Portfolio‑level “value at risk” (delta method, optional but fast)
If you have historical or scenario‑based variance for drivers and a correlation matrix, approximate output variance: Var(Y)≈g⊤Σg\mathrm{Var}(Y) \approx g^\top \Sigma gVar(Y)≈g⊤Σg, where ggg is the vector of semi‑elasticities. Report p10/p90p10/p90p10/p90 bands and which covariances dominate. This is a quick way to turn your tornado into a probabilistic view without full Monte Carlo.
9) Probabilistic sensitivity (Monte Carlo, optional extension)
For high‑uncertainty levers (demand, FX, base rate, returns), assign distributions and correlations, run 5,000–10,000 draws, and report median, p10/p90p10/p90p10/p90, and the probability of cash < 0 or covenant breach by Month‑X. Keep distributions and correlations in the Assumptions Hub with sources and dates.
Templates you can copy directly
A) Sensitivity Register (one line per driver)
- Driver name and unit; segment/channel; base value; standardized step (absolute or %); formula hook (cell/range); source & date; owner; confidence (H/M/L); controllability (H/L); lead time (≤90/>90 days); outputs tracked (Revenue, CM2 $, CM2 %, Cash, Headroom, NRR, CAC payback).
B) Sensitivity Card (one per top driver × segment)
- Driver and unit; base and step; local semi‑elasticity (ΔY/ΔX) and elasticity; curvature flag; ΔCM2 $, ΔCash, ΔHeadroom, ΔNRR, ΔPayback under the standardized shock; two‑way grid partner (if any); break‑even value and breach month; three mitigations with owners and timing.
C) Tornado Spec
- Output metric and period; standardized shocks used; segment/channel scope; ranked list of drivers with absolute dollar impact; confidence labels; footnotes for nonlinear/threshold drivers.
D) Two‑Way Grid Spec
- Pair of drivers; ranges and step counts; outputs to visualize; thresholds to overlay (cash = 0, headroom = 0, NRR = 100%); annotations for “danger corners”; decision rule (“if in zone A, then execute actions 1–3”).
E) Break‑Even Finder
- Constraint (e.g., CM2 ≥ target, Headroom ≥ 10%); variable to solve; search range and tolerance; solution; first‑order plan (price fence, channel reweighting, FinOps, terms enforcement).
Practical guidance and guardrails
- Reset state between runs. Ensure each shock starts from the same base; cache flushing avoids compounding.
- Respect fences. Do not test price moves that violate contract indexation rules or channel parity clauses (link to 8.2 and 11.4).
- Discretes ≠ derivatives. For on/off items (certification achieved, second source live), compute a discrete delta (Y_on − Y_off), not a derivative.
- Capacity caps. Clamp bookings where constraint utilization exceeds your ceiling or SLOs; sensitivities that assume unlimited capacity are fiction (10.2, 12.1).
- Monotonicity checks. If a tiny increase in X produces a non‑monotonic Y, you likely hit a step function (tiered take‑rates, penalty ladders); mark as nonlinear and grid it.
- Ratio‑of‑sums. When rolling up across segments, recompute the output at the portfolio level; do not average segment elasticities.
Business‑model‑specific lenses (pick the ones that apply)
- SaaS/usage: price re‑rating vs. expansion; entitlement accuracy (credits) as a driver of realized price; uptime/latency → SLA credits → NRR; cloud unit‑cost and on‑demand share → CM2.
- E‑commerce/DTC: returns and chargebacks; free‑shipping threshold hit rate; freight accessorials; paid‑share of traffic; promo depth vs. conversion.
- Marketplaces: take‑rate tiers; late‑ship/OTIF penalties; search rank and paid placement elasticity; dispute/fraud loss rates.
- Hardware + service/B2B distribution: inbound freight and duties; yield/scrap; service attach; warranty claim rates; project milestone slip.
Turn sensitivities into decisions
- Levers × Impact map: 2×2 of impact (from tornado rank) vs. controllability; act immediately on high–high, seek terms or structure for high–low (indexation, SLAs, covenants, earnouts).
- Term‑sheet translations:
- High price sensitivity, low control → indexation clauses, price‑fence flexibility, earnouts tied to realized price.
- High working‑capital sensitivity → inventory/service covenants, SCF targets, reserve caps.
- High take‑rate/platform sensitivity → channel diversification covenants; paid‑placement caps; special indemnities for known investigations.
- Operating plan: top five drivers become Day‑1 initiatives with quantified ΔCM2 and ΔCash and dated owners.
Early‑warning indicators (derive straight from the top five sensitivities)
- Competitor price index and promo depth; realized price slippage vs. fences.
- Returns/chargebacks; processor reserve notices; penalty letters from marketplaces/retailers.
- Cloud on‑demand share; third‑party API overage; SLA credits.
- DSO tail (>90 days); deduction backlog age; DPO slip on top vendors.
- Base‑rate and spread jumps; FX pairs outside corridor.
Red flags—and how to respond
- One driver dominates the tornado (e.g., take‑rate) → diversify exposure or hard‑wire terms; move growth in that channel to the upside case.
- Nonlinear cliffs near base (penalties, capacity, covenant) → build and run two‑way grids; set explicit flip triggers and playbooks.
- Top levers are low‑control (macro, policy) → translate into structure (indexation, CPs, indemnities, earnouts) and scenario governance.
- Opposite‑sign sensitivities across segments → stop portfolio averages; manage mix actively with price fences and channel reweighting.
72‑hour sprint plan (from blank page to a board‑ready pack)
- Day 0: Freeze outputs, periods, segments, and the shock library; populate the Sensitivity Register with sources, owners, confidence.
- Day 1: Compute local slopes via central difference for all drivers; flag nonlinear items; produce segment‑level tornados; draft the top ten Sensitivity Cards.
- Day 2: Build two‑way grids for the top three pairs; run break‑even finders for liquidity and coverage; translate findings into term‑sheet hooks and Day‑1 actions.
- Day 3: Publish the pack: tornados, spiders, grids, break‑evens, and a Levers × Impact map with owners and dates; wire early‑warning indicators into the KPI dashboard; update the model toggles and valuation range.
Acceptance criteria (use this as your “done” checklist)
- Sensitivity Register complete and segmented; step sizes standardized and documented.
- Local semi‑elasticities and elasticities computed via central difference; curvature flagged; non-linears gridded.
- Tornado charts by segment with absolute dollar impact on the primary output; spiders for top five drivers.
- Two‑way grids and threshold/break‑even values for critical pairs and constraints (cash, headroom, NRR).
- “Levers × Impact” map and a 90‑day action list with owners, costs, and expected ΔCM2/ΔCash.
- Early‑warning indicators derived from the top sensitivities and added to the dashboard; verifier reproduction passed.
Build your sensitivities this way and you will replace hand‑waving with slopes, discover where the plan is fragile or robust, and equip management with a short, dated list of moves that change cash, margin, and headroom within your hold period.
14.4 Model Integrity Quality-Assurance Checklist
A forecast model is only as good as the trust you can place in its numbers. Quality assurance (QA) is how you convert a complex, driver‑based model into something an Investment Committee can underwrite and operators can run. Think of QA as layered defenses: structure, reconciliation, arithmetic, cross‑module consistency, stress behavior, governance, and documentation. The goal is not perfection—it is decision‑grade reliability: every figure traceable to an assumption with a source and owner; every bridge reconciling; every scenario repeatable; every failure mode anticipated.
Guiding principles (use these to judge every check)
- Segmentation first. Validate at the same cuts you model (product × route‑to‑market × region × customer tier). Portfolio totals can mask defects.
- Reproduce, don’t believe. A second person must recreate any headline number from the assumption cell in ≤3 clicks.
- Ratio‑of‑sums. Rollups recompute from atomic units; never average ratios.
- Weather vs. response. Separate external shocks from management actions; show the delta and the cost.
- Document deviations. If you depart from a rule (e.g., hosting classification), write it on the page.
Layer 1 — Structural hygiene
- Separation of concerns. Assumptions on one sheet; calculations on others; outputs on dedicated pages. No inputs in calc sheets.
- Naming & units. Named ranges for key drivers, with units, time basis (monthly), and currency. Label corridors (low/base/high).
- No hard‑codes in formulas. Constants live in the Assumptions Hub with owner, source, and date.
- Consistent segmentation. Every module carries the same dimensions; no “Other” catch‑alls without definition.
- Time index integrity. Single calendar spine; start/end flags for each business line; no orphan months.
- Protected structure. Lock formula cells; highlight input cells; avoid volatile functions and external links; remove dead sheets.
Layer 2 — Reconciliation to source truth
- Last‑actual tie‑out. First forecast month equals last booked month for revenue, CM1, CM2, opex, cash—no plugs.
- LTM reconciliation. LTM figures match the GL within agreed tolerance; differences logged.
- Constant‑currency & perimeter. FX and M&A effects isolated before modeling; policy written on page.
- Bridge alignment. Revenue bridge (13.1) and margin tree (13.2) reproduce inside the model; differences explained.
- Working‑capital linkage. AR/AP/Inventory match trial balance; DSO/DIO/DPO match the diagnostic (13.3).
Layer 3 — Arithmetic and accounting integrity
- Balance sheet balances. Monthly. Cash bridge closes exactly.
- Cash is king. Operating cash flow equals EBITDA − ΔWC − cash taxes + non‑cash add‑backs ± timing.
- No silent circularity. Iteration off unless intentionally used (and documented) for interest or working‑capital feedbacks.
- No negative impossibles. Guardrails for negative headcount, negative inventory, or negative tax bases; error flags visible.
- Tax logic. Effective rate policy and NOL usage documented; cash vs. book tax timing clear.
Layer 4 — Cross‑module consistency
- Pricing waterfall → CM2. List/discounts/promos/take‑rates from 8.2 feed realized price and show up in CM2 via returns, fees, and penalties.
- Capacity rungs gate bookings. Throughput caps (10.2) constrain revenue and service SLOs (10.3); no growth beyond headroom.
- Working capital reacts to growth. DSO/DIO/DPO move with mix, seasonality, and channel; deferred revenue (recurring) offsets CCC.
- Tech costs behave with usage. Cloud/API costs follow actives or API calls and on‑demand share (12.1/12.2).
- Scenario toggles only touch intended cells. 14.2 parameters map one‑to‑one to drivers; a toggle map is published.
Layer 5 — Scenario and toggle QA
- Passive vs. managed runs. Weather‑only and weather‑plus‑actions both compute; deltas and action costs visible.
- Trigger discipline. Early‑warning indicators defined (11.3) and wired to scenario switches.
- Orthogonality tests. One toggle at a time changes the intended outputs; cross‑effects only where modeled.
- Lag realism. Price pass‑through and DSO shifts obey realistic lags; returns spike with delay after demand shocks.
Layer 6 — Sensitivity QA (14.3 alignment)
- Standardized shocks. Step sizes set once (e.g., ±100 bps realized price, +10 days DSO).
- Central‑difference slopes. Use symmetric moves for local response; label nonlinear drivers and grid them.
- Capacity & cliffs. Sensitivities respect capacity caps, penalty ladders, and covenant thresholds.
- Break‑even solves. Publish required driver levels for cash‑zero, headroom‑zero, CM2‑target.
Layer 7 — Data integrity and validation
- Type and range checks. Dates are dates; rates in 0–1; negative signs consistent (refunds/chargebacks).
- Deduplication and completeness. No double‑counted segments; every row sums to a parent; zero “mystery” residuals.
- Outlier scans. Discounts, return rates, and unit‑cost distributions flagged beyond p99.
- Seasonality sanity. Monthly patterns align to history unless assumptions justify change (and cite it).
Layer 8 — Performance and robustness
- Calc time budget. Full recalc under 5 seconds on a standard laptop or clearly documented otherwise.
- Lean formulas. Prefer INDEX/XMATCH to volatile OFFSET/INDIRECT; avoid array explosions.
- Stress runs. Extreme scenarios (zero demand, 2× demand, 300 bps rate spike, +10 pp on‑demand cloud share) complete without errors; errors flagged, not hidden.
Layer 9 — Governance, versioning, and audit trail
- Versioning. Semantic version number; change log describing what changed, why, and by whom.
- Assumption registry. Every driver: value, source link, extract date, owner, confidence (H/M/L).
- QA log. Tests executed, result (pass/fail), exceptions, remediations, verifier initials, date.
- Three lines of defense. Builder self‑check → peer verifier → partner sign‑off.
- Readme. One‑page “how to use” with module map, toggle map, and acceptance criteria.
- Clean‑team compliance. No PII or sensitive competitor data outside the ring‑fence; exhibits source‑coded.
Layer 10 — Documentation you can hand to IC
- Model dictionary. Definitions, formulas, units, and accounting policies (VRR → CM1 → CM2).
- Scenario charters. For each case: anchors, parameters, allowed actions, triggers, outputs (14.2).
- Sensitivity register. Drivers, step sizes, elasticities, break‑evens (14.3).
- Bridges on a page. Revenue and CM2 bridges from last actuals to Year‑1/Year‑2 for baseline and downside.
- Covenant tracker. Headroom by quarter; breach month flags; playbook on flip.
Smoke test (run this before any share)
- Does the model balance and cash tie?
- Can a second person find and edit the three biggest drivers in ≤3 clicks and see outputs update?
- Do baseline Year‑1 bridges reconcile to price/volume/mix/CTS/working‑capital deltas?
- Do passive vs. managed scenarios differ only by allowed actions—and is the cash cost of action explicit?
- Do top‑five sensitivities match the story told in the IC memo?
Copy‑ready QA artifacts (paste these into your workbook)
Model QA Checklist (tick before release)
- Structure: separation, naming, units, protection.
- Reconciliations: last‑actual tie‑out; LTM vs. GL; constant‑currency; perimeter; 13.1/13.2 alignment.
- Arithmetic: balanced BS; cash bridge; no negative impossibles; documented tax logic.
- Cross‑module: pricing waterfall → CM2; capacity caps; WC behavior; cloud/API scaling; scenario toggle map.
- Scenario & sensitivity: passive vs. managed; standardized shocks; nonlinear flags; break‑evens.
- Data hygiene: types, ranges, outliers, seasonality; dedup & completeness.
- Performance: recalc time; extreme runs; no volatile function abuse.
- Governance: version, change log, assumption registry, QA log; three‑line sign‑off; clean‑team compliance.
QA Sign‑Off Block (append to the front sheet)
- Model version & date.
- Scope (horizon, segmentation, perimeter, currency).
- Exceptions to standards (with rationale).
- Verifier name & date; Partner approver & date.
- Statement: “This model reconciles to last actuals and LTM, applies the pricing waterfall and CM2 definitions, enforces capacity and policy constraints, and produces passive vs. managed scenarios and standardized sensitivities. Residual risks: [list].”
Red flags—and immediate fixes
- Hidden plugs to make cash tie. Remove; rebuild the cash bridge; surface timing differences explicitly.
- Scenario toggles change the wrong metrics. Publish a toggle map; refactor links; add cell‑protection.
- Capacity ignored. Clamp bookings; introduce headroom ladders; propagate to service SLOs and SLA credits.
- Promotions treated as growth. Move promo depth to realized price; reflect CTS and payback effects.
- On‑demand cloud share rising silently. Add FinOps toggles; unit‑cost dashboards; reserved commitment milestones.
- Covenant math annual only. Switch to monthly computation; add cure periods and triggers.
Early‑warning indicators (keep these live post‑close)
- Recalc time jump; new external links; proliferation of volatile functions.
- Rising residuals in reconciliations; “Other/Misc” bars growing.
- Divergence between dashboard KPIs and model outputs.
- Frequent manual overrides in calc sheets; increase in low‑confidence assumptions.
- Scenario triggers firing without corresponding model switch or playbook execution.
72‑hour QA sprint plan (from draft to decision‑grade)
- Day 0: Structure & reconciliation. Lock definitions (VRR → CM1 → CM2), segmentation, perimeter, FX policy. Tie first forecast month to last actual; LTM to GL; publish the Readme and version.
- Day 1: Arithmetic & cross‑module. Balance sheet and cash tie; revenue/margin/working‑capital bridges inline; capacity caps enforced; pricing waterfall feeding CM2.
- Day 2: Scenarios & sensitivities. Passive vs. managed runs for baseline and recession; standardized shocks; top‑five sensitivities and two‑way grids; break‑even finds; covenant headroom chart.
- Day 3: Governance & sign‑off. Fill assumption registry and QA log; run the reproduction test; record exceptions; finalize Sign‑Off Block with verifier and partner approvals.
Acceptance criteria for a decision‑grade model
- Structure clean; inputs isolated; units and segmentation consistent; no hard‑codes in calc sheets.
- Reconciles to last actuals and LTM; constant‑currency and perimeter treatment explicit; bridges align with 13.1/13.2.
- Balanced statements; cash bridge ties; capacity, policy, and WC constraints enforced; FX overlay separate.
- Scenario Charters (14.2) implemented with passive vs. managed; standardized sensitivities (14.3) with break‑evens; covenant math monthly.
- Assumption registry and QA log complete with owners, sources, dates, confidence; version and change log current; clean‑team compliance observed.
- Verifier reproduction test and partner sign‑off completed; residual risks disclosed with proposed term‑sheet protections.
Run QA with this rigor and your model ceases to be a fragile spreadsheet. It becomes a reliable decision engine—auditable, repeatable, and ready for the pressure of the Investment Committee and the realities of operating the business on Day 1.
