In Search of a Perfect Cash Forecast
Cash forecasters will never be confused with sharpshooters, but with today’s tools they have a reasonable chance of hitting the target, if not the bulls-eye. That’s quite an improvement. For many years, forecasting cash has been a critically important, but chronically challenging, task that has humbled many a treasury manager and frustrated many a CEO and CFO.
I should know – for several years, it was my job to forecast cash for a public multinational corporation with a dozen overseas subsidiaries. The forecasts relied on numbers I had to gather from dozens of people in the US and abroad, and from many different functions. Besides collecting finance and accounting numbers, I had to hear from human resources (HR), tax, and legal eagles from each subsidiary. They all had information about anticipated cash inflows and outflows that I needed to make a solid forecast.
Gathering that information required dozens of emails, faxes, and phone calls across multiple time zones and oceans. Not surprisingly, the people I needed to hear from were more interested in doing their own jobs than in helping me do mine. They often forgot to provide their numbers, so I had to remind them. Sometimes they were unavailable, so I had to guess at what they would have sent had they been available.
When I had collected most of the information, I opened that massive spreadsheet and manually keyed in all of the numbers I had received, along with those I had guessed at. I couldn’t delegate any part of this chore because there was no one to delegate it to. I was a one-woman treasury department; forecasting was just one of my many duties. If you’ve been there, you can feel my pain.
The results of these efforts were often downright ugly. The process was flawed, so the numbers were flawed. And the forecast was consistently off the mark, sometimes way off. Yet every month I had to go through the routine, and every month I felt the frustration of senior management that we had – once again – missed our forecast. At the end of the cycle, when my forecast had once again proved inaccurate, I couldn’t even work backwards to see where it had gone wrong and take steps to correct the mistakes in the future. We couldn’t track, so we couldn’t do much to improve.
Missing the forecast was more than demeaning to me, it was costly for the company. It meant that we had to keep a substantial cash cushion in our main operating accounts to cover inaccuracies; money that could have been more productive if invested in a money market account instead of a sweep. But without this cushion, we would run the risk of overdrafts in some accounts, even when there was excess cash sitting in others. We lived with the possibility that we might be paying interest on borrowed funds when we had surplus cash we could have used to pay down debt. We were afraid to use it because we couldn’t trust our forecast.
Our goal was simplicity itself – to know where our cash was coming from, when it would arrive, where it was going to, when it needed to be sent – and putting this knowledge to use for the company. But we were so far from achieving the goal that I had to wonder if I was wasting my time. I was far from alone in this dilemma. Many treasury managers admit that their forecasting process is manual and cumbersome. Some of them will admit that they’ve stopped trying to gather reliable numbers at the granular level and are simply issuing ‘guesstimates’. Some companies have given up trying to do cash forecasts altogether because the best forecasts they are able to do are so inaccurate that they are irrelevant.
Recent improvements to traditional forecasting tools, such as spreadsheets and treasury workstations, along with the introduction of new tools, make it worthwhile to revisit the subject of cash forecasting. Several options are available to assist today’s forecaster, each with its own pros and cons.
The spreadsheet remains by far the most available and most used cash forecasting tool. Every personal computer comes loaded with a spreadsheet program, and most financial professionals know how to use them, so spreadsheet forecasting can be done with little or no IT support. And spreadsheets are flexible and easy to customise. But there are drawbacks. The most significant downside is all the manual activity that is required, which means a high probability of human error and a lack of internal controls. After Sarbanes-Oxley, spreadsheets have become so suspect that auditors are likely to raise concerns about the reliability of numbers generated from spreadsheet applications.
Ideally, spreadsheets should be populated with data that flows automatically from the sources, but this seldom, if ever, happens in the real world. There are too many sources and too many formats. Consequently, spreadsheet forecasting still depends on a person compiling and entering most of the data manually, which is time-consuming and inevitably results in mistakes. Spreadsheet forecasts are also the handiwork of someone who wrote the original formulas. If that person should leave the company, his or her successor might find it very difficult to unravel the programming steps that went into the calculations.
Alternatives to the spreadsheet are treasury modules for enterprise resource planning (ERP) systems and treasury workstations. A company that has invested millions in an ERP system logically wants to maximise the return on its investment by getting the most possible use out of it. Treasury modules, however, have a reputation for being complex in their installation, confounding in their design, and difficult for anyone other than a software programmer to work with. If you can afford one, and it works for you, that’s great.
Treasury workstations tend to be more user-friendly. They eliminate most manual activity. Source inputs are transparent, so tracking is manageable. Bank data can be integrated seamlessly with company data. Rules can be built (covering rolling averages, recurring payments, etc) to automate the calculations significantly. Most of today’s workstations are programmed to do sophisticated forecast modeling. Multiple employees in various physical locations can have their own access to the forecasting module of the workstation and input their data directly when it doesn’t flow system-to-system, reducing the need for another person to receive and re-key it.
Treasurers now have more choices in how they can access workstation functionality. Traditional workstations are based on installed software. They are robust but expensive (typically around US$1m to set up, plus monthly fees and annual per-user licensing charges), and they require significant IT resources. In most companies, IT resources are scarce, so getting IT to commit to several months on a workstation installation may be out of reach. The idea that senior management of a company that doesn’t even budget for a US$50,000-a-year analyst to help with day-to-day treasury operations would spring for a US$1m workstation is, clearly, unrealistic. I learned that from experience. Traditional workstations remain most appropriate for large corporations with deep pockets and complex cash forecasting needs.
Online treasury workstations are a relatively new development. They are hosted on internet websites, so they require little IT support. There is no installation required, which substantially reduces the costs. Online workstations typically are purchased on a subscription basis. From an accounting perspective, that makes them operating costs rather than capital expenditures. From a funding perspective, that makes their costs more affordable because they are spread over time. Since they are online, any authorised user can access them any time from anywhere. As good as they sound, there are concerns. Since the system is hosted by a third party outside the corporate users’ firewalls, executives worry about security and reliability. (For example, what if the system crashes or the service provider goes under?) There are also privacy concerns about giving a third party access to sensitive information about a company’s cash.
Several large banks currently offer or are developing their own workstation solutions that alleviate some of the security and privacy concerns. After all, treasury staffs have already done the due diligence and chosen to trust the bank with the company’s cash, so giving the bank additional information about forecasted cash needs isn’t such a big step.
No tool will deliver the perfect cash forecast, but, especially in the current economy where cash is hard to come by, driving down the cash-flow highway blind is dangerous. Each company needs to consider its cash forecasting needs and make its best choice among the available solutions. Spreadsheets are readily available but labour-intensive and out of favour with auditors. Installed software solutions are simply too expensive for many companies. Internet-based access to hosted software is generally affordable and may offer a realistic way to move away from spreadsheets and automate and improve the cash forecasting process.