AI-Assisted Weekly Cash Flow Forecasting System

The Problem

A services company manages a portfolio of recurring contracts across managed services, licensing, staffing, and fixed-price projects. Every week, leadership needs to answer one question: do we have enough cash to cover payroll and obligations, and what levers can we pull if we don’t?

The old process was a single spreadsheet tab with manually copied numbers, week-over-week column shifting, and no structured link between what the company expected to invoice, what clients actually owed, and when cash would hit the bank. It took hours of manual effort each week and was fragile, one missed copy-paste could throw off the entire forward view.

What We Built

A formula-driven, AI-maintainable cash flow forecasting system across 7 interconnected tabs, designed so that a weekly update session with AI takes minutes instead of hours, while keeping human judgment in the loop for every payment decision.

The system separates source data from forecast logic. A Contracts Register holds the master list of active agreements. A Payables Master tracks recurring vendor obligations. These feed into a 16-week rolling Weekly Forecast where every number traces back to its source, no orphaned values, no broken chains.

System Architecture

The architecture follows a data flow pattern where source registers feed into forecasting layers:

• Contracts Register holds the master list of active contracts. Each contract has its billing type, amount, frequency, and a flag that controls whether it generates future invoices in the forecast.
• Future Invoicing takes the contracts register and projects forward, bundling line items into consolidated invoice views, then projecting them as expected revenue rows.
• Outstanding AR imports from the accounting system (QuickBooks export), showing what’s actually been invoiced and is waiting for payment. AI merges new data, marks collected invoices, and preserves manual hold flags.
• Weekly Forecast is the core 16-week rolling grid. It pulls from Future Invoicing for projected revenue, Outstanding AR for confirmed receivables, and the Payables Master for obligations. The cash position shows total bank balances, then subtracts burdened payments to reveal actual cash available.
• Payables Master tracks recurring vendor obligations across categories, feeding into the forecast’s expense side.
• Weekly Summary is the Monday briefing, a condensed view of the forecast detail grid designed for quick executive review.

Key Design Decisions

Bundled invoicing view. Line items from the revenue forecast are consolidated into logical invoice groups, then projected forward. Contracts over a certain threshold get individual rows for easy spot-checking; everything else bundles by type. Same client’s months sit side by side for easy comparison.

Burdened vs. unburdened cash. Autopay obligations (payroll, insurance, loan payments) are separated from discretionary vendor payments. The cash position shows total bank balances, then subtracts burdened payments to reveal the actual cash available for payment decisions.

Override protocol. When leadership delays a vendor payment, a cell note marks it as intentional. AI reads these notes before regenerating the forecast, so manual timing decisions survive the weekly refresh cycle.

Smart contract filtering. Not every active contract generates future invoicing. Annual renewals already billed don’t keep appearing in the forward forecast. A flag on each contract controls this, auto-populated by pattern analysis of billing history.

Bookkeeping notes register. Exceptions discovered during the build (misnamed contracts, expired agreements, uncertain collections) get tracked in a running log rather than getting lost in conversation.

The AI-Assisted Weekly Workflow

Each Monday, the update follows a structured sequence:

1. Confirm opening cash balances from banking
2. Refresh accounts receivable from a QuickBooks export, AI merges new data, marks collected invoices, preserves manual hold flags
3. Check payables assumptions against the master, AI scans for override notes before regenerating
4. Roll the forecast forward one week
5. Review the Weekly Summary for payment decisions

The AI handles the mechanical work: matching invoices, shifting columns, recalculating collection timing, regenerating future invoicing from the contracts register. The human retains judgment on which vendors to delay, which receivables to flag as doubtful, and whether new contracts should be included.

Every tab carries its own AI instruction block, specific rules for that tab, not buried in a separate documentation page. The AI reads these breadcrumbs before operating on any tab.

Results

The weekly update went from hours of manual work to a 15-minute AI-assisted session. The forecast now provides 16-week forward visibility into expected collections, with clear separation between confirmed receivables and projected future invoicing.

More importantly, it surfaced things that weren’t visible before. Contracts that should have been closed. Invoices that were misattributed. Recurring charges that didn’t match any active agreement. The process of building a rigorous forecast uncovered operational hygiene issues that had been accumulating quietly.

The system is designed to be self-maintaining. AI can run the weekly update because every tab has clear rules, every number has a source, and every human decision is marked so it doesn’t get overwritten. When assumptions change, a new contract, a lost client, a shifted payment date, the update propagates through the chain without rebuilding from scratch.

Tools Used

The entire system lives in Microsoft Excel with an AI spreadsheet assistant working inside the workbook. No custom code, no macros, no external databases. The choice was deliberate, the system needs to be maintainable by anyone comfortable with Excel, augmented by AI for the repetitive and error-prone parts. The AI doesn’t replace the CFO’s judgment. It replaces the hours of manual data wrangling that used to consume the morning before judgment could even begin.