Previously, I showed you how to set up your cash flow forecast in Google Sheets. I like Google Sheets better than Excel, because of the powerful co-authoring and collaborative capabilities. Microsoft Excel does not come close to this, and contrary to what many people will tell you (out of ignorance), Google Sheets can do anything Excel can do.
In “If Cash is King, Cash Flow is a River of Kings” I showed you how to set up the cash flow forecast template. Now let’s get into action, and build the forecast.
However, know that at its heart, Google Sheets is merely a spreadsheet software that will only get you so far. If you want to forecast cash flow in a way that’s easier and more powerful, look at a project accounting solution like BQE Core. Now, back to Google Sheets.
First, we need to extend the timeline, and before doing that, we want to get the dates into a format that will make it easy to make updates later on. The video will show you how to do this.
Next we copy the formulas out, and get “filler” data into the projection, so that we can make sure the projected portion balances.
Finally we go line by line, and set up the assumptions and actual projections for each line item on both the Balance Sheet, and the Profit and Loss.
Accrual vs Cash Basis
When it comes to this process there is no cash basis. First of all, the very thing we’re doing is accrual basis in nature. The statement of cash flows has, as its purpose, to reconcile accrual basis net income to cash. On a cash basis your cash flow, in essence, IS your net income.
With that in mind, you project income based on when it is earned, or incurred. For example, you project sales based on when you would invoice the sale. Then, you will have an assumption about the A/R turnover on the balance sheet. This will take the cash flow part into consideration. You project expenses based on when they will be paid. If you need to account for something like pre-paid expenses, then you build out the pre-paid schedule and flow it to the balance sheet and profit and loss accordingly.