Executives don't want to dig through raw P&L data; they want immediate answers. This guide shows you how to use Claude AI to build a dynamic Financial KPI Dashboard in Excel. You will learn how to prompt Claude to generate the formulas required to summarize key metrics like Gross Profit Margin, Operating Expenses, and Net Income, turning flat data into an executive-ready view instantly.
The Final KPI Dashboard
By the end of this process, you will have a structured summary table that automatically calculates critical financial health metrics based on your raw input data:
A well-structured dashboard prevents the need to manually recalculate metrics during the month-end close, as discussed in our AI for Finance Teams overview.
Raw Data Setup
For Claude to generate functional Excel formulas, your raw data must be formatted vertically (tabular format) rather than horizontally. Ensure you have a central data tab with columns defining the Date, Account Category, and Amount.
If you struggle to organize your raw exports efficiently, checking off preliminary data-cleaning steps via an automated month-end close checklist is highly recommended.
The Exact Claude Prompt for KPIs
Use the following prompt to instruct Claude. It explicitly asks for formulas, preventing the AI from simply doing the math and returning dead text. We want living, dynamic Excel functions.
Context: The Next Step
Once you have this underlying data table built by Claude, you can highlight it and insert visual charts in Excel. This robust foundation is the same principle used when automating cash flow dashboards or building full three-statement models.
Frequently Asked Questions
Can Claude create the actual visual charts in Excel?
No, Claude cannot output a visual Excel chart directly. However, it will generate the exact formulas for your summary table and provide step-by-step instructions on which chart types (e.g., Line, Bar) to insert over that data.
How do I update the dashboard next month?
By instructing Claude to use dynamic functions like SUMIFS pointing to entire columns, you only need to paste your new month's raw data into the data tab. The dashboard will update automatically.
Can Claude handle complex custom KPIs like CAC or LTV?
Yes. You simply need to define the mathematical logic for your custom KPIs (e.g., Marketing Spend / New Customers) in the prompt, and Claude will translate that into the correct Excel formulas.