The sarcastic part of my brain is telling me that we have many boatloads of short-term cash flow projection experts based on a simple Google search using this string:
The number of results is dizzying. One can keep scrolling and scrolling and scrolling without finding the last page.
Aside from the endless options of choosing a template, there’s already enough friction in maintaining a weekly discipline of updating a cash flow forecast. I’ve observed countless owners get started using these templates only to quit using them a few weeks later.
The second type of friction occurs with the tool built in Excel. The spreadsheet is not the issue; it’s how it’s constructed. The forecasting is designed horizontally and has to be manually modified each week, even if the projected numbers do not change (or change that much).
In this article, I’ll first show you my vertical process of constructing a cash flow forecast for small contractors. Then, you see how I deal with the many data points within every contract. At the end of each section, I will walk you through the steps through video.
The Flat and Horizontal Nature of Spreadsheets
Every weekly cash flow forecast has three primary sections: the date range, the cash flow categories, and the data.
Most of us who create these templates have been using spreadsheets for years. I always considered using Access, but it would be too much trouble even though it would be better at storing and managing the data.
The part I hate about spreadsheets is that you start over every week when the forecast is updated. For instance, assume payroll is $100,000 every two weeks through thirteen-plus weeks. The dates don’t change, and the number doesn’t change, yet the forecaster has to cut and paste the data to the column before it after each new week. It’s a hassle to do this on every row.
There has to be a more accessible and cleaner way of forecasting cash. While the first goal is to gain an understanding of our cash balances over the next few weeks, our second goal is to do this work efficiently. Spreadsheets make this second goal problematic.
Think Vertically, Not Horizontally
The primary purpose of this post is to discuss cash flow forecasting for contractors, as this industry has nuances in determining its future cash inflows and outflows. The number of cash events can overwhelm the accountant managing vendor bills and customer invoices. Throw on cash flow projections; now we’re entering a potential nightmare. Let me address one more concept, and we’ll hit the central theme in a few minutes.
The problem with horizontal thinking is that the template is a report with direct data input. What if we kept the same report layout but had the projections (see label ‘C’ in the image above in the previous section) pulled from data tables from another location?
Several outcomes occur when forecast data is created in the following format with this overly simplistic dataset:
Once again, this is overly simplistic, and your setup will have a more granular level of detail. The forecaster only needs to add dates and when dollars will hit under the proper category in the table on the left. If a driver-based approach is desired, that can easily be added, but the final values will still reside in a table such as the one at the right, which includes the cash flow reporting for the next thirteen weeks.
In the spreadsheet horizontal world, data has to be shifted manually to the left each subsequent week. In a vertical format, static or dynamic data does not need to be moved around. Dates and dollars can be changed, deleted, or added, but no manual shifting is required for the reporting. This will become more obvious in the video series below.
Forecasting Cash For Every Contract Seems Hard, But Does it Have to Be?
Let’s pretend for a moment you have 50 contracts in WIP with the following numbers:
Average number of projected receipts per contract: three
Average number of payments per contract: five
Here is how the math works out. If we multiply 50 times three, we get 150 receipts we have to forecast. If we multiply 50 times five, we arrive at 250 payments we have to forecast. That’s a combined total of 400 economic transactions that must be forecasted.
Daunting? Yes? Maybe? What if I stated it didn’t need to be?
Let’s step this through on the receipts side of the equation with the following steps:
- ID each contract in WIP
- For each projected future receipt by contract, list a date next to it
- Ensure the cash has not been collected on that receipt already
- Also, ensure any receipt is not sitting in AR
Here is a screenshot of my system, incorporating the four points above.
Section 1 above includes the meta details for each contract. Section 2 includes projected receipt dates for each dollar amount in Section 3. The project managers keep sections 1-3 updated. The accountant owns section 4, and their job is to toggle whether receipts have already been collected or are sitting in AR.
I will not include the payment section, but it mirrors the format above.
The data runs vertically, not horizontally. It behaves like a table inside a relational database. That makes the reporting each week easy. PMs still have to monitor dates and dollar amounts, but if jobs remain relatively static throughout a contract, maintenance for this level of forecasting is minimal.
Assume there are 400 data points to forecast. Does the process above now seem less daunting? If you are still uncertain, I assure you that forecasting contract receipts and payments is far less daunting. In a company with this many projected transactions, the weekly update should take less than 30 minutes for all parties involved.
Don’t Forget to Document the Model
Even in spreadsheets, I like to document how to use the tool. I include a big-picture discussion and add specific steps, as shown in the video below.