Company context — why a 13-week cash flow finally became necessary
The client is a $120M professional-services firm with concentrated client billing (top 10 clients = 62% of revenue), milestone-and-T&M billing patterns that produce lumpy AR collection, and a payroll burden of ~$5.4M every two weeks. The company has a $25M revolving credit facility with availability based on eligible AR; weekly availability reporting is required by the lender. The CFO needed a 13-week cash forecast that could (a) support weekly availability checks, (b) flag liquidity events 4–8 weeks ahead, (c) model M&A and customer-concentration scenarios, and (d) survive the FP&A director's vacation.
The existing forecast was a 28-tab Excel workbook that the FP&A director rebuilt every Monday morning. It pulled from NetSuite saved searches via manual export, applied collection-pattern assumptions in formulas, projected payroll, AP, and discretionary spend, and produced a weekly cash position. The model worked when the FP&A director ran it. It produced wildly different numbers when anyone else ran it, and forecast accuracy was around ±12% on 13-week ending cash — not nearly tight enough for lender or treasury decisions.
- $120M revenue with 62% concentration in top 10 clients
- Mix of T&M (60%) and milestone-billing (40%) contracts
- ~$5.4M biweekly payroll burden
- $25M revolving credit facility with weekly availability reporting
- 14 entities (10 active operating, 4 dormant)
- NetSuite OneWorld with 3 currencies
- Weekly cash flow review with CFO + Treasury + lender (Friday morning)
Before automation
The Excel cash forecast was sophisticated for its kind, but it had structural problems that automation alone couldn't fix.
- Static snapshot — built every Monday, stale by Wednesday
- Single-person dependency — only the FP&A director could operate it end-to-end
- Forecast accuracy ±12% on 13-week ending cash (variance widened in weeks 8–13)
- Scenario modeling required cloning the workbook and rebuilding by hand
- Lender availability calculation was a separate workbook that didn't tie to the forecast
- AR collection patterns were composite assumptions, not customer-by-customer
- Project-level commitments (subcontractor labor, deliverables) lived only in the project ledger, not the forecast
- Forecast vs. actual variance review was manual, lagged 1–2 weeks
What Ledger Summit implemented
A driver-based 13-week rolling cash forecast that pulls live from NetSuite (AR, AP, GL, projects), applies customer-and-supplier-specific payment patterns, integrates with payroll (Gusto), and produces a daily-updated forecast with five preset scenarios. The forecast feeds the lender availability calculation directly.
- 13-week rolling forecast, refreshed daily, with cell-level drill-back to source
- AR collection model: customer-level payment patterns calibrated against 24 months of historical data
- AP payment model: vendor-level payment terms with discount-capture logic
- Payroll model: full-burden payroll + payroll tax + benefits + 401(k) match, modeled per pay date
- Project commitment model: subcontractor labor, deliverables, milestone payment outflows
- Discretionary spend model: based on rolling 4-week average + seasonality adjustment
- Tax obligation model: estimated quarterly tax payments, payroll tax remittances, sales-tax remittances
- Capital expenditure model: budget-driven with named owner per line
- Debt service model: interest, principal, fee schedule from credit facility
- Five preset scenarios: base, downside (top-3 client delay), upside (collection acceleration), M&A close, refinance
- Lender availability calculator integrated to the forecast
- Weekly variance review pack auto-generated
Driver architecture — what's modeled, how it's modeled
The defining principle of a driver-based forecast: every line is the output of an underlying business driver, not a hard-coded number. When the driver changes (a customer pays late, a project starts, payroll grows), the forecast updates.
| Cash flow line | Driver(s) | Source |
|---|
| AR collection — committed | Open invoices × customer-specific collection pattern | NetSuite AR aging + 24mo collection history |
| AR collection — billed-not-invoiced | Project WIP × billing milestone × customer pattern | NetSuite project ledger |
| AR collection — unbilled | Time-and-billing forecast × customer pattern | Billing system + sales pipeline |
| AP payments — current | Open bills × vendor-specific payment terms | Bill (BILL.com) |
| AP payments — discount capture | Discount % × eligible bill volume × policy | Bill + AP team policy |
| Payroll — net pay | Active headcount × per-pay-period rate × pay date | Gusto + budget |
| Payroll — taxes & benefits | Burden % × payroll × remittance schedule | Gusto + IRS schedule |
| Subcontractor labor | Active SOWs × labor commitment × payment schedule | Project commitments + AP |
| Discretionary spend | Rolling 4-week avg × seasonality factor | GL history |
| Capital expenditure | Approved capex schedule × payment timing | Capex pipeline + AP |
| Debt service | Outstanding × rate × amortization schedule + fee schedule | Credit facility agreement |
| Tax obligations | Federal/state estimated × quarterly schedule + payroll tax | Tax provider + Gusto |
| Sales tax remittances | Avalara liability × jurisdiction filing schedule | Avalara |
| FX impact (multi-currency) | Foreign cash positions × forward rate × translation | Bank balances + FX rates |
AR collection model — customer-by-customer payment patterns
The biggest source of cash-forecast inaccuracy is treating all customers the same. The driver-based model calibrates a payment pattern per customer using 24 months of historical actuals.
- Top 10 clients (62% of revenue): Individual payment-pattern fitting; any deviation flags an alert. Top-10 collections drive 70%+ of forecast variance, so this matters most.
- Tiers 11–50: Tier-level payment pattern; weighted average from historical tier behavior.
- Long tail: Composite pattern; smaller errors absorbed by the law of large numbers.
- New customers: Industry-typical pattern with risk premium until 6 months of payment history accumulates.
- Disputed invoices: Carved out into a separate "disputed" bucket with manual reviewer-driven timing.
- Customer financial distress flags: Pattern adjustment when distress indicators appear (D&B, payment delay trend, news monitoring).
Scenario modeling — five preset scenarios always live
Static cash forecasts handle one future. The CFO needs to see five futures at once: base case, what-could-go-wrong, what-could-go-right, M&A, and refinance.
- Base case — current driver assumptions; the "most likely" 13-week trajectory
- Downside (top-3 client delay) — top-3 customers pay 30 days late; tests sensitivity to concentration risk
- Upside (collection acceleration) — payment terms shortened by 5 days via early-pay program; tests financing-alternative case
- M&A close — close of pending acquisition with cash payment + opening-balance impact
- Refinance — extension or replacement of credit facility with revised debt service
Each scenario is a parameter override on the same driver model — not a separate workbook. Scenario comparison is two clicks; sensitivity to any individual driver is a slider.