Share

A stretched a rope is attached between two rocks on which the climber crosses the gorge - representing the risks of using spreadsheets to manage your capital budgeting process.

Written by: Richard Frykberg

Whilst spreadsheets are likely to remain a useful complement to the capital budgeting process, the risks of using spreadsheets as a basis for business case development introduces significant pitfalls to optimizing your return on capital investment as outlined below.

Spreadsheets Restrict your Depth of Business Case Analysis

Rows and columns are great for standardized data. Unfortunately, your candidate capital investment projects are more nuanced. Investments may range from regular like-for-like replacement of depreciated plant and equipment to investments in transformational new technologies or products. For most investments, there are alternative options to consider. A more effective solution will capture the required level of information to make better project prioritization and capital allocation decisions.

Spreadsheets Rely on Static Assumptions

Spreadsheet templates are frequently used in the preparation of business cases. Without very careful administration, templates may diverge over time and contain inconsistent assumptions over key factors including the discount rate, tax rate, exchange rate, growth rate, and other costing assumptions. An online solution can ensure consistency of input parameters to ensure valid comparison of investment choices to reduce the risks of using spreadsheets.

Spreadsheets Contain Formula and Data Errors

The flexibility of spreadsheets is also their major weakness when relied upon to support major capital investment decisions. The risks of using spreadsheets includes organizational master data references may be invalid or out-of-date. Row or column insertions can easily break formulae. The more complex the project, the more complex the spreadsheet, the more critical the decision, the more likely that errors are introduced by someone during the business case development and approval process. A better solution will ensure validation of all data inputs and apply standardized calculations, especially of key metrics like Net Present Value (NPV) and Return on Investment (ROI).

Spreadsheets Consume Administrative Effort

To mitigate potential errors within spreadsheets and consolidate data requires significant administrative effort. Each project manager will have their own spreadsheets. Departmental managers create summary wish-lists for budget submission. Capex controllers consolidate departmental spreadsheets for enterprise-wide analysis. Each time data is copied and pasted between spreadsheets to support various analysis perspectives takes effort. Reconciling these different data sources take much longer. A modern capital budgeting solution provides a single source of truth to eliminate the administrative effort wasted on spreadsheet data transfer and reconciliation.

Spreadsheets are Hard to Access by Others

One of many risks of using spreadsheets is that they invariably grow to many sheets and may include external file references. Macros get introduced to automate task or determine scores. They rely on source system extractions, uploads and refreshes to be up-to-date. Consequently, the source is carefully protected, and when requested, a simplified snapshot is provided by the creator. A more efficient budgeting solution provides a single source of truth for all participants to directly maintain and access.

Spreadsheet-Based Portfolio Selection Over-Relies on Sorting

The essential task of capital budgeting is to determine the most important initiatives to fund given limited budget capacity and resources. With spreadsheet-based solutions, this portfolio optimization typically relies heavily on spreadsheet sorting capabilities. A more sophisticated capital budgeting solution applies advanced initiative scoring and portfolio selection algorithms to maximize your return on investment.

Spreadsheets are Unsuitable for Agile Portfolio Management

The risks of using spreadsheets for capital budgeting processes are that they are typically designed in support of the annual budgeting cycle, and are based around the exchange of spreadsheets containing lists of initiatives. This process is typically administratively cumbersome and time-consuming given all the file manipulation and version iteration required. No one wishes it could be done more often. With an online capital budgeting solution, however, the administrative burden is eliminated, initiatives can be assessed individually, and budget funding reallocated more dynamically in response to a changing environment.

Spreadsheets and Email are Inefficient and Slow for Approval Workflow

The approval process for spreadsheets typically relies on knowing who to send the spreadsheet to, and following up repeatedly until an answer is obtained. Where multiple items are included, it is hard for approvers to provide line-item responses. Spreadsheets are best reviewed on a laptop, limiting options for rapid mobile approval of routine items. An automated workflow solution, on the other hand, ensures compliance with delegation of authority policies and provides instant access to all the required information and enables quick confident approval at both portfolio and individual project level.

Spreadsheets with Commercially Sensitive Data may not be Secure

The risks of using spreadsheets is the security at the row or column level is very difficult to achieve. Often spreadsheet data is secured by limiting access to spreadsheet data altogether. Where access is granted to a spreadsheet for legitimate purposes, commercially sensitive data may be inadvertently exposed. And data updates are hard to detect and monitor. With a centralized capital budgeting solution, data access can be more effectively controlled, and all data updates reliably traced for audit purposes.

Spreadsheets become Dependent on Key People

Capital budgeting is a complex process that includes demand management, business case development, portfolio budgeting, forecasting, and variance monitoring. Many people participate in this critical business process, and extensive data is collected. Many risks of using spreadsheets derive from controlling the latest version of all this data which can become an administrative challenge and responsibility for managing the process typically resides with a central person or team. A specialized capital budgeting solution reduces the reliance on these key individuals to administer the process, but instead liberates them to focus on more value-added investment management activities.

Eliminate the Risks of Using Spreadsheets

If you currently rely too heavily on spreadsheets to manage your capital budgeting process, you should look at specialized solutions such as Stratex Online, with solutions that cover the entire capital budgeting process from capital expenditure requests, to project forecasting and reporting.