Build a Financial KPI Dashboard in Excel Using Claude AI
Progress1 of 4
1
How the dashboard works
2
What Claude needs from you
3
Prompt, FAQ & related
4
Quick quiz
Section 01
Your financials have the data. The KPI dashboard makes it talk.
Financial statements are full of numbers. KPIs are the handful that actually drive decisions. Gross margin tells you whether the product is profitable. DSO tells you how fast you collect. Current ratio tells you whether you can pay next month's bills. The problem is that extracting these from raw statements — and tracking them period over period with status flags — takes time that most finance teams don't have.
Claude takes your income statement and balance sheet, computes the KPIs you specify, applies the thresholds you define, and returns a dashboard with RAG status flags and trend arrows. You update it monthly by pasting new numbers into the same input cells.
The Pipeline: Statements → Metrics → Signals
IS & BS data
KPI formulas
Threshold rules
RAG dashboard
Three Families of Financial KPIs
Profitability
Gross Margin %
Operating Margin %
EBITDA Margin %
Net Profit Margin %
= Are we making money?
Liquidity
Current Ratio
Quick Ratio
Cash Ratio
Working Capital
= Can we pay our bills?
Efficiency
DSO (Days Sales Outstanding)
DPO (Days Payable Outstanding)
Inventory Turnover
Revenue per Employee
= How well do we operate?
The Output: What Claude Produces
A
B
C
D
E
F
1
KPI
May
Jun
Change
Target
Status
2
Profitability
3
Gross Margin
61.2%
60.8%
-0.4pp
≥ 60%
Green
4
EBITDA Margin
18.5%
16.1%
-2.4pp
≥ 18%
Red
5
Liquidity
6
Current Ratio
1.85
1.72
-0.13
≥ 1.50
Green
7
Quick Ratio
1.10
0.95
-0.15
≥ 1.00
Amber
8
Efficiency
9
DSO
42 days
47 days
+5 days
≤ 45
Red
10
DPO
33 days
31 days
-2 days
≥ 30
Green
EBITDA Margin dropped below the 18% target — Red. DSO worsened past the 45-day threshold — Red. Quick Ratio dipped below 1.0 — Amber. Every status is formula-driven from the thresholds you set, not manually coloured.
Key insight: The dashboard doesn't just calculate ratios — it tells you which ones need attention right now. A single-period KPI is a data point. Two periods with a trend arrow is a signal. A signal that crosses a threshold is a call to action. That's the difference between a spreadsheet and a dashboard.
What Claude Needs From You
Claude computes KPIs from raw financial data — it doesn't pull numbers from thin air. You supply the income statement and balance sheet; you choose which KPIs to track; you set the thresholds. Claude handles the formula writing, status logic, and formatting.
Input Data: Two Periods of Financial Statements
A
B
C
1
Line Item
May 2025
Jun 2025
2
Revenue
520,000
535,000
3
COGS
201,760
209,720
4
Operating Expenses
180,000
195,000
5
D&A
22,000
22,000
6
Accounts Receivable
240,000
276,000
7
Current Assets
680,000
695,000
8
Current Liabilities
368,000
404,000
01
Pick your KPIs deliberately — don't track everything
A dashboard with 25 metrics is a spreadsheet with a green header. Pick 6–10 KPIs that your leadership actually reviews. The prompt lets you specify exactly which ones — Claude doesn't add extras.
02
Provide at least two periods of data
One period gives you a snapshot. Two periods give you a trend. The Change column in the dashboard only works if Claude has a prior period to compare against. More periods (3–6 months) make trend analysis even more useful.
03
Define your thresholds — not someone else's
A "good" gross margin depends entirely on your industry and business model. SaaS companies run 70–80%. Manufacturing might be 25–35%. Tell Claude your targets: Gross Margin ≥ 60%, DSO ≤ 45 days, Current Ratio ≥ 1.5. These drive the RAG colours.
04
Decide on RAG rules: two-tier or three-tier
Two-tier: Green (on target) and Red (off target). Three-tier adds Amber for "approaching the threshold." If you want three tiers, tell Claude the buffer — for example, Amber if within 2 percentage points of target.
Common mistake: Using DSO that's calculated from revenue rather than credit sales. If 30% of your revenue is cash at point of sale, DSO based on total revenue will look artificially low. Tell Claude which revenue figure to use, and whether you track this through accounts receivable tracking separately.
Pro tip: Structure your input as a dedicated "Data" tab in the workbook. Every KPI formula references that tab. Each month, you update just the Data tab — every KPI, trend, and RAG status recalculates instantly. Claude can build this structure for you if you ask for it in the prompt.
The Claude Prompt for a Financial KPI Dashboard
Prompt — paste into Claude
ROLE
You are a senior FP&A analyst building a monthly KPI dashboard in Excel.
OBJECTIVE
Take my financial statement data and produce a KPI dashboard with computed metrics, period-over-period trends, threshold-based RAG status flags, and conditional formatting.
DATA
[Paste your income statement and balance sheet data here — at least two periods, with consistent line-item names across periods]
KPIs TO CALCULATE
[List each KPI with its formula. Examples:]
- Gross Margin % = (Revenue - COGS) / Revenue
- Operating Margin % = Operating Income / Revenue
- EBITDA Margin % = (Operating Income + D&A) / Revenue
- Current Ratio = Current Assets / Current Liabilities
- Quick Ratio = (Current Assets - Inventory) / Current Liabilities
- DSO = (Accounts Receivable / Revenue) × days in period
- DPO = (Accounts Payable / COGS) × days in period
THRESHOLDS (for RAG status)
[List each KPI with Green / Amber / Red rules. Examples:]
- Gross Margin: Green ≥ 60%, Amber 55-59%, Red < 55%
- Current Ratio: Green ≥ 1.5, Amber 1.2-1.49, Red < 1.2
- DSO: Green ≤ 45, Amber 46-55, Red > 55
CONSTRAINTS
- Calculate each KPI using the formula I specified — do not substitute alternative definitions.
- Add a Change column showing the period-over-period movement (absolute and percentage or pp where appropriate).
- Apply conditional formatting: green fill for Green, amber/yellow fill for Amber, red fill for Red.
- Group KPIs by category (Profitability, Liquidity, Efficiency) with section headers.
- Do NOT invent financial data or thresholds I haven't provided.
- Do NOT add KPIs beyond the ones I listed.
- Write formulas explicitly (e.g. =(B2-B3)/B2) so I can audit the logic.
OUTPUT FORMAT
- Tab-separated dashboard table ready to paste into Excel.
- Separate Data Input section with labelled cells for each financial line item per period — so I can update monthly by changing just the input cells.
The Dashboard Is the Starting Point, Not the Answer
A KPI turning Red doesn't mean something is wrong — it means something needs investigation. EBITDA Margin dropped 2.4 percentage points this month. Was it a one-off legal expense? A deliberate hiring push? A pricing problem? The dashboard surfaces the question. Your team answers it.
If the answer involves analysing the gap between planned and actual spending, the budget variance analysis course gives you a structured way to do that. If the concern is cash position rather than profitability, the cash flow dashboard tracks where the money is going week by week. Both feed naturally into this KPI view — the variance report explains the why, the cash dashboard shows the when.
KPI Formulas Quick Reference
A
B
C
1
KPI
Formula
Watch Out For
2
Gross Margin %
(Revenue − COGS) / Revenue
Inconsistent COGS definitions between periods
3
EBITDA Margin %
(Op. Income + D&A) / Revenue
D&A must be broken out from OpEx
4
Current Ratio
Current Assets / Current Liabilities
A ratio above 3.0 may signal idle capital
5
DSO
(AR / Revenue) × Days
Use credit sales, not total revenue, if material
6
Quick Ratio
(CA − Inventory) / CL
Excludes inventory — stricter than current ratio
Tell Claude exactly which formulas to use. Different definitions of EBITDA or DSO will produce different dashboard values — clarity in the prompt prevents surprises in the output.
Frequently Asked Questions
Which financial KPIs should a dashboard include?
Start with the metrics your leadership actually reviews. Common ones include gross margin, operating margin, EBITDA margin, current ratio, quick ratio, DSO, and DPO. The prompt lets you specify exactly which ones — Claude doesn't add extras.
Can Claude calculate KPIs directly from raw financial statements?
Yes. Paste your income statement and balance sheet, tell Claude which KPIs to calculate and their formulas, and Claude produces the dashboard with computed values. It writes out each formula so you can verify the logic.
What do the RAG status colours mean?
RAG stands for Red, Amber, Green. Green means on target. Amber means approaching a threshold. Red means the threshold has been breached. You define the thresholds — Claude applies them.
How often should a financial KPI dashboard be updated?
Monthly is standard, aligned with the month-end close. Some KPIs like DSO and cash position benefit from weekly updates. The structure works at any frequency — you paste new figures into the input cells.
Can I track KPI trends over multiple periods?
Yes. The prompt includes period-over-period change columns. A margin that drops 0.5% this month isn't alarming. A margin that drops 0.5% every month for six months is. The trend makes the difference.