A financial model can contain no visible spreadsheet errors and still give management the wrong answer. The dangerous failures are usually quieter: revenue without capacity, profit without cash, a downside case that still references base-case assumptions, or history that never tied to the source statements.
Short answer: validate the decision chain, not only the formulas. Start with the source records, trace operating drivers into the statements, prove the cash roll-forward, break the scenario switches on purpose, and identify which assumptions can reverse the decision.
This is management-model validation. It is not an audit, review, compilation, fairness opinion, or tax engagement.
1. Write down the decision first
Before opening the workbook, finish this sentence: “Management will use this model to decide whether to ___.”
A model built to set next quarter’s staffing needs requires a different standard from one supporting a loan request or acquisition. Without the decision, reviewers spend hours polishing cells that cannot change the answer.
Record four items:
- The decision and the person who owns it.
- The decision date.
- The model horizon and reporting interval.
- The threshold that changes the answer: cash minimum, return, covenant, capacity, or margin.
2. Reconcile the opening position
The first forecast period should begin from a historical position that ties to a source. At minimum:
- Revenue and expenses tie to the management P&L.
- Cash ties to the bank or balance sheet.
- Accounts receivable and payable tie to their aging schedules.
- Debt ties to lender statements.
- Headcount and payroll tie to a payroll register.
If the opening point is wrong, a perfectly constructed forecast only produces a precise continuation of the wrong starting point.
3. Separate inputs, calculations, and outputs
The test is not whether the tabs have different colors. The test is whether a user can identify every assumption that management controls without searching through formulas.
An input should have an owner, unit, source, and effective date. “Growth = 12%” is incomplete. Growth in what: orders, price, locations, technicians, or total revenue? Who supplied it? When does it begin?
4. Build a revenue bridge
Revenue should decompose into operating drivers. For a service business, that may be:
available appointments × utilization × close rate × average job value
For a product business:
units × realized price, separated by channel or product family when mix matters.
Then build the bridge from the last actual period to the forecast. If volume rises 8% and price rises 4%, the compounded increase is 12.32%. A 14% revenue forecast therefore contains an unexplained 1.68 percentage points. At $4.8 million of prior-year revenue, that missing bridge equals $80,640, about $81,000.
That is a worked example, not a client result.
5. Tie demand to capacity
Forecasts often treat labor as a percentage of revenue while simultaneously assuming the same team delivers more volume. Test the operating units.
If demand requires 11,240 productive hours and the roster supplies 9,880 hours after vacation, training, meetings, absence, and other shrinkage, the plan is 1,360 hours short. The revenue forecast exceeds deliverable capacity by 13.8%.
The correction might be hiring, overtime, productivity, price, mix, or a lower forecast. The model should expose the choice rather than quietly assume it away.
6. Prove gross margin by mechanism
Do not allow gross margin to improve because someone typed a higher percentage.
Bridge the change through price, material cost, labor rate, labor productivity, freight, discounting, returns, subcontracting, and mix. A two-point improvement built from a price increase and lower material cost is inspectable. A two-point improvement sitting in one assumption cell is hope.
7. Roll working capital into cash
Profit is not cash. If the customer mix changes, receivable days may change. If growth requires inventory or deposits, cash may leave before revenue arrives. If vendors shorten terms, accounts payable stops financing the business.
Validate:
- Receivable days by meaningful customer type.
- Payable days against actual vendor behavior.
- Inventory turns or work-in-process assumptions.
- Deposits and deferred revenue.
- Payroll and tax timing.
- Capital expenditures and debt service.
8. Make cash a result, never the plug
Ending cash should equal beginning cash plus operating, investing, and financing cash flow. If cash exists only to make the balance sheet balance, the model can be mechanically balanced and economically broken.
A simple control row should show zero for every period:
ending cash − beginning cash − net cash movement = 0
9. Test formula consistency
Review formulas across time and across comparable business units. Look for:
- A single hard-coded cell inside a copied range.
- Sign changes between actual and forecast periods.
- Formulas that skip the first or last period.
- Annual assumptions applied monthly without conversion.
- Percentages entered as whole numbers in one section and decimals in another.
- Links to local files or retired workbook versions.
The goal is not “no hard-codes.” Historical actuals and explicit management inputs should be hard-coded. The goal is that every hard-code is intentional and visible.
10. Break every scenario switch
Select the downside case and trace at least five material outputs back to their scenario assumptions. Then select the upside and repeat.
Common failure: revenue switches correctly while labor, marketing, or capital spending still references the base case. The downside then shows the pain without the response, or the upside shows growth without the investment required to produce it.
11. Rank sensitivities by decision impact
Do not produce a page of tornado charts because the template supports them. Change the assumptions that can reverse the decision.
For a cash decision, test volume, price, gross margin, collection timing, hiring date, and capital spending. For a warehouse decision, test arrival volume, productivity, attendance, and backlog tolerance. For an acquisition, test revenue retention, margin, integration cost, working capital, and exit value.
12. Write the exception register
A useful review ends with decisions, not cell colors.
| Field | What belongs there |
|---|---|
| Finding | The specific condition observed |
| Location | Workbook, tab, range, or model section |
| Decision exposure | Cash, margin, capacity, covenant, return, or control effect |
| Severity | Critical, material, or housekeeping |
| Correction | Formula repair, source update, assumption decision, or model redesign |
| Owner | The person who can resolve it |
| Validation | The test that proves the correction worked |
What a clean review should answer
At the end, management should know:
- Which parts tie to source data.
- Which outputs depend on judgment.
- Which assumptions can break the plan.
- Where capacity and cash constrain the forecast.
- Which issues must be fixed before the decision.
- Which issues are merely presentation improvements.
That is the difference between checking a spreadsheet and validating a model for use. The representative review excerpt shows how these findings are presented, while the operating and financial diagnostic applies the same discipline to the business behind the workbook.