Cash flow risk simulation.
An Excel model showing how likely a small business is to run short of cash, and in which months. It also tests the effect of delayed payments or a new hire.
Fictional consultancy, synthetic GBP data, Microsoft 365 for Windows
The cash flow dashboard
The dashboard combines a percentile band with monthly shortfall probabilities and a distribution of year-end cash. An editable minimum buffer provides a second reference point above zero.
The P50 line is the median simulated balance. P10 and P90 mark the lower and upper ends of the central 80% of simulated outcomes for each month. They describe this model’s assumptions, rather than a guaranteed range.
All figures shown are illustrative. Random draws change on recalculation, so screenshots can show different results from a later workbook run. Select any screenshot to inspect it at full size.
Preparing the transaction data
The synthetic transactions cover a fictional consultancy, including invoice receipts with payment delays, payroll, rent, software, quarterly VAT payments and other operating costs. A lost client and a one-off equipment bill add realistic disruptions.
The CSV contains three date formats, inconsistent categories, duplicate transaction IDs and blank amounts. Power Query parses the dates, standardises category keys and joins an editable category map.
Duplicates are removed by transaction ID. Incomplete records remain visible with a review status; only valid records feed the monthly cash totals.
Forecasting and simulation
The baseline forecasts monthly net cash flow. Historical forecast errors then introduce variation around that baseline, before the cash flows are accumulated into balance paths.
Build the monthly history
Excel SUMIFS aggregates valid, cleaned transactions into a complete monthly calendar. Monthly cash receipts less payments provide the forecasting series.
Estimate the baseline
FORECAST.ETS projects twelve months of net cash flow using a twelve-month seasonal period. FORECAST.ETS.CONFINT supplies a separate 95% confidence half-width for each monthly cash flow forecast.
Backtest without looking ahead
Each of the final twelve historical months is predicted using only earlier observations, with at least two full seasonal cycles available. Actual cash flow less its prediction gives twelve backtest errors.
Draw 5,000 balance paths
RANDARRAY selects an error with replacement for each of twelve months in each simulation: 60,000 draws. The sampled errors are added to the adjusted forecast, and MMULT accumulates the cash flows from the opening balance.
Summarise the exposure
The model calculates monthly percentiles, the chance of closing below zero or the buffer, and the share of paths with at least one shortfall over the forecast year.
5,000 Monte Carlo simulations using resampled historical forecast errors. Monthly closing balances in GBP; intramonth shortfalls are outside this model.
The ETS confidence half-width concerns monthly net cash flow. The dashboard’s percentile band concerns accumulated simulated cash balances; these are different measures.
Testing payment and hiring assumptions
The input panel sets opening cash and the minimum buffer. Two independent toggles explore a client receipt moving two monthly buckets later and an additional monthly payroll cost from a chosen start month.
The example starts with £45,000 cash and a £15,000 buffer. These are editable assumptions, not a cash balance reconstructed from the historical transactions.
Calculation is manual. Refresh All reloads the source; F9 recalculates all sheets and generates a new sample of simulated paths.
Validation results
The workbook was tested in Excel desktop.
| Check | Result |
|---|---|
| Original synthetic CSV | 926 rows; 18 duplicate IDs removed |
| Missing amounts | 8 records flagged and excluded from cash totals |
| Payment delay | £12,000 removed from its original month and received two months later |
| New hire | £4,500 monthly cash cost from the selected start month |
| Zero-error test | Balances matched opening cash plus cumulative forecast cash flows |
| Year-end distribution and formula scan | 5,000 observations; no Excel formula errors found |
Model assumptions and limitations
- A small error sample. Twelve backtest errors provide limited information about extreme outcomes. The model cannot simulate types of shock absent from that sample.
- Independent monthly draws. The simulation does not reproduce persistent bad periods, changing volatility or forecast errors that grow with the forecast horizon. Errors are not centred, so observed backtest bias remains.
- Month-end cash only. A positive closing balance can hide a shortage within the month. This is a monthly planning model, not a daily payment schedule.
- A fixed demonstration window. Refreshing the CSV updates records in the configured period. Extending the history also requires extending the monthly table and backtest.
“Expected lowest balance” averages the minimum balance of each path, including opening cash. Runway counts full months before the median first turns negative, capped at twelve. Neither is a guarantee that individual paths stay above zero.
Workbook contents
The workbook includes the cleaned transactions, category map, monthly history, rolling backtest, simulation, dashboard and method notes. Named inputs and structured tables make the main drivers easier to trace.
This is a portfolio demonstration using synthetic data, so no client information is involved. A business version would need its own data checks, forecasting assumptions and cash timing review.
Discuss a similar project →