Build a 12-Month Cash Flow Projection

A useful cash flow projection distinguishes expected cash from cleared cash. It also shows timing risk, not just a year-end total.

The workbook doesn’t import bank transactions or reconcile accounts automatically. Instead, it provides a manually maintained cash forecast with a visible forecast-to-actual cutoff.

Before changing any assumptions or records, confirm that your copy contains:

• Cash Flow!B4: $14,000
• Cash Flow!B5: $12,000
• Cash Flow!B6: 3
• Cash Flow!G24: $12,000

The blended monthly net cash amounts should be:

January through March contribute $5,000 of actual net cash. The remaining months, April through December, contribute $39,500 of forecast net cash:

December ending cash
= $14,000 opening cash
+ $5,000 actual net cash
+ $39,500 forecast net cash
= $58,500

Recorded Google Sheets execution produced these controls:

• E4: $58,500 ending cash
• E5: $10,800 lowest month-end cash
• E6: 1 month below the buffer

If your untouched copy differs, check B4:B6, G24, and the formulas in E4:E6 and I12:T20 before entering your data.

The supplied evidence contains calculated values rather than the underlying formula text. It verifies these recorded outputs, but it doesn’t provide an independent static audit of the summary ranges, aggregation criteria, direction matching, or blank-handling rules.

Replace the illustrative values in Cash Flow!B4:B8.

With B6 = 3, row 17 uses actual net cash for January through March, followed by forecast net cash for April through December. Enter 0 when no month has reconciled actual data. In the recorded Google Sheets scenario, forecasts were then used for all 12 months.

The review date in B8 records when the forecast was reviewed. It doesn’t determine which months use actuals.

Don’t overwrite the formulas in E4:E6 or I12:T20.

This summary combines cleared cash through the selected actual month with forecast cash afterward. It also flags month-end balances below the operating buffer.

Replace the synthetic records in Cash Flow!A24:J72. Then enter subsequent records through row 100, keeping one expected receipt or payment per row.

Enter amounts as positive numbers. Column B determines whether each amount is an inflow or outflow.

Leave columns F and G blank until the cash clears. Don’t enter a zero or placeholder for an uncleared item.

Keep the headers in A23:J23. Because the existing summary covers records through row 100, entries within A24:J100 require no formula copying.

Also exclude transfers between bank accounts owned by the same business. Recording both sides would increase reported inflows and outflows without changing available business cash.

The record log distinguishes expected cash from cleared cash. Partial payments, billed invoices, and unplanned payments are represented separately.

Enter an actual month in column F and an actual amount in column G only after cash enters or leaves the account. A billed invoice remains a forecast receipt until payment clears.

For an illustrative $18,000 January receipt when only $12,000 clears:

1. Enter the actual month in column F and $12,000 in column G.
2. Change column E on that original row to $12,000.
3. Keep its January forecast month in column D.
4. Record the original $18,000 estimate and the $6,000 remainder in column J.
5. Add one future inflow row with the revised expected clearing month in column D and $6,000 in column E.

Together, the 2 forecast records total $18,000: $12,000 on the original row plus $6,000 on the future row. If you leave the original forecast at $18,000 and add a $6,000 remainder, forecast inflows will be overstated by $6,000.

For a completely unpaid invoice or bill, leave columns F and G blank. If its forecast month is at or before the reconciled cutoff in B6, use one of these methods:

• Move the existing record’s forecast month in column D to the revised future clearing month.
• Create a replacement future record, but remove the active amount from the old record so the outstanding cash appears only once.

Because row 17 uses actual net cash for reconciled months, an uncleared item left only in an old forecast month can disappear from the forward blended projection.

Use columns H through J to document partial, overdue, rescheduled, or unplanned items. These are manual fields, and the supplied evidence doesn’t establish that status text controls summary inclusion.

The summary runs from January in column I through December in column T.

In the illustrative January baseline:

Actual net cash = $12,000 actual inflows - $15,200 actual outflows
                = -$3,200

Ending cash = $14,000 opening cash - $3,200
            = $10,800

Buffer headroom = $10,800 ending cash - $12,000 buffer
                = -$1,200

The corresponding results are:

• I17: -$3,200
• I18: $10,800
• I19: -$1,200
• I20: Below buffer

A negative value in row 19 measures the shortfall against the operating buffer. It doesn’t necessarily mean the bank balance is negative.

At each review:

1. Update the manual review date in B8.
2. Enter newly cleared months and amounts in columns F and G.
3. Review partial and overdue items in columns H through J.
4. Move completely unpaid items from reconciled months to revised future clearing months, or replace them without retaining a duplicate active forecast.
5. Split partial receipts or payments so the cleared and outstanding portions don’t duplicate the active forecast.
6. Revise future forecast amounts in column E.
7. Advance B6 only after the next month is fully reconciled.
8. Review ending cash, buffer headroom, and status in rows 18 through 20.
9. Investigate each projected balance below the buffer.

When starting a new projection year, enter the prior year’s final cleared cash in B4, update B7 and B8, and reset B6 to 0. Replace the records in A24:J100, but leave the summary formulas unchanged.

For a closer analysis of shortfall timing, see how to forecast cash runway in Excel or Google Sheets. For planning beyond 12 months, use the 3-year cash flow projection example.

ModelMonkey can inspect spreadsheet context and help analyze, organize, or change content in Google Sheets and Excel. For this projection, give it a specific task such as:

• “Check Cash Flow!A24:J100 for blank forecast months or amounts.”
• “List unpaid records whose forecast month is at or before the cutoff in B6.”
• “Explain the January buffer shortfall using B4, B5, and I15:I20.”
• “Summarize the months with negative buffer headroom.”
• “Apply these approved forecast-month changes to the specified record IDs.”

When the workflow presents a preview, review the proposed changes before proceeding. Finance should still verify cash classifications, forecast timing, formulas, and control totals.