An annual budget goes stale the moment reality diverges from it, and by month nine, most budgets are more historical document than planning tool. A rolling forecast solves this by always looking exactly 12 months ahead. When January closes, next January gets added to the end, and the forecast never shrinks toward a fixed year-end. This guide walks through building one from scratch, with a focus on the part most templates get wrong: realistic cash timing.
On this page
Rolling Forecast vs. a Static Annual Budget
A static budget is set once a year and measured against actuals until the next budget cycle. A rolling forecast is updated every month and always extends 12 months from the current point, which keeps management looking forward instead of explaining variances against assumptions made a year ago.
Step 1: Set Up the Rolling Structure
Build the frame before filling in numbers
- Create 12 month columns, labeled with actual calendar months rather than "Month 1, Month 2," so the model stays readable as it rolls forward.
- Reserve one row near the top for the beginning cash balance, and one row near the bottom for the ending cash balance.
- Group your forecast into three clear sections: cash inflows, cash outflows, and net cash flow, in that order down the sheet.
Beginning Cash (Month 2) = Ending Cash (Month 1) Beginning Cash (Month 3) = Ending Cash (Month 2) ' ...and so on for every subsequent month
Step 2: Forecast Cash Inflows
Model when customers actually pay, not when you invoice them
The single biggest error in cash forecasting is assuming a sale converts to cash the same month it happens. Most businesses collect a portion in the month of sale, another portion the following month, and a smaller portion later still.
| Timing | Percent collected |
|---|---|
| Month of sale | 20% |
| One month after sale | 55% |
| Two months after sale | 20% |
| Uncollectible or written off | 5% |
Cash Collected (Month N) = Sales(Month N) * 20% + Sales(Month N-1) * 55% + Sales(Month N-2) * 20%
Pull the actual collection percentages from your historical accounts receivable aging report rather than guessing. A business with a 45-day average collection period will look very different from one collecting mostly upfront.
Include every other cash inflow the same way: loan proceeds, asset sales, tax refunds, and any other one-time or recurring cash receipts, each on its own row with its own timing assumption.
Step 3: Forecast Cash Outflows
Match payment timing, not expense timing
Just as sales do not equal cash collected, expenses do not equal cash paid. Build separate rows for each major outflow category, using the timing that actually applies to each one.
| Outflow category | Typical timing basis |
|---|---|
| Payroll and payroll taxes | Paid on fixed pay dates, usually semi-monthly or biweekly, largely independent of revenue timing |
| Vendor payments (accounts payable) | Based on days payable outstanding, often 30 to 60 days after the purchase |
| Rent and fixed overhead | Paid on a fixed monthly schedule |
| Debt service (principal and interest) | Fixed by the loan amortization schedule |
| Capital expenditure | Often paid in large, lumpy amounts tied to specific projects, not spread evenly |
| Income taxes | Paid on quarterly estimated due dates, not evenly across the year |
Vendor Payments (Month N) = Purchases(Month N-1) * (DPO_Days / 30) ' Simplified example assuming roughly one month of payment lag
Step 4: Calculate Net Cash Flow and Ending Cash
Bring both sides together
Net Cash Flow (Month N) = Total Cash Inflows (Month N) - Total Cash Outflows (Month N) Ending Cash (Month N) = Beginning Cash (Month N) + Net Cash Flow (Month N) Beginning Cash (Month N+1) = Ending Cash (Month N)
This final link, ending cash of one month feeding directly into beginning cash of the next, is what turns 12 separate monthly estimates into one continuous forecast.
Step 5: Build the Roll-Forward Mechanism
Make the model extend itself every month
- When an actual month closes, replace that month's forecasted figures with the real actuals from your accounting system.
- Add one new forecast month to the end of the sheet, keeping the total horizon at 12 months.
- Update the beginning cash balance of the newly actualized month to match the true bank balance, then let every subsequent month recalculate from there.
- Refresh your collection and payment timing assumptions periodically using the most recent 3 to 6 months of actual data, since customer and vendor behavior can drift over time.
Build the sheet with 18 to 24 month columns from the start, most hidden, so that "adding a new month" is really just unhiding the next column rather than rebuilding formulas each time.
Step 6: Add a Minimum Cash Check
Catch a cash shortfall months before it happens
Many loan agreements require maintaining cash above a specific minimum balance at all times. Even without a formal covenant, every business should know its own minimum comfortable cash level.
Covenant Check (Month N) = Ending Cash (Month N) - Minimum_Cash_Requirement ' A negative result means the forecast breaches the minimum in that month
Apply conditional formatting to turn this row red whenever the result goes negative. Seeing a shortfall flagged five months in advance gives you time to arrange financing, delay a purchase, or accelerate collections, rather than discovering the problem the week it happens.
Using accrual timing instead of cash timing. Revenue and expenses on the income statement are recognized when earned or incurred, not when cash actually moves. A cash forecast built on accrual timing will be wrong even if every other assumption is correct.
Assuming a flat collection or payment percentage without checking history. Pull actual aging data before assuming customers pay in 30 days or vendors are paid in 45. Guessing here undermines the entire forecast.
Ignoring seasonality. A retailer with a large Q4 naturally has very different monthly cash patterns than a subscription business with even revenue. Apply seasonal patterns to both inflows and outflows, not just top-line sales.
Treating capital expenditure and debt payments as smooth, even amounts. These are often large, lumpy, and tied to specific dates. Averaging them across all 12 months hides exactly the kind of large cash swing this forecast exists to catch.
Forgetting to refresh assumptions. A forecast built once and never revisited slowly drifts away from how the business actually behaves. Revisit collection and payment timing assumptions at least quarterly.
Key Takeaways
- A rolling forecast always looks 12 months ahead; a static budget shrinks toward a fixed year-end as the year progresses.
- Cash timing, not accrual timing, is what belongs in this model. Sales and expenses must be translated into when cash actually moves.
- Beginning cash in one month must equal ending cash in the prior month; this single link is what makes the forecast continuous.
- Build extra hidden month columns up front so extending the forecast each month is simple rather than a rebuild.
- A minimum cash check with conditional formatting turns a spreadsheet into an early warning system, not just a record-keeping exercise.
Frequently Asked Questions
What is a rolling forecast, and how is it different from an annual budget?
An annual budget is fixed at the start of the year and covers a set 12 months, such as January through December. A rolling forecast always looks 12 months ahead, so every time a month closes, a new month is added to the end, keeping the horizon constant.
Should a cash flow forecast use accrual or cash timing?
Cash timing. A cash flow forecast should reflect when money actually moves, such as when a customer pays an invoice, not when revenue or expenses are recognized under accrual accounting.
How often should a rolling forecast be updated?
Monthly is standard for most companies. Some fast-changing businesses, particularly those with tight cash positions, update weekly for at least the next four to six weeks.
What is a minimum cash covenant?
Many loan agreements require the borrower to maintain cash above a specified minimum balance at all times. A rolling forecast with a covenant check flags any forecasted month where the balance would fall below that threshold, giving management advance warning.
How do I forecast customer collections accurately?
Look at historical data on what percentage of invoices are typically collected in the month of sale, the month after, and later, then apply those percentages to forecasted sales rather than assuming all sales convert to cash immediately.
Can a rolling forecast be built without Excel, using accounting software instead?
Some ERP and accounting platforms include built-in cash forecasting modules, but many finance teams still build or refine the model in Excel because it offers more flexibility for company-specific timing assumptions.
Related Articles
External References
- Corporate Finance Institute, Cash Flow Forecasting: corporatefinanceinstitute.com
- Microsoft Support, Create a forecast in Excel: support.microsoft.com
0 Comments