Skip to main content

Frac CFO

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.

Fraccfo
See Cash Flow More Clearly
Frac CFO helps businesses using spreadsheets build customized financial models and forecasts for clearer cash flow and more informed decisions.

Explore spreadsheet finance support

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

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.

How the 12-month forecast shifts forward

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:

  1. Assumptions: growth rates, collection timing, payment terms, payroll dates, rent, loan repayments and seasonal swings.
  2. Forecast: 12 monthly columns, with cash categories down the rows.
  3. Actuals and variance: what really happened, and how far off the forecast was.
  4. 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.

A worked example

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:

  1. Export last month’s bank activity and enter the actual cash in and out.
  2. Update what customers still owe you and what you still owe suppliers.
  3. Confirm the new opening balance matches your bank statement.
  4. Revisit assumptions: collection speed, sales growth, upcoming large payments.
  5. Add the new month at the end of the horizon so you still see 12.
  6. 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.

13-Week Cash Flow Forecast

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

3 Responses

Leave a Reply