Home / Data & Analysis / Reports & Dashboards

Build a Finance Dashboard in Excel or Google Sheets in 7 Phases

Builds a finance dashboard step by step: plans sheets, KPIs and charts, writes the formulas, sets up a P&L with color rules, forecasts 6 months of cash flow, picks the charts, packages an investor-ready view and automates it with Apps Script.

finance dashboardgoogle sheetsexcelcash flowapps script
  1. Phase 1 — Dashboard plan: sheets, KPIs and charts
    Role: you are an experienced FP&A analyst.

What I want: a finance dashboard for my [business / freelance work / personal finances].

Task: tell me precisely which sheets, KPIs and charts I need. Organize the plan under these headings: Income, Expenses, Cash Flow, Profit, Runway.

Once the plan is ready, ask me for my data.
  2. Phase 2 — Copy-paste formulas for the core metrics
    My finances: [business / freelance work / personal finances]
My income and expense sheet, which is messy: [paste data or describe the columns]

Task: write clean formulas for Excel or Google Sheets that calculate automatically:
- Monthly Revenue
- Total Expenses
- Net Profit
- Profit Margin %
- Month-over-Month Growth

They must be ready to copy and paste. Present them as a table with the metric, the formula and the cell or column it refers to.
  3. Phase 3 — P&L template with conditional formatting
    My data: [paste data]

Task: build a complete P&L template from this data, organized into: Revenue Streams, COGS, Gross Profit, Operating Expenses, Net Profit.

Then set up conditional formatting: losses appear in red, growth appears in green.
  4. Phase 4 — 6-month cash flow forecast
    Role: you are a CFO.

My income and expenses over the last 6 months: [paste data]

Task: build a cash flow forecast for the next 6 months. Show:
- the expected runway
- the burn rate
- the months in which I will go negative
- 3 levers to fix it

Present everything in a table.
  5. Phase 5 — Charts that explain your finances in 10 seconds
    My data: [paste data]

Question: which charts should I build so I can understand my finances within 10 seconds?

Cover: Revenue Trend, Profit Trend, Expense Breakdown, Top Spending Categories.

For each chart give:
1. the type of chart to use
2. which data goes on the X axis and which on the Y axis
3. the insight it gives me
  6. Phase 6 — Investor-ready dashboard view
    My financial data: [paste data]

Task: turn this data into a dashboard ready to show investors. Create:
- an Executive Summary of 3 lines
- Key Metrics: MRR, Burn, Profit Margin, Runway
- the charts to include
- for each chart, one paragraph explaining it in a way a person with no finance background would understand
  7. Phase 7 — Apps Script automation
    Sheet name: [sheet name]

Task: write a Google Sheets Apps Script that automates this dashboard:
- the charts refresh automatically whenever I add a new income or expense
- I receive a weekly email summarizing profit and loss
- expenses are highlighted when they are more than 20% above the previous month

Then explain step by step how to install the script and set up its triggers.
More in Reports & Dashboards
Find the 5 Key Patterns in a Spreadsheet or CSVNewReports & DashboardsAnalyzes the spreadsheet or CSV you upload, pulls out its five most important patterns, flags anything unusual and explains in plain English what to do next.
Attached: a spreadsheet or CSV file.

Task:
1. Analyze it and pick out the five patterns that matter most.
2. Point out anything that looks unusual.
3. Explain, in plain English, which actions should come next.

Output: three sections, Patterns, Anomalies, Next actions.
Open page
Turn a Dataset or Report Into an Interactive HTML DashboardNewReports & DashboardsTakes data from an earlier answer or a dataset and writes the HTML and CSS for a colorful, interactive dashboard of its key metrics, with a minimalist, modern and very readable design.
Data: [PREVIOUS OUTPUT/DATASET]

Task: write the HTML/CSS for an interactive, colorful dashboard that visualizes the key metrics in this data.

Design: minimalist, modern and easy to read at a glance.
Open page
Business Data Analysis at the Level of a $10,000 ConsultantNewReports & DashboardsAnalyzes your business data the way a top consultant on a $10,000 engagement would: 3 non-obvious insights, 2 hidden patterns, 1 early warning sign, 1 missed opportunity and a prioritized action plan, while challenging what you assume.
Below is data from my business. Examine it the way a top-tier strategy consultant hired for a $10,000 engagement would. Skip surface-level remarks; I want insights I can act on.

Data: [paste metrics, financials, customer feedback, performance figures or survey results]

Deliver:
- 3 insights that are not obvious at first sight but could have a big impact on my business.
- 2 patterns or trends hidden in the data that I am probably missing.
- 1 early warning sign that could turn into a serious problem if I ignore it.
- 1 opportunity I am losing money on by not pursuing it.
- A prioritized action plan: first the fixes, then the optimizations, then where to double down.

Push back on my assumptions. Where the data contradicts my likely beliefs about the business, state it clearly. I want the truth, not comfort.
Open page
Interactive HTML Dashboard from Your Data via a Master Prompt2 phasesNewReports & DashboardsGets Claude to write an expert master prompt for an interactive HTML dashboard from your uploaded data. Run that master prompt in a new chat with the same file, then refine the result, e.g. swapping a line chart for a heatmap and adding KPI cards.
  1. Phase 1 — Write the master prompt
    Role: you are a top expert in prompt engineering and a data analyst.

Attached: my data file.
Audience for the dashboard: [audience, e.g., CEO/CFO]
Minimum number of visuals: [number, e.g., 10]

Task: review the attached file and write a complete, very detailed prompt that I will use to generate an interactive dashboard in HTML for this audience. The prompt must require at least that number of visuals, interactive slicers and clear insights drawn from the data.

Rules:
- Do NOT write any code at this stage.
- Give me ONLY the master prompt, in one block I can copy, written so it works in a new chat where I upload the same file again.
  2. Phase 2 — Refine the dashboard
    Update the dashboard:
1. Replace the line chart with a heatmap of sales volume by day of the week.
2. At the top, add 3 KPI metric cards: Total Revenue, Net Profit and Total Orders.
Open page
Plain-English Insights from a Dataset for Non-Technical TeamsNewReports & DashboardsAnalyzes the data you paste or describe and explains it in plain English: key insights, hidden trends, patterns, anomalies and notable comparisons, with percentages and actionable takeaways that a non-technical team can use to decide.
Role: you are a data analyst who explains results to non-technical colleagues.

Data:
[paste or describe your data]

Task:
1. Pull out the key insights and the trends that are not immediately visible.
2. Point out the patterns, anomalies and comparisons that stand out.
3. Summarize what you found in plain English.

Rules:
- simple explanations, no jargon
- back the points with relevant percentages or statistics
- close with actionable takeaways that help the reader make decisions
- if the data is incomplete or ambiguous, say what you assumed

Output sections: Key insights / Trends and patterns / Anomalies and comparisons / What to do next.
Open page