How to build an Excel cash flow forecast

Cash flow projection spreadsheet
iStock_Cash flow_maybefalse
Share this content

In this tutorial, Valme Claro explains how to create a cash flow forecasting model in Excel and the structure behind any cloud forecasting model.

This tutorial was prompted by a recent introduction to cash flow forecasting from Propel by Deloitte, which argued that cash flow forecasts are especially important for startup busiensses that may depend on eeking out their founders’ capital until the revenue starts to arrive.

Putting in place and revisiting a cash flow forecast can help businesses find new finance or take other measures before they run out of cash.

Depending on the needs of the business and its cash requirements, a company might need to recalculate its forecast every three months, every month or even on a weekly basis.

But many starting out in business do not have a basic appreciation of what goes into the cash forecast. To help get them started, this tutorial shows how to build a cash flow forecasting model for new businesses in Excel.

Forecasting in Excel

Step 1: In one row, enter the months that you will be forecasting for. In this case, we have entered twelve months from the beginning of the tax year.

In the column to the left of the first month, enter “opening balance” in the first cell. Continue inserting different types of income. In this case, for instance, we have opted to include owner’s capital as if it were a forecasting model for a new business. You will want to adapt the different income and expense headings according to your needs.

Basic cash flow forecast spreadsheet structure

Step 2: Select the area that we are going to work with. Right click and select Format Cells >Accounting. Make sure the pound symbol is selected and click OK.

Select the Accounting number format for all entries

Step 3: In the second cell of the opening balance (D4 in our example) insert an equal (=) sign and select the closing balance of the previous month (C23).  

Reference each month's opening balance to the previous month's closing balance

Insert a formula for the total money in that sums up all the revenue. In this example, the formula that we insert in C11 is =SUM(C7:C10). Follow the same procedure for the total money out; in the example, =SUM(C14:C20) in cell C21.

Sum all the income lines using a formula

Insert a fourth formula for the closing balance (opening balance + total money in – total money out; in this case, =C4+C11-C21). Drag these four formulas across the four rows so that they are in every column.

You can also separate the total money in, total money out and closing balance with a border.

Build a formula for the closing balance and drag it across the forecast columns

Step 4: Insert the data you have predicted for each row of money in and money out. Your opening balance should automatically populate with the closing balance of the previous month. The totals will also be automatically calculated.

You can add notes to explain where the expenses come from by right clicking on the appropriate cell and selecting “insert comment”. Once you do that, your note will be visible every time the cursor hovers over the cell.

Insert a comment if necessary to add more insight to the figures

Step 5: To make it more readable and highlight the important values, you can highlight the row with the total money in and the one with total money out in a different colour. For the closing balance, you can select the values for the formats from the Conditional Formatting Highlight cell rules menu option and and click on Colour scales to select one of the suggested colour schemes. In this case, we have chosen the third red-amber-gree option.

Add Conditional Formatting to your cash flow forecast

Cloud forecasting

The example above is a simple cash flow forecasting model. However, besides Excel, there are a number of forecasting tools that offer more advanced functionalities in the cloud, which can save time and reduce the possibilities of making manual errors.

Xero, for instance, enables users to upload budgets from Excel and other cash flow forecasting tools such as Fathom, Futrli, Spotlight Reporting and Float offer the facility to build forecasts within their software.

For more sophisticated tutorials that cover everything from budgeting to KPI dashboards, check AccountingWEB’s Excel page and sign up for our monthly email bulletin on your MySubscriptions page.

About Valme Claro

Replies

Please login or register to join the discussion.

avatar
04th Apr 2018 11:32

Great article Valme.

Perhaps the next one should be "how to add up a profit and loss account" for those accountants who haven't learned to do that yet!

Then we can move onto "how to save a spreadsheet". That should be really valuable.

If you are going to post "content" then please make it worth reading or don't bother.

Yet more space taken up at the expense of valuable "content".

Thanks (0)
to Trethi Teg
04th Apr 2018 12:03

Hi Trethi Teg,

Thanks for your comment. We try to vary the levels of difficulty in our 'how to' articles.

In the case of Excel, we mainly try to publish tutorials catering for intermediate and advanced users, but also include a few for our student audience as well, which tend to be quite popular.

While I'm glad that you don't have the need for this guide, the title is fairly self-explanatory. I'd suggest that next time you spot a similar heading you don't click on the link.

Best wishes and have a good day,

Tom

Thanks (0)
avatar
05th Apr 2018 10:53

Sorry Valme, this is a sort of Noddy's Guide to Cash Flow Forecasting that could easily have been written by a banker.

If you want to write a cash flow forecast for a slightly (or even significantly) more grown up business then you need to:

1 Project the profit and loss account, monthly or quarterly, matching costs to revenue on a proper accruals basis.
2 Project the balance sheet, also monthly or quarterly. Some of the balance sheet entries (such as debtors and creditors) will be informed by the levels of business indicated in the profit and loss account.
3 Then you can derive the cash flow from the two.

A cash flow forecast that does not include both a profit and loss account and a balance sheet lacks integrity, and is simply not to be trusted.

This is not a complex thing to do, and it's what accountants are for. A banker would not know where to begin.

Thanks (1)
05th Apr 2018 11:06

I have to agree with John Francis. A "stand alone" monthly cash flow forecast lacks integrity. It really needs to be linked to a forecast of P&L and balance sheet, and should take into account invoicing dates, profile of debtors collection, etc. I would suggest that a "stand alone" cash flow forecast could be done alongside the monthly forecast (that would be linked to monthly P&L and balance sheet forecasts) but should be a daily forecast, as this would highlight any problems that may occur during the month. For example, if all your receipts come in at the month end and your payments go out during the month, there may well be an overdrawn situation during the month, even though the month end forecast appears to be well in credit.

Thanks (0)
05th Apr 2018 11:50

I think it's a good idea to be a little careful as to the assumptions we make about the spreadsheet capabilities of others. Many of us will have come across examples of people doing some very unexpected things in spreadsheets - such as using a calculator to add up values in cells and then typing the result back into the spreadsheet. It doesn't hurt to restate the basics now and again.

Thanks (5)