Year over Year Revenue Check
Lines up current revenue against the same stretch last year and flags where the trend actually shifted.
The prompt
Copy and customize
prompt.txt
Role: You are a senior FP&A analyst preparing a revenue trend read for a monthly business review.
Context:
- Revenue amounts are in the {amount_field} field. Reporting dates are in the {date_field} field.
- Anchor the "current" 6-month window on the most recent month present in the data below (not today's date). Compare it to the same 6 calendar months one year earlier.
- Revenue data: {revenue_data}
Task:
1. Aggregate revenue by calendar month using {date_field} and sum {amount_field}.
2. Build a month-by-month comparison of the current 6-month window vs. the same 6 months one year prior.
3. If a month is missing from either period, mark it "N/A" in that column, exclude it from the total row, and note the exclusion in the summary.
4. Verify your monthly sums and the total row reconcile before producing output.
Output format — respond with exactly this, no preamble, no extra commentary:
- A markdown table with columns: Month | Current-Year Revenue | Prior-Year Revenue | Variance ({currency}) | Variance %
- Month format: "Mon YYYY" (e.g., "Jun 2026").
- Currency values: rounded to whole units, thousands separator, no repeated currency symbol per cell.
- Variance ({currency}) = current − prior. Show negative values with a leading minus sign.
- Variance % = variance ÷ prior-year revenue, one decimal place, with a % sign. If prior-year revenue is 0, show "N/A".
- Add a Total row. Its Variance % must be recalculated from the summed totals (total variance ÷ total prior-year revenue) — never averaged from the monthly percentages.
- Below the table, exactly two sentences: (1) the overall trend direction — accelerating, decelerating, flat, or reversing — based on the total variance %; (2) the single month with the largest year-over-year swing in absolute {currency} terms (not percentage — a small prior-year base can inflate percentage swings), naming the month and its dollar variance.
Constraints: Use only figures derivable from the provided data. Do not fabricate or estimate any missing values.
Open in
We’ll copy the prompt and open the chat.
How to use
Run it in four steps
- Export six months of revenue from the {amount_field} column, plus the same six months one year prior, on one consistent fiscal calendar.
- Paste it into
{revenue_data}, set{amount_field}and{date_field}to your revenue and reporting-date column names, and set{currency}. - Run it for the month-by-month comparison and trend read.
- Before reading too much into the trend, confirm both periods use the same revenue definition and that no prior-year month was still open when the data was pulled.
When to use
When to reach for this prompt
Use this when you need a fast, directional read on revenue before a monthly review, for example someone asks "how does revenue compare to last year?" and you want a defensible answer before the meeting. It's not a substitute for a full budget-variance analysis. It's also a useful gut check earlier in the process: if the current and prior-year windows don't line up cleanly here, you'll want to know that before it skews a more robust analysis.
Limitations · Worth knowing
This prompt has real limitations you should understand.
This is the easiest comparison to over-read: the math is trivial, so the result looks more certain than it is. It only holds if revenue meant the same thing in both periods; the {amount_field} column can silently mix bookings, billings, and recognized revenue. The most common failure is on the prior-year side: a month that was still open or closed late a year ago shows up as a false decline, and the percentage column reports it with full confidence. None of this is fixable by the prompt itself. It depends on a consistent revenue definition and an aligned fiscal calendar in the data you feed it.
Prerequisites
What your data needs to look like
- Same source, both periods. Pull the current six months and the prior-year six months from the same report or query, not two exports taken at different times with different filters.
- One definition of revenue. Don't mix recognized, billed, and booked revenue in the same column; pick one and confirm every row uses it.
- Prior year is closed. If a month from last year was still being adjusted after the fact, it'll look like a decline that isn't real. Use final, closed numbers only.
- Same fiscal calendar on both sides. If your fiscal year doesn't match the calendar year, make sure "the last six months" refers to the same six fiscal months in both periods, not just the same calendar dates.
See it run on real data
See how FinanceOS handles this prompt on real financial data.
Book a 20-minute walkthrough. We’ll run this exact prompt against a sample dataset reconciled through FinanceOS, and show you what changes when the data underneath is right.
Book a walkthrough