The Prompt
Copy it, paste it, change the details.
The “About my business” section is filled in for Smith Automotive so the sample works straight away. Replace it with your own details before you run it on your books.
## About my business (example: replace with your own details)
- Business name: Smith Automotive Inc.
- My QuickBooks reports run to (the last day my books are up to date): November 8, 2026
- Forecast starts on (the Monday after that): November 9, 2026
- Minimum cash I want in the bank at all times: $40,000
- Sales tax: Ontario HST, 13%, filed quarterly
- Card and POS sales reach my bank: the Monday after each sales week
- Sales growth to assume: 0% (sales follow last year's pattern)
- Big payments I know are coming (exact date, amount, what): January 15, 2027, $14,800, garage insurance renewal (Ridgeway Insurance Brokers); December 31, 2026, $6,000, corporate income tax instalment
- Anything else you should know (a customer who won't pay, a loan ending, a price increase): none
## What I need
Build me a 13-week cash flow forecast from the attached QuickBooks reports: an Excel workbook I can work in, and a PDF summary I can share. I want to know which week my cash is lowest, whether it drops below my minimum, which payment causes it, and what I can do about it, cheapest options first.
Attached reports (from QuickBooks Online, as of the same date):
1. Balance Sheet
2. A/R Aging Detail (what customers owe me)
3. A/P Aging Detail (bills I owe)
4. Bank transaction list for the last 90 days (Transaction List by Date, filtered to my bank account)
5. Profit and Loss by Month, accrual basis, last 12 months or more
*(If you are connected to my QuickBooks instead, run these same five reports yourself, all as of the date in "About my business", not today's date. The bank list covers the 90 days up to that date.)*
## Ground rules
- Use only the numbers in the reports and the details I gave above.
- **Follow the method below exactly, every time.** It is written so that the same reports always give the same forecast. Where it gives a rule, use that rule even if you think another approach is better; put your suggestion in Data checks instead. Never add a cash item, lever or adjustment the method doesn't ask for.
- Do not do arithmetic in your head: build the workbook with formulas so every number can be checked.
- If you are connected to my QuickBooks instead of reading files: **read only**. Do not create, change, send or delete anything, and do not use any lending or loan tools.
- Do not recommend a specific lender, loan or financial product. If financing might be needed, say "talk to your accountant or lender" and stop there.
## How to build the forecast (fixed method)
**Data checks first.** In a "Data checks" tab, list: invoices more than 90 days past due; suppliers paid regularly from the bank who have no bills in A/P; anything that looks duplicated or odd; and any place where the method below didn't fit my data. Tell me about these first, in plain words. Data checks never change the numbers.
Then build each cash line with these rules. "The bank list" means the 90-day bank transaction list. Dates are calendar dates; a week runs Monday to Sunday; week 1 starts on the forecast start date.
1. **Opening cash** = the total of the bank accounts on the Balance Sheet.
2. **Monthly sales** = Total Income for the same month last year in the P&L by Month, times (1 + the growth % above). For the month the forecast starts in, subtract the Total Income already recorded this month. Spread each month's remaining sales evenly over its remaining days (from the forecast start date, or the 1st of later months).
3. **Paid-on-the-spot share** = card/POS deposits in the bank list for the last two complete calendar months before the report date, divided by (Total Income for those two months × (1 + tax rate)). Invoiced share = 1 minus that. Use these two shares for every month.
4. **Sales paid on the spot**: for each sales week, add up that week's daily sales × paid-on-the-spot share × (1 + tax rate). The cash arrives on the day given above (for "the Monday after each sales week", the following Monday). Sales from before the forecast start are already in the bank.
5. **Customer payment terms and days to pay**: terms = due date minus invoice date on that customer's open invoices in the A/R Aging Detail. Days to pay = the larger of (a) the terms and (b) the age of the customer's oldest open invoice on the report date, **plus 7 days**. Round to whole days. (They haven't paid the oldest invoice yet, so assume at least another week.)
6. **Doubtful invoices**: an open invoice more than 90 days past due, or any past-due invoice from a customer with no payment in the bank list. Leave them out of the forecast.
7. **Open customer invoices**: each arrives on its invoice date plus that customer's days to pay.
8. **Future customer invoices**: for each week, that week's sales × invoiced share × (1 + tax rate), split across customers in proportion to their open A/R balances (doubtful invoices excluded). Each is dated the Wednesday of the sales week and arrives that many days to pay later. Keep rows that land after week 13 in the Detail tab; they just aren't counted.
9. **Bills in A/P**: each paid on its due date. Overdue bills: paid on the forecast start date.
10. **Regular payments** (payroll, remittances, rent, leases, loan payments, utilities, software, fees, owner draws, and suppliers paid without bills): a payee paid at least twice in the bank list at a steady interval. Amount = the average of its last three payments (or all of them if fewer). Cadence = the usual gap, rounded to weekly, every two weeks or monthly. Next dates = continue from the last payment on that cadence (monthly: same day of the month). If one payee has different kinds of payment (the Receiver General's payroll remittances, sales tax and income tax; a bank's loan payment and service charge; a payroll company's net pay and its fee), split them by memo and treat each as its own payee. Use the exact dates; don't move a date that falls on a weekend. A payee paid once is regular only if the P&L by Month shows the same expense every third month; then it is quarterly, three months after the last payment. Don't add a regular payment for a cost that's already a bill in A/P for the same period.
11. **Monthly supplier statements** (a supplier paid around the same day each month for the last three months whose memo or bills show it sends a statement, whether paid by bill payment or straight from the bank; these go in the "Suppliers not yet billed" row, and this rule wins over rule 10): forecast each statement not yet in A/P at the average of the last three payments, paid on the same day of the month as the last payment. Suppliers who bill only now and then: nothing beyond their A/P bills. List them under Data checks as a risk.
12. **Sales tax**: payment = the sales tax balance on the Balance Sheet (it already includes every invoice and bill dated up to the report date, including the bills in A/P), plus tax on forecast sales from the forecast start to the end of the current filing period, minus the tax in supplier costs **dated** from the forecast start to the end of the period. Those costs are the regular payments under rule 10 and the monthly statements under rule 11 for rent, leases, utilities, phone, software, advertising, supplies, parts, payroll service, professional and disposal fees (not wages, remittances, loans, insurance, bank or card fees, owner draws or government payments). A regular payment is dated the day it's paid; a monthly statement is dated the last day of the month it covers, even though it's paid the next month. Tax in a payment = amount × rate ÷ (1 + rate). Paid one month after the period ends.
13. **Known payments** from "About my business": on the exact date and amount given. They replace any matching pattern you found (don't count anything twice).
14. Nothing else. No other annual or irregular items, no growth, no extra purchases. If you think something is missing, put it in Data checks.
## The workbook (Excel, .xlsx)
Use exactly these tabs, in this order:
- **Summary** (first tab, prints on one landscape page): my one-page cash flow summary. Full spec below.
- **Assumptions**: business name, forecast start date, opening cash, minimum cash, GST/HST rate and frequency, scenario settings (delay weeks = 3; downside sales cut = 15%). Every other tab refers to these cells.
- **Detail**: one row per expected cash item. Columns: Category, Description, Source (which report), Expected date, Amount (+ in, − out), Confidence (High/Medium/Low), Week (formula from date and start date), Week if delayed (formula: +delay weeks for the two largest customer receipts), Amount in downside (formula: cuts on-the-spot sales by the downside %).
- **Forecast**: 13 weekly columns with week-start dates. Rows, in this order: Opening cash; receipts by category (Sales paid on the spot, Open invoices, Future invoices, Other receipts); Total receipts; payments by category (Payroll, Payroll remittances, Bills in A/P, Suppliers not yet billed, Rent and leases, Loan payments, GST/HST, Income tax, Insurance and annual items, Other operating, Owner draws); Total payments; Net cash flow; Closing cash; Minimum cash; Above/(below) minimum. Every amount is a SUMIFS on the Detail tab. Closing cash cells turn red below the minimum.
- **Scenarios**: closing cash by week for Base, Delayed (the two largest single customer receipts in weeks 1 to 13, open or future invoices, each 3 weeks later) and Downside (sales paid on the spot × 85%), with the lowest week and lowest balance for each. Add a line chart of the three scenarios against the minimum.
- **Levers**: exactly the four levers defined below, each with the dollar effect and the week it helps. Add a row showing closing cash by week with all the cheap levers applied, and a line chart of that against the base forecast and the minimum.
- **Data checks**: the problems you found.
### The Summary tab (one page)
Write it for an owner, not an accountant: short sentences, no jargon. Top to bottom:
1. **Title**: "[Business name]: 13-week cash flow summary", the forecast period, and the date the reports run to.
2. **Four headline numbers**, as formulas linked to the Forecast tab: opening cash; lowest closing cash and the week it happens; how far above or below my minimum that is; total cash in and total cash out over the 13 weeks.
3. **The chart**: one combined chart by week, with cash in as green columns, cash out as red columns (shown as negatives), closing cash as a dark line, and my minimum as a dashed line. Label the axis in dollars and the weeks by their start date.
4. **What's happening**: three or four plain sentences on why cash moves the way it does, and the one or two payments that cause the low point.
5. **Biggest payments ahead**: the five largest single payments in the 13 weeks (date, who, amount), pulled from the Detail tab.
6. **What you can do, cheapest first**: a small table of the four levers (action, dollar effect, week it helps), ending with the financing line from the Levers tab.
7. **Fix with your bookkeeper**: the top three data checks, one line each.
8. **Footer**: "This is a forecast, not a promise. Update it weekly. Check the Assumptions tab."
Set the print area to this content, landscape, fit to one page wide by one page tall. Nothing on this tab may spill onto a second page.
The levers, exactly these four, in this order (no others):
1. **Collect overdue invoices**: every past-due invoice that isn't doubtful and is forecast after week 2 arrives in week 2 instead; doubtful invoices are collected in week 3. The dollar effect shown is the doubtful amount (the only new cash); say that the rest is timing only.
2. **Move one supplier payment**: the single largest A/P bill paid in the lowest week moves 4 weeks later.
3. **Pause owner draws**: every owner draw in the 13 weeks is not taken.
4. **Delay spending that can wait**: advertising and professional fees in weeks 5 to 13 move past week 13.
Then the financing line: if the lowest closing cash with all four levers is below my minimum, the amount to discuss is the shortfall rounded up to the next $5,000, from the first week below the minimum. Also show the same figure without lever 2. If it isn't below the minimum, say no financing is expected.
## The PDF cash flow summary (portrait, for my banker or advisor)
As well as the workbook, give me a PDF I can hand to my banker, lender or advisor: **US Letter, portrait, two pages at most**. It must use exactly the same figures as the workbook. Calculate them in Python from the same Detail rows, and check that the lowest balance, lowest week and 13-week totals match the workbook's Summary before you finish. Use reportlab for the layout and matplotlib for the chart. Keep the design plain and professional: one accent colour, no emoji, and money shown as $12,345.
**Page 1: Summary**
1. **Header**: "[Business name]", "13-Week Cash Flow Forecast", the forecast period, and "Prepared [today's date] from QuickBooks reports as of [report date]".
2. **Four key figures in a row**: opening cash; lowest projected balance and its week; position against the minimum cash level; total receipts and total payments over 13 weeks.
3. **The chart**, the full width of the page: by week, receipts as green columns, payments as red columns (negative), the closing balance as a dark line, and the minimum cash level as a dashed line. Week start dates go on the x-axis and dollars on the y-axis, with a legend.
4. **Commentary**: four to six sentences in a measured, factual tone, suitable for a lender. Cover the seasonal pattern, the main receipts and the main payments that drive the low point, and when it occurs.
5. **Planned actions**: a short table of the four levers from the workbook (action, amount, timing), and the resulting low point if they are all taken. End with one sentence giving the financing line from the Levers tab. Leave it out if none is needed.
**Page 2: Detail**
6. **Weekly cash flow table**: one row per week (week starting, opening balance, receipts, payments, net cash flow, closing balance), with a totals row. Highlight in light red any week that closes below the minimum.
7. **Largest scheduled payments**: the five largest single payments (date, payee, amount).
8. **Key assumptions**: five or six bullets covering sales basis, customer payment timing, sales tax, payroll, supplier statements, and the known payments I gave you.
9. **Sensitivity**: one line each for the Delayed and Downside scenarios (lowest balance and week).
10. **Footer on both pages**: "Unaudited management forecast prepared from QuickBooks Online reports. Actual results will differ. Page X of 2."
Leave out the data-quality issues for my bookkeeper. They belong in the workbook, not in a document for a lender.
## What to tell me in the chat
Keep it to a few lines: the lowest week, the lowest balance against my minimum, the main cause, and "open the Summary tab first; the PDF is ready to share." Then list any assumptions I should double-check. Everything else belongs in the workbook and the PDF.