Cash Flow Forecasting

Revenue and profit tell you whether the business model is working. Cash flow tells you whether the business can survive. A company can be profitable on paper and still run out of cash — if customers pay late, if inventory builds up, or if a large expense falls due before revenue arrives. Cash flow forecasting translates your revenue and cost projections into actual money movements, period by period, so you can see funding gaps before they become crises.

The Difference Between Profit and Cash Flow

This distinction is fundamental and worth stating clearly before building any formulas.

Profit is an accounting concept. It records revenue when a sale is made and costs when they are incurred, regardless of when money actually changes hands.

Cash flow is a liquidity concept. It records money when it physically moves — when a customer actually pays and when the business actually pays its suppliers.

The gap between them is created by three timing differences:

• Accounts Receivable — revenue is recognized when the sale is made, but cash arrives later when the customer pays. A 30-day payment term means January revenue arrives as cash in February;
• Accounts Payable — costs are incurred when goods are received or services delivered, but cash leaves later when the supplier invoice is paid. A 45-day payment term means January costs leave as cash in mid-February;
• Inventory and Prepayments — cash spent on inventory or prepaid expenses leaves before the corresponding revenue or cost is recognized in the profit forecast.

Your dataset records transactions at the point of sale, so it approximates an accrual profit view. The cash flow forecast adjusts these timings to show when money actually moves.

Building the Cash Flow Timing Model

The core mechanic of cash flow forecasting is applying collection and payment delays to your revenue and cost forecasts. These delays are controlled by two assumptions:

• Days Sales Outstanding (DSO) — the average number of days between a sale and cash collection. A DSO of 30 means revenue collected this month = revenue from 30 days ago;
• Days Payable Outstanding (DPO) — the average number of days between receiving goods/services and paying for them. A DPO of 45 means costs paid this month = costs incurred 45 days ago;

Add both to your Assumptions sheet as named, validated cells:

DSO:  named DSO_Days   Data Validation: integer 0 to 120
DPO:  named DPO_Days   Data Validation: integer 0 to 180

Convert days to months for monthly cash flow modeling:

Collection_Lag (months):  =ROUND(DSO_Days / 30, 0)
Payment_Lag (months):     =ROUND(DPO_Days / 30, 0)

Calculating Cash Inflows

Cash inflows in each forecast month are the revenue from prior months, adjusted for the collection lag:

Cash_Inflow (Month N):
=OFFSET([@Projected_Revenue], -Collection_Lag, 0)

For the first months of the forecast where the lag reaches back into historical data, reference the actual historical revenue from your Forecast_Input_Table:

Jan 2025 Cash Inflow (with 1-month lag):
=INDEX(Forecast_Input_Table[Total_Revenue],
       MATCH(EDATE([@Period_Date],-Collection_Lag),
             Forecast_Input_Table[Period_Date], 0))

This formula looks up the revenue from Collection_Lag months prior — whether that month is in the historical table or the forecast table — and returns it as the current month's cash inflow.

Add a Return Rate adjustment to cash inflows — returned products mean refunds, which reduce actual cash collected:

Net_Cash_Inflow:
=[@Cash_Inflow] * (1 - Return_Rate)

Calculating Cash Outflows

Cash outflows have two components in your model: variable cost payments (delayed by DPO) and marketing spend payments (assumed immediate — marketing is typically paid in the period incurred).

Variable cost payments:

Cost_Payment (Month N):
=OFFSET([@Variable_Cost_Forecast], -Payment_Lag, 0)

With the same historical lookup pattern for early forecast months:

=INDEX(Forecast_Input_Table[Total_Cost],
       MATCH(EDATE([@Period_Date],-Payment_Lag),
             Forecast_Input_Table[Period_Date], 0))

Marketing spend payments (no lag assumed):

Marketing_Payment:
=[@Marketing_Forecast]

Total Cash Outflow:

=[@Cost_Payment] + [@Marketing_Payment]

Building the Monthly Cash Flow Statement

Assemble all components into a structured monthly cash flow table. This is the standard format used in financial planning:

Row	Formula
Cash Inflows	=[@Net_Cash_Inflow]
Cost Payments	=-[@Cost_Payment]
Marketing Payments	=-[@Marketing_Payment]
Net Cash Flow	=[@Cash_Inflows]+[@Cost_Payments]+[@Marketing_Payments]
Opening Balance	=Prior_Month_Closing_Balance
Closing Balance	=[@Opening_Balance]+[@Net_Cash_Flow]

The Closing Balance row is the most important output — it shows the cumulative cash position at the end of each month. A negative closing balance is a funding gap: the business does not have enough cash to meet its obligations in that month.

Opening Balance (Jan 2025):
=Named cell Cash_Opening_Balance — add to Assumptions sheet
  Default: link to a reasonable starting cash position

Opening Balance (Feb 2025 onward):
=Prior_Month_Closing_Balance
  =OFFSET([@Closing_Balance], -1, 0)

Identifying and Quantifying Funding Gaps

Add a Funding_Gap column that flags months where the closing balance goes negative:

=IF([@Closing_Balance]<0, ABS([@Closing_Balance]), 0)

And a Gap_Flag indicator:

=IF([@Closing_Balance]<0, "FUNDING GAP", "")

Apply conditional formatting to the Closing_Balance column:

Green fill:  value >= Cash_Minimum_Buffer (a named assumption — minimum acceptable cash)
Yellow fill: value >= 0 and < Cash_Minimum_Buffer
Red fill:    value < 0

The Cash_Minimum_Buffer is a critical assumption — it represents the minimum cash reserve the business wants to maintain at all times. Add it to your Assumptions sheet:

Cash_Minimum_Buffer: named cell, default = 1 month of average operating costs
=AVERAGE(Forecast_Input_Table[Total_Cost]) + AVERAGE(Forecast_Input_Table[Marketing_Spend_Monthly])

Stress-Testing the Cash Flow Forecast

Cash flow is more sensitive to assumption changes than revenue or profit — small timing shifts compound quickly. Use Goal Seek and your scenario assumptions to stress-test the model before presenting it.

Stress test 1 — DSO extension:

What happens to cash flow if customers take 60 days to pay instead of 30?

Goal Seek: Set Cash Closing Balance (worst month) to >= 0
           By changing: DSO_Days

This tells you the maximum DSO the business can sustain before running out of cash.

Stress test 2 — Revenue shortfall:

Goal Seek: Set minimum Closing_Balance to 0
           By changing: Growth_Rate

This tells you how far revenue can fall before the business faces a cash crisis — the cash-based equivalent of the breakeven analysis from Lesson 3.4.

Stress test 3 — Scenario impact:

Switch Active_Scenario to Worst Case and observe which months develop funding gaps. The number of red months and the depth of the largest gap are the two numbers to communicate in any risk discussion with stakeholders.

Visualizing the Cash Flow Forecast

The standard cash flow visualization combines two chart types in one:

Waterfall chart — shows each month's net cash flow as a positive or negative bar, making inflow and outflow periods immediately visible. In Excel: Insert → Waterfall Chart.

Line chart overlay — shows the cumulative closing balance as a line running across the waterfall bars, so you can see both the monthly movements and the running position simultaneously.

To create the combination:

1. Build a waterfall chart from Net_Cash_Flow column;
2. Right-click → Change Chart Type → Combo;
3. Set Net_Cash_Flow series as Waterfall;
4. Add Closing_Balance as a Line series on the secondary axis;
5. Add a horizontal reference line at zero (Cash_Minimum_Buffer threshold).

Color the waterfall bars using conditional formatting logic — positive net cash flow months in blue, negative months in red. The visual pattern immediately shows whether cash generation is consistent or lumpy.

Task 1: Build the Cash Flow Timing Model

Goal: Add collection and payment lag assumptions and calculate monthly cash inflows and outflows from your revenue and cost forecasts.

1. In your Assumptions sheet, add four new named cells: DSO_Days (default 30), DPO_Days (default 45), Cash_Opening_Balance (enter a reasonable starting figure), and Cash_Minimum_Buffer (linked to the average cost formula from this lesson).
2. Add helper cells Collection_Lag and Payment_Lag converting days to months using the ROUND formula above.
3. In your monthly forecast table, add a Cash_Inflow column using the OFFSET formula for months where the lag stays within the forecast period, and the INDEX/MATCH historical lookup for early months where the lag reaches back into 2024 actuals.
4. Add Net_Cash_Inflow applying the Return_Rate adjustment.
5. Add Cost_Payment and Marketing_Payment columns using the same lag logic.
6. Verify the total cash inflows over the 12-month forecast period — they should approximately equal total revenue forecast shifted forward by Collection_Lag months, minus returns.

Task 2: Build the Cash Flow Statement and Stress Tests

Goal: Assemble the complete monthly cash flow statement, identify any funding gaps, and stress-test the model against key assumption changes.

1. Add Net_Cash_Flow, Opening_Balance, and Closing_Balance rows to your forecast table using the formulas from this lesson.
2. Apply three-color conditional formatting to the Closing_Balance column using Cash_Minimum_Buffer as the green/yellow threshold and zero as the yellow/red threshold.
3. Add Funding_Gap and Gap_Flag columns to flag months with negative closing balances.
4. Run Stress Test 1: use Goal Seek to find the maximum DSO_Days the model can sustain before any month's closing balance goes negative. Record the result in your KPI_Calculations sheet.
5. Run Stress Test 2: switch to Worst Case scenario and count how many months show a funding gap. Note the deepest gap amount and which month it occurs in.
6. Build the combination waterfall and line chart from this lesson showing Net_Cash_Flow bars and Closing_Balance line. Add a horizontal reference line at zero. Title it "Monthly Cash Flow Forecast — [Active_Scenario]" using a cell link to Active_Scenario.