How to Build a 13-Week Cash Flow Forecast from a Monthly Model
Weekly cash flow forecasts usually aren’t necessary unless a company is dealing with liquidity issues. Distress or rapid growth can both be a culprit. Here’s how to use a monthly cash flow to back into an optional weekly forecast. This 13-week cash flow forecast is not built from the bottom up. It’s built from the top down. In other words, I build the company forecast on a monthly basis and then back into the 5-4-4 weekly allocation. How do I do this?
Step 1: Forecast the P&L monthly
Beginning with sales, we forecast out key customer revenue. Then we forecast out non-key customer revenue. The reason for the segmentation is to maintain strong accuracy on the forecasts that are most material.
Then, based upon cost analysis, we can forecast direct costs using a combination of assumptions related to variable and fixed behaviors.
Finally, given the expectations of sales growth and gross margins, we can then forecast operating expenses to support the growth or turnaround.
Step 2: Develop balance sheet assumptions
In this example, I have collections of accounts receivable and payments of payables, which are all going to be driven by the P&L forecast, collection and payment terms. This helps determine the timing of cash and working capital on a monthly basis.
Similar analysis should be done with capex investments, projects, hiring, professional fees, and financing.
Step 3: Determine the weekly versus monthly timing
It’s one thing to get the timing right month-over-month. But it’s another to get the timing right within each week.
While most companies don’t need this degree of precision, if there’s a challenge around cash flow, managing weekly can bring advantages.
Step 4: Create formulas that help with automation
In this walkthrough, which is the first of two, I illustrate how I use header mapping to get the timing right. It’s a bad use of time to spend hours each week redoing the forecast and timing everything out.
Once we know the timing, we can use triggers and weightings in the data mapping to back into the weekly cash flow forecast from the monthly.
