Formula Audit Checker

When to use

Use this skill when reviewing an Excel model that was built by someone else, or verifying your own model before sharing it with a client or senior stakeholder. Trigger it when a model has been flagged for quality review, when a version handover is happening, or before a model is used to make a significant financial decision. It is most valuable as a pre-submission quality gate: running this audit before a model is presented to a board, an investment committee, or a client.

What it does

Provides a systematic, step-by-step model audit process covering: Excel's native formula auditing tools (Trace Precedents, Trace Dependents, Error Checking), detection and correction of hardcoded values in formulas, identification of circular references, consistent IFERROR wrapping, and documentation of audit findings in a review log. The output is a completed audit log and a list of prioritized issues to fix.

Method

1. Save a working copy before auditing. Before making any changes, save the model as a new file with "_Audit_YYYY-MM-DD" appended to the name. All edits during the audit should be made on this copy. This preserves the original state for comparison.

2. Run the Error Checking tool. Go to Formulas > Error Checking. Excel will step through all cells that contain errors (#N/A, #REF!, #DIV/0!, #VALUE!, #NAME?) and display the error and the formula. For each error: diagnose the cause (missing lookup key, deleted source cell, wrong data type, missing named range), fix it, and document it in the audit log with: cell reference, error type, cause, fix applied, severity (Critical, Major, Minor).

3. Find and resolve circular references. Go to Formulas > Error Checking > Circular References. Excel will list any cells with circular dependencies. Circular references are almost always an error in a financial model (except in iterative calculation scenarios, which are rare and must be explicitly intentional). For each circular reference: trace the dependency chain, identify the loop, and break it by restructuring the formula logic. Document each circular reference found.

4. Use Trace Precedents to verify formula logic. For every major calculation row (revenue, EBITDA, FCF, equity value, IRR), click the cell and press Ctrl+[ or go to Formulas > Trace Precedents. This draws arrows showing which cells feed this formula. Verify: (a) the formula is pulling from the correct tabs (Inputs tab for assumptions, Data tab for references, correct Calc tab for intermediate results), (b) no unexpected cells are being referenced, (c) the formula does not reference cells that should be on a different tab.

5. Use Trace Dependents to check what a cell feeds. For key output cells on Calc tabs, use Formulas > Trace Dependents to see which downstream cells reference this output. This confirms: (a) that output cells are being picked up by the Outputs tab correctly, (b) that no unexpected downstream formulas are referencing a cell that should be a local intermediate.