Create structured inflow and outflow tables

Start with two Excel Tables rather than manually typing numbers into the forecast.

Example table: tblInflows

• ExpectedDate
• Customer
• Description
• Category
• Amount
• Confidence

Example table: tblOutflows

• ExpectedDate
• Supplier
• Description
• Category
• Amount
• Committed

Create a rolling 13-week calendar

Decide which day starts your reporting week. Monday is a common choice.

If cell B2 contains the first week commencing date, the next week can simply be:

=B2+7

Copy the formula across until you have 13 weekly periods.

Alternatively, if you are using a modern Excel version with dynamic arrays:

=SEQUENCE(1,13,$B$2,7)

Calculate weekly expected cash inflows

Suppose the week commencing date is in B$5.

Sum all expected receipts that fall inside the seven-day period:

=SUMIFS(
    tblInflows[Amount],
    tblInflows[ExpectedDate],">="&B$5,
    tblInflows[ExpectedDate],"<"&B$5+7
)

You can repeat the same pattern by category.

=SUMIFS(
    tblInflows[Amount],
    tblInflows[ExpectedDate],">="&B$5,
    tblInflows[ExpectedDate],"<"&B$5+7,
    tblInflows[Category],$A10
)

Calculate weekly cash outflows

Apply the same weekly logic to your payment table:

=SUMIFS(
    tblOutflows[Amount],
    tblOutflows[ExpectedDate],">="&B$5,
    tblOutflows[ExpectedDate],"<"&B$5+7
)

Typical outflow categories could include:

• Payroll
• Supplier payments
• VAT
• PAYE / NI
• Corporation tax
• Rent
• Loan repayments
• Insurance
• Capital expenditure

Use positive values in your input table and subtract total outflows in the forecast logic. That keeps source data easier to understand.

Roll the cash balance forward

The first forecast week starts from your opening bank position.

Closing Cash =
Opening Cash
+ Total Inflows
- Total Outflows

In the next week:

Next Opening Cash =
Previous Week Closing Cash

Example:

=B20+B14-B18

If:

• B20 = opening cash
• B14 = weekly inflows
• B18 = weekly outflows

Then the next week's opening balance can simply reference the previous closing balance.

Add minimum cash-buffer warnings

A positive bank balance does not necessarily mean the business is comfortable.

Management may want a minimum operational cash buffer — for example, enough to cover payroll, VAT and critical suppliers.

If the minimum buffer is stored in $B$3:

=IF(
    B25<$B$3,
    "Below Buffer",
    "OK"
)

Apply conditional formatting so that weeks below the threshold become visually obvious.

Add scenario assumptions

A forecast becomes much more useful when management can ask:

• What happens if customer collections are 10% lower?
• What happens if supplier payments move forward?
• What happens if a major receipt is delayed two weeks?
• How much cash headroom remains under a downside case?

Create assumption cells such as:

Inflows Adjustment = 90%
Outflows Adjustment = 105%

Then apply those assumptions:

=BaseInflows*InflowsAdjustment

=BaseOutflows*OutflowsAdjustment

Build the management dashboard

Keep the reporting page focused on decisions rather than showing the entire workbook.

Useful headline indicators include:

• Current cash position
• Lowest forecast cash position
• Week of lowest cash point
• 13-week inflows
• 13-week outflows
• Minimum cash buffer
• Weeks below buffer
• Largest upcoming payments

The most useful visual is often a simple line chart showing cash position against:

• Minimum operating buffer
• Zero cash line
• Optional target buffer