• April 16, 2020

Forecasting Cash Flow in Excel: How to Avoid the Loopholes


The creation of a cash flow forecast in any spreadsheet application program like Excel is a crucial step in making business plans for the future. However, it is quite challenging to maintain accuracy in manual forecasting, and in keeping it updated to the latest information. Keep your eye out for the loopholes enlisted below while forecasting in Excel or any other spreadsheet app.

The pitfalls of forecasting in Excel using the Cash Flow App

1. The risks of errors

An accidental error, like, adding an extra zero by mistake or missing out on the decimal point, can magnify to the extent of rejection of the entire forecast. Such mistakes have the potential to have a gravely negative impact on the business decision-making process. The study by the University of Hawaii reports that almost 88% of Excel spreadsheets are flawed, and the repercussions of these errors are critical indeed.

2. Control of the version

You may want to share the link of the Excel sheet with another for feedback, especially if you require the inputs of the heads of different departments. This triggers the pitfall of several overlapping editions of the same forecast. Such a situation implies that the risk of error is too high to be controlled. Avert this pitfall, store an edition of the forecast in the cloud storage, like Dropbox, Google Drive, or Office 365. This way, everyone can get access to the forecast at any time, and every change will be recorded in the same edition without any creation of different versions.

3. Do not allow the cash flow spreadsheet to lag in date.

It is a grave mistake to set up the forecast and then neglect it for the rest of the year. If you are seeking a precise short-to-mid term forecast for the operation of your business, then always make sure that the forecast is updated. Building an optimistic shortcut might feel tempting, but then that forecast ends up being far from reality after a few months or so. Recheck the forecast every week and make sure that the forecasts adhere to the reality of the business.

4. Do not lose sight of the detail

If you do not take note of the assumptions and thought process of entering the figures in the Excel sheet, then a few months later, the forecast will seem like a bunch of numbers and nothing more. And if multiple people work on the forecast without entering any detail, then the forecast will seem to be an unrealistic mess. To avoid this, you should take note of every bill, invoice, due date, and other details for creating the forecast of cash flow.

5. Spending excessive time on the forecast

It takes time to keep the forecast up to date, and Float users say that, on average, it takes almost a month every year to update the forecast. If you are a user of Xero, FreeAgent, or QuickBooks online, then there is software available to do the work for you. If not, then you can hire a bookkeeper to maintain the forecasting.

@ 2020 CashPundit. All Rights Reserved