Are Spreadsheets Impeding Your Forecasting Efficiency?

How many spreadsheets does it take to run your company?

This is a trick question.

If you know the answer you probably should be appalled at the number of spreadsheets it still takes to learn where your cash is, whether it is safe, whether you are meeting plan or whether your company is complying with various regulatory edicts.

If you do not know the answer you probably should be appalled by your company’s inability to create well defined policies and procedures designed to track compliance, forecast the future and / or borrow or invest in a rising interest rate environment.

The continued use of spreadsheets is not too difficult to understand. They are inexpensive, allow individuals to massage large amounts of data and are easy to use “out of the box”. However, they are difficult to audit, their outputs are not well integrated with other data sources, can present information in non standard formats and usually depend on a single individual to “program”.

In this age of web based, real time information, a dependency on spreadsheets can create unacceptable levels of operating risk, especially for those senior mangers within a company who are responsible for certifying, under penalties, that their company has well established financial controls, that they have examined them and that they work.

Today’s headlines are filled with examples of what can happen, on a personal or professional level, when good companies go bad. Just ask Fannie Mae. In October 2003 it was reported that Fannie Mae uncovered errors valued at over $1billion because it was not able to integrate various spreadsheets with its internal financial systems when marking its portfolio to market. The financial markets were not amused.

The Survey Says …

Since spreadsheets were computerized in the late 1970s they have been both the benefit and bane of every company’s existence. In a recent treasury technology survey of over 220 companies by the Financial Executive’s Consulting Group (the FECG) the survey found that over 64% of the companies continue to depend on spreadsheets despite the presence of alternative and more sophisticated financial systems.

FECG’s study was designed to determine the role of technology and the resources available to treasury as they struggle to add value and managing their company’s banking networks, cash balances, or access the capital markets to invest / borrow in a manner than minimizes financial risks.

Over 220 companies of all sizes responded to the 17 question survey, from small ones to Fortune 500 sized companies.

The survey also asked the respondents about their current titles to understand who within the company was most knowledgeable about the topic of treasury technology. By title the respondents were:

• 39% CFOs
• 17% Treasurers or VP finance
• 14% Directors of treasury
• 10% Corporate controllers
• 8% Assistant treasures
• 12% other titles (managers, analysts, etc)

As with any survey, the responses from the participating companies confirmed the existence of a set of widely used practices like a company’s dependence on one vendor, Microsoft, as the primary source of most treasury technology, but the survey also suggests that many companies need to better utilize the investments already made in their “state of the art” systems. In addition, the survey hints that the continued use of old technology is retarding a company’s ability to adopt new policies and procedures that will allow the company and its treasury function to adapt to the future and the need to remain competitive in the company’s chosen marketplaces.

Where am I going?

In considering whether any technology, including spreadsheets or other financial system can adequately manage a company’s financial health it is necessary to determine what goals are important to a company. The FECG survey asked each company 19 different questions concerning their treasury or business goals and asked the respondents to rank their importance. In addition, companies were asked whether they had recently completed or were in the process of completing any efforts associated with these important goals.

Regardless of company size, three issues were ranked most highly by the respondents as shown below.

1. Develop new methods to improve cash forecasting that accurately predicts our future liquidity requirements
2. Seek a mandate to reduce and / or more efficiently manage the company’s working capital and operating cash flows in conjunction with the company’s business units
3. Play a more active role controlling financial risks in accordance with the company’s corporate governance policies.

At first glance you may think that these goals are not new or that their importance would be fairly self obvious. What was most surprising was the relatively small amount of resources devoted to these efforts. In other words, these most important issues had the largest variance between importance and action by all companies in the survey (i.e. few companies had efforts either underway or completed to address these issues)

What was even more surprising was the inability of a company to reduce its dependence on spreadsheets as a primary means of controlling / accounting for treasury related transactions. While reducing dependencies was only the 6 th most important issue among the 19, there was more effort devoted to this effort than the first two issues mentioned above.

Bottom line, no matter where you are going, spreadsheets are unlikely to take you there.

How much is enough?

To undertake any change requires a set of goals and resources, often a combination of staff, systems and the assistance of a variety of vendors or financial institutions. Among the companies surveyed very few had the luxury of using “under-utilized staff” to manage change as the number of staff reporting directly to the treasurer is small. Even among the larger companies only 46% of the companies had more than 7 people. Among the midsize companies, the staff levels are closer to 3 to 5. For the smallest companies, over 40% had no treasury staff at all but depended on another area (mostly the Controllers area) to manage their cash, banking relationships, etc

What is today’ staff working on?

Assuming for the moment that the top 3 issues identified above are THE most important issues faced by treasury it is unlikely that treasury’s current staff could perform their current responsibilities and plan for / affect change at the same time. According to the survey, treasury’s staff resources are consumed by process oriented tasks (e.g. creating the daily cash position, disbursing / transferring funds, investing, borrowing, etc). Even among the larger companies with international operations, more time is spent managing domestic balances rather than cash balances on a global basis. Little time is left to plan for change.

Why is so little time left to plan or execute change? The survey suggests that the lack of integrated systems (i.e. no “straight through processing”) is the main culprit as staff is busy extracting data from one financial system, cutting and pasting it into another system then emailing or communicating the results to others. For example, despite the well established presence of ERP vendors and their systems:

• Almost 54% of all companies still have 2 – 5 major financial systems in use.
• Among the large companies 15% have 6 – 10 systems.
• 11% of the large company respondents did not even know how many systems they have

More systems increase the need to “interface” the outputs from one system to the inputs of another. Typically the response is to impose limits or standards to control / reconcile every interface at headquarters or in the field and to task individuals with the responsibility of identifying variances. Of course hiring additional staff may help alleviate this situation, but hiring more staff will not reduce a company’s operating risk or the underlying causes for hiring the staff in the first place.

Bottom line: Limited staff resources are spending all of their time processing instead of planning for the future, affecting a company’s future effectiveness and leaving little time to determine if today’s controls are adequate for tomorrow’s challenges.

Am I doing a good job?

All may not be doom and gloom if a company’s “numbers” are positive or the trend is up. If only it were that simple for treasury.

Regardless of the size of the company, when asked what indicator is most often used to measure corporate performance EBITDA was most cited followed by sales growth. Since EBITDA, by definition, ignores interest expense (a function of liquidity and market risk) and sales ignore the cost of using multiple currencies or any costs associated with cash flow it is no wonder that treasury’s performance goes unnoticed.

All the spreadsheets in the world can not overcome this oversight and can actually retard any acknowledgement of treasury’s performance because their continued use make it difficult to report to senior management a coherent view of the company’s uses and sources of funds across all of its lines of business.

It is not until you get into the large company’s that treasury’s performance is more closely watched because the larger company’s use additional performance measures such as return on capital, economic value added and other risk based measures that speak to the time value of cash flows and a company’s exposure to market rates. Also, as detailed below, the larger companies depend less on general purposes spreadsheets to measure their performance and that of their company.

Are my banks complementing or constraining my abilities? Let’s review progress to date:

• Interest rates will soon be rising (difficult to stop)
• Treasury has limited resources (maybe I can get some help here)
• The company has too many systems ( I spend too much time extracting data from / to spreadsheets to get that integrated picture I need)

If this situation sounds familiar, then seeking outside help, from your banks or others, could be warranted. After all, a bank is where your funds reside; every major bank has made large investments in modern, web based systems. It is only when you try to sum their data across all your banks that their systems show their limitations. The problem: companies continue to use a large number of banks, making data integration and forecasting trends elusive.

If you are like most of the companies in the FECG survey, you probably deal with 7- 10 domestic (i.e. US based) banks. If you are a large company you may be using over 20 banks. When geography is considered, the total number of banks used globally, especially among the larger companies, easily doubles. For example 11% of the large companies use more that 50 banks outside the US.

To control this large number of vendors at least 65% of the responding companies rely on bank systems for their basic cash management information and the raw data to create forecasts, repay loans, invest, etc, even though those same companies posses other more sophisticated systems like treasury workstations or ERP systems.

Why do companies continue to use methods (i.e. spreadsheets) that may have worked in the past when they were smaller, simpler companies? The survey results are unclear on this point, except for the fact that cost is an issue: ease of use and the relatively low cost of using spreadsheet based systems make it appear that a company is being efficient in relying on spreadsheets; however, low cost doesn’t necessarily translate into effective, value added output.

As mentioned above, most treasury staffs spend more time mapping bank data inputs into company reports than actually analyzing the results. In addition there are accounting entries to post, or at least send on to another area, which probably needs the data to be mapped into yet another spreadsheet format as the data needs to be fed into all of those financial systems.

What role should technology play to help treasury?

There are no right answers here, only alternatives. Technology vendors will try to get you to subscribe to a “one size fits all” solution by buying the proper number of “modules” that accomplish a set of standard tasks. There is nothing wrong with this approach as long as you realize that it is the system outputs that are the key to measuring the effectiveness of treasury, not its inputs.

Having modern treasury systems is the first step to lessen a company’s dependence on spreadsheets. After all, spreadsheets are very much a 1970’s technology when it was not important for one computer or spreadsheet to talk to another. Back then, there was no such thing as networks and “global” was less important. Even today, the only way that spreadsheets talk to each other is by being emailed as attachments to someone else within the company, hardly a process that contributes to real time updates or allows a process to be easily audited, what with so many spreadsheets flying around the company. When queried about the age of their treasury and other financial systems:

• More companies knew how old their financial (i.e. G/L) systems were than their treasury systems.
• 34% of all respondents said that their non treasury systems had been upgraded within the last 12 months
• Only 24% said that their treasury systems had been upgraded within the last 12 months

Another issue to wrestle with is the “Swiss army knife” vs. “best of breed” conundrum. If a company has a limited technology budget which type of system should it acquire: one that does everything (i.e. the Swiss army knife) or one that is a best of breed? There are no right answers here (especially without knowing a company’s special circumstances), but the larger companies have found that specialized treasury systems are preferable even to the best of the ERP systems or even systems developed in house. Among the different sized companies:

• 47% of large companies have specialized treasury systems acquired from non bank, non ERP vendors.
• 6% of medium size companies have specialized treasury systems; most depend on spreadsheets and bank systems
• 1% of small companies have specialized treasury systems; most depend on spreadsheets and bank systems

The FECG’s survey asked the respondents for the specific names of 3 rd party, treasury software in use. Among the larger companies they reported using over 22 different treasury systems, but the “big three” among the large companies were SunGard, XRT, SAP’s R3 Accounting and treasury.

Bottom line: As a company grows the use of the “same old” systems will not due. All companies should explore alternatives including non bank vendors to reduce dependence on non integrated (i.e. spreadsheet) systems.

What factors should I consider when exploring change?

When asked what factor was most important in deciding to change technologies all companies agreed: economic justification was key followed by an ability to integrate any new system with the old and a vendor’s ability to support the company’s business goals. Perhaps most interestingly, “state of the art” requirements ranked at the bottom of the list of 12 issues presented.

Finally when asked what was the single largest factor that prevented them from acquiring the latest modern systems the answer across all sized companies was uniform: cost, both initial and on-going) was the largest factor.

Conclusions

• Companies are still highly dependant on a series of non integrated systems (i.e. spreadsheets) to manage / control their financial processes.
• Older systems together with large banking networks, limited staff resources and a lack of good performance metrics present companies and their treasury areas with a host of problems as they struggle to comply with regulatory (e.g. Sarbanes Oxley) and business pressures
• Despite the presence of these market forces, few companies have initiated or completed efforts to control the overall size of operating their banking networks or upgrading their systems which could more adequately safeguard their company’s assets, manage their liabilities and control the costs, both internal and market related, associated with them.
• While it is too early to tell whether delays in implementing technological change have comprised any business goals it is possible that some senior managers could be taking on unacceptable levels of operating risk without being aware of it.
• Technology can be a useful tool as companies grow as long as the companies do not depend on the same old spreadsheets they used when they were smaller or operated in fewer countries or in fewer lines of business.
• An investment in new technology appears warranted if a company wishes to remain competitive.