Claude Using Excel Part 1: 5 Practical Financial Analysis Use Cases

The goal is simple: remove mechanical work and get to insights faster. Below I walk through five high‑impact analyses I build inside Excel using Claude and share practical prompts and tips you can reuse.

Subscribe to our YouTube Channel for Free Videos on FP&A. Click here to Subscribe.

1. Price • Volume • Mix • FX variance analysis

Price volume mix analysis isolates what drove revenue changes: price, quantity, product mix, and foreign exchange. With Claude inside Excel you point it at your budget and actuals table and it builds the decomposition, variance waterfall, and a product‑level bridge (for example, oil, gas, ATF).

Prompt : Using this dataset calculate price/volume/mix/FX variance analysis for actuals vs budget revenues from product-level data (Oil, Gas, ATF).

2. Capital budgeting: NPV, IRR and payback

I use a common case: initial investment, multi‑year cash flows, discount rate to demonstrate Claude calculating NPV, XIRR/IRR, payback and discounted payback. Rather than writing individual Excel formulas, Claude reads the cash flow table, creates the discount factors and returns the same numbers plus a short recommendation based on NPV and IRR.

Using the given dataset Calculate NPV, IRR, Payback Period, and Discounted Payback Period and provide recommendation.

3. Cohort retention analysis

Cohort analysis groups customers by signup month and tracks retention month by month. Traditionally this requires helper columns, pivot tables and conditional formatting. Claude can process the raw customer-month data, produce 12 or 24‑month retention tables and replace hardcoded values with dynamic formulas that reference the source sheet.

Create a cohort retention matrix grouped by signup month with monthly retention percentages. Expand cohort retention matrix from 12 months to 24 months. Expand cohort retention matrix from 12 months to 24 months

4. Horizontal and vertical analysis

Horizontal analysis measures change over time. Vertical analysis expresses each line as a percentage of a base (usually revenue). Claude automates both across income statements and balance sheets, inserts correct absolute references and formats results so the formulas are visible and auditable.

Perform horizontal and vertical analysis on this financial dataset with visible formulas.

5. Ratio analysis

Claude builds profitability, liquidity, efficiency and leverage ratios using formulas that reference your financial statements. It even adds valuation ratios when relevant. The result is a ready‑to‑explain dashboard you can use for stakeholder conversations.

Perform Ratio Analysis across Profitability, Liquidity, Leverage, and Valuation categories using formulas referencing the FS sheet.

Why this matters

Automation does not replace judgment. It removes repetitive execution so you can spend time answering the right questions: is the business profitable, stable, efficient and where do risks live. When you combine domain thinking with Claude inside Excel, execution speed improves dramatically and your recommendations become the valuable part of the conversation.

Tips and Best practices

Be explicit: include whether you want formulas (dynamic) or hardcoded numbers.
Allow it to create and overwrite temporary sheets when expanding outputs (for example 12 to 24 months).
Co create with claude providing intrsuctions step by step.

Conclusion

Using Claude inside Excel turns repetitive FP&A tasks into a few structured prompts. Claude using excel is about leverage: same analytical thinking, much faster execution. Use that time to tell the story behind the numbers.

FAQs

Q1 What inputs does Claude need to run a PVM variance analysis?

Budget and actual revenue tables broken down by product, units, price and currency. The clearer the column headers and the cleaner the tables, the faster it produces an accurate bridge.

Q2 Can Claude produce formulas rather than hardcoded results?

Yes. Include in your prompt that you want formulas. Claude can replace static numbers with Excel formulas that reference the source sheet so results remain dynamic.

Q3 Is the output auditable?

Claud creates a log and shows the exact steps. You should always review those calculations before sharing with stakeholders.

Q4 How should I prompt Claude for capital budgeting?

Provide initial investment, each period cash flows, discount rate and ask for NPV, IRR, payback and discounted payback with formulas and a short decision summary.

Q5 Can this approach scale to multi‑entity or multi‑year models?

Yes. Claude handles repetitive structure well. The main work is organizing the source data consistently across entities and years so prompts remain simple and repeatable.

I encourage you to experiment with these prompts and keep the focus on interpretation rather than mechanics.

Join the FP&A Community and Keep Learning

If you are serious about FP&A then checkout CGFPA® Certified Global FP&A Professional Program. A six-month in-depth CPD accredited FP&A Certification Download the CGFPA® Brochure.

You can find more information and bunch of free FP&A resources, including books, courses, and free templates, on the FP&A Professionals Institute website www.fpnaprofessionals.com

Subscribe to Weekly FP&A Jobs Newsletter (every Wednesday)

Subscribe to our YouTube Channel for Free Videos on FP&A. Click here to Subscribe.

Find out about our Weekly Live Webinars: https://fpnaprofessionals.com/