Provide ChatGPT with a complete historical base period, forecast assumptions, and model conventions, then use it to draft a linked income statement, balance sheet, cash flow statement, and supporting roll-forwards. Reconcile the historical period and validate every forecast year before relying on the model.
ROLE You are helping me build an auditable 3-statement Excel model. OBJECTIVE Draft a fully linked Income Statement, Balance Sheet and Cash Flow Statement (indirect method) using one historical base year and [3] forecast years. EXCEL / MODEL CONVENTIONS - Excel version: [Microsoft 365 / 2024 / 2021 / 2019 / 2016] - Currency / units: [e.g. GBP £000] - Forecast years: [e.g. FY25E–FY27E] - Sign convention: [state whether expenses/outflows are positive or negative] - Dividends/distributions policy: [amount / payout ratio / none] - Minimum cash / revolver rule, if any: [describe or "none"] - Circularity: do NOT use iterative calculation unless I explicitly ask for it. HISTORICAL BASE YEAR — FY [year] Income Statement: [Revenue, COGS, operating expenses excluding D&A, D&A, interest expense, other income/expense, tax expense, net income] Balance Sheet: [Cash, Accounts Receivable, Inventory, Other Current Assets, Gross PP&E or Net PP&E, any accumulated depreciation if separately available, Other Assets, Accounts Payable, Accrued Liabilities, Other Current Liabilities, Current Debt, Long-Term Debt, Other Liabilities, Share Capital / APIC or equivalent, Retained Earnings, Other Equity, Total Assets, Total Liabilities, Total Equity] Important: - The historical Balance Sheet must already balance. - If any listed line does not exist, write "not applicable"; do not invent it. - If I provide Net PP&E only, do not pretend you have a full fixed-asset schedule. FORECAST ASSUMPTIONS - Revenue growth: [by year] - COGS / gross-margin assumption: [by year] - OpEx excluding D&A: [growth or % revenue] - CapEx: [by year] - Depreciation method: [existing asset base + new CapEx convention; useful life; timing] - DSO / AR driver: [e.g. AR = Revenue × DSO / days in year] - Inventory-days driver: [state whether based on COGS] - DPO / AP driver: [state whether based on COGS] - Drivers for Other Current Assets / Accrued Liabilities / Other Current Liabilities: [provide formulas/assumptions or say "hold flat"] - Debt opening balance, repayments, new borrowing and interest rate: [by year] - Interest convention: [beginning debt / average debt / other] - Tax assumption: [tax rate + treatment of losses / NOLs if relevant] - Dividends/distributions: [by year] REQUIRED MODEL LOGIC 1. Income Statement: Revenue → operating profit → interest/other items → pre-tax income → tax → Net Income. 2. Retained Earnings: Ending RE = Beginning RE + Net Income − Dividends/Distributions (plus/minus any other explicitly provided RE adjustments). 3. Working capital: Forecast AR / Inventory / AP and every other driven WC balance on the BS. CFS uses period-over-period changes with the correct cash-flow sign. 4. PP&E: Ending Net PP&E = Beginning Net PP&E + CapEx − D&A only if that simplified roll-forward is consistent with the data I supplied. 5. Debt: Ending Debt = Beginning Debt + New Borrowing − Repayment. Do not let repayment exceed available debt unless I explicitly provide refinancing. 6. Interest: Use my stated convention. If I choose beginning debt, keep it non-circular. 7. Cash Flow Statement: CFO + CFI + CFF reconcile Beginning Cash to Ending Cash. BS Cash must link to CFS Ending Cash. 8. Balance Check: Total Assets − Total Liabilities − Total Equity. Show the check in every period and flag any non-zero amount above my rounding tolerance: [e.g. £1 in model units]. VALIDATION BEFORE OUTPUT - Confirm the historical BS balances before forecasting. - Confirm historical Net Income agrees with the IS data I supplied. - Do not plug an unexplained number into Cash, Other Assets, Debt or Equity just to force balance. - Do not invent missing assumptions. - If an input is insufficient to build a valid formula, stop and list exactly what is missing. - Distinguish assumptions from formulas and historical hardcodes. OUTPUT 1. Assumptions section 2. Income Statement 3. Balance Sheet 4. Cash Flow Statement 5. Supporting schedules needed for working capital, PP&E and debt 6. Balance Check and cash reconciliation 7. Explicit Excel formulas using the cell layout you propose 8. A short audit checklist with at least one independent check per schedule Use tab-separated tables suitable for pasting into Excel.
Replace all bracketed sections with your real data, then paste the prompt into ChatGPT.
My 3-statement financial model has a Balance Check that is not zero. Balance Check shows: [e.g. −8,000 in FY25, 0 in FY24] Here are the relevant rows from my model: [Paste: Total Assets row, Total Liabilities + Equity row, Balance Check row, and the Net Income, Retained Earnings, and Ending Cash rows] Please: 1. Recalculate the Balance Check from the rows I provide. 2. Trace whether the variance first appears in Assets, Liabilities, Equity or Cash. 3. Compare the variance with the relevant roll-forward movements, but do not assume a matching amount proves causation. 4. State which additional rows/cell formulas you need if the evidence is insufficient. 5. Only when the broken link is demonstrated, give the corrected formula. Do not rebuild the entire model and do not force the Balance Check to zero with a plug.
A non-zero Balance Check means the model is not internally consistent; a broken linkage, omitted roll-forward item, or sign/formula error is a common cause.
I have a 3-statement model with [3] years of projections. I need to extend it to [5] years. Here are my existing projection years and assumptions: [Paste the header row and assumption rows from your model] New assumptions for years 4 and 5: - Revenue growth: [e.g. 8% per year] - CapEx: [e.g. $250,000 per year] - Debt repayment: [e.g. same as current] - All other assumptions: same as year 3 unless I specify otherwise Please extend the model by two forecast years using the same row structure and supporting schedules. Before writing formulas: - identify the last existing forecast column and the two new year headers - list every schedule that must extend (working capital, PP&E, debt, interest, taxes, retained earnings and cash) - flag any assumption that cannot safely be copied from Year 3 Then write the exact formulas for the two new columns, preserving relative/absolute references correctly. Validation: - retained earnings roll-forward must continue - debt cannot go below zero unless the model explicitly allows it - BS Cash must equal CFS Ending Cash - Balance Check must remain within the stated rounding tolerance - do not hard-code a forecast output merely to make the model balance.
Provide the full assumption set for the new years — vague inputs produce vague projections.
| A | B | C | |
|---|---|---|---|
| 1 | Line | FY24 | FY25 |
| 2 | Income Statement | ||
| 3 | Net Income | £120,000 | £135,000 |
| 4 | Balance Sheet | ||
| 5 | Cash | £210,000 | £210,000 hardcoded |
| 6 | Net PP&E | £760,000 | £760,000 hardcoded |
| 7 | Retained Earnings | £500,000 | £500,000 hardcoded |
| 8 | Cash Flow Statement | ||
| 9 | Ending Cash | £210,000 | not linked |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Line | FY24 | FY25 | FY25 formula |
| 2 | Net Income (IS) | £120,000 | £135,000 | =IS!C18 |
| 3 | Dividends / Distributions | £0 | £20,000 | =Assumptions!C12 |
| 4 | Retained Earnings (BS) | £500,000 | £615,000 | =B4+C2-C3 |
| 5 | Net PP&E (BS) | £760,000 | £850,000 | =B5+CapEx-D&A |
| 6 | Ending Cash (CFS) | £210,000 | £268,000 | =BeginningCash+CFO+CFI+CFF |
| 7 | Cash (BS) | £210,000 | £268,000 | =CFS!C30 |
| 8 | Balance Check | £0 | £0 | =TotalAssets-(TotalLiab+Equity) |
Retained Earnings: Prior RE + Net Income − Dividends/Distributions Net PP&E: Prior Net PP&E + CapEx − D&A Cash: BS Cash = CFS Ending Cash Balance Check: Total Assets − (Total Liabilities + Equity) = 0 ✓ Net income does not simply "flow into retained earnings" if dividends/distributions exist.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Line | FY24 | FY25 | Formula / source |
| 2 | CFS Ending Cash | £210,000 | £268,000 | CFS calculation |
| 3 | BS Cash | £210,000 | £260,000 | =B3 + £50,000 |
| 4 | Total Assets | £1,800,000 | £1,972,000 | Includes BS Cash |
| 5 | Total Liabilities + Equity | £1,800,000 | £1,980,000 | |
| 6 | Balance Check | £0 | −£8,000 | Assets − L&E |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Line | FY24 | FY25 | Formula / source |
| 2 | CFS Ending Cash | £210,000 | £268,000 | CFS calculation |
| 3 | BS Cash | £210,000 | £268,000 | =CFS!C30 |
| 4 | Total Assets | £1,800,000 | £1,980,000 | Cash corrected +£8,000 |
| 5 | Total Liabilities + Equity | £1,800,000 | £1,980,000 | |
| 6 | Balance Check | £0 | £0 | =C4-C5 |
Broken FY25 BS Cash: £260,000 CFS Ending Cash: £268,000 Difference: £8,000 Correct BS Cash formula: =CFS!C30 Total Assets rise from £1,972,000 to £1,980,000. Balance Check: £1,980,000 − £1,980,000 = £0 ✓
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Assumption / Output | FY25E | FY26E | FY27E | FY28E |
| 2 | Revenue Growth | 10% | 10% | 10% | — |
| 3 | CapEx | £200k | £200k | £200k | — |
| 4 | Debt Repayment | £50k | £50k | £50k | — |
| 5 | Revenue | £1.100m | £1.210m | £1.331m | No FY28 formula |
| 6 | Balance Check | £0 | £0 | £0 | — |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Assumption / Output | FY27E | FY28E | FY29E | Formula pattern |
| 2 | Revenue Growth | 10% | 8% | 8% | Years 4–5 assumption |
| 3 | CapEx | £200k | £250k | £250k | New assumption |
| 4 | Debt Repayment | £50k | £50k | £50k | Same as Year 3 |
| 5 | Revenue | £1.3310m | £1.4375m | £1.5525m | =PriorRevenue*(1+Growth) |
| 6 | Net PP&E | linked | Prior + CapEx − D&A | Prior + CapEx − D&A | Roll-forward |
| 7 | BS Cash | =CFS | =CFS Ending Cash | =CFS Ending Cash | Cross-statement link |
| 8 | Balance Check | £0 | £0 | £0 | Must stay zero |
FY28 Revenue = £1.3310m × 1.08 = £1.43748m ≈ £1.4375m FY29 Revenue = £1.43748m × 1.08 = £1.5524784m ≈ £1.5525m ✓ But the extension is only valid if every linked schedule also continues: working capital, debt, interest, CapEx, D&A, PP&E, cash and retained earnings. Balance Check must remain £0 in every added year.
Keep D&A separately traceable from cash operating expenses. It is an income-statement expense and a non-cash add-back on the indirect CFS; the fixed-asset schedule must also reflect depreciation in PP&E / accumulated depreciation where applicable.
Start from a complete historical balance sheet that already balances, including all material asset, liability and equity lines—not only selected working-capital accounts. Any forecast balance driven by operations should have an explicit roll-forward or assumption and a corresponding CFS treatment where applicable.
Revenue grows 10% per year is usable. "Revenue will increase" is not. ChatGPT needs specific percentages, dollar amounts, and ratios to build formulas. Vague inputs produce vague models.
Check the Balance Check in every historical and forecast period. It should be zero within the model's stated rounding tolerance. A non-zero amount signals inconsistency, but the variance itself does not identify the cause; trace the affected roll-forwards before changing formulas.
Keep forecast assumptions in a clearly separated assumptions area and link forecast formulas to them. Historical actuals remain hardcoded inputs; forecast outputs should be formula-driven except where an explicit forecast assumption itself is the intended hardcode.
Look at the Balance Check row to see exactly how much you are off by. A variance that matches a known flow amount can point to a missing linkage, but confirm the direction and the relevant roll-forward before changing a formula.
Review the Diagnosis Table above. Ensure Cash is driven by the CFS, CapEx increases PP&E, and D&A is properly added back.
Use the debug prompt with the affected period plus enough supporting roll-forward rows and formulas to prove the break. Do not dump the whole workbook, but do not omit a schedule merely because you assume it is unrelated.
Define the exact additional forecast years and label the last existing forecast column before extending formulas.
Specify exactly what happens to growth, margins, working-capital drivers, CapEx, depreciation, taxes, debt, interest and distributions in the added years; do not silently carry Year 3 assumptions forward.
Paste ChatGPT's new formula columns next to your existing ones. Verify the Balance Check row for the new years still equals 0 before saving.