By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 7, 2026
A rolling cash flow forecast in Excel is a workbook that always looks 12 months ahead. Each time a month closes, you replace it with actual results and add a new month at the end. You don’t need special software for this. You need one clean layout, three formulas, an assumptions tab and a monthly habit. This guide shows you how to set all four up, with a worked example you can copy.
TL;DR
- A rolling forecast keeps a fixed horizon (12 months, or 13 weeks) by dropping the month that just ended and adding a new one.
- Your core sheet needs cash in, cash out and an ending balance per month. Three formulas run the whole thing.
- Put assumptions on their own tab so you change inputs, not formulas.
- Update monthly (weekly when cash is tight), compare actuals with the forecast, then revise the months ahead.
- Add best, base and worst cases and a minimum-cash flag so you see a shortfall while you can still fix it.
Table of Contents
- What a rolling cash flow forecast is
- What to put in the workbook
- The three formulas that run the model
- A worked example
- Set assumptions you can defend
- Your monthly update routine
- Test scenarios and find shortfalls early
- Choosing or adapting a rolling forecast template
- Our perspective
- Get a rolling forecast built for your business
- FAQ
- Sources
What a rolling cash flow forecast is
A rolling forecast is a planning model that keeps predicting performance by adding a new period each time one is completed. IBM gives a clear example: a 12-month rolling forecast made in January 2026 covers February 2026 through January 2027. When February ends, it updates to cover March 2026 through February 2027.

The mechanics are simple. As Corporate Finance Institute explains, the model drops a period as it passes and adds a new one, so the projection always rests on your most recent numbers.
For cash, this means you always see the next year of money coming in, money going out and the bank balance that results.
How it differs from an annual budget
A budget is a plan you commit to for the year. A rolling forecast is a view you keep correcting. Neither replaces the other. The budget sets targets and spending limits. The forecast answers operating questions: can we cover payroll in March, and should we push a vendor payment to April?
If you want the budget side built properly, our guide to an annual budget in Excel with a 12-month cash plan covers it. Use the budget to set direction and the rolling cash forecast to steer.
What to put in the workbook
A cash forecast tracks three things: cash received, cash paid out and the cash left at the end of each month. SCORE’s 12-month cash flow template adds one point that matters a great deal. Count cash received when you expect the payment to arrive, not when you make the sale.
On the payment side, list the items that actually drain your account: inventory and purchases, payroll, rent, utilities, taxes and loan payments.
A practical rolling workbook usually has four tabs:
- Assumptions: growth rates, collection timing, payment terms, payroll dates, rent, loan repayments and seasonal swings.
- Forecast: 12 monthly columns, with cash categories down the rows.
- Actuals and variance: what really happened, and how far off the forecast was.
- Weekly view (optional): a 13-week window for when cash is tight.
Enterprise Nation’s guidance on small business cash flow templates describes this same combination: a 12-month forecast, an assumptions tab, an actuals-and-variance tab, a 13-week cash view and warning flags for low projected cash.
The row structure
Wells Fargo’s cash flow projection guide suggests 12 columns for the next 12 months. Its rows run from operating cash at the beginning, through sources of cash and uses of cash, to the excess or deficit. Here is that idea as a layout you can build in ten minutes:
| Row | What goes in it |
|---|---|
| Opening cash | Last month’s closing balance |
| Cash from customers | Collections, timed to when you expect payment |
| Other cash in | Loan proceeds, owner funding, refunds |
| Total cash in | Sum of the two rows above |
| Payroll and contractors | Actual pay dates |
| Rent and utilities | Fixed monthly outflows |
| Inventory and supplies | Timed to when you pay, not when you order |
| Taxes and loan payments | Quarterly or scheduled amounts |
| Total cash out | Sum of the outflow rows |
| Net cash flow | Total in minus total out |
| Closing cash | Opening plus net cash flow |
The three formulas that run the model
DataHub Pro describes the engine of a 12-month Excel cash forecast as three formulas:
- Net movement: total inflows minus total outflows.
- Closing balance: opening balance plus net movement.
- Opening balance: the previous month’s closing balance.
That is all of it. Everything else is inputs and discipline.
In cell terms, if your inflow total is in row 6 and outflow total in row 14, net cash flow in column C is =C6-C14. Closing cash is =C3+C15 if opening cash sits in row 3. Opening cash for the next month, in D3, is =C16 (the closing cell of the month before).
Two checks worth adding
A minimum-cash line is the most useful check. Add one row with =MIN(C16:N16) to show the lowest balance in the whole period. Then add a row that compares each closing balance with a floor you choose, such as two weeks of operating expenses.
Use conditional formatting to turn any month below that floor red. A red cell in month 9 is a problem you can plan around. A bounced payment gives you no time at all.
A worked example
Here is an illustrative four-month slice for a small service company. The numbers are made up to show the mechanics.
| Jan | Feb | Mar | Apr | |
|---|---|---|---|---|
| Opening cash | $20,000 | $18,000 | $13,000 | $17,000 |
| Cash in | $30,000 | $28,000 | $35,000 | $38,000 |
| Cash out | $32,000 | $33,000 | $31,000 | $34,000 |
| Net cash flow | -$2,000 | -$5,000 | $4,000 | $4,000 |
| Closing cash | $18,000 | $13,000 | $17,000 | $21,000 |
The business looks fine for the quarter. But February closes at $13,000, which is the low point. If the owner’s floor is $15,000, that cell turns red and the question becomes concrete: collect two invoices earlier, or move a supplier payment from February to March?
Now imagine January closes and actual cash in was $27,000, not $30,000. You overwrite January with actuals. January’s real closing balance flows into February’s opening cell automatically. Every later month recalculates. You then add January of next year as a new column on the right.

Set assumptions you can defend
Timing assumptions cause most forecast errors. Wells Fargo names receivables and payables as the two key assumption areas. Its examples are 90% of sales collected the month after the sale and payables due within 14 days of purchase. It also advises against optimistic assumptions.
Put these on the assumptions tab, one input per cell:
- Percentage of sales collected in the month of sale, one month later and two months later
- Customer payment terms
- Payroll dates and amounts
- Rent, loan repayments and tax payment months
- Seasonal highs and lows
- Monthly sales growth
Because the forecast tab reads from this tab, changing a collection rate from 90% to 80% updates all 12 months at once. You never rewrite formulas, and anyone reviewing the file, whether owner, bookkeeper or accountant, can see your reasoning in one place.
If your payments come through processors, remember that money isn’t yours the day a customer pays. Sellers of digital products, for example, often see a gap between sale and payout. For more on how payment timing affects what you can forecast, see these cash flow management examples for small businesses.
Your monthly update routine
A rolling forecast only works if you keep rolling it. Enterprise Nation advises updating at least monthly, or weekly when cash is tight or weekly payment activity is significant. Each review compares actual results with the forecast and revises the periods that remain.
Here is a routine that fits in about an hour:
- Export last month’s bank activity and enter the actual cash in and out.
- Update what customers still owe you and what you still owe suppliers.
- Confirm the new opening balance matches your bank statement.
- Revisit assumptions: collection speed, sales growth, upcoming large payments.
- Add the new month at the end of the horizon so you still see 12.
- Read the lowest-balance check and any red months.
Wells Fargo adds a point many owners miss: review assumptions even when actuals beat projections, because a strong month can hide a shortfall ahead. It also suggests handing the updates to a bookkeeper or accountant, which frees you to look at the bigger picture.
Keep one live version of the file and archive older ones. Xero’s guide to 12-month rolling forecasts lists poor version control among the common mistakes, alongside treating forecasts as fixed and ignoring timing differences.
Test scenarios and find shortfalls early
One forecast is a guess. Three are a plan. Xero’s guide describes scenario planning as comparing most likely, best-case and worst-case outcomes to see how changes in income or costs affect cash. The variables it lists to adjust are:
- Sales volume or pricing
- Cost of goods sold
- Payroll and headcount
- Timing of major purchases
- Tax payments and financing terms
In Excel, the simplest approach is a scenario selector cell on the assumptions tab (1 for base, 2 for upside, 3 for downside). Use CHOOSE or INDEX so the forecast pulls the right assumption set. For a downside case, try your largest customer paying 30 days late, or sales 15% under plan. Then check the first month the closing balance falls below your floor.
That date matters most. Knowing the month cash gets tight lets you act: negotiate terms, delay a purchase, collect sooner or arrange financing before you need it.
When to switch to weeks
Twelve months is the right horizon for planning. For day-to-day cash decisions, a weekly window is sharper. If payroll, vendor payments or collections are making you nervous, build a weekly view alongside the monthly one. We cover that in detail in our 13-week cash flow forecast guide for Excel.
Choosing or adapting a rolling forecast template
Most people searching for a template want to skip the blank-sheet stage. That is sensible. Whichever template you use, check these points before you rely on it:
- Rolling structure: can you extend the horizon without rebuilding the sheet?
- Separate assumptions: are inputs on their own tab, not buried in formulas?
- Actual versus forecast: is there a place to record results and see the variance?
- Scenarios: can you flex revenue and costs together?
- Warning flags: does it highlight months under your minimum cash?
- Cash timing: does it separate when you sell from when you get paid?
- Fit to your business: a service firm, a retailer and an online store collect and pay very differently.
Also keep it simple. Overbuilt files are the ones that stop being updated. A model with 15 rows you refresh every month beats one with 150 rows you open twice a year.
Our Budgeting & Cash Flow templates and the wider cash flow forecast collection are built in Excel and Google Sheets for businesses that prefer to stay in spreadsheets.
Our perspective
Most small businesses don’t need new software to get cash clarity. They need the spreadsheet they already use to be set up so it answers real questions.
That is why we only work in Excel and Google Sheets. When the forecast lives in a tool you understand, you keep control of your numbers. You can see every assumption, change it yourself and explain it to a partner, a lender or an investor.
We see the same pattern often: a rolling forecast fails not because the formulas are hard, but because nobody owns the monthly update. So we design workbooks around a short routine, clear inputs and a handful of flags that tell you where to look.
Our approach is to start with the cash question you need answered, build the smallest model that answers it, then add scenarios, dashboards and KPIs as your business grows.
frac
Get a rolling forecast built for your business
If you’d rather start with a working model than a blank sheet, we can help in three ways. Our Excel templates are ready to download. Our one-time finance projects include a 13-week cash flow forecast, a budget build and a KPI dashboard, all tailored to your numbers. And our fractional CFO plans, from Starter at $600 per month, give you ongoing support to keep the forecast current.
New to Frac CFO? The Launch Kit at $650 one-off is a straightforward way to get a working finance setup in place. Our designs include hands-on onboarding and email support.
FAQ
What is the difference between a rolling forecast and a 12-month cash flow spreadsheet?
A 12-month spreadsheet covers a fixed span, such as one calendar year, and goes out of date as the year passes. A rolling forecast uses the same layout but always shows the next 12 months. You replace the completed month with actuals and add a new month at the end.
How often should I update a rolling cash flow forecast?
Update it at least monthly. If cash is tight or you have many weekly payments, update weekly, as Enterprise Nation advises. Each update compares actuals with the forecast and revises the months ahead.
Do I need accounting software to run one?
No. Excel or Google Sheets handles it well. You need a month-by-month layout, three formulas, an assumptions tab and actual bank figures to update it. Accounting software can speed up data entry, but it isn’t required for the forecast itself.
Should I use months or weeks?
Use months for the 12-month planning view and weeks for tight-cash periods. Many owners keep both: a monthly rolling forecast for direction and a 13-week view for near-term decisions on payroll and vendor timing.
What should I do if the forecast shows a shortfall?
Find the first month the balance falls below your minimum, then look at what you control: collection timing, supplier terms, purchase timing and financing options. The earlier you see it, the more options you have. Share the forecast with your bookkeeper or accountant to pressure-test the assumptions.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources
- IBM: What is a Rolling Forecast?
- Corporate Finance Institute: Rolling Forecast
- SCORE: 12 Month Cash Flow Statement
- Enterprise Nation: Cash flow forecast template for small businesses
- Wells Fargo: How To Create a Cash Flow Projection
- DataHub Pro: Cash Flow Forecast in Excel
- Xero: Cash Flow Forecasting, 12-Month Rolling Forecasts
- InvoiceLabs: Cash Flow Management Examples for Small Business Owners


3 Responses