Preparing a cashflow forecast

… or “Why is cashflow so important?”

There are many articles about putting budgets together. This one is a little different. In this article I assume you have worked out the budget for your scheme, and I want to take you a stage further and emphasis the importance of putting together a cashflow forecast as part of your budgeting process.

[NB – you might find it helpful to download the three tables mentioned in the article.  They’re in pdf format so you can print them (preferably in colour) or look at them on your screen.  There’s Table 1, Table 2 and Table 3.]

For the sake of this example, let’s assume you’re putting together a budget for a day service of some sort. In this service you rent premises, employ staff, and run activities.

The day service is part of a portfolio of services your organisation provides, so it is expected to contribute to the infrastructure costs of your organisation.

Let’s assume you’ve prepared the budget that’s in Table 1. This example is taken from a real life scheme, but the headings and some of the numbers have been changed to “protect the innocent”. Even if you don’t agree with some of the headings or the numbers, at least the principles are good.

Income

There are two source of income. The main one, by far, is the fees you receive from the local authority for delivering this day service. In addition, you sometimes run “specials” for which you will receive about £400.

Expenditure

There are 6 main areas of expenditure.

  • staff – all the costs associated with staff
  • premises costs – costs associated with the building; you’d have to pay these no matter what you did inside the building
  • office costs – costs to do with administrating the scheme
  • transport – part of your scheme involves your service users out and about
  • centre costs – these are costs to do with running the day service as opposed to running the building, so equipment, provisions etc
  • misc – anything that doesn’t fit neatly into one of the other 5 categories

Management charges

This scheme is only one of several your organisation runs. In order to function, your organisation has overheads – maybe a chief executive, an accounts person etc. You will almost certainly have to pay an accountant to put together your annual accounts.
It is only right that each project contributes towards the infrastructure costs, and so you have decided to apportion the overheads across the projects. (NB this is one of the principles of full cost recovery) I’m not going into how you do this, but I will assume that the apportionment is fair and can be justified to the various funders.

Looking at Table 1 it looks as though this scheme will make a small surplus. £2,376 is quite a lot of money, but on a scheme bringing in £282k, it’s only 1% of that, so this tells us that the scheme budget is just about balanced.

Now comes the ‘but’.

But we haven’t taken into account WHEN the money will come in or be paid out. So the next step is to prepare a table with 12 columns, one for each month in the year, and use our judgement to make the best estimate of when things will happen. We know we’ll have to pay the staff every month so that bit’s easy. And although there may be the odd hiccup, it’s reasonable to assume that the fees will come in monthly too.
What about rent? When does that go out? Electricity, gas? When do we need to pay the accountant for his part in the annual accounts?

It’s relatively easy to get a lot of the numbers pretty accurate. You know exactly when the utilities will bill you, for example. What about more intangible numbers, like training and recruitment? All you can do is put some numbers in somewhere and recognise that the timing may change and you might get lucky and not have to spend the money at all.

For expenses within your control you can make sensible estimates. If you take your service users out for the day, you’re probably more likely to do that when the weather’s nice, so minibus hire might cost more in the summer months than in the winter.

For general expenses, the most pragmatic approach is to spread the cost evenly across the year. You know it won’t happen like that, but it’s really the more reasonable way to forecast. Remember you’re trying to get the best idea you can of when things will happen. It won’t be perfect but it will help you plan

Having considered all these time issues, we’ve come up with Table 2. The totals haven’t changed from Table 1, which is encouraging, but the really interesting numbers are on the bottom line. Here we have the cumulative surplus/deficit figures, month on month. And there are a couple of red numbers which indicate that in these months, the scheme will be in deficit.

If these numbers were up to about £500, I wouldn’t worry too much. I’d still take steps to keep the deficit as low as possible, but we’re running a scheme that’s bringing in nearly £24k every month. £500 is 2% of that, so I reckon it’s not worth losing sleep over.

However, April, October and March show seriously large deficits, and these are worth looking at.

Now, if you’re working for a large charity with a healthy bank balance, it may be able to cope with these deficits’, but, let’s face it, this isn’t likely. It’s far more probable that the charity won’t have enough spare cash lying around to cover this scheme.

So what can we do?

Well. it’s clear that the biggest deficits occur in months when the rent is due. There’s not much we can do about that, but look more closely at the rest of the figures. In April we have an insurance premium of £1,500 and we’ve allocated quite large chunks of training budget to October and January. We’re also collecting £900 every month as a management charge. Could the management charge be collected quarterly, in a month when the scheme’s bank balance is healthy? (How would that affect the cashflow for the whole organisation?) What about moving the accountancy charge to earlier in the financial year?
In Table 3 you can see the results of some re-jigging. We spread the insurance over 12 months, and because we rounded the numbers the total went up a bit. For the rest, all we did was adjust the timings of when things happened.

Now the totals are more or less the same, but there’s now only 3 months where we’re in the red and the numbers are much, much lower. £871 in April is still quite large, so I’d be very frugal at the start of the year, maybe put off spending £120 on computer software until May, for example.

You could carry on adjusting, but to be honest I wouldn’t bother now. I would go with this cashflow, and just monitor expenditure carefully, remembering which months are likely to be particularly bad.

The important lesson from this whole exercise is that we are now aware of the financial issues, before we have even started the scheme. We know what to look out for, and so we’ve just significantly increased our chances of a successful scheme.

You can find the Excel spreadsheet I used for this article here in Excel 2007 format.  Feel free to download it and modify it for your own use.  Just be aware that I don’t guarantee it’s 100% fault free.  So check the maths if you’re going to use it for something serious.

What next?

There are lots of clever things you can do with Excel spreadsheets. One of them is to use your cashflow forecast as a basis for monitoring income and expenditure on your project or scheme. Setting these up takes a little time, but once they’re done, it can save you so much effort. Sounds like a subject for another article …

For help with preparing cashflow forecasts or any other aspect of charity management, contact us.

Print this page