What this automation does
Every Monday, the finance lead opens a forecast that built itself over the weekend, with notes on what changed and why.
- Start from actual cash. The workflow reads closing balances for every bank account, and available credit on any line of credit.
- Project inflows. Open invoices from QuickBooks Online or Xero are placed in the week each is likely to be paid, using each customer’s own history of days late, not the due date. Promises to pay from collections override that estimate. Weighted pipeline from HubSpot adds expected new invoices, lagged by your typical time from close to cash.
- Project outflows. Open bills land on their scheduled payment date or due date. Payroll comes from the payroll calendar. Recurring items such as rent, loans, software and insurance are detected from twelve months of bank history and projected forward.
- Add known one-offs. Quarterly estimated taxes, annual renewals, capital purchases and loan payments are kept in a short list the finance lead maintains.
- Explain the change. The workflow compares last week’s forecast with what actually happened and with this week’s forecast. An LLM writes a short note on the largest differences, citing the invoices, bills or categories behind them.
- Answer what-ifs. In Slack or a simple web page, the finance lead asks scenario questions. The AI turns each into input changes, the forecast reruns as a scenario, and the answer shows the lowest-cash week under each case.
When it is worth building
Build this when cash is tight enough that a bad week would hurt, and the current forecast is a spreadsheet one person updates by hand when they have time. Construction firms with retainage and progress billing, distributors carrying inventory, and services firms with a few large clients usually get the most from it.
Skip it if cash is comfortable and predictable. A monthly look at the balance sheet is enough. Also skip it, for now, if your AR and AP records are unreliable. The forecast depends on open invoices and bills being current, so fix that first. Collections are often the best first step, since automated AR collections produce the promise-to-pay dates that sharpen the inflow side.
Architecture
| Layer | What we use | Why |
|---|---|---|
| Ledger data | QuickBooks Online or Xero, using n8n’s built-in nodes for invoices and bills and the HTTP Request node for bank balances and payment detail | Open invoices, bills, payment history and bank balances |
| Pipeline | HubSpot deals with stage, amount and expected close date | Future invoicing that is not in the ledger yet |
| Payroll | A payroll register export (Gusto limits its API to approved partners, so exports are the usual path) | Payroll is usually the largest predictable outflow |
| Forecast engine | Python or SQL running against Postgres, scheduled by n8n | Forecast math must be transparent, testable and reproducible |
| Snapshots | Postgres tables storing every weekly forecast | Accuracy can only be measured if past forecasts are kept |
| AI layer | Claude Sonnet 5.5 through the API, with tool calls for running scenarios | Classifies bank history, writes variance notes and translates questions into inputs |
| Output | Google Sheets or Excel with a week-by-week grid and a chart | Finance teams and lenders read cash forecasts in spreadsheets |
How to build it
- Agree on the format. Define the weekly grid, the inflow and outflow categories, and the minimum cash threshold that triggers an alert. Use the format your lender or board already expects.
- Load two years of history. Pull paid invoices, paid bills and bank transactions. This is the raw material for payment timing and recurring item detection.
- Model customer payment timing. For each customer with enough history, compute typical days from invoice to payment. Customers with little history use a segment default. Keep it simple and explainable.
- Detect recurring outflows. Use the model to classify bank transactions by counterparty and category, then use code to find regular amounts and intervals. Show the detected list to the finance lead to confirm or edit.
- Build the engine and back-test. Generate forecasts as of past Mondays and compare with what actually happened. Tune the timing model and lags until misses are understood.
- Add commentary and scenarios. Give the model read access to the snapshot tables and one tool that runs a scenario with changed inputs. It cannot edit the base forecast.
- Schedule and alert. Run weekly, write the output, and alert in Slack when any week drops below the minimum cash threshold.
What breaks in production
- Thin customer history. Two payments are not a pattern. Fall back to a segment average until a customer has enough paid invoices, and show which customers use defaults.
- Double-counted revenue. A deal closes, gets invoiced, and is counted once from pipeline and again as an open invoice. Remove closed-won deals once their invoice exists, linking them by customer and amount.
- Lumpy annual items. Insurance premiums, annual software and tax payments are missed because they do not appear in a twelve-month window often enough. Keep them in the one-offs list and review it quarterly.
- Transfers and financing. Moves between your own accounts, credit line draws and repayments look like inflows and outflows. Tag them so they do not distort operating cash.
- Optimistic pipeline. Weighted pipeline is only as good as your stage probabilities. Use historical win rates by stage rather than the CRM’s defaults.
- Stale inputs. If bills are entered late, the forecast looks better than reality. Track how many outflows each week were not in the forecast at all.
Timeline and cost
A single-entity forecast with ledger, pipeline and payroll inputs, back-testing and scenario questions typically takes four to seven weeks. Lumeor builds it for $10,000 to $28,000, depending on the number of entities and bank accounts, whether job costing or project billing needs modeling, and how much history needs cleaning. We price it as an AI integration project because it adds AI to your existing ledger and spreadsheets rather than replacing them. See what adding AI to existing software costs. For why the engine is code rather than a chain of no-code steps, see no-code vs custom-code automation. Pipeline inputs are easier to trust once pipeline reporting is in place.
How to measure success
- Forecast error in net cash at one, four and thirteen weeks ahead, tracked weekly
- Whether the lowest-cash week was correctly identified at least four weeks in advance
- Outflows each week that were not in the forecast at all
- Finance lead hours per week spent producing the forecast
- Credit line draws that were planned rather than reactive