Managing Cash Flow Forecasting

Managing Cash Flow Forecasting

When To Use

• Building or refreshing a short-term (13-week) or long-term (12–24 month) cash flow forecast
• Preparing liquidity analyses for board reporting, lender covenant compliance, or capital planning
• Performing variance analysis on prior forecast periods to improve future accuracy
• Stress-testing cash positions under downside scenarios (revenue shortfall, delayed collections, accelerated payables)
• Supporting treasury decisions on draw-downs, investments, or debt repayment timing

Inputs To Gather

• Historical cash flow data: Minimum 12 months of actual bank statements or ERP cash ledger exports (operating, investing, financing categories)
• Revenue pipeline: Booked revenue, contracted backlog, and probabilistic pipeline by expected collection date
• Payables schedule: Committed expenditures, vendor payment terms, payroll calendar, tax due dates, debt service schedule
• Working capital metrics: Current DSO, DPO, DIO; any recent trend shifts or seasonal patterns
• Capital expenditure plan: Approved and pending capex with expected disbursement timing
• Financing facilities: Revolver availability, term loan amortization, letter-of-credit commitments, covenant thresholds (minimum liquidity, fixed charge coverage) [VERIFY specific covenant definitions per credit agreement]
• Assumptions register: FX rates, interest rate curves, intercompany settlement timing, dividend policy

Workflow

1. Define forecast horizon and granularity

   • 13-week forecast: weekly buckets, direct-method (receipts and disbursements)
   • 12–24 month forecast: monthly buckets, indirect-method starting from EBITDA or net income
   • Confirm reporting currency and any multi-currency consolidation requirements

2. Build the receipts model

   • Map booked receivables to expected collection weeks using historical DSO distribution (not a single-point average)
   • Layer in probabilistic pipeline receipts with weighted probability haircuts
   • Add non-operating inflows: asset disposals, tax refunds, insurance proceeds, intercompany loans

3. Build the disbursements model

   • Slot committed payables by contractual due date and actual payment behavior (early-pay discounts vs. stretch)
   • Include payroll, benefits, and tax withholding on their fixed calendar dates
   • Schedule debt service (principal + interest), capex draws, and dividend payments
   • Add contingency line items for unplanned outflows (litigation reserves, warranty claims) with probability weighting

4. Calculate net cash flow and cumulative position

   • Net weekly/monthly cash flow = total receipts minus total disbursements
   • Cumulative cash = opening cash + net cash flow + any facility draws/repayments
   • Flag any period where projected cumulative cash falls below minimum liquidity threshold or covenant floor

5. Run scenario and sensitivity analysis

   • Base case: Management's best estimate
   • Downside case: Revenue delayed by X days, top-customer default, capex acceleration
   • Upside case: Accelerated collections, deferred discretionary spend
   • Identify the breakeven assumptions that would trigger a liquidity shortfall

6. Perform variance analysis on prior periods

   • Compare each line item's forecast vs. actual for the most recent 4–8 periods
   • Compute forecast accuracy metrics: Mean Absolute Percentage Error (MAPE) by line item
   • Identify systematic biases (e.g., consistently over-forecasting collections) and adjust current model inputs accordingly

7. Document liquidity action triggers

   • Define thresholds that trigger specific treasury actions (e.g., draw on revolver if projected cash < $X for 2+ consecutive weeks)
   • Specify escalation path: treasury analyst -> CFO -> board if minimum liquidity is breached under base case

Output

The deliverable is a Cash Flow Forecast Report containing:

• Executive summary: Current liquidity position, forecast horizon covered, key risks and recommended actions
• Forecast schedule: Tabular receipts/disbursements by period with net and cumulative cash rows
• Scenario comparison table: Base, downside, and upside cumulative cash by period with covenant headroom noted
• Variance analysis: Prior-period forecast vs. actual with MAPE and bias direction per major line item
• Assumptions register: Every material assumption documented with source and last-validated date
• Action trigger matrix: Liquidity thresholds mapped to specific treasury responses and responsible parties

Quality Checks

• Confirm opening cash balance ties to the most recent bank reconciliation or treasury report
• Verify debt service amounts match the amortization schedule in the credit agreement [VERIFY]
• Ensure covenant calculations use the same definitions as the credit agreement (e.g., whether cash includes restricted cash) [VERIFY]
• Check that FX conversion rates are sourced consistently and dated appropriately
• Validate that the sum of weekly buckets in the 13-week forecast reconciles to the corresponding monthly totals in the long-term forecast
• Confirm no double-counting of intercompany flows in consolidated forecasts
• Review that probability weights on pipeline receipts reflect current sales team assessments, not stale data
• Ensure variance analysis covers enough periods (minimum 4) to identify trends rather than one-off anomalies