Build a monthly performance tracker in Excel
Designs a reusable monthly tracker with KPIs, targets, month-over-month and year-to-date comparisons, and red/amber/green status.
The prompt
Help me build a monthly performance tracker in Excel that I can update in a few minutes each month.
What I'm tracking: [e.g. "team sales performance", "customer service KPIs", "project portfolio", "marketing results"]
Metrics I report today, with definitions: [LIST - e.g. "revenue, new customers, average handle time, NPS"]
Targets: [monthly or annual targets per metric - or "none yet"]
Data I have: [PASTE a sample of the source data or last few months of numbers]
Breakdowns needed: [e.g. "by person", "by region", "none"]
Who reads it: [e.g. "my manager in the monthly review"]
Design:
1. Layout: metrics as rows, months as columns (Jan-Dec), with Target, Actual, Variance, and YTD blocks. Explain where raw data lives and how it feeds the tracker.
2. Formulas: month actuals (SUMIFS on the date column), variance in value and %, YTD actual vs. YTD target, month-over-month change, and a rolling 3-month average. Handle months not yet reported so they show blank, not zero or #DIV/0!.
3. Direction of good: for each metric, whether higher or lower is better, so variance signs and colors are right (e.g. lower cost is favorable).
4. Status rules: red/amber/green thresholds per metric with conditional formatting formulas.
5. A comment column for one line explaining each red or amber.
6. Monthly update routine in 5 steps or fewer.
Return the layout sketch, a metric table (Metric | Definition | Source | Formula | Higher is better? | RAG thresholds), and the build steps. Mark any target or definition you had to assume as [CONFIRM].
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- monthly or annual targets per metric - or "none yet"
- e.g. "by person", "by region", "none"
- e.g. "my manager in the monthly review"
- CONFIRM
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Flag impossible or suspicious values in my dataChecks your data against logic and business rules for impossible, placeholder, and implausible values, and gives reusable validation rules.
- Create a data-quality checklist for a recurring report or datasetA reusable pre-publish checklist with checks, formulas, pass thresholds, owners, and what to do when a check fails.