Forecasting Cash Flow is imperative for every business owner to recognize whether they must cut cash outflows or increase cash inflows to keep the business stable. Most owners run their businesses based on the amount of money they have in the bank, but this is a big mistake. The simple fact is that by the time you see you’re running out of cash flow, it is too late to avoid a negative effect. If you’re experiencing this problem, fractional CFO services can help.
Unalp CPA Group utilizes technology to seamlessly reforecast your cash flow for the next twelve months, on a daily basis, based on the latest transactions in your bank account. We call this approach “Dynamic Accounting.” Whereas most accountants and bookkeepers just look at your numbers and tell you where you’ve been, we tell you where you’re going, based on your numbers and our forecasting capabilities. Hence, our approach to accounting is not static, but dynamic.
Dynamic Accounting is necessary because your business’s numbers alone — your profit and loss — act only as general indicators of your business’s health. Your numbers will not tell you, for instance, when you might pay money out to a vendor or receive payment from a client. Without a warning system, you could find yourself scrambling to bridge gaps in a month with lots of early payouts and later client payments. In other words, your business could be profitable and still run out of money. This is why “Dynamic Accounting” is so powerful. Our system anticipates these situations. When you choose fractional CFO services, we do the work for you.
In this white paper, I will teach you how to do cash forecasting for yourself. In my cash excel forecast model, which I share with clients, I start with the balance at the end of the prior month. Going from left to right, you will note the following items.
- Column A, Actual Date, stays unpopulated until a given transaction hits the bank.
- Column B is Estimated Date. This is the column we use to sort everything. (When a transaction comes through, the estimated date will become the actual date.)
- Column C is Description. This column labels each transaction so when we look back, we can understand them easily.
- Column E is Revenue. This is the business’s cash inflow for services and sales. Rather than recording every single sale, we enter revenue on a daily basis. Here, it is ok to estimate sales for the future. For instance, you could enter your daily average based on the past six months of sales. Or if your accounting system is good, you could use your daily average for the same month last year, which allows you to factor in seasonality, but be conservative with your estimates. It is better to estimate too little and be pleasantly surprised than to estimate too much and find yourself short.
- Column G is Operational Costs for the business. You will need to anticipate at what points in the month you will pay them out.
- Column I is dedicated to Owner transactions. For instance, if you are an S Corp, you may take shareholder distributions, or if you are an LLC, you might take draws and make investments in the business.
- Column K is the Total of the transactions in Columns E, G and I.
- Column L is Balance. It simply takes the amount from the row above (the previous day’s balance) and adds the total from Column K in the current row. That balance should equal your bank balance for the actual date. Again, I will show you how this works later in the white paper.
Forecasting a Month Before it Starts
This model is designed to start on the first day of a month and to allow you to forecast the entire month’s expenses. Within the Excel document, you can organize models for an entire year by creating a separate tab for each month, with that month’s name on the tab. In my example, for a furniture business, I am starting with November 2020.
(Click on images to make them larger.) Here is how the model looks when I start:
This is the setup to start forecasting the monthly cash flow.
Next, I can start adding inflows (revenue). Let’s assume that the furniture business brings in $1,000 in sales per day, on average. I want to add this data point for the first day of the month. We add this to the spreadsheet using an estimated date of 11/1/2020 in Column B (remember, we do not use Column A, Actual Date, until we verify the date registered by the bank) and the amount of $1,000 in Column E for Revenue. Now the model looks like this:
Because that estimated revenue of $1,000 per day is consistent throughout the month, we want to add that data point for every day from 11/1/2020 through 11/30/2020. To do this, just pull down on the little square in cell B10. Pull it down 29 more rows, and you will see the dates appear. Then, copy the $1,000 in cell E10 and copy it down 29 more rows. The model will now look like this:
Now we have the opportunity to customize some of those daily revenue cells. For instance, if your business is closed on Thanksgiving (11/26/20), then you can just zero out cell E36. If you expect lots of sales on Black Friday (11/27/20), then you can change that cell accordingly.
Now let’s look at outflows, starting with Operational Costs. Let’s assume you pay rent the first of every month, and your rate is $4,000. In cell B40, type in 11/1/20, the date of the expected outflow. (No need to worry that this date is “out of order” right now; we will reorder the dates in a future step.) In cell G40, enter the amount of the outflow, $4000. Now the model shows the following:
Next, let’s think about payroll. Let’s assume that you pay one sales associate $2,000 per week, every Friday. The employer tax is an additional 10%, or $200, and we will roll that amount into the payroll expense. In this month, the Fridays are 11/6, 11/13, 11/20 and 11/27. We will put this expense of $2,200 in each of 4 cells in Column G, Operational Costs, and we will put the corresponding dates in Column B, Estimated Date. Let’s also assume that the business has a credit card bill of $3,000, with a payment date of 11/15. We will add that. Now the model looks like this:
Now, let’s consider inventory. Let’s assume that the business needs to buy $25,000 of furniture on 11/10/20 in order to receive and display it for Black Friday. That expense looks like this:
Now, let’s consider Owner costs. Let’s assume that you need to take an Owner’s Draw of $10,000 on 11/20/20. I will put that in as minus $10,000 in Cell I47, with the date in Column B.
Of course, you can keep adding data points as necessary. For now, let’s assume that you are finished adding inflows and outflows. Now, we want to organize the entries by Estimated Date. First, highlight the columns and rows that are to be sorted. DO NOT include columns K and L, because these are totaling columns. The shaded columns and rows below are what you need to highlight.
Next, let’s sort these transactions. Go to the top of the page and select “Data,” then select “Sort”:
Choose Column B as your column, and order oldest to newest. If you press “OK”, it will organize the transactions by date and give you a clear view of your cash flow by Estimated Date. Here’s how that looks:
Now, with these dates telling the story of inflow and outflow for the business throughout the month, we have a birds-eye view that lets us anticipate challenges. For instance, we see that with an owner draw on 11/20/20, cash flow hits a low. Without this model, it would be much harder to predict the impact of that outflow.
Adjusting the Model Based on Real-Time Data
Now, let’s assume that on 11/9/20, you review the model. Your actual sales so far this month have totaled as follows:
11/1/2020 – $1250
11/2/2020 – $1100
11/3/2020 – $5000
11/4/2020 – $0
11/5/2020 – $1100
11/6/2020 – $1300
11/7/2020 – $500
11/8/2020 – $1250
11/9/2020 – $800
Let’s also assume that you paid a payroll of $2,400 on 11/6/20 and that you had to pay $30,000 in inventory on 11/8/20. Input all of these real numbers in place of their anticipated estimates, with their Estimated Date in Column B and their Actual Date in Column A. Now the model looks like this:
Now, you are going to want to sort the transactions again based on the “Estimated Date” in column B. First, highlight the columns and rows that are to be sorted. This time, you should include Column A, “Actual Date.” Again, DO NOT include columns K and L, as they are totaling columns. Also, DO NOT include row 9, as that is a title row. The shaded columns and rows below are what you need to highlight:
Next, let’s sort these transactions. Go to the top of the page and select “Data,” then select “Sort”:
Sort by Column B, in order Oldest to Newest. If you have done the sort right, your Estimated days will appear in order up through the Actual Date of the transactions. See below:
If you have done this correctly, your cash flow forecast should equal your bank balance on 11/09/20 of $76,100.
Setting Up a New Month
To start a new month, for instance, so you can start forecasting for December, you can create another tab by right clicking on the tab “Template.”
Select “Move or Copy.”
You will now see a “Template (2)” tab appear. Click on that tab and change the name to “December 2020”. Now change the dates in cells A4 and A9. The file will look like this:
The final step is to pull the final balance from the last day November 2020, because that will be the starting point for December 2020. First, in the new month’s tab, go to the opening day’s balance, cell L9. Click on it and hit the “+” sign. Then, in the old month’s tab, look at the last cell in the Balance column, in this case cell L47. Click on that cell:
Then click back to the new month’s tab, in this case, December. The file should look like this:
Now, as December begins and progresses, you can repeat the steps you followed for predicting and adjusting your numbers in November.
Long-term Benefits of Dynamic Accounting
Our goal with Dynamic Accounting is to make accounting an easy process and to help you learn more about the inner workings of your business. The longer you use this approach, the more beneficial it is, because it builds your expertise on your business over time. As you learn your cash flow and understand it better, we will work with you to help trends, spot potential problems before they happen, and protect the health and growth of your business.
Outsourced CFO Services
Do you have questions about forecasting your cash flow? Outsourced CFO services can help. Contact Unalp CPA Group at (925) 256-6321 to find out if outsourcing accounting and bookkeeping services is right for you. We’re here to answer all your financial questions.
Recent Comments