The financial model is the analytical foundation of most transactions. It projects the target’s future performance, calculates the valuation, tests the sensitivity of assumptions and determines the price the buyer is willing to pay. In the vast majority of mid-market transactions, this model is built in Microsoft Excel.
It is built by one or two people. It is reviewed by no one with the same depth of familiarity. It is version-controlled through file-naming conventions. And it contains, in most cases, errors that the builder does not know about, that the reviewer does not catch and that the decision-maker does not question.
Research across financial modelling audits consistently shows that more than 90% of complex spreadsheets contain at least one material error. “Material” means the error changes the model’s output by more than 5%. In a transaction context, 5% of a Rs 1,200 crore valuation is Rs 60 crore. The model error, undetected, becomes a Rs 60 crore pricing error that the buyer pays.
The Spreadsheet Risk Test is a structured methodology for identifying, quantifying and correcting the errors that transaction models contain, before the model’s output is used to approve the price.
Where Spreadsheet Errors Hide
Hard-coded values disguised as formulas
A cell that appears to contain a formula but actually contains a manually typed number. The builder inserted the value during a debugging session and forgot to restore the formula. The model produces a result. The result is wrong. The error is invisible unless every cell is individually inspected.
In Northrop Management Private Limited’s model audit experience, hard-coded overrides are found in 60% to 70% of transaction models. The typical impact: Rs 5 crore to Rs 50 crore of valuation distortion.
Example: the EBITDA margin in Year 3 of a five-year projection was manually set to 18% during a sensitivity analysis, overriding the formula that calculates margin from revenue and cost assumptions. The formula was never restored. The model projects 18% margin in Year 3 regardless of what happens to costs. The resulting EBITDA is Rs 8 crore higher than the formula would produce. At 10x, that is Rs 80 crore of enterprise value attributable to a forgotten debug value.
Hidden rows, columns and sheets
Rows, columns or entire sheets that are hidden (not deleted) and contain data or formulas that feed into visible cells. The builder hid them to simplify the visual presentation. The formulas referencing them continue to operate. A reviewer who does not unhide every element may not see the data driving the model’s output.
Example: a hidden sheet contains a manual revenue adjustment of Rs 12 crore labelled “management overlay” that was added during an earlier version and never removed. The adjustment feeds into the visible revenue calculation on the summary sheet. The reviewer sees the revenue figure. They do not see the Rs 12 crore overlay that produces it.
Circular references
A formula that refers, directly or through a chain of references, back to itself. Excel resolves circular references through iterative calculation, producing a result that depends on the number of iterations, the convergence tolerance and the starting values. The result is deterministic on one computer but may differ on another with different Excel settings.
In transaction models, circular references most commonly occur in the debt schedule (interest expense depends on debt balance, which depends on cash flow, which depends on interest expense) and in tax calculations (tax depends on profit, which depends on tax-deductible expenses, which depend on tax). When the circularity is intentional and properly managed, it is a modelling technique. When it is accidental, it is an error source.
Inconsistent formulas across periods
A model projecting 10 years should use the same formula structure in every period. In practice, the builder modifies the formula in one period to accommodate a specific event (a one-off cost, a step change in pricing, a structural break) without applying the modification consistently. The result: two periods that look identical in presentation but calculate differently.
Example: the working-capital formula in Year 4 was modified to exclude a one-time inventory build. The modification was not reversed in Year 5. Working capital in Years 5 through 10 is understated, overstating free cash flow by Rs 3 crore per year, or Rs 18 crore cumulatively.
External links to unavailable files
References to cells in other workbooks. When the linked workbook is not open, Excel uses the last cached value, which may be weeks or months old. The model’s output depends on data from a source the reviewer cannot access and cannot verify.
Version control failures
Multiple versions circulating among advisors, management and the buyer. Each may contain different assumptions, different formulas and different outputs. The version used for the investment committee memo may not be the version the board approves, which may not be the version independently reviewed.
Example: the board approves the transaction based on “Model_v12_Final.xlsx.” The model that was independently audited was “Model_v10_Revised.xlsx.” Two versions, potentially different formulas, potentially different valuations. Nobody confirmed they are the same.
The Model Audit Methodology
In Northrop Management Private Limited’s transaction advisory practice, every financial model used to support a transaction decision is subjected to a five-step audit.
Step 1: Structural review. Map the model’s architecture: input sheets, calculation sheets, output sheets, links between sheets, links to external files, hidden elements, named ranges and macros. Identify the logical flow from assumptions through calculations to outputs. Determine the model’s complexity and the risk areas.
Step 2: Formula integrity testing. For every calculation row, verify that the formula is consistent across all periods. Identify every hard-coded value in a calculation sheet (not an input sheet, where hard-codes are expected). Identify every broken reference, error value, circular reference and inconsistent formula. Quantify the financial impact of each error.
Step 3: Assumption traceability. For every significant assumption (revenue growth, margins, capex, working capital, discount rate, terminal growth), trace the assumption from its input cell through every calculation that uses it to its impact on the valuation. Verify that the assumption is not overridden by a hard-coded value at any point in the chain.
Step 4: Sensitivity and scenario testing. Independently run the model under base, downside and severe downside scenarios. Compare the results to the builder’s sensitivity analysis. Identify assumptions where small changes produce large valuation swings (the load-bearing assumptions). Test whether the sensitivity analysis captures the full range of plausible outcomes.
Step 5: Reconciliation. Compare the model’s historical output to the target’s audited financial statements. If the model’s “actuals” differ from the published financials, the model contains a reconciliation error that may propagate into the projections. Every Rs 1 crore of reconciliation error is a Rs 1 crore of unreliability in the projection.
The output: a model audit report listing every error found, its financial impact, the corrected value and the net effect on the model’s valuation output. The corrected model becomes the basis for the transaction decision. The uncorrected model becomes evidence of the risk the buyer would have accepted without the audit.
The Financial Impact
In Northrop Management Private Limited’s model audit experience across mid-market transactions:
Average number of material errors per model: 8 to 15.
Average aggregate valuation impact: Rs 30 crore to Rs 80 crore (3% to 7% of enterprise value).
Most common error types: Hard-coded overrides (found in 65% of models), inconsistent formulas across periods (55%), hidden elements feeding visible calculations (40%), circular references in debt or tax schedules (35%), external links to unavailable files (30%).
Largest single error found: Rs 120 crore valuation impact from a hard-coded terminal growth rate that was set to 5% during a presentation, overriding the linked input cell that contained 2.5%.
Ashish Chaudhary, frames the model discipline directly: “A financial model should be treated as evidence requiring testing, not as an authoritative representation of the business. A model built by one person, reviewed by none with the same depth and version-controlled through file naming is not a financial instrument. It is a single-point-of-failure calculation that the transaction treats as gospel. The model audit converts it from an assertion into a verified instrument. The cost of the audit is trivial. The cost of the errors it prevents is not.”
Questions for the Boardroom
- Has the financial model supporting this transaction been independently audited for formula integrity, hard-coded values, circular references and hidden elements?
- How many material errors were found, and what is the aggregate impact on the valuation?
- Does the model’s historical output reconcile to the target’s audited financial statements, and if not, what is the reconciliation difference?
- Which three assumptions in the model carry the largest valuation sensitivity, and has the board seen the valuation under adverse assumptions for each?
- Is the version of the model the board is reviewing the same version that was independently audited?
Practical Checklist
Before relying on any transaction model: Unhide all rows, columns and sheets. Check for external links. Identify all hard-coded values in calculation sheets. Test formula consistency across all periods. Resolve all error values. Verify circular reference handling. Reconcile historical output to audited financials.
Red flags: More than 10 hard-coded values in calculation sheets. Circular references in key outputs. Historical output that does not match published financials. No version control. Sensitivity analysis with only the base case. Terminal value exceeding 70% of enterprise value.
Quantify every error: Calculate the valuation at the corrected assumption. Compare to the uncorrected valuation. The difference is the pricing error the model audit prevented.
Closing Implication
The transaction model is the calculation that determines the price. If the calculation contains errors, the price is wrong. In most mid-market transactions, nobody has independently verified that the calculation is correct. The model audit is the discipline that converts a spreadsheet from an assumption into evidence. The cost of the audit is Rs 5 to 15 lakh. The cost of the errors it finds is typically Rs 30 crore to Rs 80 crore. No other due diligence workstream offers a comparable return on investment.