Study guide

8.2 Formula Auditing and Protection

Financial Modeling & Valuation Analyst FMVA

Track your progress as you read

Finished sections get a check mark.

1 Introduction

Current Exam Facts Program Path and How to Use This Guide

2 Chapter 1: Accounting Foundations

1.1 Accounting Principles 1.2 Reading Financial Statements 1.3 Accounting Decisions in Models

3 Chapter 2: Time Value and Capital Budgeting

2.1 Time Value of Money 2.2 NPV, IRR, and Decision Rules 2.3 Capital Budgeting Cash Flows

4 Chapter 3: Cost of Capital

3.1 Capital Structure 3.2 CAPM, Beta, and Cost of Equity 3.3 WACC

5 Chapter 4: Profitability and Returns

4.1 Profitability and Margins 4.2 Returns and DuPont Analysis 4.3 Per-Share Metrics and Growth

6 Chapter 5: Liquidity, Leverage, and Cash Flow

5.1 Liquidity and Working Capital Metrics 5.2 Leverage, Coverage, and Credit 5.3 Cash Flow Statement Analysis

7 Chapter 6: Excel Fundamentals

6.1 Shortcuts, Setup, and Navigation 6.2 Core Formulas and Error Handling 6.3 Named Ranges and Workbook Hygiene

8 Chapter 7: Excel Lookups and Financial Functions

7.1 Lookup Functions 7.2 Conditional Aggregations 7.3 Financial Functions

9 Chapter 8: Excel What-If Analysis and Auditing

8.1 Goal Seek and Data Tables 8.2 Formula Auditing and Protection

10 Chapter 9: Financial Modeling Guidelines

9.1 Model Structure and Layout 9.2 Color Conventions and Formatting 9.3 Integrity Checks and Documentation

11 Chapter 10: Three-Statement Modeling

10.1 Articulating the Three Statements 10.2 Building the Income Statement 10.3 Building the Balance Sheet and Cash Flow Statement

12 Chapter 11: Circularity and Model Audit

11.1 Circular References and Iteration 11.2 Auditing and Balancing a 3-Statement Model

13 Chapter 12: Operational Modeling

12.1 Revenue and Operating Drivers 12.2 Working Capital Modeling 12.3 Capex and Depreciation

14 Chapter 13: Debt Schedules, Scenarios, and Sensitivity

13.1 Debt and Interest Schedules 13.2 Scenario Analysis 13.3 Sensitivity Analysis

15 Chapter 14: Valuation Foundations

14.1 Valuation Methods and Enterprise Value 14.2 Unlevered Free Cash Flow

16 Chapter 15: DCF Valuation

15.1 DCF Structure, WACC, and Mid-Year Convention 15.2 Terminal Value and the Equity Bridge

17 Chapter 16: Comparable Valuation

16.1 Trading Comps and Multiples 16.2 Precedent Transactions and the Football Field

18 Chapter 17: Budgeting and Forecasting

17.1 Budget Types 17.2 Forecasting Techniques 17.3 Rolling Forecasts and Variance Analysis

19 Chapter 18: Presentation and Visuals

18.1 Chart Types and Selection 18.2 Dashboards and Data Visualization 18.3 PowerPoint and Pitchbooks 18.4 Presenting Model Outputs

20 Chapter 19: Professional Ethics and Qualitative Analysis

19.1 Professional Ethics 19.2 Qualitative Business Analysis

8.2 Formula Auditing and Protection

Key Takeaways Explain This Topic AI

Audit with Trace Precedents, Trace Dependents, F2, Evaluate Formula, Show Formulas (Ctrl+`), and Excel's error checker before you rebuild a case model.

A circular-reference warning is a bug when the loop is accidental and intended iteration when interest and a revolver feed each other; CFI 3-statement models use two circular loops plus a Circularity Switch (0 = reset, 1 = live).

Unlock blue input cells first, then Protect Sheet; do not password-protect a timed FMVA exam workbook.

Flag Assets = Liabilities + Equity with CFI-style OK green / Error red conditional formatting and a $1-style tolerance that does not hide a material miss such as $50,000.

On the timed Excel case, read check cells and the Circularity Switch first; CFI's published pace is about 45 minutes per case with a 60-minute ceiling inside a 3-hour, unpausable exam.

Last updated: August 2026

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

Tool What it shows Exam use

Trace Precedents Blue arrows from cells this formula reads Confirm WACC really comes from the CAPM block, not a hardcoded 9%

Trace Dependents Arrows to every formula that reads this cell Before you overwrite an input, see what will break

Remove Arrows Clears tracer arrows Declutter after a jump

F2 (edit) Colors same-sheet precedents; shows sheet refs for off-sheet links Fastest single-cell audit; cancel with Esc so you do not edit

  • Evaluate Formula Steps a nested formula left to right Unwrap INDEX/CHOOSE/IFERROR and circular interest
  • Show Formulas (Ctrl+`) Displays formulas instead of values Hunt hardcoded numbers in black calculation cells

Error Checking Green triangles and the error dropdown #DIV/0! , #REF! , #N/A , #VALUE! , #NAME? , #NUM! , #NULL!

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:

  • Error Typical cause in a model First move
  • #DIV/0! Margin or turnover with zero revenue or zero assets Check the driver, not the ratio label
  • #REF! Deleted row or broken sheet link Trace Precedents; restore the range
  • #N/A MATCH/XLOOKUP found no key Scenario map or ticker mismatch
  • #VALUE! Text in a math cell, or iteration exploded Circularity Switch off, then F9
  • #NAME? Misspelled named range or UDF Show Formulas and fix the name
  • #NUM! IRR without a sign change, or an impossible iterate Inspect cash-flow signs

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 :

Interest loop — interest on average (or opening) cash and debt feeds the income statement, which feeds retained earnings and cash.

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.

Loading diagram... Timed Excel Case Audit Path

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 .

  • Layer Typical lock state Why
  • Blue input cells Unlocked , then sheet protected Users can edit drivers only
  • Black formula cells Locked (Excel's default) Prevents accidental Goal Seek overwrites
  • Calculation sheets Entire sheet protected No casual typing on the IS / BS / CF
  • Circularity Switch and scenario number Unlocked inputs You must be able to reset loops and flip cases
  • Timed exam case Usually unprotected Do not password-lock yourself out

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

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.

Review → Protect Sheet (optional password). Leave "Select unlocked cells" checked so Tab still walks the blue cells.

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.

  • Item Amount
  • Total assets $85,400,000
  • Total liabilities $41,200,000
  • Total equity $44,150,000
  • Liabilities + equity $85,350,000
  • Assets − (L+E) $50,000 Error (red fill)

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:

  • Check Pass test
  • BS identity Assets = L + E (green OK)
  • Cash tie BS cash = CFS ending cash
  • NI tie IS net income = CFS net income (indirect-method start)
  • RE roll Opening RE + NI − dividends ± other = closing RE
  • PPE roll Opening net PPE + capex − disposals − D&A = closing
  • Debt roll Opening + draws − repayments = closing

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:

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.

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.

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.

Show Formulas on IS / BS / CF. Hardcoded black cells are suspects. Ctrl+` back to values before you copy a number into an answer.

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.

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.