In Part 1 we looked at a secret weapon for business owners to find some certainty in the current world of business chaos triggered by the Coronavirus pandemic. The secret weapon is knowing your numbers around cash flow. Clearly understanding and visualizing the detailed, specific and current status of the lifeblood of your business, cashflow – it’s existing supply, new production capacity, and usage rate.
We laid out the steps necessary to get clear on where your business stands today on cash position and the cash drivers. Part 1 was all about gaining insight.
Hopefully you spent some time with your bookkeeper or with your head buried inside your QuickBooks data file.
Did you do the work?
Great! Now it’s time to move into foresight on what the next three months will look like for your cash flow. It’s time to see how each week will affect your cash position, and what decisions you need to make to survive.
The tool used to unleash our secret weapon is called The 13-Week Rolling Cash Flow Forecast.
Notice the “Rolling” feature of our tool. You are going to stay on top of your cash flow over the next three months by coming back to your forecast and advancing it another week, so you are always looking out 13 weeks, or three months.
Warning – you are going to get frustrated your first time through this. You may feel some overwhelm getting into the accounting technicalities of the moving parts. Keep pushing through it and clarity will come. You may also feel like you are trying to look into the proverbial crystal ball and see the future. And, you are.
And even amongst all this uncertainty and chaos, you must communicate with your customers, vendors, and team to gain some insight to predict what will happen.
Here’s the thing, it will get easier to predict how things will unfold.
Do the hard work and make the first pass at your rolling cash flow forecast. Then as you update and review weekly it will get easier. And your certainty about what’s happening with your business, your cash, your customers, your vendors- it will all become more clear and you’ll make better decisions as the leader of your business.
Now, for the technical step by step process. Refer back to the rest of this blog as often as needed as your work through your first-pass at setting up your forecast.
Here’s what it will look like when it’s all done.
The Rolling 13-Week Cash Flow Forecast is a simple formula with a lot of details behind it. We will get into the details, but for now here is the simple formula:
Beginning Cash Balances plus Receipts and Sources of cash less Payments and Expenditures of Cash equals Ending Cash Balances.
This formula is applied weekly.
Where does the information to plug into the formula come from? It comes from everything you gathered from Part 1 – all the work you did yesterday.
Beginning Cash Balance is your current cash position, as of Monday morning since this is a weekly forecast.
Receipts and sources of cash include ongoing cash sales, collections on receivables, cash proceeds from sales of any assets, money raised from banks, SBA, or other lenders, and owner contributions.
Payments and Expenditures of cash are your ongoing overhead expenses along with the timing on payments of your accounts payable, inventory purchases, debt reduction payments.
Ending Cash Balance is simply the sum all this. Your ending cash balance this week will become next week beginning cash balance.
Now all you need is a model spreadsheet to follow.
Excel has an excellent template for this. It is based on a monthly cash flow forecast; you’ll need to re-label some columns to adapt it to a weekly cash flow forecast.
- Open Excel
- Click on File, then New
- Then in the “Search for online templates” field type in Small Business Cash Flow Projection
You’ll need to adapt the template to 13 Weeks by re-labeling headers and two additional columns.
One for the 13th week at the end of the spreadsheet, and one at the beginning of the spreadsheet for a weekly Actual Results plug in to see how the past weeks “actual” results compare to what you forecasted.
This weekly comparison of actuals to forecast gives you the insight to keep sharpening your foresight. Each week you will be able to more accurately modify and update your projections, making modifications to certain weeks where needed, and build out the new 13th week at the end of the spreadsheet.
The best place to start is to run a 13 Week historical “cash basis” Profit & Loss statement from your accounting software.
If you use QuickBooks here’s how that is done.
- Reports menu – Company & Financial – Profit & Loss Standard
- Once the report opens, in the upper left corner click Customize Report
- Dates range for the last 13 weeks – Report Basis is Cash – Display columns by Week
You may want to take this report out to Excel.
QuickBooks does have an Excel export button at the top of the report, and hopefully your software does too. Once in Excel, you can cut and paste your expense account list into the Cash Flow Projection excel template, so your expenditures labeling is congruent with your specific business reporting.
Here’s the process to set up your first pass at the Rolling 13-Week Cash Flow Forecast.
- Complete the Cash Receipts top section of the worksheet for the next 13 weeks. Here is where the crystal ball is needed. Drop in whatever you are projecting your weekly Cash sales will be. These are cash sales. If you invoice your customers on terms, your weekly cash sales are then Zero. Then on the next line drop in any anticipated returns or refunds.
- Accounts Receivable takes a little more effort. If you sell on terms, you may want two lines here. Or better yet, use another worksheet to predict weekly cash collections on accounts receivable.
The first line is for your current accounts receivable as of the opening date of your forecast. After you have studied your Accounts Receivable aging (you did the work from Part 1, right?) drop in each week the amount of collections on the current list of accounts receivable.
The second line is the anticipated collections on new invoicing – this is where you might want to backup work sheet to show how much in new business you anticipate invoicing each week, then projecting out when those invoices will be collected.
- Complete the cash receipts by dropping in amounts in each week of anticipated loan proceeds, borrowings on lines of credit, owner capital contributions, other outside funding. If you are considering selling assets (equipment, vehicles, etc) to generate cash, add a line here called Asset Sales. Don’t worry about exact weekly timing on this. You will be constantly adjusting your forecast with new information as it comes.
- Cash Paid Out – Expenditures – Remember we are completing this spreadsheet on the CASH basis. Look at your 13-week history Profit & Loss Statement of expenditures on a cash basis.
Analyze each expense line item as to the timing of when those expenditures hit. With that analysis, and the information from the analysis you have done on your Accounts Payable as of the opening date of forecast.
Much like with Accounts Receivable, you need to blend in the timing of paying the existing Accounts Payable with the timing of paying on new expenditures. Just like receivables, create another worksheet to see the timing of paying accounts payable and new expenditures as bills come in.
- Other Cash Out – As you can see on the spreadsheet, these line items pick up the cash out on items that do not hit your Profit & Loss Statement. Such as principal payments on loans, Cap X payments (new equipment, etc), increases in inventory levels, and Owner’s withdrawals.
Clearly the farther out you project the less precise your projections will be. The first three weeks will be more accurate than weeks 11, 12 and 13. Do the best you can, and do not let “perfection” paralyze you from getting this done. After you have made your first pass, put it down for a day. Pick it up again tomorrow with a fresh eye and make appropriate adjustments.
Now you have a 13-Week Cashflow Forecast. So what?
Use it every day to help you make decisions.
Keep in mind this is a living analysis. Analyze your weekly ending cash balances and start making hard decisions about how to deal with cash shortfalls. There are only so many choices:
- Generate Operating Cash
- More sales – think in terms of more business with existing customers, new customers, innovative new ways to package what you do, new niches you can go after.
- Accelerate A/R collections – Personally follow up with slow paying customers. Reach out to the biggest receivables on your books to see how they are doing, and if necessary, what can be worked out on the timing of payments.
- Increase Operating Cash
- Reduce expenses. Cut the fat out of your business. Cut non-essential expenses. Be real with yourself about “Must Haves” vs “Nice To Haves”.
- Delay payments on accounts payable, loans, rent, credit, whatever you can work out.
- Reduce inventory purchases. It’s nice to be fully stocked, but not necessary. Has the demand lessoned anyway.
- Reduce investing cash
- Postpone or delay plans for expansion, growth and capital expenditures.
- Generate investing cash
- Consider selling assets. Is there a market for the equipment you won’t be using with the contraction of business?
- Generate financing cash
- Initiate new bank loans or SBA loans.
- Borrow on all available Lines of Credit or revolving credit
- Injection of owner capital contributions
- Raise money from investors
In a time of contraction all the options to deal with cash shortfalls need to be reviewed and considered every week. Update your rolling forecast every week. Refer to it daily when trying to make decisions about how to treat customers, vendors and your team.
If your head is spinning right now, just start. Download the excel template and follow the steps I’ve laid. Just start, keep pushing, and the clarity will come.