Paste your income statement and balance sheet into ChatGPT and get back a computed KPI dashboard — profitability, liquidity, and efficiency metrics with RAG status flags and period-over-period trends.
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 / Credit Sales) × days in period [Credit Sales = Revenue excluding cash-at-point-of-sale transactions] - DPO = (Accounts Payable / Purchases) × days in period [Use Purchases if available; COGS is an acceptable proxy if purchases data is not provided — state which you are using] 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.
Replace all bracketed sections with your real data, then paste the prompt into ChatGPT.
I need help setting sensible RAG thresholds for my KPI dashboard — I don't want to just guess. Industry / business model: [e.g. B2B SaaS, manufacturing, professional services] KPIs I'm tracking and my last 6 months of actuals for each: [Paste: KPI name and 6 monthly values] For each KPI, suggest a Green / Amber / Red threshold based on the trend in my own data (not generic industry benchmarks unless I ask for them), using the best 3 months as a reference for Green and flagging any KPI where my historical range is too volatile for a stable threshold.
A "good" gross margin depends entirely on your industry — SaaS runs 70-80%, manufacturing might be 25-35%.
I have a KPI dashboard with month-over-month data. I want to add a trend view across the last 6 months instead of just comparing two periods. Here is my KPI data (columns: KPI, Month 1, Month 2, Month 3, Month 4, Month 5, Month 6): [Paste your 6-month KPI history here] Please add a trend column that flags any KPI moving in the same direction for 3+ consecutive months, even if no single month breached the RAG threshold. Label these "Trending — watch" separately from the standard RAG status.
A margin that drops 0.5% once isn't alarming. A margin that drops 0.5% every month for six months is a trend worth flagging early.
| A | B | C | |
|---|---|---|---|
| 1 | Line item | Current qtr | Prior qtr |
| 2 | Income Statement | ||
| 3 | Revenue | 850,000 | 820,000 |
| 4 | COGS | 340,000 | 328,000 |
| 5 | Operating Income | 127,500 | 131,200 |
| 6 | D&A | 18,000 | 18,000 |
| 7 | Balance Sheet | ||
| 8 | Current Assets | 295,000 | 310,000 |
| 9 | Inventory | 72,000 | 65,000 |
| 10 | Accounts Receivable | 204,000 | 185,000 |
| 11 | Current Liabilities | 210,000 | 180,000 |
| 12 | Accounts Payable | 102,000 | 109,000 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | KPI | Prior | Current | Change | RAG |
| 2 | Profitability | ||||
| 3 | Gross Margin % | 60.0% | 60.0% | 0.0pp | Green |
| 4 | Operating Margin % | 16.0% | 15.0% | −1.0pp | Amber |
| 5 | EBITDA Margin % | 18.2% | 17.1% | −1.1pp | Red |
| 6 | Liquidity | ||||
| 7 | Current Ratio | 1.72 | 1.40 | −0.32 | Amber |
| 8 | Quick Ratio | 1.36 | 1.06 | −0.30 | Amber |
| 9 | Efficiency | ||||
| 10 | DSO (90-day qtr) | 20.3d | 21.6d | +1.3d | Green |
| 11 | DPO (COGS proxy) | 29.9d | 27.0d | −2.9d | Amber |
Gross Margin = (850,000−340,000)/850,000 = 60.0% EBITDA Margin = (127,500+18,000)/850,000 = 17.1% Current Ratio = 295,000/210,000 = 1.40 Quick Ratio = (295,000−72,000)/210,000 = 1.06 Quarterly DSO = 204,000/850,000 × 90 = 21.6 days Quarterly DPO = 102,000/340,000 × 90 = 27.0 days ✓
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 — ChatGPT doesn't add extras.
One period gives you a snapshot. Two periods give you a trend. The Change column only works if ChatGPT has a prior period to compare against.
A "good" gross margin depends entirely on your industry. SaaS companies run 70-80%. Manufacturing might be 25-35%. Tell ChatGPT your targets: Gross Margin ≥ 60%, DSO ≤ 45 days.
Two-tier: Green (on target) and Red (off target). Three-tier adds Amber for "approaching the threshold." If you want three tiers, tell ChatGPT the buffer.
Every KPI formula should reference that tab. Each month, update just the Data tab and every KPI, trend, and RAG status recalculates instantly. Ask ChatGPT to build this structure.