Provide ChatGPT with reconciled historical actuals, explicit forecast drivers, and your current forecast layout, then use it to draft a formula-driven 12-month rolling forecast. Each close should preserve the prior forecast for variance analysis, move the closed month to actuals, and add one new far-end forecast month.
I need a rolling 12-month forecast model in Excel. MODEL SETTINGS - Currency / units: [e.g. GBP £000] - Last closed actual month: [e.g. June 2025] - First open forecast month: [e.g. July 2025] - Active horizon required: exactly 12 future months - Sign convention: [e.g. revenue positive, costs positive and subtracted] - Forecast version/date: [e.g. July 2025 forecast] HISTORICAL ACTUALS Columns: Line Item | [one column per CLOSED month] [Paste reconciled actuals here — include the most recent closed month] FORECAST DRIVERS For each material line item, tell me the driver rather than guessing: - Revenue: [e.g. 2% MoM / units × price / other] - COGS: [e.g. 40% of revenue] - OpEx: [e.g. 0.5% MoM, or line-specific drivers] - Headcount / payroll: [if applicable] - Seasonality: [none / monthly index on Assumptions tab] - One-offs: [month | line item | amount | add/subtract | recurring? no] - Other material lines: [state driver or "hold flat"] MODEL DEFINITION 1. Preserve historical actuals as values; do not overwrite prior closed months. 2. Keep an ACTIVE FORECAST HORIZON containing exactly 12 future months, beginning with the first open forecast month. 3. Keep a separate "Prior Forecast" snapshot or version before replacing a forecast month with actuals, so forecast-vs-actual variance can still be measured. 4. When a month closes: - record the final reconciled actual, - remove that month from the active forecast horizon, - append one new month at the far end, - retain exactly 12 future forecast months. FORECAST RULES - Put recurring assumptions in an Assumptions area/tab and link forecast formulas to them. - Do not hard-code recurring forecast outputs. - Define the Revenue base explicitly and apply growth only once. - If seasonality is used, explain whether it is applied to an annual base, a deseasonalised monthly base, or another base. Do not multiply by a seasonality factor twice. - One-offs must be separate explicit additions/overrides and must not automatically roll forward. - Include Gross Profit and Operating Income. - Include Net Income only if I provide every required below-operating-income driver. - Add Forecast 12M Total using only the 12 active forecast months. - Add validation rows/checks: a) active forecast month count = 12 b) first forecast month = month after last closed actual c) no overlap between Actuals and Active Forecast d) one-offs occur only in specified periods e) Forecast 12M Total range contains exactly 12 month columns - Do not invent assumptions or infer missing business drivers from a few historical rows. - If a required driver is missing, list it before creating that forecast line. OUTPUT 1. Proposed Assumptions layout 2. Actuals + Active Forecast layout 3. Explicit Excel formulas for the first 2 forecast months and formula pattern thereafter 4. Forecast 12M Total formulas 5. Monthly roll-forward procedure 6. Validation checks Use tab-separated tables suitable for Excel.
Paste your real actuals and assumptions. Works in ChatGPT.
I need to add seasonality to my rolling revenue forecast without changing the annual base unintentionally. BASE-YEAR ANNUAL REVENUE: [e.g. £600,000] Historical monthly revenue shares: Jan: [%] Feb: [%] Mar: [%] Apr: [%] May: [%] Jun: [%] Jul: [%] Aug: [%] Sep: [%] Oct: [%] Nov: [%] Dec: [%] How these shares were calculated: [one representative year / average of several years / other] Growth treatment in my current forecast: [growth NOT yet applied / growth already embedded / not sure] Requirements: 1. Check that all 12 shares are present and calculate their actual sum. 2. Do not merely assume the shares total exactly 100%. 3. Normalize them so the 12 seasonality indices average exactly 1.0: NormalizedShare = MonthlyShare / SUM(All12MonthlyShares) SeasonIndex = NormalizedShare × 12 4. Verify SUM(12 normalized shares) = 100% and AVERAGE(12 indices) = 1.0 within a stated rounding tolerance. 5. Define BaseMonthlyRevenue = BaseYearAnnualRevenue / 12 only if this annual base is the intended forecast base. 6. If growth is not embedded elsewhere, state exactly how growth interacts with seasonality. Do not apply both a growing monthly base and a second growth factor to the same forecast. 7. If growth is already embedded in a deseasonalised base forecast, apply only the normalized seasonality factor. 8. Put the raw shares, normalized shares and seasonality indices on Assumptions. 9. Give exact Excel formulas and an annual-control check showing that seasonality alone redistributes revenue across months rather than creating/destroying annual revenue. 10. Do not invent missing monthly shares or a growth method.
Use this after building the base model — add it as a separate tab so your assumptions stay organised.
I need to roll my 12-month forecast forward after month-end close. Month just closed: [e.g. June 2025] Final reconciled actual figures: [Paste the closed-month line items] Current ACTIVE FORECAST HORIZON: [e.g. June 2025 through May 2026] Current forecast values for the month that just closed: [Paste them, so I can retain forecast-vs-actual variance] New far-end forecast month: [e.g. June 2026] Current far-end assumptions / formulas: [Paste the prior far-end month formulas and relevant assumption rows] Instructions: 1. First confirm that the closed month is the first month of the current active horizon. If it is not, stop and flag the timeline inconsistency. 2. Preserve the closed month's PRIOR FORECAST as a snapshot/version for variance analysis. 3. Record the final reconciled actual in the Actuals section without changing earlier actuals. 4. Remove the closed month from ACTIVE FORECAST only after the prior forecast is preserved. 5. Append the new far-end month. Copy formula logic only where the driver is genuinely recurring. Do NOT carry forward expired one-offs, temporary overrides, promotions or dated assumptions. 6. Re-evaluate seasonality using the new calendar month. 7. Confirm the new ACTIVE FORECAST contains exactly 12 future months with no gaps/duplicates. 8. Update Forecast 12M Total to those 12 columns only. 9. Show: - prior forecast vs actual variance for the closed month, - changed cells/formulas, - the new first and last forecast months, - validation checks. 10. Do not overwrite history, silently change assumptions, or invent a new far-end driver.
Run this after month-end close: the closed month moves to Actuals, one new far-end month is appended, and the active forecast stays at 12 future months.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line Item | Jan Budget | Feb Budget | Mar Budget | FY Total | Driver |
| 2 | Revenue | £480k | £480k | £480k | £5,760k | Hardcoded |
| 3 | COGS | £192k | £192k | £192k | £2,304k | Hardcoded 40% |
| 4 | OpEx | £220k | £220k | £220k | £2,640k | No escalation |
| 5 | Operating Income | £68k | £68k | £68k | £816k |
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Line Item | Apr A | May A | Jun A | Jul F | Aug F | Sep F | … Jun F+1 | Forecast 12M |
| 2 | Revenue | £520.0k | £535.0k | £510.0k | £520.2k | £530.6k | £541.2k | linked | =SUM(E2:P2) |
| 3 | COGS @ 40% | £208.0k | £214.0k | £204.0k | £208.1k | £212.2k | £216.5k | linked | =SUM(E3:P3) |
| 4 | OpEx | £221.0k | £223.0k | £225.0k | £226.1k | £227.3k | £278.4k* | linked | =SUM(E4:P4) |
| 5 | Operating Income | £91.0k | £98.0k | £81.0k | £86.0k | £91.1k | £46.3k | linked | 12M sum |
Assumptions: Revenue growth = 2.0% MoM COGS = 40% of Revenue OpEx escalation = 0.5% MoM Sep one-off relocation = £50k Jul Revenue = £510.0k × 1.02 = £520.2k Aug Revenue = £520.2k × 1.02 = £530.604k Jul COGS = £520.2k × 40% = £208.08k Jul OpEx = £225.0k × 1.005 = £226.125k Sep OpEx includes the explicit £50k one-off. Forecast 12M totals sum only the 12 active forecast months. Control checks: • Last actual = Jun • First forecast = Jul • Active forecast count = 12 • No Actual/Forecast overlap ✓ The shown values are illustrative and follow the stated drivers.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Metric | Jul | Aug | Sep | Oct | Nov | Dec | Check |
| 2 | Flat base | £50k | £50k | £50k | £50k | £50k | £50k | Ignores seasonality |
| 3 | Historical share | 7.0% | 6.5% | 7.5% | 9.0% | 10.5% | 12.5% | Full year must = 100% |
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Metric | Jul | Aug | Sep | Oct | Nov | Dec | Logic |
| 2 | Historical share | 7.0% | 6.5% | 7.5% | 9.0% | 10.5% | 12.5% | Input |
| 3 | Season Index | 0.84 | 0.78 | 0.90 | 1.08 | 1.26 | 1.50 | Share × 12 |
| 4 | Base monthly revenue | £600,000 annual / 12 = £50,000 | Annual / 12 | |||||
| 5 | Seasonal revenue | £42,000 | £39,000 | £45,000 | £54,000 | £63,000 | £75,000 | Base × Index |
Average monthly share = 1/12 = 8.333% If the complete 12-month raw shares sum to exactly 100%: Jul index = 7.0% / 100% × 12 = 0.84 Dec index = 12.5% / 100% × 12 = 1.50 General formula: =MonthlyShare / SUM(All12Shares) * 12 With £600k base-year annual revenue: Base month = £600k / 12 = £50k Jul = £50k × 0.84 = £42k Dec = £50k × 1.50 = £75k ✓ Control: average of all 12 normalized indices must equal 1.0. If the base forecast already contains growth, apply seasonality only. Do not multiply by a growth factor twice. ✓
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line Item | May A | Jun F | Jul F | … May F+1 | Forecast Horizon |
| 2 | Revenue | £40k | £42k | £45k | linked | Still starts Jun |
| 3 | COGS | £16k | £16.8k | £18k | linked |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line Item | May A | Jun A | Jul F | … May F+1 | Jun F+1 NEW | Active Forecast |
| 2 | Revenue | £40k | £44k | £45k | linked | new formula | Jul→Jun = 12 months |
| 3 | COGS | £16k | £17.6k | £18k | linked | =Revenue*40% | 12 months |
| 4 | Forecast 12M Total | =SUM(Jul F : Jun F+1) | Updated | ||||
Prior June forecast = £42k Final June actual = £44k Forecast variance = £44k − £42k = +£2k Before close: Active forecast = Jun 2025 → May 2026 (12 months) After June closes: June 2025 actual moves to history. Active forecast = Jul 2025 → Jun 2026 (12 months) The new June 2026 formula uses recurring drivers only. Expired one-offs / dated overrides must not be copied blindly. Historical June actual and the prior June forecast snapshot are both retained. Forecast 12M Total sums Jul 2025 through Jun 2026 only. ✓
Provide enough closed history to support the drivers you intend to use. A few months may show recent run-rate, but seasonality normally requires a fuller annual pattern and often multiple comparable periods. Do not let ChatGPT infer a growth assumption automatically from a short sample unless that is explicitly your chosen method.
"Revenue grows 2% per month" is actionable. "Revenue will increase" is not. ChatGPT builds formulas from numbers — vague inputs produce vague models. Include COGS as a % of revenue, OpEx escalation rate, and any planned headcount changes with their hire months.
The prompt instructs ChatGPT to reference an Assumptions tab for every forecast driver. Central assumptions let future formulas recalculate consistently and make each forecast driver auditable instead of embedding the same input in many cells.
At the end of each month, use the third tab prompt above. Paste the closed actuals; ChatGPT can identify the cells to move into history and provide the new far-end forecast formulas while keeping the active horizon at 12 future months.
Use enough comparable historical periods to support a seasonal pattern, and remove/flag structural breaks or unusual one-offs before calculating monthly shares. The appropriate history depends on the business.
Calculate each monthly share, normalize the 12 shares to 100%, then convert them to an index whose 12-month average is 1.0. Verify both control totals before using the index.
Keep these modifiers separate from your main calculation sheet. This allows you to tweak seasonal expectations without breaking cell formulas.
Apply the index to a clearly defined revenue base only once. Confirm whether growth is already embedded in that base so seasonality does not accidentally compound growth twice.
Use final reconciled actuals for the closed month and preserve the prior forecast/version before changing the active forecast horizon.
Provide the final actuals, the current active horizon, the prior forecast for the closed month, and the far-end formulas/assumptions. ChatGPT should identify proposed changes; verify them before applying them in Excel.
Store the prior forecast snapshot for variance analysis, record the final actual, then remove that month from the active forecast horizon.
Append the new far-end month using only valid recurring drivers, then check for expired one-offs, date-specific assumptions, missing months, duplicates and the 12-month total range.