Excel Agent Mode vs. Claude in Excel for Financial Models

Excel Agent Mode and Claude in Excel can both create and edit workbooks from natural-language instructions. In a financial model, however, a polished layout is not evidence that the statements link correctly or the valuation is valid. This comparison examines the output from one matched prompt and treats the result as a case study, not a permanent ranking of either product.

Microsoft describes Agent Mode as an in-place content creation and editing agent in Microsoft 365 apps. Anthropic says Claude in Excel can build models, audit formulas, work across linked workbooks, and run sensitivity analysis. Access, naming, supported models, and plan requirements can change; see the current Microsoft Agent Mode guide and Anthropic finance-agents overview.

What this test asked each tool to build

Each tool received the same core task:

Build a fully integrated three-statement model with a 10-year forecast, transparent assumptions, working-capital schedules, a PP&E roll-forward, FCFF, a DCF valuation, and a two-way WACC/terminal-growth sensitivity table. Use formulas rather than hard-coded forecast outputs.

The requested workbook contained three tabs: ASSUMPTIONS, CALCULATIONS, and DCF VALUATION. Inputs included revenue growth, margins, days sales outstanding, days inventory, days payable, capital expenditure, depreciation, tax, WACC components, and terminal growth.

This setup has an important limitation: a model generated from a prompt without company filings or a supplied historical balance sheet must invent placeholder assumptions. Those numbers can demonstrate mechanics, but they cannot support an investment decision.

How the workbooks were evaluated

• Are input cells visually distinct from formulas?
• Do all forecast rows link to the assumptions rather than contain unexplained hard-coded values?
• Do the income statement, balance sheet, and cash-flow statement reconcile?
• Does the PP&E schedule use a consistent opening balance, CAPEX, depreciation, and closing balance?
• Are working-capital changes signed and linked correctly in cash flow?
• Is FCFF calculated consistently from operating profit, tax, D&A, CAPEX, and change in working capital?
• Are forecast cash flows and terminal value discounted with the same WACC convention?
• Does terminal growth remain below WACC?
• Does the sensitivity table reference the correct valuation output?

Excel Agent Mode: results from this workbook

Assumptions and presentation

Agent Mode created the requested three-tab structure and supplied coherent placeholder assumptions. Inputs and calculations were separated clearly enough to trace the basic model.

The assumptions were usable for a demonstration, but they were not sourced company data. A professional model would replace them with dated historical information, documented operating drivers, and market inputs from approved sources.

Three-statement calculations

Revenue, cost of goods sold, operating expenses, and EBIT followed understandable formulas. The working-capital rows used the requested day-based approach. The weakness appeared in the PP&E schedule: depreciation and net book value did not follow one consistent roll-forward throughout the workbook.

That defect matters because depreciation affects EBIT, tax, net income, operating cash flow, PP&E, and ultimately FCFF. A workbook can remain visually tidy while carrying the error into several statements.

DCF valuation

The DCF section was the strongest part of the Agent Mode output in this run. It used a recognizable WACC calculation, applied the discount rate consistently, reconciled enterprise value to equity value through net debt, and produced a functioning WACC/terminal-growth table.

That does not eliminate the PP&E problem. A correct-looking discount schedule still produces an unreliable valuation if the forecast FCFF feeding it is wrong.

Claude in Excel: results from this workbook

Assumptions and presentation

Claude produced the cleaner layout in this test. Inputs were grouped logically and the workbook was easier to scan. Several financial assumptions were nevertheless implausible or incorrectly derived, including the equity-risk-premium, beta, cost-of-debt, and WACC logic used in the generated file.

Formatting should therefore be scored separately from finance logic. A model reviewer should trace every cost-of-capital input to its source and inspect the WACC formula rather than accepting a percentage because the cell is professionally styled.

Three-statement calculations

The income statement and working-capital sections were readable, but this output contained incorrect cell references for changes in working capital and capital expenditure. Cash was also used as a balancing item without a fully explained financing and cash-roll-forward policy.

These are not cosmetic issues. An incorrect CAPEX reference and change-in-working-capital range directly change FCFF.

DCF valuation

The Claude workbook presented a clean discount schedule and sensitivity table. Tracing the formulas showed inconsistent discounting: forecast cash flows referenced a debt-to-equity input where WACC should have been used, while terminal value used WACC. Because the discount rate and upstream FCFF were inconsistent, the resulting equity value was not decision-ready.

Which tool performed better?

In this single run, Excel Agent Mode produced the stronger DCF mechanics, while Claude produced the cleaner and more auditable visual structure. Neither workbook met an investment-grade standard without manual correction.

The conclusion should not be generalized beyond the tested files. Outputs can change with the model version, application release, source workbook, prompt, connected data, and follow-up instructions. A fair repeat test would run each tool several times, record the product version, use identical historical data, and score formulas against a predetermined checklist.

How to use AI safely for financial modeling

1. Provide historical data. Do not ask the tool to invent the starting balance sheet or market assumptions.
2. Separate inputs, formulas, and checks. Use consistent colors and protect formula cells after review.
3. Require audit checks. Add balance-sheet balance, cash-roll-forward, debt-roll-forward, and sources/uses checks.
4. Trace precedents. Inspect every line that feeds EBITDA, taxes, working capital, CAPEX, debt, FCFF, WACC, and terminal value.
5. Test sensitivities manually. Confirm that increasing WACC lowers value and that terminal growth below WACC behaves as expected.
6. Change one assumption at a time. Verify that statements and valuation update in the expected direction.
7. Retain a human reviewer. AI-generated workbooks are drafts until a qualified modeler validates the accounting, finance theory, formulas, and source data.

A better prompt for the next run

Before editing the workbook, list the proposed schedules and validation checks. Do not invent company data: mark missing inputs clearly. Build formulas with no unexplained hard-coded forecast values. Include a balance-sheet check, cash-roll-forward check, debt-roll-forward check, and a DCF check requiring WACC greater than terminal growth. After building, audit every formula feeding FCFF and enterprise value and report any unresolved issue.

This prompt does not guarantee correctness, but it makes missing data and validation requirements explicit and gives the reviewer a clearer audit trail.