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.
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.
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).
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.
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.
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.
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.
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.