Cash Flow Forecast in Excel – Pitfalls to Avoid
Creating a cash flow forecast in Excel (or another type spreadsheet) can be a critical step towards proactively planning for the future of your business. But keep in mind that a manual forecast is tricky to get right and keep up to date. When you’re getting started forecasting in Excel, take care to watch out for these potential pitfalls:
1. Risk of error
With a cash flow forecast built in Excel, one accidental additional ‘0’ or missed decimal place could completely throw off your whole forecast, and potentially have serious impacts on business decisions. In fact, according to a University of Hawaii study, 88% of spreadsheets contain errors. Just check out this article for a look at the repercussions of spreadsheet errors.
Check out this article for a full run down of how to mitigate your risk of errors.
2. Version control
You will likely want to share your spreadsheet with others to get their input – especially if you need different heads of department to enter their forecasts. This heightens the likelihood of having multiple overlapping versions of the same forecast, which means your risk for error will be high. To avoid this, have one version of your forecast stored in the cloud – try somewhere like Office 365, Google Drive, or Dropbox. This will mean that multiple individuals can access your forecast at one time and you’ll all be working on one version. Be sure to limit the accessibility to ‘read only’ for people who don’t need to make any changes to the forecast.
3. Letting your cash flow spreadsheet fall out of date
It might be tempting to set up your forecast once and then leave it be. This is not the right way to forecast. If you want an accurate short-to-mid term forecast that you can use operate your business, you will need a forecast that’s up to date. It’s all too easy to build an optimistic forecast that ends up having no real bearing on reality after a few months go by. Be sure to avoid the ‘set it and forget it’ mentality by revisiting your forecast every week and ensuring your forecasts are still close to what’s really happening in your business.
4. Losing sight of detail
With a cash flow forecast in Excel, a number in a cell is simply a number in a cell, unattached to any detail. You might not remember which assumptions you made for the figures you entered a few months ago. And if you have multiple people building your forecast, the risk of unrealistic forecasts will grow.
You will need to keep sight of all your individual expected cash movements including upcoming bills and invoices, and their due dates, to build a truly accurate forecast. And if anything changes with one of those bills or invoices, you must remember to come back and update your forecast accordingly. To keep track of your assumptions, make a note on each cell listing the bills, invoices or transactions that make up the overall number. That way, when things change (because they will) you can more easily keep your forecast updated.
5. Spending too much time forecasting
The flip side of keeping a cash flow forecast spreadsheet completely up to date on a weekly or daily basis is the time it will take. Float users often tell us that before using our forecasting software, it would take them 2-3 days a month just to keep their forecast up to date. That’s about one month every year. Yikes!
If you find that keeping an accurate forecast is becoming too much of a time drain, consider bringing on an internal bookkeeper who can keep it up to date for you. Another option is looking into software to do the updating for you automatically. Float can do this for you if you are a Xero, QuickBooks Online, or FreeAgent user.