Preparing Cashflow Forecasting in a spreadsheet is hard work

Forecasting cashflow is hard work in a spreadsheet. Or, more precisely, forecasting cashflow well is hard work in a spreadsheet. The traditional method of managing timing of debtors and creditors, for example, is to make simplistic assumptions that everything is paid in 30 days or 60 days and then it’s easy to link to the previous column or the one before that.

There are a myriad of tools available that will help you with cashflow forecasting. But how do you evaluate them? Here are some pointers on what to look out for:

 

1. Timing of Payments

An important factor in most businesses is the timing of payments and receipts – unless the business is totally cash-based. Generally, invoices are issued and payments are made at a later time. So the first question should be how well the tool manages this. There are software-forecasting tools that ignore this factor altogether, or assume that your actuals and budgets have been entered on a cash basis.

Look for something that lets you set up varying profiles for different accounts (some expenses are always paid immediately; some are on account). If it calculates these profiles for you based on average debtor days or creditor days, that’s a bonus. Basing it on your trading terms is generally less reliable: you may ask for payment in 30 days but that’s not often what you will get.

 

2. Timing of GST payments

Does the app handle GST payments at all? There are some which require you to manually calculate the GST and enter it into the forecast. The better tools will calculate it for you – or at least work on a reasonable estimate.

Then it’s a matter of looking at the timing options they give you. Is there an option that matches your payment schedule? If you’re in New Zealand, does it forecast your 2-monthly or 6-monthly payments in the right period? If you’re in Australia, does it take into account the extensions for electronic lodgement or lodgement by a tax agent? Does it account for the varied payment dates after the Christmas period?

GST can be a substantial amount for many businesses and it’s vital to incorporate it correctly in your cashflow forecast. There’s a big difference between forecasting a payment or receipt in January rather than February.

 

3. Wages and PAYG/PAYE

Most of the time, wages are budgeted for and accounted for on a gross basis. Most accounting systems record them in your expenses this way, with tax deductions going to a liability account for payment later. Your cashflow forecasting tool should work the same way. It shouldn’t require you to budget for the net wages and then the tax withheld as a separate expense line.

 

4. Balance Sheet movements

There are cashflow forecasting tools that completely ignore balance sheet movements. That’s OK if your business is really simple but for most people these can have a significant effect on the forecast bank balance.

Important factors to include are loan repayments (as well as the receipt of new loans), drawings or dividend payments, asset purchases or disposals. Make sure your chosen tool can incorporate these into the forecast – ultimately it should be able to use the same information to produce a full forecast of your balance sheet.

 

Find the right tool for your needs

There are many tools to forecast cashflow because none of them are perfect for every need. Some people need something simple, some have very complex businesses. When doing your evaluation, keep your eyes open and make sure you get something that will give you a reasonable estimate of your future cashflows, preferably without too much hard work.

 

Check out our comparison list ‘Compare Calxa to other Reporting Tools‘ for more ideas.