AI-Powered Financial Statement Analysis

Use Claude to analyze financial statements, compute ratios, and build a reusable prompt library.

Introduction

30 Second Summary

Financial statement analysis eats hours of your week. Pulling numbers from reports, calculating ratios, writing variance commentary, and formatting outputs is repetitive work that keeps you from the strategic thinking your role actually demands.

In this project, you will use Claude to analyze a fictional company's financial statements, extract key metrics, perform variance analysis, and build a verification system that ensures every AI-generated number is accurate before it reaches anyone else.

What You'll Build

You will build an AI-powered financial analysis workflow that transforms raw income statement and balance sheet data into verified metrics, variance commentary, and Excel-ready formulas using structured prompts and a reusable prompt library.

By the end of this project, you'll have:

  • A completed financial metrics analysis with profitability, liquidity, and efficiency ratios generated by Claude and verified in Excel.
  • A budget-to-actual variance analysis with narrative commentary explaining deviations, formatted for a CFO briefing.
  • A personal prompt library of 5+ tested, reusable finance prompts built on the ROLES framework that you can apply to your real work immediately.
  • A three-layer verification workflow that catches AI hallucinations before outputs reach stakeholders.
  • Secret Mission: Build an automated ageing report analysis prompt that categorizes receivables by risk tier and generates collection priority recommendations.

Are there any prerequisites?

You need a basic understanding of financial statements (income statement, balance sheet, cash flow statement). You also need a free Claude account at claude.ai and Microsoft Excel installed on your computer.

No coding or AI experience is required. This project teaches you everything you need to know about working with LLMs for finance from scratch.

Set Up Your AI Finance Workspace

It's Monday morning. The CFO wants a quick analysis of last quarter's performance, and you're staring at a pile of financial statements. Most finance professionals open an AI tool, type "analyze my financials," get a generic response, and conclude AI is useless for real work.

The problem is not the tool. The problem is the setup. In this step, you'll create a dedicated workspace in Claude with custom instructions that tell it exactly how to behave as your financial analyst assistant. You'll also prepare the fictional NovaTech data in Excel that you'll analyze throughout this project.

In this step, get ready to:
  • Learn the AI fundamentals that directly affect your financial outputs.
  • Create a Claude finance workspace with custom instructions.
  • Prepare the NovaTech financial data in Excel.
Learn the AI fundamentals that matter for finance

Before touching Claude, you need to understand five terms. These directly affect how reliable your financial outputs are.

  • LLM (Large Language Model): The engine behind Claude, ChatGPT, and Grok. Think of it as a probability engine that predicts the most likely next word. It is NOT a calculator. It predicts what numbers LOOK right, not what they ARE. This is why verification is non-negotiable in finance.
  • Prompt: Your instruction to the AI. A vague prompt ("analyze this") produces vague outputs. A structured prompt produces structured, usable outputs.
  • Tokens: The units AI processes (roughly 1 token = 0.75 words). Claude's 200K token context window means it can hold approximately 500 pages of text at once. That's enough for an entire annual report.
  • Hallucination: When AI generates plausible-sounding but factually incorrect information. In finance, a hallucinated number can lead to wrong decisions, audit findings, or compliance issues.
  • Context window: The amount of information AI can "see" at once. Claude's 200K tokens means you can paste entire financial statements and it maintains cross-reference capability.

Why does this matter for your finance work?

Next time a colleague says "AI makes things up," you can explain WHY (probability prediction, not calculation) and HOW to control it (structured prompts and verification steps). Understanding these fundamentals separates professionals who use AI effectively from those who get burned by it.

Create your Claude finance project

Claude's Projects feature lets you create a persistent workspace with custom instructions. This means Claude already knows your role, your standards, and your preferred output format before you ask a single question.

  • Open claude.ai in your browser and sign in to your account.
  • Hover over the left side of your screen and click Projects.
  • Click + New Project in the upper right corner.
  • Enter Financial Analysis Workspace as the project name.
  • Leave the description blank and confirm the project creation.

What is a Claude Project?

A Project is a workspace that groups related conversations together. Any instructions or files you add to the project apply to every chat within it. This saves you from re-explaining your role and requirements each time you start a new conversation.

Now you'll add custom instructions that tell Claude how to behave as your financial analyst assistant.

  • Inside your new project, click Set project instructions.
  • Paste the following instructions into the text field:
You are a senior financial analyst assistant. Your role is to help me analyze financial statements, compute metrics, and generate variance commentary.

Rules you must follow:
- Use only the data I provide. Never invent or assume financial figures.
- If data is missing or insufficient, stop and ask me for it rather than guessing.
- Show all calculation steps so I can verify your work.
- Present numerical outputs in table format suitable for pasting into Excel.
- Flag any assumptions you make with [ASSUMPTION] tags.
- Use IFRS/GAAP terminology consistently.
- When I ask for formulas, provide Excel-compatible formulas.
- Round figures to 2 decimal places unless I specify otherwise.

What do these instructions do?

Each rule prevents a specific failure mode. "Use only the data I provide" stops Claude from inventing figures. "Show all calculation steps" creates an audit trail you can verify.

The [ASSUMPTION] tags make it obvious when Claude is guessing instead of calculating. These instructions apply to every conversation inside this project automatically.

  • Click Save instructions.

Don't see the Projects option?

Make sure you are signed in at claude.ai (not the API console). Try hovering over the left edge of the screen to reveal the sidebar. You can also navigate directly to claude.ai/projects in your browser.

Still stuck? Help me find the Projects feature in Claude

Prepare the NovaTech financial data

You'll now create the fictional financial statements you'll analyze throughout this project. NovaTech Solutions Ltd is a fictional mid-size technology services company.

Why fictional data?

Never paste confidential, pre-release, or client financial data into consumer AI tools. Enterprise plans have data protection agreements, but for learning, fictional data keeps you safe and focused on technique.

  • Open Microsoft Excel and create a new workbook.
  • Save it as NovaTech_Analysis.xlsx on your Desktop.
  • Rename the first sheet to Income Statement by double-clicking the sheet tab at the bottom.
  • Enter the following data into the Income Statement sheet, starting from cell A1:
Line Item              | FY2025 Actual ($000s) | FY2025 Budget ($000s) | FY2024 Actual ($000s)
-----------------------|-----------------------|-----------------------|----------------------
Revenue                | 12,450                | 13,000                | 11,200
Cost of Services       | 7,470                 | 7,800                 | 6,720
Gross Profit           | 4,980                 | 5,200                 | 4,480
Operating Expenses     | 3,100                 | 2,900                 | 2,800
Operating Income       | 1,880                 | 2,300                 | 1,680
Interest Expense       | 180                   | 180                   | 200
Net Income             | 1,700                 | 2,120                 | 1,480

How to enter this data

Put the column headers (Line Item, FY2025 Actual, FY2025 Budget, FY2024 Actual) in row 1 across columns A through D. Enter each line item in column A starting from row 2, and fill in the corresponding numbers in columns B, C, and D.

Enter the numbers without commas or dollar signs (e.g., enter 12450 for Revenue FY2025 Actual). You can format them later with Excel's number formatting if you prefer.

Now create the Balance Sheet on a second sheet.

  • Click the + icon next to your sheet tab at the bottom to add a new sheet.
  • Rename the new sheet to Balance Sheet.
  • Enter the following data, starting from cell A1:
Line Item                | FY2025 ($000s) | FY2024 ($000s)
-------------------------|----------------|---------------
Cash & Equivalents       | 2,100          | 1,800
Accounts Receivable      | 3,200          | 2,600
Inventory                | 450            | 380
Total Current Assets     | 5,750          | 4,780
Total Assets             | 14,200         | 12,500
Accounts Payable         | 1,900          | 1,600
Short-term Debt          | 1,200          | 1,000
Total Current Liabilities| 3,100          | 2,600
Long-term Debt           | 2,800          | 3,000
Total Equity             | 8,300          | 6,900

What does this data represent?

The Income Statement shows NovaTech's revenue, costs, and profit for three scenarios: what actually happened in FY2025, what was budgeted for FY2025, and what happened the prior year (FY2024).

The Balance Sheet shows what the company owns (assets) and owes (liabilities plus equity) at the end of FY2025 and FY2024. You'll use both sheets for ratio analysis and variance commentary in the next steps.

  • Save the workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Verify you have two sheets at the bottom of Excel: Income Statement and Balance Sheet.

Seeing only one sheet tab?

Make sure you clicked the + icon at the bottom of the Excel window (next to the existing sheet tab) to create a second sheet. If you accidentally typed the Balance Sheet data on the same sheet as the Income Statement, right-click the sheet tab, select Insert, and choose Worksheet. Then move the Balance Sheet data to the new sheet.

Need help? Help me set up my Excel workbook with two sheets

✔️ Awesome, I've got everything!

Great. Make sure you've saved both your Claude project instructions and your Excel workbook.

ⓧ I'd like to double check the full setup

Here's what you should have at this point:

  • A Claude project called Financial Analysis Workspace with saved custom instructions defining Claude as a senior financial analyst assistant with 8 rules (use only provided data, stop and ask for missing data, show calculation steps, table format, [ASSUMPTION] tags, IFRS/GAAP terminology, Excel-compatible formulas, round to 2 decimal places).
  • An Excel workbook called NovaTech_Analysis.xlsx with an Income Statement sheet (7 line items across 3 columns of figures: FY2025 Actual, FY2025 Budget, FY2024 Actual).
  • A Balance Sheet sheet in the same workbook (10 line items across 2 columns of figures: FY2025 and FY2024).

Your AI finance workspace is ready. Next up, you'll put Claude to work by extracting financial metrics from the NovaTech data using structured prompts built on the ROLES framework.

Extract Financial Metrics Using Structured Prompts

Your workspace is set up, your data is loaded, and Claude knows how to behave like a financial analyst. Now it is time to put it to work.

The CFO wants NovaTech's key profitability and liquidity ratios with year-over-year comparisons in 20 minutes. Manually, this means pulling figures from two statements, setting up ratio formulas, calculating changes, and formatting a summary. That is 45 minutes minimum. In this step, you will use the ROLES prompting framework to get Claude to compute all seven metrics simultaneously and then verify the results in Excel.

In this step, get ready to:
  • Submit a ROLES-framework prompt to Claude that extracts seven financial metrics from NovaTech's statements.
  • Verify Claude's calculations manually in Excel to confirm accuracy.
  • Generate Excel-compatible formulas that dynamically compute ratios from your spreadsheet data.
Apply the ROLES framework for your first financial prompt

The ROLES framework prevents hallucinations and produces audit-ready outputs. Each letter gives Claude a specific constraint that keeps its response useful and verifiable.

  • R (Role): Assign a specific identity. "You are a senior FP&A analyst preparing analysis for the CFO."
  • O (Objective): State exactly what you need. "Calculate profitability and liquidity ratios for FY2025 vs FY2024."
  • L (Limits): Constrain the output. "Use only the data provided. Present as a table. Show formulas."
  • E (Evidence): Require sourcing. "Reference the specific line items used in each calculation."
  • S (Safeguards): Prevent hallucination. "If any data is missing, stop and ask. Do not estimate."

Why ROLES instead of just asking a question?

A vague prompt like "analyze my financials" produces vague outputs. ROLES forces structure because each element constrains Claude's behavior in a specific way.

The Safeguards element is the most critical for finance work. It instructs Claude to stop and ask for missing data rather than guessing. Without it, Claude might invent plausible-sounding numbers that pass a quick glance but fail an audit.

  • Switch back to your Claude conversation in the Financial Analysis Workspace project.
  • Paste the following ROLES-framework prompt into the message field:
Role: You are a senior FP&A analyst preparing a financial metrics summary for the CFO of NovaTech Solutions Ltd.

Objective: Using the data below, calculate the following ratios for FY2025 and FY2024, then compute the year-over-year change:
- Gross Profit Margin
- Operating Profit Margin
- Net Profit Margin
- Current Ratio
- Quick Ratio (exclude Inventory from current assets)
- Debt-to-Equity Ratio
- Return on Equity (ROE)

Data - Income Statement (in $000s):
Revenue: FY2025 = 12,450 | FY2024 = 11,200
Cost of Services: FY2025 = 7,470 | FY2024 = 6,720
Gross Profit: FY2025 = 4,980 | FY2024 = 4,480
Operating Income: FY2025 = 1,880 | FY2024 = 1,680
Net Income: FY2025 = 1,700 | FY2024 = 1,480

Data - Balance Sheet (in $000s):
Total Current Assets: FY2025 = 5,750 | FY2024 = 4,780
Inventory: FY2025 = 450 | FY2024 = 380
Total Current Liabilities: FY2025 = 3,100 | FY2024 = 2,600
Long-term Debt: FY2025 = 2,800 | FY2024 = 3,000
Short-term Debt: FY2025 = 1,200 | FY2024 = 1,000
Total Equity: FY2025 = 8,300 | FY2024 = 6,900

Limits: Present results in a markdown table with columns: Metric | FY2025 | FY2024 | YoY Change | Commentary. Show the formula used for each metric. Round percentages to 1 decimal place.

Safeguards: Use only the data provided. If any figure seems inconsistent or missing, flag it with [FLAG] rather than assuming a value.
  • Press Enter (or click the send button) to submit the prompt.
  • Wait for Claude to generate the full response. This takes 10-20 seconds.

What does this prompt do?

The Role section tells Claude to think and write like a senior analyst, not a chatbot. The Objective lists all seven ratios explicitly so Claude does not pick and choose.

The Data section feeds both statements in a compact format Claude can parse. The Limits section forces a specific table structure and formula visibility. The Safeguards section prevents Claude from inventing numbers if something is missing.

Claude should return a table with all seven metrics calculated for both years, plus year-over-year change percentages and brief commentary on each trend.

Claude's response looks different from expected?

Claude's exact wording and table formatting will vary between conversations. What matters is that you see all seven metrics, both years of data, and the YoY change column. If any metric is missing, ask Claude: "You missed [metric name]. Please add it to the table."

If Claude says it cannot access your data, you may have forgotten to paste the data section of the prompt. Re-send the full prompt including the Data sections.

Still having trouble? Why isn't Claude generating a complete metrics table from my ROLES prompt?

Verify Claude's output in Excel

Claude is a probability engine, not a calculator. It predicts what numbers look right based on patterns. This means even simple arithmetic can be wrong. You must verify before trusting any figure.

  • Return to your NovaTech_Analysis.xlsx workbook in Excel.
  • Create a new sheet by clicking the + tab at the bottom of the workbook.
  • Name the new sheet Metrics Verification by double-clicking the tab name and typing it in.

Now manually verify at least three of Claude's ratios by entering the formulas yourself.

  • Enter the following verification formulas into cells B2, B3, and B4:
Cell B2: =4980/12450
Cell B3: =1880/12450
Cell B4: =5750/3100

What do these formulas check?

  • B2 calculates Gross Profit Margin FY2025 (Gross Profit divided by Revenue). Expected result: approximately 40.0%.
  • B3 calculates Operating Profit Margin FY2025 (Operating Income divided by Revenue). Expected result: approximately 15.1%.
  • B4 calculates Current Ratio FY2025 (Total Current Assets divided by Total Current Liabilities). Expected result: approximately 1.85.
  • Press Enter after each formula to confirm it.
  • Compare your results against Claude's table. Do the numbers match exactly (allowing for rounding to 1 decimal place)?

If all three match, Claude's arithmetic is correct for these metrics. If any do not match, you have caught a hallucination in real time.

Why only verify three ratios instead of all seven?

This is the "trust but verify" workflow. You spot-check a representative sample that covers different formula types (percentage margin, ratio). If the sample passes, the remaining calculations are likely correct.

In practice, you would verify any figure that feeds into a board report or external filing. For internal working papers, a three-ratio spot check is proportionate.

Numbers do not match Claude's output?

Check whether the difference is a rounding issue. Claude rounds percentages to 1 decimal place, so 39.96% becomes 40.0%. Your Excel cell may show more decimal places by default.

If the difference is larger than a rounding discrepancy, Claude used incorrect source numbers. Go back to the metrics table in Claude's response and check which input figures it cited for that ratio.

Need help? Claude's metrics don't match my manual Excel verification. How do I identify where the error is?

Generate Excel-ready formulas

Your verification formulas use hardcoded numbers. That works for a one-time check, but in your real workflow you want formulas that pull dynamically from your data sheets. Claude can generate those for you.

  • Switch back to your Claude conversation.
  • Send this follow-up prompt asking Claude to generate dynamic Excel formulas:
Now provide Excel formulas I can paste into my spreadsheet to compute these same ratios dynamically. Assume:
- Income Statement data is on sheet "Income Statement" with FY2025 Actual in column B, rows 2-8
- Balance Sheet data is on sheet "Balance Sheet" with FY2025 in column B, rows 2-11

Give me the formulas in a table with columns: Metric | Excel Formula | Cell Reference Logic

What does this prompt do?

This prompt tells Claude the exact structure of your workbook so it can generate formulas with correct sheet and cell references. The output will be formulas you can paste directly into your Metrics Verification sheet.

The "Cell Reference Logic" column forces Claude to explain which cell maps to which line item. This makes it easy to spot if Claude references the wrong row.

  • Review Claude's formula table. Check that each formula references the correct sheet name ('Income Statement' or 'Balance Sheet') and the correct row numbers.
  • Copy Claude's formulas into your Metrics Verification sheet, placing each one in a new cell below your hardcoded verification formulas.
  • Confirm that the dynamic formulas produce the same results as your hardcoded formulas above.

Save time with formula cross-checking

A quick way to verify is to subtract the dynamic formula result from the hardcoded result. If the difference is zero (or near zero due to rounding), the formula is correct. Type =B5-B2 (or whichever cells correspond) to automate the comparison.

Getting a #REF! or #NAME? error?

These errors usually mean the sheet name in the formula does not exactly match your actual sheet tab name. Check that your tabs are named exactly Income Statement and Balance Sheet (with spaces, no extra characters).

Also verify that the row numbers Claude references match where your data actually sits. If your headers are in row 1 and Revenue is in row 2, Claude's formula should reference row 2 for Revenue.

Need more help? My Excel formulas from Claude are showing errors. How do I fix cell references?

  • Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Your Metrics Verification sheet should now have both hardcoded check formulas and dynamic formulas that pull from your data sheets. Save the workbook if you have not already.

ⓧ I'd like to double check the full setup

Here is what your Metrics Verification sheet should contain at this point:

  • Cell B2: =4980/12450 (result: approximately 0.40 or 40.0%).
  • Cell B3: =1880/12450 (result: approximately 0.151 or 15.1%).
  • Cell B4: =5750/3100 (result: approximately 1.85).
  • Below those: Claude's dynamic formulas referencing 'Income Statement' and 'Balance Sheet' sheets for all 7 ratios.

Your Claude conversation should show the original ROLES prompt, the metrics table response, and the follow-up exchange about Excel formulas.

You have just compressed a 45-minute manual ratio analysis into a few minutes of prompting and verification. Next up, you will tackle the part of financial reporting that eats even more time: variance analysis with narrative commentary.

Perform AI-Assisted Variance Analysis

Your metrics analysis is verified and ready. Now comes the question that actually matters to leadership: why did the numbers land where they did?

NovaTech's Net Income came in at $1.7M against a budget of $2.12M. That is a $420K unfavorable variance. The CFO does not just want the number. The CFO wants to know what drove it and what management should do about it.

What is variance analysis?

Variance analysis compares actual financial results against a budget or forecast. For each line item that deviates materially, you calculate the gap and write commentary explaining the likely business driver.

Writing this commentary is one of the most time-consuming month-end tasks because it requires both numerical analysis and narrative explanation. Claude can draft both in under 2 minutes.

In this step, get ready to:
  • Build a ROLES-framework variance analysis prompt and submit it to Claude.
  • Transfer Claude's variance outputs into a new Excel sheet with formulas.
  • Create a reusable prompt library document with your first saved templates.
Build a variance analysis prompt

Your role shifts from computation to verification and interpretation. Claude handles the repetitive extraction-calculation-formatting cycle. You handle the judgment calls.

  • Switch back to your Financial Analysis Workspace project in Claude.
  • Start a new conversation within the project (your custom instructions will apply automatically).
  • Paste the following ROLES framework prompt into the message field:
Role: You are the FP&A analyst responsible for monthly management reporting at NovaTech Solutions Ltd, a mid-size technology services company.

Objective: Perform a budget-to-actual variance analysis for FY2025. For each line item with a variance exceeding 5% of budget, provide:
1. The dollar variance (favorable or unfavorable)
2. The percentage variance
3. A likely business driver (based on common patterns in technology services companies)
4. A recommended management action

Data (all figures in $000s):
| Line Item | Actual | Budget | Prior Year |
|---|---|---|---|
| Revenue | 12,450 | 13,000 | 11,200 |
| Cost of Services | 7,470 | 7,800 | 6,720 |
| Gross Profit | 4,980 | 5,200 | 4,480 |
| Operating Expenses | 3,100 | 2,900 | 2,800 |
| Operating Income | 1,880 | 2,300 | 1,680 |
| Interest Expense | 180 | 180 | 200 |
| Net Income | 1,700 | 2,120 | 1,480 |

Limits:
- Present the variance table first, then narrative commentary below
- Format the narrative as bullet points suitable for a CFO briefing
- Mark any hypothesized drivers with [HYPOTHESIS] since you do not have the detailed sub-ledger data
- Do not invent specific operational details (client names, project names, etc.)

Safeguards: Flag with [NEEDS VERIFICATION] any conclusion that would require sub-ledger data to confirm. Note that your business driver hypotheses are educated guesses that I must verify against internal records before including in any report.
  • Press Enter to send the prompt.

What does this prompt do?

  • The Role assigns Claude a specific FP&A identity so it frames outputs for management consumption.
  • The Limits section forces Claude to tag its guesses with [HYPOTHESIS]. This is how you separate what AI knows (the math) from what it guesses (why revenue missed target).
  • The 5% materiality threshold ensures Claude only flags variances worth management attention.

Claude should return a variance table followed by narrative bullet points. You should see material variances flagged for Revenue (approximately -$550K unfavorable), Operating Expenses (+$200K unfavorable), and the resulting Net Income shortfall (-$420K unfavorable).

Each narrative bullet should include [HYPOTHESIS] or [NEEDS VERIFICATION] tags wherever Claude is guessing at business drivers.

Claude not tagging its hypotheses?

If Claude's response lacks [HYPOTHESIS] or [NEEDS VERIFICATION] tags, check that you are inside your Financial Analysis Workspace project (the custom instructions reinforce this behaviour). If you are on the free plan and pasting instructions manually, add them to the top of your message before the prompt.

help me get Claude to use HYPOTHESIS and NEEDS VERIFICATION tags in its variance analysis output

Transfer variance outputs to Excel

Claude has done the heavy lifting. Now you transfer the results into a format you can share, update, and audit.

  • Switch back to your NovaTech_Analysis.xlsx workbook in Excel.
  • Create a new sheet and name it Variance Analysis.
  • Set up the following column headers in row 1:
  • A1: Line Item
  • B1: Actual
  • C1: Budget
  • D1: $ Variance
  • E1: % Variance
  • F1: Commentary
  • Enter the line item names in column A (Revenue, Cost of Services, Gross Profit, Operating Expenses, Operating Income, Interest Expense, Net Income) starting in row 2.
  • Enter the Actual values in column B and the Budget values in column C, matching the data from your Income Statement sheet.
  • In cell D2, enter the dollar variance formula:
=B2-C2
  • In cell E2, enter the percentage variance formula:
=(B2-C2)/C2
  • Copy both formulas down through row 8 (one row per line item).
  • Format column E as a percentage.

What do these formulas tell you?

The dollar variance (=B2-C2) shows the raw gap between what actually happened and what the budget predicted. A negative number for Revenue means you earned less than planned (unfavorable). A negative number for an expense means you spent less than planned (favorable).

The percentage variance (=(B2-C2)/C2) scales the gap relative to what was budgeted. A $550K miss on $13M revenue is a 4.2% variance. A $200K miss on $2.9M expenses is 6.9%. The percentage reveals which line items deviate most significantly.

  • In column F, paste Claude's narrative commentary for each material variance. Keep the [HYPOTHESIS] and [NEEDS VERIFICATION] tags intact.

Why keep the tags in your spreadsheet?

Those tags are your audit trail. They mark the boundary between what AI calculated (verifiable math) and what AI hypothesized (business drivers that need your domain knowledge to confirm). Before any of this commentary goes into a board pack, you strip the tags by replacing each hypothesis with confirmed information from your sub-ledger data.

  • Save NovaTech_Analysis.xlsx.
  • Verify that Revenue shows approximately -$550 in column D and approximately -4.2% in column E.
  • Verify that Operating Expenses shows +$200 in column D and approximately +6.9% in column E.

Variance percentages look wrong?

Make sure your formula divides by column C (Budget), not column B (Actual). The denominator should always be the value you are comparing against. Also confirm column E is formatted as a percentage, not as a decimal.

help me fix my Excel variance percentage formula

Build a reusable variance commentary prompt template

You have a working variance prompt. Now save it as a reusable template so that next month-end, you swap in real numbers and get a first draft in seconds instead of crafting the prompt from scratch.

  • Create a new document in your preferred tool (Word, Notepad, or a note-taking app).
  • Name it My Finance Prompt Library.
  • Paste in the ROLES framework reference as your first template:
ROLES FRAMEWORK REFERENCE
--------------------------
R (Role): Assign a specific professional identity.
O (Objective): State exactly what output you need.
L (Limits): Constrain the format and scope.
E (Evidence): Require sourcing and line item references.
S (Safeguards): Prevent hallucination. Stop and ask for missing data.
  • Below the ROLES reference, paste in the variance analysis template:
VARIANCE ANALYSIS TEMPLATE
--------------------------
Role: FP&A analyst at [Company Name], a [industry] company.
Objective: Budget-to-actual variance analysis for [period].
Materiality threshold: [X]% of budget.
Output format: Variance table + CFO briefing bullets.
Safeguards: Mark hypotheses. Flag items needing sub-ledger verification.

[PASTE DATA HERE]

What makes this template reusable?

The bracketed placeholders mark where you swap in your company's specific details each time. The structure, safeguards, and output format stay the same. Next month-end, you replace the bracketed values, paste your data at the bottom, and send.

Crafting a good prompt takes 10 minutes the first time. Every subsequent use takes 30 seconds. Across a year of month-ends, that compounds into hours of recovered time.

  • Save your My Finance Prompt Library document.
  • Confirm it contains two templates: the ROLES Framework Reference and the Variance Analysis Template.

Keep your library accessible

Save this document somewhere you can reach during month-end pressure. Your desktop, a pinned note, or a bookmarked cloud document all work. The value of a prompt library is zero if you cannot find it when you need it.

You now have a complete variance analysis workflow: prompt, verified outputs, Excel formulas, and a reusable template. Next up, you will build a verification system that ensures every AI-generated number is safe to share with stakeholders.

Master Verification and Hallucination Detection

You have used Claude to generate a metrics analysis and variance commentary. Your manager asks: "Can I trust these numbers for the board pack?"

This is the make-or-break moment. If you say "yes" and a number is wrong, your credibility is at risk. If you say "I don't know," you have not added value. The correct answer is: "Yes, here's my verification process."

Why verification matters more than speed

A single incorrect figure in a board report can trigger restatements, audit findings, or loss of stakeholder trust. The cost of one unverified AI hallucination in finance far exceeds the time saved by using AI in the first place.

"I checked it manually" is not scalable when AI is generating 20 data points in seconds. You need a systematic verification framework that is fast, repeatable, and documentable.

In this step, get ready to:
  • Apply a three-layer verification framework to your earlier AI outputs.
  • Build a reusable verification checklist for your prompt library.
  • Test Claude's hallucination safeguards with a deliberately incomplete data request.
Apply the three-layer verification framework

You can use AI to verify AI. By asking Claude to show its work, re-derive calculations, and cross-check its own outputs, you create a layered verification system.

The three layers are: Source Match (does the output match your data?), Calculation Audit (are the formulas correct?), and Reasonableness Check (do results make business sense?).

Layer 1: Source match

The first layer confirms that Claude used the exact numbers you provided. If any input was misread or swapped, every calculation downstream is wrong.

  • In your Claude conversation (the same one from Steps 2 and 3), send this verification prompt:
Verification request: Please list every numerical input you used in the previous analysis. For each, state:
- The figure
- Where you got it from (which line item I provided)
- Confirm it matches my original data exactly

If there is ANY discrepancy between what I provided and what you used, flag it immediately.

What does this prompt do?

This forces Claude to enumerate every number it used and trace it back to your source data. If Claude hallucinated a figure or misread a value, this prompt surfaces the mismatch before it reaches anyone else.

  • Compare Claude's listed figures against your NovaTech_Analysis.xlsx source data on the Income Statement and Balance Sheet sheets.
  • Check every number. Any mismatch between what Claude listed and what your spreadsheet says means a hallucination was caught.
Layer 2: Calculation audit

The second layer checks whether the formulas were applied correctly. Even with correct inputs, an LLM can produce wrong arithmetic because it predicts likely-looking numbers rather than computing them.

  • Send this follow-up prompt to Claude:
For the following three metrics, show me the full calculation step-by-step, including the raw numbers plugged into the formula:
1. Net Profit Margin FY2025
2. Current Ratio FY2025
3. The Revenue budget variance percentage

Show each step on a separate line. Do not skip steps.

What does this prompt do?

By requesting step-by-step derivations, you can see exactly where a calculation might go wrong. If Claude shows Net Profit Margin as 1700/12450 = 13.7%, you can verify that formula and result independently.

  • Open your Metrics Verification sheet in Excel.
  • Compare Claude's step-by-step calculations against your own formulas from Step 2. The Net Profit Margin should show 1700/12450. The Current Ratio should show 5750/3100.
  • For the Revenue budget variance percentage, verify it shows (12450-13000)/13000.
Layer 3: Reasonableness check

The third layer asks whether the results make business sense. A mathematically correct ratio can still be an outlier that signals a data entry error or an unusual business event worth investigating.

  • Send this final verification prompt to Claude:
Review the metrics you calculated. Flag anything that seems unusual for a mid-size technology services company:
- Are any ratios outside typical industry ranges?
- Do any year-over-year changes seem implausibly large?
- Is there any internal inconsistency (e.g., margins improving while revenue falls)?

For each flag, explain why it caught your attention and what I should investigate.

What does this prompt do?

This leverages Claude's pattern-matching strength. While Claude cannot verify your specific company's context, it can flag ratios that look unusual compared to industry norms. Think of it as a second pair of eyes catching things you might overlook under time pressure.

  • Review Claude's flags. For NovaTech, you might see flags around the operating expense increase or the accounts receivable growth outpacing revenue growth.
  • Note which flags align with what you already know from the variance analysis. This cross-reference strengthens your confidence in the outputs.

Claude not flagging anything unusual?

If Claude says "everything looks normal," try adding more specific prompting: "What is the typical current ratio range for technology services companies? Is NovaTech's 1.85 within that range?" Sometimes you need to narrow the question to get useful flags.

help me get Claude to flag unusual financial ratios in my verification process

Build your verification checklist

Now that you have experienced the three layers in practice, turn them into a permanent tool you can use every time AI generates financial output.

  • Open your "My Finance Prompt Library" document.
  • Add a new section at the bottom with the heading "AI OUTPUT VERIFICATION CHECKLIST" by pasting the following:
AI OUTPUT VERIFICATION CHECKLIST
---------------------------------
Before sharing ANY AI-generated financial output:

[ ] Source Match: Confirmed all input numbers match my source data
[ ] Calculation Audit: Spot-checked 3+ calculations manually in Excel
[ ] Reasonableness: Ratios/trends make business sense for the industry
[ ] Assumption Check: All [ASSUMPTION] and [HYPOTHESIS] tags reviewed
[ ] Sensitivity: Identified which outputs change if one input is wrong
[ ] Audit Trail: Saved the Claude conversation for reference
[ ] Sign-off: I can explain every number if questioned

Red Flags That Demand Extra Verification:
- Any number Claude provides that you did NOT provide as input
- Percentages that do not correspond to the underlying figures
- Commentary that references specific details you never mentioned
- Trends that contradict what you know about the business

What does this checklist do?

The 7 checkboxes give you a repeatable sign-off process. The 4 red-flag indicators tell you exactly when to dig deeper. Together, they turn "I trust it" into "I verified it" with a documentable trail.

The Sensitivity check is especially important. If changing one input by 5% flips a ratio from "healthy" to "concerning," that number deserves manual verification regardless of what Claude produced.

  • Save your prompt library document.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your prompt library document with the new verification checklist section added below your existing templates.

ⓧ I'd like to double check the full document

Your prompt library document should now have these four sections:

ROLES FRAMEWORK REFERENCE
--------------------------
R = Role: Assign a specific identity
O = Objective: State exactly what you need
L = Limits: Constrain the output
E = Evidence: Require sourcing
S = Safeguards: Prevent hallucination


VARIANCE ANALYSIS TEMPLATE
--------------------------
Role: FP&A analyst at [Company Name], a [industry] company.
Objective: Budget-to-actual variance analysis for [period].
Materiality threshold: [X]% of budget.
Output format: Variance table + CFO briefing bullets.
Safeguards: Mark hypotheses. Flag items needing sub-ledger verification.

[PASTE DATA HERE]


AI OUTPUT VERIFICATION CHECKLIST
---------------------------------
Before sharing ANY AI-generated financial output:

[ ] Source Match: Confirmed all input numbers match my source data
[ ] Calculation Audit: Spot-checked 3+ calculations manually in Excel
[ ] Reasonableness: Ratios/trends make business sense for the industry
[ ] Assumption Check: All [ASSUMPTION] and [HYPOTHESIS] tags reviewed
[ ] Sensitivity: Identified which outputs change if one input is wrong
[ ] Audit Trail: Saved the Claude conversation for reference
[ ] Sign-off: I can explain every number if questioned

Red Flags That Demand Extra Verification:
- Any number Claude provides that you did NOT provide as input
- Percentages that do not correspond to the underlying figures
- Commentary that references specific details you never mentioned
- Trends that contradict what you know about the business


THREE-LAYER VERIFICATION PROMPTS
---------------------------------
Layer 1 - Source Match:
"Please list every numerical input you used. For each, state the figure, where you got it from, and confirm it matches my original data exactly."

Layer 2 - Calculation Audit:
"For [specific metrics], show me the full calculation step-by-step, including the raw numbers plugged into the formula. Show each step on a separate line."

Layer 3 - Reasonableness Check:
"Review the metrics you calculated. Flag anything unusual for a [industry] company: ratios outside typical ranges, implausibly large YoY changes, or internal inconsistencies."
Practice catching a deliberate error

Time to test your verification skills with a deliberately tricky request. You will ask Claude a question that cannot be answered with the data you provided. A well-configured AI should refuse to guess.

  • Send this intentionally tricky prompt to Claude in the same conversation:
Quick question: Based on the NovaTech data, what was the company's EBITDA for FY2025?

Why is this a trap?

EBITDA requires Depreciation & Amortization figures. You never provided those in the NovaTech data. Claude should flag that it cannot calculate EBITDA because the D&A data is missing.

If Claude invents a number anyway, you have caught a hallucination in real-time. This demonstrates exactly why the safeguard instruction ("stop and ask for missing data rather than guessing") matters.

  • Read Claude's response carefully.

✔️ Claude refused to calculate EBITDA

Claude correctly identified that Depreciation & Amortization data was not provided and declined to guess. This confirms your project instructions are working as intended. The safeguard instruction is protecting you from invisible errors.

ⓧ Claude invented an EBITDA number

You have caught a live hallucination. Claude produced a plausible-looking number with no data to support it. This is exactly the kind of error that slips into board reports when verification is skipped.

Try reinforcing the safeguard by sending: "You just provided an EBITDA figure, but I never gave you Depreciation & Amortization data. Please review your response and explain where that number came from." This teaches Claude to be more cautious in subsequent responses.

What did this demonstrate?

Whether Claude refused or hallucinated, you now understand the boundary. LLMs predict likely-looking text. When a financial figure "looks right" based on patterns, the model may produce it even without supporting data. Your verification checklist exists to catch these moments systematically.

You now have a verification system that separates professionals who use AI well from those who get burned by it. Next up, you will build out your complete finance prompt library with templates for reconciliations, financial modelling, and month-end tracking.

Build Your Finance Prompt Library

You have learned structured prompting and verification. You can extract metrics, generate variance commentary, and systematically catch hallucinations.

But it is Friday afternoon, you are under month-end pressure, and you need to produce a reconciliation check, a financial model summary, AND a close progress update. You do not have time to craft the perfect prompt from scratch each time. By building a library of tested, reusable prompts, you create a personal AI toolkit that becomes more valuable with every use.

In this step, get ready to:
  • Create a reconciliation analysis prompt template.
  • Create a financial modelling assistant prompt template.
  • Create a month-end progress tracker prompt template.
Create your month-end reconciliation prompt

Reconciliation is one of the most repetitive month-end tasks. You compare two data sets, find discrepancies, categorize them, and suggest fixes. A well-structured prompt lets Claude do the heavy lifting while you focus on the judgment calls.

  • Open your My Finance Prompt Library document from earlier.
  • Add the following reconciliation template as a new section below your existing content:
RECONCILIATION ANALYSIS TEMPLATE
---------------------------------
Role: Senior accountant performing month-end reconciliation review at [Company Name].

Objective: I will provide two data sets that should reconcile. Your task:
1. Identify the total for each data set
2. Calculate the difference
3. List every individual discrepancy (line items that do not match)
4. Categorize each discrepancy as: Timing difference, Classification error, Missing entry, or Unexplained
5. Suggest the journal entry or action needed to resolve each item

Limits:
- Present findings in a table format
- Sort discrepancies by dollar amount (largest first)
- Provide Excel formulas I can use to flag future discrepancies automatically

Safeguards:
- Do not assume any discrepancy is immaterial
- If a discrepancy could have multiple causes, list all possibilities
- Flag anything that could indicate a control failure with [CONTROL RISK]

[PASTE DATA SET 1 HERE]
[PASTE DATA SET 2 HERE]

What makes this template effective?

The Objective section breaks the task into 5 numbered steps. This forces Claude to work through the reconciliation systematically instead of jumping to conclusions.

The [CONTROL RISK] tag in the Safeguards section ensures Claude flags discrepancies that could indicate a breakdown in internal controls. These are items your audit team needs to know about immediately.

  • Save your prompt library document.
  • Confirm the template has all five Objective items, three Limits, three Safeguards, and two data paste areas at the bottom.
Create your financial modelling assistant prompt

Financial models require precise formula construction, clear assumption labeling, and sensitivity awareness. This template turns Claude into a modelling co-pilot that traces every assumption and flags the ones that matter most.

  • Add the following template as the next section in your prompt library document:
FINANCIAL MODEL HELPER
-----------------------
Role: Financial modelling analyst assisting with [model type: DCF / 3-statement / budget / forecast].

Objective: Help me build/review a financial model. Current task: [describe what you need]

Context:
- Company: [name and industry]
- Model purpose: [board presentation / investment decision / budget planning]
- Time horizon: [number of years]
- Key assumptions: [list your assumptions]

Limits:
- Provide Excel formulas compatible with my existing model structure
- Label all assumptions clearly with [A1], [A2], etc. so I can trace them
- Use absolute cell references where appropriate for sensitivity analysis
- Present outputs in a format I can paste directly into Excel

Safeguards:
- Flag any assumption that significantly impacts the output with [SENSITIVE]
- If my assumptions seem unrealistic for the industry, challenge me
- Do not invent growth rates, discount rates, or market data. Ask me for these.
- Note where circular references might occur in Excel

[PASTE MODEL STRUCTURE OR DATA HERE]

Why label assumptions with [A1], [A2]?

When you run sensitivity analysis, you need to know exactly which assumptions drive the output. The [A1], [A2] labels give you a traceable reference for each assumption Claude uses.

The [SENSITIVE] tag highlights assumptions where a small change in input creates a large change in output. These are the ones to stress-test before any board presentation.

  • Save your prompt library document.
  • Confirm the template includes the Context section with four fill-in fields, four Limits with the assumption labeling and absolute reference instructions, and four Safeguards with the [SENSITIVE] tag.
Create your month-end checklist tracker prompt

Month-end close involves dozens of tasks with dependencies. This template turns Claude into a project manager that tracks progress, identifies bottlenecks, and even drafts your status update email for the Finance Director.

  • Add the following template as the final section in your prompt library document:
MONTH-END PROGRESS TRACKER
----------------------------
Role: Finance team lead managing month-end close at [Company Name].

Objective: Based on the checklist and status updates I provide, generate:
1. A progress summary showing % complete by category
2. Items at risk of missing deadline (flag in red)
3. Suggested priority order for remaining items based on dependencies
4. Draft status update email for the Finance Director

Limits:
- Present the progress summary as a table
- Group items by: Reconciliations | Journals | Reporting | Review & Approval
- Include estimated time to complete each remaining item
- Format the email in professional tone, 3 paragraphs maximum

Safeguards:
- Do not mark anything as "complete" unless I explicitly say so
- If dependencies mean Item X must finish before Item Y, flag the critical path
- Ask me for clarification if any status is ambiguous

[PASTE CHECKLIST WITH CURRENT STATUS HERE]

What does this template produce?

The Objective asks for four distinct outputs in one pass: a progress table, risk flags, a priority sequence, and a draft email. The Limits section constrains each output so you get consistent formatting every time.

The critical path safeguard is particularly valuable. If your bank reconciliation must complete before you can finalize the cash flow statement, Claude will flag that dependency instead of letting you work out of order.

  • Save your prompt library document.
  • Confirm the template has four Objective items, four Limits including the grouping categories, and three Safeguards including critical path flagging.

Organize prompts by reporting cycle

As your library grows, group prompts by when you use them: daily (cash summaries), weekly (variance flags), monthly (management commentary), quarterly (investor updates), annual (budget narratives). This makes it easy to find the right template under pressure.

✔️ Awesome, I've got everything!

Great. Double check you have saved your prompt library document with all three new templates.

ⓧ I'd like to double check the new templates

Here are all three templates added in this step. Confirm your document matches:

RECONCILIATION ANALYSIS TEMPLATE
---------------------------------
Role: Senior accountant performing month-end reconciliation review at [Company Name].

Objective: I will provide two data sets that should reconcile. Your task:
1. Identify the total for each data set
2. Calculate the difference
3. List every individual discrepancy (line items that do not match)
4. Categorize each discrepancy as: Timing difference, Classification error, Missing entry, or Unexplained
5. Suggest the journal entry or action needed to resolve each item

Limits:
- Present findings in a table format
- Sort discrepancies by dollar amount (largest first)
- Provide Excel formulas I can use to flag future discrepancies automatically

Safeguards:
- Do not assume any discrepancy is immaterial
- If a discrepancy could have multiple causes, list all possibilities
- Flag anything that could indicate a control failure with [CONTROL RISK]

[PASTE DATA SET 1 HERE]
[PASTE DATA SET 2 HERE]


FINANCIAL MODEL HELPER
-----------------------
Role: Financial modelling analyst assisting with [model type: DCF / 3-statement / budget / forecast].

Objective: Help me build/review a financial model. Current task: [describe what you need]

Context:
- Company: [name and industry]
- Model purpose: [board presentation / investment decision / budget planning]
- Time horizon: [number of years]
- Key assumptions: [list your assumptions]

Limits:
- Provide Excel formulas compatible with my existing model structure
- Label all assumptions clearly with [A1], [A2], etc. so I can trace them
- Use absolute cell references where appropriate for sensitivity analysis
- Present outputs in a format I can paste directly into Excel

Safeguards:
- Flag any assumption that significantly impacts the output with [SENSITIVE]
- If my assumptions seem unrealistic for the industry, challenge me
- Do not invent growth rates, discount rates, or market data. Ask me for these.
- Note where circular references might occur in Excel

[PASTE MODEL STRUCTURE OR DATA HERE]


MONTH-END PROGRESS TRACKER
----------------------------
Role: Finance team lead managing month-end close at [Company Name].

Objective: Based on the checklist and status updates I provide, generate:
1. A progress summary showing % complete by category
2. Items at risk of missing deadline (flag in red)
3. Suggested priority order for remaining items based on dependencies
4. Draft status update email for the Finance Director

Limits:
- Present the progress summary as a table
- Group items by: Reconciliations | Journals | Reporting | Review & Approval
- Include estimated time to complete each remaining item
- Format the email in professional tone, 3 paragraphs maximum

Safeguards:
- Do not mark anything as "complete" unless I explicitly say so
- If dependencies mean Item X must finish before Item Y, flag the critical path
- Ask me for clarification if any status is ambiguous

[PASTE CHECKLIST WITH CURRENT STATUS HERE]

Your library in action

The first time you craft a new prompt takes 10 minutes. Every subsequent use takes 30 seconds because you only swap in the new data. Across a year of month-ends, that compounds into days of recovered time.

Your finance prompt library is complete with 6 tested templates ready for Monday morning. Next up, you will tackle a Secret Mission that combines everything you have learned into an automated ageing report analyzer.

Secret mission

Build an Ageing Report Analyzer

The Credit Controller needs help. Accounts receivable has ballooned, and the Finance Director wants a receivables ageing analysis with risk categories and collection priority recommendations by end of day. Can you build an automated prompt that categorizes receivables by risk tier and generates prioritized collection actions?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally and uses Claude's free tier, so there are no ongoing costs. Decide whether to keep your files for future reference or delete them entirely.

Resources you created:

  • NovaTech_Analysis.xlsx workbook (local file, 5 sheets: Income Statement, Balance Sheet, Metrics Verification, Variance Analysis, Ageing Analysis).
  • My Finance Prompt Library document (local file, 7 templates).
  • Claude "Financial Analysis Workspace" project with conversation history (free, no ongoing cost).

Keep everything running

No action needed. Choose this if you plan to continue using your finance prompt library and NovaTech workbook for practice or reference.

  • Your Claude project remains available at no cost. You can return to it anytime to review conversation history or run new prompts.
  • Your Excel workbook stays on your computer with all verification formulas and conditional formatting intact.
  • Your prompt library document is ready to use at your next month-end or analysis task.

Pause - I'll come back to this later

Since all resources are local files and a free Claude project, there is nothing to pause or shut down. Everything stays as-is at zero cost.

  • Close the NovaTech_Analysis.xlsx workbook in Excel.
  • Close your Claude browser tab. Your project and conversations persist automatically.
  • Your prompt library document remains wherever you saved it.

Delete - I don't want to use this again

Remove all project files from your computer. Your Claude conversations can be deleted from within the Claude interface.

  • Delete NovaTech_Analysis.xlsx from wherever you saved it on your computer. Right-click the file and select Move to Trash (macOS) or Delete (Windows).
  • Delete your My Finance Prompt Library document using the same method.
  • In Claude, open Projects in the left sidebar. Open the "Financial Analysis Workspace" project and delete it along with its conversations.

Nice Work!

Nice Work!

Nice work! You just built a complete AI-powered financial analysis workflow using Claude and Excel, from raw financial statements to verified metrics, variance commentary, and a reusable prompt library.

You've learned how to:

  • Perform a full financial statement analysis using Claude, including profitability, liquidity, and efficiency metrics with year-over-year comparisons and budget-to-actual variance analysis with narrative commentary suitable for a CFO briefing.
  • Apply the ROLES prompting framework (Role, Objective, Limits, Evidence, Safeguards) to produce structured, audit-ready outputs from AI and build a personal prompt library of 7 tested, reusable finance templates.
  • Build a three-layer verification system (Source Match, Calculation Audit, Reasonableness Check) that catches AI hallucinations before they reach stakeholders or board reports.
  • Secret Mission: Built an automated ageing report analyzer that categorizes receivables by risk tier and generates prioritized collection recommendations.

Ready to quiz yourself?