Cookies on Knowhow Nonprofit

We use cookies in order for parts of Knowhow Nonprofit to work properly, and also to collect information about how you use the site. We use this information to improve the site and tailor our services to you. For more, see our page on privacy and data protection.

OK

Skip to content. | Skip to navigation

How to set up a cash flow forecast in a spreadsheet

A cashflow forecast is used to predict peaks and troughs in your cash balance, enabling you to consider at what point you may need a loan or sales drive or conversly, periods when there should be an excess. Banks may ask for this as part of a loan application.

Things you'll need

  • Access to spreadsheet software, e.g. Excel or Calc (free @ OpenOffice.org). How-to instructions and screenshots for each step have been completed in Excel 2007, other spreadsheet software may work in a slightly different way.
1

Initial spreadsheet set-up

Open up a new spreadseet in your software program. Start at either cell A3 or A4, so that you can enter a heading at the top of the sheet later on. Type 'Income' into this box, make it bold if you wish.

Move across to column B and enter the first date that you wish the cashflow to start from, e.g. if you want a month by month forecast then start in the following month from the one that you are currently in or processing in your accounts. Move to column D and type in the next date. Repeat until you have all of the dates you require e.g monthly for 6 months.

Merge each month heading with the cell next to it, i.e. B4 and C4: this is done by highlighting the two cells and pressing the button with an 'a' between to arrows pointing left and right, and in a single cell (figure 1). Again, make each heading bold if you wish.

Under your first merged heading enter 'Forecast' in cell B5, then enter 'Actual' in C5. Repeat this across the row so that each date has these two sub headings underneath it. Make each sub-heading bold, centred, etc if you wish.

2

Completing the spreadsheet set-up

Fill in column A with all of the income and expenditure streams. If your company uses an accounts software package then it may be useful to use account code headings for each income or expenditure stream that you enter, you could even include the code, i.e. 4000 Sales Type A, 4001 Sales Type B, etc (these are standard Sage accounting software codes). Once you have listed your income streams, type 'Total Income' into the next cell down.

Leave a blank row and type 'Expenditure' in the next cell in column A, then list all of your exenditure, e.g. rent, rates, purchases, wages, utilities, office supplies, VAT, PAYE. Again, once all expenditure streams have been listed, type 'Total Expenditure' in the next cell down.

Leave a blank row and type 'Bank b/fwd'; underneath that type 'Add Total Income', then down another row to type in 'Less Total Expenditure', and lastly once more down a row and type in 'Bank c/fwd'.

DON'T worry if the items overlap into column B, once you have completed your list go up to the top of the spreadsheet where the column letters are. Place the mouse over the line between columns A & B, your mouse pointer will become a thick black line disected horizontally with a double arrow pointing left and right, double click the left mouse button and the column will increase in width to fit the contents. Make 'Total Income', 'Expenditure', 'Total Expenditure' and 'Bank c/fwd' bold if you wish.

It would also be a good time to save the file, e.g. ABC Non Profit Cashflow Forecast for 6 months from February 2011 (figure 2).

3

Filling in the figures

Now is the time to fill in those all important figures. For forecasting purposes it is best to look back over figures for previous months or even years. Trends in income can be spotted this way, e.g. a lull in spending in January and February after a busy Christmas period; an increase after a successful marketing campaign that takes place once every three weeks. Each business is different.

For those who do not have much in the way of previous figures to look at then it is best to be prudent on how you believe figures can be acurately represented. It is no good believing you will receive £100,000 in sales revenue if it is more likely you will only receive £22,000.

It is also important to note that these figures represent actual income including VAT if you account for it, i.e. income actually going to be received. This cashflow forescast example is based on payments received for goods and services before they have been delivered. If you sell goods and services on credit then you should make contingency for those customers that will pay on time and those that will pay in future, e.g. you expect 60% of customers to pay in 30 days as per the agreement. You then expect 25% of those that are left to pay in 60 days and the remaining 15% to pay in 90 days. You could add in extra rows to include these payment scales if you wished.

Begin by making sure that the cells you are entering the figures into are set up for currency, do this by highlighting all relevant cells and either changing the number format on the Home ribbon to currency via the drop down menu (figure 3a) or by right clicking the mouse, choosing Fomat Cells, and changing from General to Currency on the Category listing in the Number tab (figure 3b). Make sure the options are as you want them before accepting the changes.

Enter figures for expected income and expenditure (including any VAT if applicable). NB: Any VAT that is due to be paid to HMRC quarterly (or as per any arrangement you may have with them) should be input under the relevant date. Any other payments that are made on different terms, e.g quarterly rent, commision and bonuses paid yearly, a one off legal fee, rates paid monthly April to January only, should all be included into the spreadsheet only when they become due to be paid (figure 3c).

4

Setting up the formulas

Now that you have typed in all of the relevant figures it is time to create the formulas. Select the first cell under column B next to 'Total Income'. Left click on the Sigma button (the Greek E symbol on the top right of the Home ribbon, figure 4a); this will automatically add up the closest list of numbers, in this case all of the numbers above it. Hit return on the keyboard to accept the formula. Copy this formula across the row - the easiest way to do this is make sure the cell containing the formula in column B is highlighted, allow the mouse pointer to sit over the black square at the bottom right of the highlighted box, the pointer will turn into a black cross. Left click holding the mouse button down, fill across the row to where you want the formula to end and let the mouse button go (figure 4b).

Do exaclty the same for the 'Total Expenditure' row, be careful if there are missing values in your list. Make sure to inlcude all of the cells needed to calculate the formula in each column by highlighting the fields you want in your formula (figure 4c). If you do not do this there is a danger that figures will be missed and the end result is not accurate.

Add a border at the top of the row for both 'Total Income' and 'Total Expenditure' to distinguish that they are the sum of everything above them. This is done by highlighting and selecting the relevant cells and either clicking the left mouse button on the border menu on the Home ribbon or right clicking and then selecting the border from the drop down menu (figure 4d). Make these figures bold if you wish.

5

Finalising the bank position forecast

In the cell in column B next to 'Add Total Income', type an equal sign '=' (next to the backspace key on the keyboard) and then highlight the cell which it represents (figure 5a). Again highlight cell, keep left mouse button pressed down on the bottom right hand corner of the cell and drag the formula across to the last column. Repeat in the cells next to 'Less Total Expenditure'.

In the cell in column B next to 'Bank c/fwd', type in the formlua '=B(cell number next to 'Bank b/fwd')+B(cell next to 'Add Total Income')-B(cell next to 'Less Total Expenditure')' (figure 5b). Highlight cell, keep left button pressed down on the bottom right hand corner of the cell and drag the formula across to the last column. Highlight the cells in that row, click on the border drop down menu and select the button with 1 line on the top and 2 lines on the bottom. Make these totals bold if you wish.

We will now create the formulas for the continuing bank position for both the forecast and the actual position of the bank. In column D next to 'Bank b/fwd' type in '=B(number of the cell next to 'Bank c/fwd'), this figure represents the brought forward figure of the Bank from the previous month [c/fwd - carried forward figure to the next month] (figure 5c). Repeat for all of the 'Forecast' columns, i.e. in column F the formula will start =D...; column H will start =F...; etc. Input the same formulas for the 'Actual' columns, starting with column E, which will be =C...; and follow =E...; etc (figure 5d). You might wish to change the font colour, include borders or set a fill colour to the 'Forecast' or 'Actual' columns so that the figures are easily identifiable.

The last figure to enter is that of the starting position in the bank, this will also include any cash in a till or petty cash as well as in the bank. Both figures in columns B and C next to 'Bank b/fwd' will be the same.

As you read along the line you may notice that some of your figures may be in red, have a minus sign, brackets around them or a combination. This means that there is a defecit for that month or months and should be analysed (figure 5e). Also type in the title and period to the report before saving.

6

Actual results against forecast figures

Once actual figures have been finalised for each of the months these can be input into the spreadsheet. This will give you an accurate picture of what actually happened against what was expected for further scrutiny and analysis, the figures for future months can be adjusted and the spreadsheet extended as approprite(figure 6).

Further information

Contributors

Page last edited Jul 05, 2017 History

Help us to improve this page – give us feedback.

1 star 2 stars 3 stars 4 stars 5 stars 3.1/5 from 1620 ratings