The Official BQE Software Blog

How to Create a Cash Flow Forecast in Google Sheets

Apr 26, 2017 | By Seth David | 4 Comments

Topics: Tips and Tricks, Cash Flow Forecast, Industry Insights, Cash Flow

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.

From tracking time and preparing invoices to managing projects and allocating resources, Core is the single platform that does everything your firm needs. Click below to start your free trial today!

Comments