8.2 Formula Auditing and Protection

Formula auditing tools under a 45-minute case clock

Quick Answer: Audit with Trace Precedents, Trace Dependents, F2, Evaluate Formula, Show Formulas (Ctrl+`), and Excel's error checker. A circular-reference warning is a bug when the loop is accidental and designed iteration when interest, a revolver, or taxes feed each other — CFI's 3-statement courses use two circular loops plus a Circularity Switch to reset a model that has blown up. Protect unlocked input cells on an assumptions sheet; leave calculation sheets formula-locked in a delivered file, but do not password-lock a timed exam case. Flag Assets = Liabilities + Equity with CFI-style OK green / Error red conditional formatting and chase a red check before you trust a valuation output.

The Excel case on the FMVA final is a 45-minute modeling problem ( 60-minute ceiling) inside a 3-hour, 50-question, 70% exam that cannot be paused. You will not have time to re-derive every formula from a blank sheet. You will have time to F2 a suspicious NPV, trace a $50,000 balance-sheet difference, and turn a Circularity Switch off and on when iteration has exploded to #VALUE!.

The Formula Auditing group

Trace Precedents on an equity-value cell should walk back through net debt, enterprise value, and unlevered free cash flow — not stop at a typed millions figure. If the tracer shows no arrows, you are looking at a constant, which on a calculation row is a modeling defect unless it is a documented switch.

F2 is the tool you actually use under time pressure. Click the IRR cell, press F2, glance at the colored ranges, press Esc. If the range misses year 0 or double-counts terminal value, you have the answer to the case item without rebuilding the DCF. F2 also color-codes each precedent on the same sheet; off-sheet links appear as sheet references, which is your cue to jump rather than assume the number is local.

Evaluate Formula is slower and more powerful. Nested INDEX / CHOOSE / IF is hard to parse by eye. Evaluate peels one expression at a time. On a circular interest formula, Evaluate may look like it is stuck because the cell still depends on itself — that is a clue to look at iteration and the Circularity Switch, not to keep pressing Evaluate until the exam clock dies.

Show Formulas is a sheet-wide sanity pass. In CFI color convention, blue font = inputs, black font = formulas. A black cell that Show Formulas still displays as 85000000 rather than =C20 is a hardcoded override. Those overrides are how models that used to balance stop balancing after someone pasted values. Toggle Ctrl+` again to return to values before you answer a numeric item so you do not misread a formula string as a result.

Error types you will actually see in a case file:

CFI-style modeling keeps errors visible during the build. Wrapping every line in IFERROR(...,0) hides the audit trail and can make a broken plug look like a balanced zero. Handle presentation-layer labels on the dashboard, not inside the trial-balance check.

Circular-reference warning versus intended iteration

Excel throws a circular-reference warning when a formula refers, directly or indirectly, to its own cell. The status bar may also show Circular References with a cell address. Two very different situations share that dialog.

Unintended circularity is a bug. Examples: a subtotal that includes itself; a cash line that points at the same cash line; copying a formula one row too far so the SUM range includes the SUM cell.

Intended iteration is a 3-statement design choice. Interest income and interest expense often depend on average cash and average debt. Average cash depends on ending cash. Ending cash depends on net income. Net income depends on after-tax interest. That is a loop. A revolver / cash sweep that plugs the balance sheet creates a second loop: the plug depends on assets versus liabilities and equity, while interest depends on the plug.

CFI's 3-Statement Modeling and Auditing and Balancing a 3-Statement Model cores teach two circular loops and a Circularity Switch:

1. Interest loop — interest on average (or opening) cash and debt feeds the income statement, which feeds retained earnings and cash.
2. Plug / revolver loop — the debt or cash plug that forces the balance sheet to balance feeds interest, which feeds the plug.

The Circularity Switch is a 0/1 (or Yes/No) input, usually on the assumptions or control panel:

• Switch = 0 (OFF): circular formulas return 0 or a non-circular fallback. The loops break. Excel can recast after a #VALUE! explosion or a first-open warning.
• Switch = 1 (ON): live circular formulas run, and File → Options → Formulas → Enable iterative calculation must be on (typical starting points: max iterations 100, maximum change 0.001).

A typical interest line looks like =IF(CircSwitch=0, 0, AvgDebt * InterestRate) rather than a naked AvgDebt * InterestRate. When the switch is off, interest is zero, cash and the revolver can settle without feeding themselves, and you get a dirty but finite model. When the switch is on, iteration closes the loops.

Worked reset. You paste a new revenue forecast. The revolver explodes toward 10^15 and EBIT, tax, and cash all show #VALUE!. Set the Circularity Switch to 0, press F9, confirm the balance check is at least calculating (it may be red; that is acceptable while the switch is off), then set the switch back to 1 and F9 again. That is the designed recovery. Turning iteration off globally in Excel Options without the switch can leave the file calculating as zeros forever, which is harder to reverse in a 45-minute case.

Exam traps:

• Treating every circular warning as a fail. On a CFI 3-statement case, first look for the switch and for iteration.
• Treating every loop as fine. A self-including SUM is still a bug.
• Goal Seeking a cell inside a live circular loop. Reset with the switch first; otherwise Goal Seek and iteration fight each other.
• Leaving the switch at 0 and then answering a valuation item that needs live interest. The BS may "balance" on a zero plug that is not the intended forecast.

Full circular-interest algebra belongs with the later circularity chapter. This section is the audit and reset skill the timed case actually uses.

Protecting inputs versus calculation sheets

Delivered CFI-style files separate assumptions from calculations. Color is the first control (blue input, black formula). Sheet protection is the second, and it only works if you unlock inputs before you protect.

Workflow if you are sending a model, not sitting the exam:

1. Select input cells → Format Cells → Protection → uncheck Locked. Default Excel locks every cell, so skipping this step means Protect Sheet freezes the entire grid, including the switch.
2. Review → Protect Sheet (optional password). Leave "Select unlocked cells" checked so Tab still walks the blue cells.
3. Do not protect workbook structure until you know the user does not need new sheets.

On the FMVA case, protection is secondary to auditing. Spending five minutes on Review → Protect Sheet is a poor use of a 45-minute budget. Do not apply a password you then forget. Do not Goal Seek a locked formula cell and conclude the model is broken — unlock or work on the dedicated input.

A related hygiene point: Goal Seek and protection collide. If the By changing cell is locked on a protected sheet, Goal Seek cannot write the new input. Unlock the driver, or run Goal Seek before protecting a delivered file.

Balance checks and CFI's green OK / red Error fill

A 3-statement model is not done when the income statement looks pretty. It is done when the balance sheet balances and the cash-flow statement ties to cash.

Primary check:

=TotalAssets − (TotalLiabilities + Equity)

or a labeled flag:

=IF(ABS(TotalAssets − TotalLIE) < 1, "OK", "Error")

The $1 (or $0.50) tolerance absorbs floating-point pennies. It must not swallow a $50,000 miss. Do not wrap this flag in IFERROR; a #VALUE! on the check is information.

CFI formatting: conditional formatting on the check cell — green fill for OK, red fill for Error. That is the visual you hunt at the top of a case workbook the moment the file opens.

Worked imbalance.

The sheet is $50,000 off. Do not start the DCF. Trace Precedents from the check. Typical CFI miss list:

• Cash on the BS does not equal ending cash on the CFS.
• Net income does not roll into retained earnings (missing OCI, a dividend, or a leftover prior-year RE).
• PPE rollforward: opening + capex − disposals − depreciation ≠ closing PPE.
• Debt rollforward: opening + draws − mandatory paydown ≠ closing debt.
• The revolver plug is sitting on the wrong side, or the Circularity Switch is still off so the plug is zero and the $50,000 is an unplugged residual.

Suppose CFS ending cash is $8,150,000 and BS cash is $8,200,000. That $50,000 cash-tie miss is the BS identity error if cash is the only unmatched line. Trace the CFS ending-cash formula (usually opening cash + net change in cash) and the BS cash formula (often a plug or a link to CFS). F2 both; do not type 8,200,000 into either cell.

Secondary checks to park next to the BS identity:

Conditional formatting belongs on all of those flags, not only the BS identity. One green cell beside five unformatted zeros is how a $50,000 cash-tie miss survives. Use the same OK / Error text plus green / red fill so a reviewer (or you, at minute 10 of a case) can scan without reading formulas.

If several checks are red, fix the cash tie and RE roll before you chase depreciation. Those two links close most 3-statement breaks. The later auditing-and-balancing chapter expands the checklist; on the exam you need the flags, the colors, and the habit of not answering an equity-value item off a red sheet.

Formula auditing on the timed Excel case

CFI's published pace is under 3 minutes for a typical item and about 45 minutes per case study (60-minute hard ceiling) inside a 3-hour clock that cannot be paused. A practical audit sequence:

1. Scan structure (about 2 minutes). Find the assumptions sheet, the scenario number, the Circularity Switch, the output block, and the balance-check row. If you cannot find the check, build a one-cell Assets − (L+E) flag before you do anything else.
2. Read the check cells. Red Error → audit before answering any valuation item that depends on a balanced model. Green OK is not a skip-the-rest pass; still F2 the output the question names.
3. Circularity. If #VALUE! or 1E+15, switch OFF, F9, switch ON, F9. Confirm iteration is enabled if the file is designed to be circular. If there is no switch and a warning points at a SUM that includes itself, that is a bug, not CFI iteration.
4. Show Formulas on IS / BS / CF. Hardcoded black cells are suspects. Ctrl+` back to values before you copy a number into an answer.
5. F2 the output the question names (NPV, equity value, year-3 EBIT). Esc out. Trace Precedents only on the disputed cell, then Remove Arrows so the sheet stays readable.
6. Do not Goal Seek a formula cell. Do not protect the sheet. Do not rebuild three scenario copies. Use the scenario number if it exists; use a data table on the same sheet as the inputs if the item is a sensitivity read.

If the BS is still red at minute 40, pick the best-supported multiple-choice answer from the unbalanced output only if the item does not depend on the plug; otherwise flag and move. A perfectly balanced model that you never submit because the clock hit 60 minutes scores zero on that case. CFI's own guidance is a 45-minute appetite and a 60-minute stop. Treat minute 45 as the decision point: answer from the audited cells you have, then return only if easier items are already banked.

This section's job is to keep the case calculating and reconcilable. Chapter 11 (circularity and model audit) goes deeper on loop algebra; Chapter 13 applies scenario and sensitivity to debt and DCF. On test day, the Excel case rewards the tools in this chapter: F2, TABLE, a scenario toggle, a Circularity Switch, and a green OK on Assets = Liabilities + Equity.