Monthly Cash-Flow Model: When to Use One, and How?
by Prateek Gupta, on Jun 14, 2018
The Long View: Building a Monthly Cash-Flow Model
A monthly cash-flow model looks a lot like a weekly one, with the obvious exception that the spreadsheet columns represent whole months. Companies typically use a revolving model that projects 12 or 18 months ahead, giving executives the tools to forecast and make key decisions on expanding the business, making capital purchases, adjusting the marketing strategy, and seeking loans or investors.
With the months entered across the top of the spreadsheet, the line items down the left-hand side are pretty much the same as in a weekly model: the beginning cash balance, each category of potential cash inflows, and a “total” line of cash sources for the month; then a line for each potential cash outflow, a total of outflows, and the all-important bottom line, the net of cash in and out. The accompanying template has everything you need to get started; in the Introduction worksheet, you can change cell D2 to your own company name, and cell D5 to the start date you plan to use.
Naturally, you want to keep the Net Cash Flow line positive, and the monthly model will help you see what’s in the financial pipeline so you can make sound business decisions and course corrections. Some owners and executives like to use their most optimistic sales projections in the model, especially if they are trying to woo investment firms; but that can be problematic when the actuals fall short of projections and the whole model is thrown off. Assumptions should be as realistic as possible so the model is as accurate a forecasting tool as possible. That goes not only for the projected sales totals, but also when you collect them (which is what goes into the model). Knowing the average rate of receivables collection is essential to the accuracy of your cash-flow model.
Just as in the weekly model, the monthly model is not a P&L, and you input the forecasted cash receipts and payments—not the accrued income or expenses. For example, loan proceeds and monthly principal repayments, which do not hit your P&L (only the interest expense does), need to be recorded in the cash-flow model. On the other hand, when you pay a supplier with a credit card, that expense hits the P&L but not the cash-flow model. Then when you make a payment on the credit card, that goes into the cash-flow model but not the P&L.
A note on inputting payroll: in the monthly model, you need to count the number of pay dates that fall within the month, as it will vary (4 or 5 if you pay weekly, 2 or 3 if you pay biweekly).
It Pays to Plan Ahead
An entertainment company we worked with wanted to expand into new cities, but needed to find economies of scale to justify the cash outflows required for growth. Surveying the markets, they saw opportunities for mergers with existing companies, which would reduce the margin of their budget they needed to spend on marketing. Plugging new assumptions (income up, marketing expense down) into the monthly cash-flow model, they saw how quickly the cash saved on their marketing spend added up, leaving more cash for continued growth.
Building and using a sound monthly cash-flow model is all about strategy: How are your investments going to grow? When can you afford to invest in new equipment (very important for manufacturing and trucking industries that have high fixed-asset costs)? Is your pricing strategy covering expenses and keeping enough cash coming in over the long haul? If the bottom line on your model keeps shrinking from month to month, maybe the pricing strategy is off.
Other ways you can use the monthly cash-flow model as an analytical tool include: evaluating the effectiveness of your marketing spend by watching how it bumps up sales, and when you actually get the cash in the door; watching your key performance indicators (KPIs) over time to see which ones are showing strong results and which ones you need to rethink; and using insights gleaned from the model to make adjustments to show robust performance over 12 months, in anticipation of presenting your financials to bankers or venture capitalists.
The Future Looks Bright (And If It Doesn't, You Have Time to Brighten It)
If your monthly cash-flow model is forecasting good times ahead, then you know when you’ll have money in the bank for new equipment, new employees, and new investments in growing the business—not to mention a good story to tell potential lenders or investors. And if your model shows rainclouds down the road, don’t panic—the beauty of a good model is it gives you time to buy an umbrella and stay dry while you make the necessary adjustments to get the sun shining again.