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#

FlagDefaultDescription--slugrequiredQBO company slug--weeks13Forecast horizon (weeks)--months3Months of CF history to pull (1–6)--low-cash-threshold25000Warn when balance < X--critical-threshold10000Critical alert when balance < X--accountautoSpecific bank account name filter--out~/DesktopOutput directory--sandboxfalseUse 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#

ScenarioCollectionsExpensesBaseHistorical avgHistorical avgOptimistic+10%UnchangedPessimistic-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 SignalEffect"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):

WeekInflowsOutflowsRationaleWeek 1 (days 1–7)30%35%Collections/payroll land earlyWeek 2 (days 8–14)20%20%Mid-month quietWeek 3 (days 15–21)20%25%Mid-month payroll + rentWeek 4 (days 22–28)30%20%End-month collectionsWeek 5 (overflow)0%0%Rarely used

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

CDC Accuracy Loop#

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

Low-Cash Alert Colors#

ColorExcel FillCondition✅ OKWhiteBalance ≥ low threshold⚠ WarnYellowBalance < $25K (configurable)🔴 CriticalOrange/RedBalance < $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