Cash Flow Forecast

Generates a 13-week rolling cash flow forecast from QuickBooks Online data with base, optimistic, and pessimistic scenarios including burn rate and runway an...

samledger67-dotcom@samledger67-dotcom

Install

openclaw skills install @samledger67-dotcom/cash-flow-forecast

Cash Flow Forecast Skill

When to Use

Client asks for a 13-week (or rolling n-week) cash flow forecast
Need to project runway for a high-burn client (GlowLabs-type)
Monthly or quarterly cash flow advisory deliverable
Any time cash position monitoring or scenario modeling is needed
SOP for the client marks "Cash Flow Forecast: ✅"

NOT for

Historical cash flow analysis (use P&L Deep Analysis pipeline)
Bank reconciliation (use bank-reconciliation.py)
Full audit of cash flows (use QBO CF report directly)
Balance sheet projections / full financial models

Script Location

scripts/pipelines/cash-flow-forecast.py

Quick Start

bash

cd /Users/samshouse/.openclaw/workspace

# Standard 13-week forecast
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs

# 6-month history for better seasonality
python3 scripts/pipelines/cash-flow-forecast.py --slug sb-paulson --months 6

# Custom low-cash threshold (GlowLabs: high burn)
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs \
    --low-cash-threshold 50000 \
    --critical-threshold 25000 \
    --out ~/Desktop/reports

# Sandbox testing
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs --sandbox

All CLI Options

Flag	Default	Description
`--slug`	required	QBO company slug
`--weeks`	13	Forecast horizon (weeks)
`--months`	3	Months of CF history to pull (1–6)
`--low-cash-threshold`	25000	Warn when balance < X
`--critical-threshold`	10000	Critical alert when balance < X
`--account`	auto	Specific bank account name filter
`--out`	~/Desktop	Output directory
`--sandbox`	false	Use QBO sandbox

Output Excel Tabs

Tab 1: Cash Flow Summary

Current cash position (all bank/cash accounts from Balance Sheet)
13-week KPI summary: total inflows, outflows, net flow, end balance
Burn rate and runway in callout box
Full weekly trajectory table with alert coloring
SOP notes printed at top (client-specific warnings)

Tab 2: Weekly Detail

Matrix: each row = a CF subcategory, each column = a week
Inflows section (green) → Outflows section (red)
Subtotals, Net Flow row, Running Balance row
Alert coloring: yellow = warn, orange/red = critical

Tab 3: Scenarios

Side-by-side: Base | Optimistic | Pessimistic
12 KPI rows: inflows, outflows, net, end balance, burn, runway, alert weeks
Weekly balance comparison table across all 3 scenarios
Best/Worst delta and % spread columns

Tab 4: Burn Rate

Detailed burn metrics (base case)
Runway comparison table (all 3 scenarios)
Week-by-week burn trend
🔴 High burn emphasis for GlowLabs-type clients

Tab 5: Assumptions

All parameters used (thresholds, adjustments, seasonality weights)
Monthly averages per category (the baseline used)
Client SOP config summary
Methodology notes

Tab 6: CDC Log

Run-over-run changes: starting cash, burn rate, runway
Forecast accuracy: compares prior-run predicted starting cash vs. actual
First run: saves baseline; second+ run shows deltas

Scenario Definitions

Scenario	Collections	Expenses
Base	Historical avg	Historical avg
Optimistic	+10%	Unchanged
Pessimistic	-15%	+5%

Cash Flow Categories

Automatically classified from QBO CF rows:

Operating — Inflows

collections: Revenue, AR, Net Income, payment processor receipts
interest: Interest income

Operating — Outflows

payroll: Wages, salaries, officer comp, Deel (GlowLabs)
rent: Rent, lease, occupancy
vendors: AP, COGS, professional fees, software, subscriptions
interest: Interest expense (SB Paulson: material line)
taxes, insurance

Investing

capex: Equipment, property, asset purchases

Financing

debt: Loans, line of credit, notes payable
equity: Owner distributions/contributions
safe: SAFE notes (GlowLabs)

Client SOP Integration

The script reads clients/{slug}/sop.md automatically and adjusts:

SOP Signal	Effect
"burn rate", "runway", "high cash burn"	Burn Rate tab emphasized, 🔴 alert
"interest expense"	Interest tracked separately
"POS collection", "collected at POS"	No AR lag — flat weekly inflow distribution
"accounts receivable"	AR-based: note 30-45 day receipt lag
"SAFE"	SAFE financing category added
"crypto", "wallet", "ETH"	Crypto wallet note added
"Deel"	Deel inbound classified as expense reduction

Seasonality Weights

Default week-of-month distribution (configurable in script CONFIG section):

Week	Inflows	Outflows	Rationale
Week 1 (days 1–7)	30%	35%	Collections/payroll land early
Week 2 (days 8–14)	20%	20%	Mid-month quiet
Week 3 (days 15–21)	20%	25%	Mid-month payroll + rent
Week 4 (days 22–28)	30%	20%	End-month collections
Week 5 (overflow)	0%	0%	Rarely used

POS clients (e.g. SB Paulson): inflows flattened to equal weekly distribution.

CDC Accuracy Loop

First run: saves starting_cash, first_week_balance, avg_monthly_burn, months_of_runway
Second run: compares new starting_cash against prior first_week_balance
Accuracy % = how close the forecast was to reality
Burn rate and runway deltas tracked run-over-run
Cache stored in .cache/cash-flow-forecast/{slug}.json

Low-Cash Alert Colors

Color	Excel Fill	Condition
✅ OK	White	Balance ≥ low threshold
⚠ Warn	Yellow	Balance < $25K (configurable)
🔴 Critical	Orange/Red	Balance < $10K (configurable)

Client-Specific Notes

GlowLabs (High Burn)

--low-cash-threshold 50000 --critical-threshold 25000 recommended
SOP auto-detected: burn rate tab emphasized with red header
SAFE financing and Deel categories auto-classified
Runway is the #1 KPI — shown prominently in Summary tab

SB Paulson / Willo Salons

--months 6 for better seasonality (salon business is seasonal)
POS collection = no AR lag; inflows distributed flat across weeks
Interest expense tracked as separate operating outflow category
Cash Flow Forecast frequency: quarterly advisory (per SOP)

Dependencies

text

pip install openpyxl

Node.js QBO client must be authenticated with a valid token.

Output Naming

CashFlowForecast_{slug}_{YYYY-MM-DD}.xlsx Default output: ~/Desktop/

Related Pipelines

pl-quick-compare.py — P&L variance analysis
pl-deep-analysis.py — Controller-level with GL drill-down
bank-reconciliation.py — Bank statement reconciliation