The AI For Series

Auditing an Inherited Excel Model with AI: Hardcodes, Broken Links and Inconsistent Formulas

By Chandan Gupta, CA, CIA · 2026-11-14

Sooner or later every FP&A analyst and controller inherits a model. It is called something like Forecast_v19_FINAL2.xlsx, the person who built it has moved on, and it is due at the bank or the board next week. You need to know whether its mechanics can be trusted before anyone relies on the output.

Checking every formula by hand is slow. AI can do the reading. The method below has an AI tool write a script that reads every formula, lists each problem by sheet and cell, and leaves the model untouched. You then decide what gets fixed.

The rule: let the AI write the script, not read the data

The tool here is Claude Code, Anthropic's AI tool that works on files on your own computer (it needs a paid Claude plan). Instead of reading the workbook into a chat, it writes a short Python script that runs on your laptop and reads the formulas, then shows you the results. The model mostly sees labels, a few formulas and the script's findings, not the whole file.

That reduces what is sent; it does not make it zero, because the findings quote real cells and labels. Copy the model into a clean job folder with nothing else in it, start Claude Code there, and read each permission prompt before you approve it.

Step 1: Map the workbook before reading formulas

Forecast_v19_FINAL2.xlsx is a forecast model built by an analyst who has left. I need to know whether its mechanics can be trusted before it goes to the bank. Do not change it. First, write and run a short Python script that prints the sheet names (with any hidden ones marked), the size of each sheet, the labels in columns A and B of each sheet, any notes about units, and whether the workbook links to other files. Show me that and stop.

This step often finds problems before a single formula is read: a hidden sheet labeled "do not use" that something still refers to, a link to a workbook that isn't in the folder, a cover note saying one sheet is in thousands while the summary is in dollars. What is hidden, what is linked, and what units each sheet is in: ask those three questions of any inherited model.

Step 2: The formula review

This prompt is a model-review checklist, written out as instructions:

Now write review_model.py, a Python script that runs here and reads the formulas (not the values) with openpyxl. Save Model_Review.xlsx with: 1. An Issues sheet with columns Sheet, Cell, Issue Type, Formula or Value, Comment. Write formulas as text so they do not become live formulas in the review file. 2. Check for: numbers typed into rows that are otherwise formulas; formulas that differ from the pattern of their row; numbers typed inside formulas (like *1.03); SUM ranges that stop short of the block they total; #REF! and other errors; links to other workbooks; hidden sheets and who uses them; hidden rows or columns that contain numbers; circular references; sheets in different units ($ vs $ thousands) feeding each other; and inputs whose source note is more than a year old. 3. An Inputs sheet listing every typed number in the model with its sheet, cell and label. 4. A Sheet Map sheet showing which sheets feed which. Then show me the number of issues by type, and explain in plain English how total revenue is built up from the inputs.

Two parts of this prompt matter more than they look. Rule 1 keeps formulas as text: a review file whose formula column holds live formulas recalculates, breaks or points back at the model, and text is evidence where a live formula is not. And asking for a plain-English build-up of revenue tests whether the review understood the model's flow, not just its cells.

What each check catches:

Step 3: Rank by what the reader would see

Using Model_Review.xlsx, write a one-page summary for our CFO in plain English: the problems that could change the numbers the bank sees, in order of likely impact, each with the sheet and cell, and what we need to decide before we fix it. Save it as Model_Review_Summary.md. Don't fix anything in the model.

Rank by effect on the output, not by how odd a cell looks. A #REF! on a ratio nobody uses looks alarming and changes nothing; a units slip changes every line below it. Each item should end in a decision, not a fix: is the typed figure a deliberate override, is the old premium still policy, what is the unexplained adjustment? Those answers come from people, not from a script.

Step 4: Fix in a copy, with a log

Fixes come after the decisions, in a new version:

Make a copy of Forecast_v19_FINAL2.xlsx called Forecast_v20_review.xlsx and fix only these items in the copy: Summary C8:H8 (multiply the Opex total by 1,000), Opex F15 (SUM down to row 14), and Pricing G7 (use Inputs!$C$5). Do not touch anything else. Write Fix_Log.xlsx with Sheet, Cell, Old Formula, New Formula and Reason, formulas as text. Then compare every formula in the two files and confirm that only those cells changed.

Replace the cells with the ones your review found. The last sentence is the important one: a fix that changes one cell more than you meant is a new problem.

How to check the result

What goes in the review file

Keep the model exactly as received, with who sent it and when; review_model.py, Model_Review.xlsx and the summary, dated; the decisions and who made them; then the new version, the fix log, and a rerun of the review script on it showing what is left and why.

Limits

The script finds mechanical problems: hardcodes, broken links, inconsistent formulas, hidden content, unit mismatches. It does not tell you whether the assumptions are reasonable or the business logic is sound, and a circular reference may be a deliberate, documented loop rather than an error. Treat its ranking as a starting point for a conversation with the CFO, not the conclusion. Results also vary between runs and versions of the tool, which is why every finding gets checked in Excel before it goes in a memo.

Not affiliated with or endorsed by Anthropic or Microsoft.

From the book

This guide is a short version of one method in Excel + AI for Finance Teams. Chapter 17 of Excel + AI for Finance Teams walks through it end to end on practice files with an answer key.

More free guides