Cash Flow Forecasting

The company produced a 13-week rolling cash flow forecast manually every Friday. It was the CFO's most important planning tool and also the most painful part of their week.

The data came from four systems, none of which talked to each other. AR aging lived in QuickBooks — exported to CSV, then pasted into a master Excel model. AP schedules were in Bill.com — another export, another paste. Payroll projections came from Gusto, exported as a payroll calendar. Bank balances were checked by logging into the bank website and reading the balance. Each week the analyst would spend Friday morning collecting these four exports, reconciling the opening balance against the actual bank balance, then building out the 13-week projection using a combination of the aged receivables, the payment schedule from Bill.com, and fixed assumptions about payroll and overhead.

The forecast took about 4 hours to produce. It was done once a week, which meant the CFO was making decisions with data that was up to 7 days old. Twice in the preceding 12 months, the company had come closer to a cash constraint than was comfortable — once due to a large customer paying 30 days late, once because a vendor prepayment requirement wasn't visible in the forecast until too late. Both situations were identified only by chance during the manual weekly build.

We built a forecasting pipeline that runs automatically every weekday morning at 6am and deposits an updated forecast into a Google Sheet before anyone arrives at their desk.

The pipeline runs on a Raspberry Pi in the client's office network. A Pi Agent orchestrator pulls live data from all four sources in parallel — via REST where APIs are allowed, and from scheduled CSV exports dropped to a local folder where not. QuickBooks AR aging, Bill.com open bills, Gusto payroll dates, and Plaid bank balances are assembled on the device. Raw ledgers never leave the LAN.

With the raw data assembled, the workflow runs the forecast calculation. AR is projected based on the due dates in the aging report, with a payment probability applied to each invoice by aging bucket: current invoices are assumed to collect at 95% on due date, 31–60 days is 75%, 61–90 is 50%, over 90 is 20%. These probability percentages were calibrated against 18 months of historical payment data during the initial setup and can be adjusted in a configuration sheet.

AP projections are taken directly from Bill.com scheduled payment dates with no adjustment — these are committed outflows. Payroll is taken from the Gusto schedule as a fixed outflow on each payroll date. Overhead expenses (rent, subscriptions, other recurring costs) are maintained in a configuration table in the Google Sheet and applied as fixed weekly outflows.

The output is a week-by-week cash flow table for the next 13 weeks: opening balance, expected collections by week, scheduled AP payments, payroll, overhead, net cash movement, and closing balance. The model runs three scenarios — base (using the calibrated probabilities), conservative (all probabilities reduced by 15%), and optimistic (due dates taken at face value without probability discounting). All three are written to separate tabs in the Google Sheet.

A Slack alert fires automatically if any week in the base scenario shows a projected closing balance below a threshold the CFO defined (in this case, a two-week operating expense buffer). The alert includes the projected low week, the projected balance, and which receivables or payables are driving it.

• •Pi Agent on Raspberry Pi 5 (8GB) — systemd service restarts the agent if the device reboots; UPS recommended for reliability
• •QuickBooks Online REST API — AR aging report endpoint; the response structure required some parsing to extract the bucket breakdown by customer
• •Bill.com API — session token refresh handled by a small Python tool the agent calls locally
• •Gusto API — payroll schedules endpoint; the payroll data is the simplest of the four sources, returning fixed dates and amounts
• •Plaid — connected to both bank accounts; returns previous-day closing balances; Plaid requires an initial link flow where the CFO authorised access once via a hosted link
• •Claude API — customer payment probability step; prompt includes customer name, invoice history summary, and asks for a probability and expected collection week; output is structured JSON
• •Google Sheets as the output — the CFO preferred Sheets over a custom dashboard because they could add their own annotations and share specific ranges with the board

The customer payment probability model was the most time-consuming part to set up correctly. We pulled 18 months of historical AR data from QuickBooks and calculated actual days-to-pay by customer. Claude's initial probability estimates were then back-tested against this history — we compared what the model would have predicted with what actually happened. The first version was overoptimistic on a few slow-paying customers. We adjusted the prompt to be more conservative for customers with high variance in their payment timing.

The configuration file (forecast.yaml on the Pi) is designed so the finance team can adjust assumptions without touching code. Overhead expenses, probability buckets, and Slack alert thresholds are editable in one place.

We also set up a monthly reconciliation step where the workflow compares what it predicted 4 weeks ago against what actually happened. The output goes to a tab in the Google Sheet labelled "accuracy log." This isn't used to auto-adjust the model — it's for the analyst to review and decide if any assumptions need updating.