Auditing an Inherited Excel Model with AI: Hardcodes, Broken Links and Inconsistent Formulas
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:
- Hardcodes in formula rows. A 2028 revenue cell typed as a number in a row of formulas. It looks right today and never moves when an input changes.
- Inconsistent formulas. One cell in a row pointing at the wrong growth rate, which is easy to miss in a row of similar formulas.
- Numbers inside formulas.
=F7*1.03gives the right answer today and the wrong one when the price-increase input changes. - Short SUM ranges. A total that stops one row above the block, leaving out a line such as software costs.
- External links. A rate pulled from a file nobody has.
- Hidden sheets and rows. An old assumption still feeding revenue, or an unexplained manual adjustment in a hidden row.
- Circular references. Interest, net income and debt depending on each other, sometimes deliberately.
- Unit mismatches. A sheet in thousands feeding a summary in dollars. This one looks like a normal formula and can change EBITDA by more than everything else on the list combined.
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
- Every named cell, in Excel. Go to each cell on the Issues sheet (Ctrl+G) and look. Formulas, Show Formulas makes breaks in a row's pattern easy to spot.
- The model is unchanged. Its date modified should not have moved during the review.
- The Formula column is text. Click a few cells on the Issues sheet; you should see the formula as text, not a calculated value.
- The dollar effects. If the model has never been calculated in Excel, the script may have worked out impacts in Python. Check the two largest yourself.
- Cell references in the chat. The chat can name the wrong column even when the file is right. Use the references in Model_Review.xlsx, not the conversation.
- What it was not asked to check. Logic, not just mechanics: how tax behaves on a loss, how debt is repaid, whether the growth rates are sensible. The review covers what the prompt listed and little more.
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
- Free guide · Excel + AI for Finance Teams
Bank Reconciliation with AI: A Month-End Method
Use Claude Code to write a bank reconciliation script that runs on your own computer, ties both balances to zero and lists every exception for review.
Read the guide → - Free guide · Excel + AI for Finance Teams
Claude for Excel vs Claude Code for Finance Teams
When to use the Claude for Excel add-in and when to use Claude Code for finance work: what each does, what each sends, and a job-by-job rule.
Read the guide →