Error-Checking Your Model

Professional analysts spend 20% of their time building a model and 80% debugging it. A single misplaced minus sign, a forgotten absolute reference, or an inconsistent assumption can propagate through dozens of cells, producing a final valuation that looks plausible but is entirely wrong. This article covers systematic techniques for finding and preventing errors in your valuation spreadsheet, from structural audits to formula review to stress-testing assumptions.

Quick definition: Model auditing is the systematic process of verifying that a spreadsheet's formulas are correct, assumptions are consistent, and outputs are defensible—catching errors before they influence a decision.

Key takeaways

• Use the dependent and precedent cells feature (Trace Dependents, Trace Precedents in Excel) to map how formulas connect and verify that dependencies flow correctly.
• Implement ratio and growth rate sanity checks: ensure margins stay within plausible ranges, growth rates don't exceed industry norms, and year-over-year changes are smooth.
• Audit the formula waterfall: follow a single cash flow item (e.g., Year 3 free cash flow) backward to its inputs to verify every step is correct.
• Use alternative calculation methods to cross-check critical outputs. If your DCF-based intrinsic value is $100, can you verify it using comparable company multiples? If not, investigate.
• Document your audit findings in a separate sheet so reviewers (or you, months later) can see that verification was done.

Structural Audits: Is the Layout Sound?

Before diving into formulas, audit the structure:

Check that inputs are inputs: Review your input section. Every cell should be a raw number or a dropdown, not a formula. If you see =B5+B6 in the inputs section, move that calculation to the working area.

Check that outputs are truly outputs: Your output cells should reference only the working area, never directly from inputs. Trace each output cell backward. If it references fifty cells in the working area, that's fine; if it directly pulls from three hundred input cells, it's too complex.

Check sheet organization: Do you have a data staging area separate from the main model? If financial data and model calculations are mixed, errors hide. Create a separate "Data" sheet with historical financials, a "Model" sheet with projections and calcs, and an "Outputs" sheet with final results.

Check that calculations are in the right place: Are expenses being subtracted (negative contribution) or added (positive contribution)? Are growth rates expressed as decimals (0.15 for 15%) or percentages (15%)? Inconsistency breeds errors.

Formula-Level Audits: Finding Calculation Errors

Trace precedents and dependents (Excel):

• Select a cell with a formula.
• Go to Formulas > Trace Precedents. Blue arrows show which cells this formula uses as inputs.
• This reveals whether the formula is pulling from the right source.

Example: Your Year 2 revenue formula is =B10 * (1 + growth_rate). Trace precedents should point to Year 1 revenue (correct) and the growth rate input (correct). If the arrow points to an unrelated cell, the formula is wrong.

Check for circular references: Excel will warn you if a cell references itself (directly or indirectly). Google Sheets often hides circular references; use Tools > Iterative calculation to find them. Circular references break the model; fix them immediately.

Verify absolute vs. relative references: When you copy a formula across columns or rows, should the referenced cells move with the copy (relative: B5 becomes C5 becomes D5) or stay fixed (absolute: $B$5 stays $B$5)? Most projection formulas use relative references for years. Growth rate inputs use absolute references ($inp_growth_rate).

Copy a formula from Year 1 to Year 5 and verify it updated correctly. If Year 2 revenue still references Year 1 instead of moving to Year 2 and Year 3 values, the reference logic is wrong.

Check for hardcoded numbers: Search your model for hardcoded values (numbers typed directly in cells) mixed with formulas. These are usually errors. A formula like =Revenue * 0.4 should be =Revenue * inp_gross_margin, with 0.4 stored in the input section.

Use Find & Replace to search for obvious hardcoded numbers and verify whether they should be inputs or are intentional constants.

Ratio Consistency Checks

After building projections, verify that derived metrics stay plausible:

Gross margin: Should gross margin in Year 5 match your assumption? If you set inp_gross_margin to 40% but Year 5 gross margin calculates to 38%, track down why. Is the formula correct? Is working capital distorting it?

Operating margin: Similarly, verify operating margin trends. If you assume operating leverage (margins improving as the company scales), margins should show a trend upward, not bounce around randomly.

Asset turns: Calculate Year-over-year changes in key balance sheet items relative to revenue. If accounts receivable grows 50% but revenue grows only 10%, that's suspicious—either collection is degrading or you've made an assumption error.

Tax rate: Your effective tax rate should hover around the statutory rate (35% federal for U.S. corporations, adjusted for state/local). If your model shows 8% one year and 45% the next, investigate.

Return on Invested Capital: Calculate ROIC (NOPAT / Invested Capital) for your projections. Is it increasing over time (good, company becoming more efficient) or declining (warning sign)? If ROIC drops from 20% to 12%, make sure that's intentional.

Waterfall Audits: Following a Single Item

Pick a critical output (e.g., Year 3 free cash flow) and trace it backward to inputs:

1. Year 3 FCF = $150M (from output cell)
2. FCF formula = Operating CF - CapEx
3. Operating CF = Net Income + D&A - Change in WC
4. Net Income = (Revenue - COGS - OpEx - D&A) * (1 - Tax Rate)
5. Revenue = $500M (from Year 3 projection)
6. Growth rate = 10% (from inputs)
7. COGS = Revenue * 55% (from inputs)
8. (etc., drill down until you reach raw inputs)

As you trace backward, verify each step:

• Is the formula correct?
• Are the cell references pointing to the right places?
• Are assumptions reasonable?

Document your findings. If every step checks out, you've verified the final answer. If something's wrong, you've found the location.

Cross-Checks: Alternative Valuation Methods

If your DCF-based intrinsic value is $100 per share, can you verify it using other methods?

Method 1: DCF. Intrinsic value = PV(projected FCF) + PV(terminal value). You've calculated this as $100.

Method 2: Comparable multiples. Find 5 peer companies with similar growth and margins. Their average P/E is 20. Your projected Year 5 earnings per share is $5. Implied intrinsic value = $5 × 20 = $100. Match!

Method 3: Reverse math on market price. Stock currently trades at $90. That implies the market values it slightly below your DCF estimate. Why? Review your assumptions. Is your discount rate too low? Is the market expecting lower growth? Documenting this gap is valuable analysis.

If all three methods cluster around $100, you have confidence. If DCF says $100 but multiples say $60, dig into the differences. Is your terminal value too optimistic? Are peer margins too high?

Sensitivity Analysis as Verification

Run a sensitivity table on your two most uncertain assumptions (e.g., WACC and terminal growth rate). This reveals whether your model is reasonable.

WACC \ Terminal Growth	2.5%	3.0%	3.5%	4.0%
8.0%	$75	$85	$98	$115
8.5%	$70	$79	$90	$104
9.0%	$65	$74	$84	$96
9.5%	$61	$69	$79	$89

Does the table look reasonable? Valuation should increase as WACC decreases (investors demand less return) and terminal growth increases. If the table moves in the opposite direction, there's a formula error.

Also check: are the numbers in a realistic range? If changing WACC from 8% to 9% swings valuation from $100 to $200, that's suspicious—check the formulas.

Peer Review and Fresh Eyes

Before finalizing your model, have someone else review it. They don't need to be a modeling expert, just someone who can:

1. Follow the flow. Can they understand where inputs are, where calculations happen, and where outputs are?
2. Spot inconsistencies. Do they notice that revenue grows 15% every year (unrealistic) or that you assume 0% CapEx for a capital-intensive company?
3. Question assumptions. Do your revenue growth and margin assumptions align with management guidance and analyst consensus?

A fresh set of eyes catches errors you've become blind to.

Testing Extremes: Stress Tests

Push your model to extremes to verify it doesn't break:

What if revenue goes to zero? Your spreadsheet should still calculate (with zero FCF, maybe negative due to fixed costs). If the model crashes or produces #DIV/0! errors, there's a formula issue.

What if growth is 0%? The model should flatten projections. That should work.

What if WACC is 1% or 20%? Terminal value formulas might break if WACC approaches terminal growth (if WACC = 3% and terminal growth = 3%, the denominator is zero). Your model should either warn you or handle it gracefully.

Extreme tests don't have to be realistic; they verify that the model is robust.

Documentation: The Audit Trail

Create a "Model Audit" sheet listing:

Check	Status	Notes
Structural audit	PASS	Three zones (inputs, working, outputs) properly separated.
Circular references	PASS	None found.
Gross margin trend	PASS	40% consistent across all years (intentional, mature company).
Operating margin trend	PASS	Improves from 12% to 15% due to operating leverage.
Tax rate check	PASS	28% effective rate, aligned with statutory rates.
DCF sensitivity	PASS	Values range from $65–$115 across plausible scenarios.
Peer comparison	PASS	DCF value of $98 compares to peer P/E implied value of $100.
Waterfal audit (Year 3 FCF)	PASS	Traced back to inputs; all formulas correct.
Peer review	PASS	Jane reviewed on 2024-05-07; approved with minor notes.

This documentation is proof that you've verified the model. If someone questions a valuation months later, you can show your audit trail.

Flowchart

Real-world examples

Example 1: The missing minus sign. You're modeling a clothing retailer. Operating expenses are $50M. In the formula, you type =Revenue - COGS + OpEx instead of =Revenue - COGS - OpEx. Operating income is now inflated by $100M (the OpEx is added instead of subtracted). The error cascades through the entire model, inflating valuation by 50%. Structural audit would catch this—operating income would be unrealistically high, margins would be > 100% profit. Waterfall audit would find it immediately: Year 3 profit = $200M, but Revenue = $300M and COGS = $150M, so profit should be $100M, not $200M. The error is found and fixed.

Example 2: Circular reference in terminal value. You want terminal value to be 10x EBITDA. You write Terminal_Value = Year5_EBITDA * 10. Then, to calculate your discount rate, you include a formula: Discount_Rate = 0.05 + (1 / Terminal_Value). Now Terminal_Value depends on Discount_Rate, which depends on Terminal_Value—a circle. Excel would warn you; Google Sheets would silently compute. A structural check (tracing dependencies) finds this immediately.

Example 3: Inconsistent growth assumptions. You set revenue growth at 10% in the inputs but margin improvement assumes 20% growth (fixed costs declining as a % of sales). These are incompatible—at 10% revenue growth, you won't see the margin lift you've assumed. Ratio consistency check catches this: margins don't improve as projected, revealing the inconsistency. You then reconcile: either accept slower margin improvement, or revise growth assumptions.

Common mistakes

Skipping verification because the model "looks right." Professional appearance ≠ correctness. A model can look polished and be completely wrong. Verify before you present.

Only checking the final output. If intrinsic value = $100, and you verify it's correct, you're done. But trace backward to see which assumptions drive value most. If 70% of value comes from terminal value (Year 10+), you should scrutinize terminal assumptions harder than near-term projections.

Trusting peer review as the only audit. Peer review catches gross errors but not subtle ones. Waterfall audits and sensitivity analysis find subtle problems peers might miss.

Not documenting the audit. If you can't explain what you checked and how, you have no evidence of verification. Document it.

Assuming historical trends continue. A company's historical gross margin was 40%. You project 40% forever. But the industry is commoditizing; margins should decline. The model isn't wrong, but the assumption is unrealistic.

FAQ

Q: How much time should I spend auditing my model? A: Proportional to the importance of the decision. If you're making a $1M investment decision, spend 4–6 hours auditing. If you're running a quick valuation for educational purposes, 1 hour suffices. A rule of thumb: 20–25% of total time modeling.

Q: Should I use Excel's audit tools or review formulas manually? A: Both. Excel's Trace tools are fast and visual. Manual review (reading the formula bar) is thorough. Use both.

Q: What if I find an error after I've presented the valuation? A: Update the model, recalculate, and send out a corrected version. Don't hide it. Presenting corrections builds credibility, not the opposite.

Q: How do I know if an assumption is "wrong" vs. just different from mine? A: Question it. Is there evidence for the assumption in company guidance, analyst reports, or industry benchmarks? If your margin assumption differs from peers by 5+ percentage points, dig into why. If you can justify it, it's fine. If not, reconsider.

Q: Should I use a model template, or build from scratch? A: Templates are faster but may hide errors or include irrelevant calculations. If you use a template, audit every assumption and formula, don't assume it's correct just because it's polished.

Q: How often should I re-audit a model? A: Any time you make a significant change to assumptions or formulas. If you're just updating historical data or refreshing stock prices, minimal re-audit needed. If you change the discount rate or terminal growth assumption, re-audit.

Related concepts

• Chapter 14, Section 1: Spreadsheet Structure — Good structure makes auditing easier.
• Chapter 13: Discount Rate and WACC — Often the most uncertain input; worth special audit focus.
• Chapter 12: Terminal Value — Dominates many valuations; critical to verify.
• Chapter 10: Comparable Company Analysis — Cross-check DCF against multiples.

Summary

A valuation model is only as reliable as its audit. Spend time on systematic verification: structure audits ensure clarity, formula audits find calculation errors, ratio checks verify reasonableness, waterfall audits trace items to inputs, and sensitivity analysis tests whether the model responds correctly to changes. Cross-check against alternative valuation methods and have someone else review. Document your findings so the model is auditable and defensible. The time spent auditing is the most important modeling time you spend—it's the difference between a decision you can defend and one you can't.

Next

Proceed to Charts and Visualization.