Will cash last through the next 12 months?
Count the simulated cash paths with a negative balance at any month end, not just at the end of the year. In this illustrative 12-month model, 34.1% of 10,000 paths have a shortfall, while 27.3% end the year below zero. A business can run short before its revenue recovers.
Monthly revenue persists: a weak month makes another weak month more likely. The model turns those revenue paths into cash balances, then compares two choices on the same paths: more starting cash and lower monthly fixed costs. Amounts are in USD thousands.
The model
The Assumptions sheet holds starting cash of 50, monthly fixed costs of 55, variable costs of 45% of revenue and typical monthly revenue of 100. The Cash sheet has one column per month, from B to M. Revenue is collected and costs are paid in the same month; there are no receivables, taxes, debt payments or financing charges.
Row 7 calculates net cash as revenue minus variable and fixed costs. Row 8 carries the balance forward: B8=Assumptions!$B$3+B7, then C8=B8+C7 through month 12. On Summary, B2=MIN(Cash!B8:M8) is the lowest month-end balance and B3=Cash!M8 is the final balance. The two targets ask whether each is below zero.
Why revenue needs a path
The simulation fills Cash!B3:M3 with an AR(1) process for log revenue. Each month’s log revenue is its long-run center plus 0.75 times the previous month’s deviation from that center, plus a normal shock with standard deviation 0.18. Row 4 applies EXP, so revenue stays positive. The process starts at revenue of 100 before month 1.
Positive persistence carries a weak spell forward instead of redrawing each month independently. The typical revenue of 100 is the log-space center expressed in cash terms, not the expected revenue: the upper tail lifts the mean. These process parameters are illustrative assumptions, not a fit to a company’s history.
Read the cash-balance fan chart
The line is the median balance in each month; the bands show P10–P90 and P5–P95. At month 12, the median is 63, with P10 −64 and P90 222. These are pointwise ranges: a 90% band at each month does not mean that 90% of complete paths stay inside it all year. The median line need not be any single simulated path.
A positive year-end balance can hide a shortfall
6.9% of all paths finish at or above zero after an earlier negative month end. Looking only at the final balance misses those paths. Use the minimum-balance target for cash sufficiency across the modeled months; use the final-balance target for the year-end position.
A negative balance represents a funding gap. The workbook keeps calculating afterward, as though a bridge could cover it, but does not model whether funding is available or what it costs. It also cannot detect a shortfall between month ends. A weekly or daily model is needed when payment timing within a month matters.
More cash or lower fixed costs?
| Assumptions | Any negative month end | Negative at month 12 | Median month-12 cash |
|---|---|---|---|
| Base assumptions | 34.1% | 27.3% | 63 |
| More starting cash | 14.5% | 13.1% | 113 |
| Lower fixed costs | 14.3% | 10.8% | 123 |
Raising starting cash from 50 to 100 adds 50 to every month’s balance. Cutting monthly fixed costs from 55 to 50 adds 5 in month 1, then accumulates to 60 by month 12. Both lower the shortfall probability here, but their timing differs. The comparison assumes the cost reduction leaves revenue unchanged and has no implementation cost.
Each scenario uses the same simulated revenue paths. Differences therefore come from the decision, rather than a luckier set of draws. This is a comparison of two specified changes, not a search for the minimum cash reserve or an optimal cost policy.
Try it yourself
- Open the example and run its 10,000 trials with seed 1. The monthly balance range and both shortfall targets are already set.
- Open the monthly balance results to inspect the fan chart, then compare the minimum and final balance outputs.
- Run the two saved scenarios. Compare their shortfall probabilities and how their balance paths move over time.
- For your own workbook, include collection delays, payment dates and financing rules before treating the output as a funding plan.
Questions
Is runway just starting cash divided by monthly burn?
That ratio works for a constant positive burn rate. When revenue changes, burn can change sign and weak months can cluster. Simulating the balance path answers whether cash lasts through the chosen horizon under those assumptions.
Does a 12-month model show when cash will run out after that?
No. A path without a shortfall here only lasts through the modeled month ends. Extend the horizon and its assumptions to study later cash needs; do not read a 12-month survival result as unlimited runway.
Does a low shortfall probability guarantee funding is sufficient?
No. It is conditional on the revenue process, costs, timing and horizon you put into the workbook. Test adverse assumptions and finer payment timing as well as running enough trials for the reported probabilities to settle.
Related
- Business case
What is the chance of a negative NPV?
A business case with a base-case NPV of USD 562k has a negative NPV in 29.6% of simulated outcomes. How to simulate NPV and IRR, with the workbook. - Guide
P50, P80 and P90: what they mean and which to budget at
P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands). - Guide
Monte Carlo simulation in Excel, with or without add-ins
Three ways to run a Monte Carlo simulation on an Excel model: RAND() and a data table, an add-in, or a browser tool with no add-in. PERT formula included.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.