To make a realistic cash flow forecast, you must estimate how much unknown revenue and cost will happen in the next 13 weeks - even new business unknown as of yet! Nobody can know for sure, but nobody has a better foundation for making this estimate than you. Consider trends, seasonal patterns, probability percentages for new work, and timing for each cash event.
You already know how a 13 week cash flow statement is developed (see 13 Week Cash Flow Statement), and you have forecasted known revenue and costs (see 13 Week Cash Flow, Part 2 – Forecasting the Knowns).
The most obvious technique is to assume trends for the last few weeks will continue for the rest of the 13 week period. You may believe the trends will change, for reasons such as a new sales approach, or a new marketing campaign, or lower prices from a new process that reduces cost. But the bank will doubt your belief and will assume that past trends continue.
Since you want the bank to believe your forecast, resist the urge to assume improvement. The only reason to assume a change in the trends of the last few weeks is a seasonal pattern demonstrated in your results from the same weeks for the last two years. If you do not have that data, do not put it in your base forecast.
However, you have the option of making more than one forecast. You can do a most likely case, best case, and worst case. The most likely case is based on current trends continuing. The best case is where you can show the results of more optimistic assumptions from better marketing or undocumented seasonal trends that you expect. The worst case would assume a lower “probability percentage” in capturing new business, or higher costs. But the bank is in the risk management business, so you should not expect it to base a loan decision on your best case. In fact, it may decide that your worst case is actually the most likely one!
Now that you have decided what trend basis to use, how do you actually forecast? The problem is the variety of products or jobs that you offer. So make your forecast by product type. Define the types of products you may sell in the next few weeks. Using a separate worksheet, estimate the revenue and the variable costs (material, labor, subcontractors, shipping/packaging, and commissions) for one sale of each type, and the timing of each cash event—when you pay and when you are paid. Then create formulas for each product type to show the cash effect each week. For example, if the sale is in week six, and the work completes in week eight, the variable costs may be paid for in week eleven, and the cash for the work may be received in week thirteen. Finally, estimate how many (unit) sales you expect for each type, in each week.
On the master spreadsheet, enter the estimated unit sales per product type per week, and enter your formulas for cash in and out for each week for that product type.
In the next post we will address job shop complexity, and how to double-check your work and draw conclusions. Up to this point you have learned how a 13 week cash flow statement is built, how to forecast the knowns, and how to estimate future revenue and costs for jobs as yet unknown.