Managing cash flow, particularly during difficult times, is critical for the health of a business. As you decide what to buy and where to invest, you need to know the impact on your cash position. Below we will walk through a simple cash flow projection. This post will be a bit dry, but it is a detailed walkthrough of a 13-week cash projection and how it works so that you can take the spreadsheet and use it, or take some ideas from it to add to your forecasts.
Here is the link to the spreadsheet we are using for your reference. Feel free to copy or download the spreadsheet to use in your company. The information in this spreadsheet is from QuickBooks but changed to de-identify it. If you have questions, want to review it in greater detail, or need help with setting it up for your needs, feel free to reach out to us and schedule a free Zoom call.
Cash Receipts
Cash receipts are the cash inflows you expect to receive from your customers. These projections should be coming from your Accounts Receivable report as well as your booked sales. Do not include any estimates for orders you haven’t already won yet.
You can base the timing of these cash flows on the due dates, but I usually use the average days to pay for our total client base. Here I am using 45 days, controlled from cell D11 on the summary page. I also add 15 days to any past-due AR to recognize that these payments may take longer than average.
I also detail out these payments on the AR Summary page, so I can review which clients and invoices I expect to get paid when. I separate that view between three groups:
- What we got paid last week
- Past-Due AR
- AR that isn’t due yet
All of this pulls off of our AR report from QuickBooks. I export the CSV file for the AR Aging Report and paste it into column C of the Current AR tab. It also pulls from the Open Orders report, which is a production report from QuickBooks. That uses the orders that are already in-house, projects when they will ship, and then adds the 45 days to that to project when we will receive the payment for those items.
Cash Disbursements
The cash disbursements portion helps you plan your spending. It pulls invoices from vendors using the AP aging report from QuickBooks, on the Current AP tab.
It also pulls other AP items that are very regular and predictable through the Other AP Schedule. For those items, I typically remove them from the AP report by copy and pasting their name into Column M of the Current AP report “Vendors to Not Include.” This process takes the expected pay date away from the AP report and keeps us from double counting something by mistake.
We also project debt payments, which is critical for ensuring liquidity for highly leveraged businesses. I include the debt schedule of the company on the “Debt Payment Schedule.” I also have a rent schedule, since those payments should be contractual and usually change through the life of the lease.
Payroll projections are also essential. I have the full payroll listed in this version on the Payroll Schedule to see the effects of adding employees. I also add 15% for payroll tax expenses; that percentage will vary from state to state.
This company also has a revolver, which is on auto-sweep. So, if the company’s bank account is overdrawn, the amount is automatically swept from the revolver to cover it. Or, if there is a balance, it is automatically swept to pay down the revolver’s balance. That is estimated after the net cash flow and ending cash balance so that you can see the effects of cash flow decisions before the sweep. I also include a line to estimate revolver interest payments every month.
Quick Ratio
The quick ratio is a test to see if the company has enough cash and AR to cover its required payments. In this model, I take two approaches to it. The first is just using AR and cash over AP. So, do we have enough AR (or future cash) and current cash on hand to pay for what we already bought. The second includes the revolver and current debt load. These are principal payments that we will need to spend on our debt over the next year. You want to see these numbers be over 1, and closer to 2 when you don’t include debt payments.
