If you receive errors when attempting to view this white paper, please install the latest version of
"However, most companies that rely on spreadsheet programs for their
supply chain management needs are unaware that there are better alternatives- affordable inventory
planning solutions that provide strategic value, improve visibility to the supply chain management
process and propel company growth."
Source : Demand Solutions
Are Spreadsheets Sabotaging Your Profits?
is also known as :
Balance Sheet Spreadsheets,
Business Plan Spreadsheets,
Based on a broad study of U.S. corporations, Sanders & Manrodt (2002) determined that almost
90 percent of businesses rely on spreadsheets for their forecasting software. This study also concluded
that using commercial software packages for forecasting produced better performance than spreadsheets.
A recent advertisement showed a ransom note that said: "give up the spreadsheet and no one
gets hurt." The reality is that many firms are tied to spreadsheets for multiple business
applications and are afraid to let go. Spreadsheets are ubiquitous, relatively easy to use and
can be very sophisticated; but they are often uncontrolled, poorly designed and inadequately
maintained. This paper examines the history and use of spreadsheets in business applications,
the risks associated with the use of spreadsheets, and a practical example referencing demand forecasting.
The Evolution of the Spreadsheet
In 1979, Dan Bricklin and Bob Frankston published the first modern PC-based electronic
spreadsheet called VisiCalc. While previous row/column programs existed, the modern
WYSIWYG ("what you see is what you get") interface in VisiCalc created a user-friendly,
functionally rich solution that some credit with launching the PC business revolution.
Lotus took over the spreadsheet lead in the early 1980's with the introduction of Lotus 1-2-3,
which incorporated additional innovation and functionality. The current spreadsheet leader, Microsoft Excel,
was introduced in 1985 and was originally developed to support the Apple Macintosh. By 1987 Excel was
introduced for the IBM PC, and by the early 1990's Excel had surpassed Lotus 1-2-3 in both feature/function and sales.
Spreadsheets might have begun as basic row/column calculators, but they quickly matured
into feature-rich software that included sophisticated built-in mathematical functions and
programming language capabilities.
As spreadsheet software increased in functionality, so too did the sophistication of
its business use. Power users began to use spreadsheets for larger scale business solutions;
they were tackling tasks as diverse as financial statement analysis and supply chain planning.
As the business problems being addressed by spreadsheets grew, so did the spreadsheets themselves.
It is not uncommon to find spreadsheets with thousands or even tens of thousands of cells. Today,
the complexity of some spreadsheets rivals or even exceeds that of applications created in standard
Spreadsheets are the ultimate "end-user" business application. They are typically built
by business professionals and not information technology professionals. As such, it is unusual to find
a spreadsheet that has been designed, developed and tested using the rigorous methods in use by professional
The fact that mission critical business functions are now being supported by spreadsheets that have been
developed without formal methodologies has created a business risk that is not fully understood by most
corporations. As the business applications being supported by spreadsheets become more critical, the greater
the need becomes to manage the development so that the ultimate users can rely upon the results of complex calculations.
To help ensure accuracy, proper software procedures are required for "change control" or "version
control" for all applications. Simply put, no one is allowed to make a change to the software without proper
authorization, design approval, testing and then distribution of the new code to all users. Very few spreadsheets
can satisfy these requirements.
Within the world of financial reporting, the requirements imposed by Sarbanes Oxley, namely that corporate
management must attest to the accuracy and validity of its financial It is unusual to find a spreadsheet that
has been designed, developed and tested using the rigorous methods in use by professional software engineers.
statements, is going to force many users to re-examine how they create, use and maintain spreadsheets.
Spreadsheet solutions built by non-systems professionals in an uncontrolled environment will not pass muster with
auditors evaluating Sarbanes Oxley compliance. .
Spreadsheets created for non-financial functions face similar problems and risks — even if not
specifically covered by Sarbanes Oxley regulations.
The use of spreadsheets to perform complex business functions exposes a business to a number risks
and limitations, some of which are discussed below.
A detailed study by Panko (2000) revealed that many companies are using highly error-prone spreadsheets to
perform numerous functions. According to research over a long period of time, Panko has proven that spreadsheet
errors are common and result in meaningful, harmful business impacts.
Spreadsheet errors are common and often undetected. Panko's studies of spreadsheets in use by companies of many
different sizes have found error rates from 24 percent to over 85 percent. Error levels of this magnitude should be
unacceptable, and they will clearly have a measureable and non-trivial impact on business decisions based on the
Panko (2000) identified two categories of errors: quantitative and qualitative.
- Quantitative errors produce incorrect values elsewhere in the spreadsheet Quantitative errors can
then be categorized into three main types: mechanical, omission and logic. In addition, there are lifecycle
errors that occur as spreadsheets are updated, modified and enhanced. These errors can be introduced long after
the spreadsheet was designed and the original testing was completed
- Qualitative errors are flaws of design that may later cause errors through incorrect input or modifications
that do not maintain the integrity of the original spreadsheet.
The fact that these types of errors exist in spreadsheets is not surprising given the ad hoc nature in
which most spreadsheets are created and maintained.
Most spreadsheet authors have not been trained in the intricacies of system design, testing or development.
Issues such as security, documentation, version control and validation are neglected or not even considered.
Without methodologies in place to assure the use of standard practices, the error rates will continue to increase.
Also, without a formal testing/feedback system, spreadsheet end users might not realize the extent to which output
data is inaccurate.
Although companies are armed with this data, the use of spreadsheets in uncontrolled environments continues
to grow. The total negative impact on global businesses can only be imagined.
The use of spreadsheets to perform complex business functions exposes a business to a number risks...
A detailed study by Panko (2000) revealed that many companies are using highly error-prone
spreadsheets to perform numerous functions. According to research over a long period of time, Panko has proven
that spreadsheet errors are common and result in meaningful, harmful business impacts.
Difficult to Maintain
Even if the initial version of a spreadsheet is created successfully, it often will not remain that way
through future revisions, iterations and/or enhancements.
There are a couple of factors that contribute to the challenge of maintaining large spreadsheets.
The primary issues, which have already been discussed, are due to the lack of formal design, testing,
and maintenance disciplines on the part of spreadsheet users/developers.
Another important element is related to the lack of documentation and the inevitable migration of employees
to new job duties or even companies. Professional software developers budget for and invest significant time in
the documentation of their systems. This is a necessary prerequisite to allow the system to be maintained and to
allow ongoing support even if the original solution's authors are no longer available. Spreadsheet documentation
is a rarity in today's hectic business environment.
As has been discussed, spreadsheet programs can turn into very large and complex business applications.
The size and complexity of these spreadsheets, however, is not indicative of their functionality, sophistication
or business value.
Spreadsheets provide good functionality for certain classes of computationally intensive business applications
but may be weak or inappropriate for others. Collaborative applications, those requiring data integration, and
process-oriented business applications are examples where spreadsheets frequently do not work well.
Additionally, spreadsheet programs are almost exclusively built based on the business experience and expertise of
a single user, department or corporation. Compare this to more mature business applications built over many years by
professional software firms. Such applications include the "best practices" of hundreds or even thousands
of end users. It is often difficult to manage and gain true business value with internally developed spreadsheets.
Controlling input data is the final area of concern to be examined in this paper. Source information identification
should be documented on all reports, but it is rarely programmed into spreadsheets. Potential questions such
as: "Were the calculations run on current data or historical data?" "Where did the source data
come from?" and, "Which cells have been updated" will largely go unanswered. When looking at the
output of a report, the user should be confident what data was used on which version of the program and when it
was generated. In all cases, it must be possible to replicate the exact results, or the system will be suspect.
Businesses should view the uncontrolled nature of spreadsheets as a warning sign. There are few organizations
that thoroughly manage spreadsheet development and use, few that recognize the likelihood of spreadsheet errors,
and few that understand the ramification of using inaccurate results. According to Panko (2005), real life audits
found errors in 94 percent of the spreadsheets examined. In summary, it is fair to say that the use of spreadsheets
to make business decisions is potentially dangerous.
Innate Forecasting Dilemmas
No forecast is ever 100 percent accurate; nonetheless, forecasting should be a valuable tool for coordinating
the production and inventory functions throughout the supply chain. Too much or too little inventory creates
multitudes of problems, all of which reduce customer service levels and increase expenses. Other restrictions
such as the increasing cost of capital, declining margins due to global competition and growing lead times for
offshore production magnify the critical need for accurate forecasting.
Industry cannot build manufacturing capacity to meet peak needs. The cost of idle plant and personnel is too
expensive. By the same token, executives cannot expect to build unlimited amounts of inventory to be stored until
it is needed. Even eliminating concerns of obsolescence or shelf life - there is the cost of storing, managing,
finding and having to touch the inventory multiple times that all increase the cost of doing business.
The best-case scenario is to be able to operate a facility at or near capacity for the greatest number of
periods consistent with future product demand, while simultaneously minimizing storage and handling costs - all
consistent with maintaining targeted service levels. Accurate demand forecasts would enable proper production
schedules that will also allow for the planned down times necessary for maintenance and repair.
In a perfect world, the forecaster would magically produce an accurate picture of future product requirements.
Unfortunately, the supply chain world is far from perfect.
Many studies have examined the results of inaccurate forecasts on production planning and inventory levels.
The "Bullwhip Effect" has been studied and explained in many previous papers. Lee,
Padmanabhan, & Whang (1997) clearly describe the distortion that is caused in the supply chain based upon poor
In 1961, Jay Forrester at MIT defined the basics for what is now known as the Bullwhip Effect. He pointed
out that consumers do not buy based on demand that can be perfectly modeled. Businesses create forecasts to
approximate what they think actual demand will be. Most companies carry "safety stock" or some people
refer to it as "just-in-case-stock" to provide the ability to meet most customer demand. The difference
between the projected need and reality becomes more pronounced the further one is from the actual users. As end
user demand fluctuates, the variations are inflated as you move up the supply chain. This causes a "bullwhip"
effect on inventory levels.
Forecasting with Spreadsheets Highlights Limitations
It is in this environment that many forecasting departments have moved from large pencil and paper
spreadsheets to electronic ones. Although technology use aides the forecasting process, spreadsheets also
introduce the inevitable potential for error. Flaws in the spreadsheet can appear as overstocks or stock-outs,
unplanned overtime and variances in production and warehousing capacity utilization.
When the actual product movement is incorrect, forecasters look for answers in the algorithms and actual events
that took place. They then might try to adjust individual cells to force the right answers. Without control, each of
these guess-and-check fixes will reduce the accuracy of future analysis. As the numbers become less reliable, the
supply chain creates excess inventory and increases the use of expediting to protect its service levels (Klatch, 2007).
Instead of correcting the problem, a symptom is patched.
Accuracy can be increased significantly through the use of collaborative efforts within the supply chain.
The accuracy of a forecast is not only affected by the spreadsheet design, it is also impacted by the implementation
of limited functionality. The use of collaborative forecasting in spreadsheet environments serves as a notable example.
Accuracy can be increased significantly through the use of collaborative efforts within the supply chain. Forecasts
that are created within a single organization are often built with the limited facts or input available from within the
operation. Wilson (2001) provided examples of negative results from a lack of collaboration. He described how, at the
end of a product lifecycle, incorrect forecasts can lead to excessive inventory that might need to be scrapped.
Wilson continues by pointing out that there are also problems caused by the natural conflict in forecasting between
sub-organizations with different goals and requirements. One organization might want to minimize investment in inventory
while another might desire an unlimited supply of that very same inventory.
Better communication between members of the supply chain could eliminate or greatly minimize such problems. The most
accurate results come from forecasts that use information that leverages historical data and then collaborative review
from multiple supply chain constituents. What is required is a methodology to include data, insights and experience from
across the supply chain.
Information sharing among application systems has been encouraged through the development of
Electronic Data Interchange (EDI).standards While common in packaged systems, its use
by stand-alone spreadsheets is minimal and therefore reduces the ability to build collaborative solutions.
A Better Way to Forecast Demand
Based on a broad study of U.S. corporations, Sanders & Manrodt (2002) determined that almost 90 percent of
businesses rely on spreadsheets for their forecasting software. This study also concluded that using commercial
software packages for forecasting produced better performance than spreadsheets.
Although some businesses are resistant to abandoning their spreadsheets for fear of the unknown, stand-alone
and collaborative package software systems are available and work well. They have been designed to correct many
of the problems that have been reported with spreadsheets. These systems are documented, controlled, well-tested,
auditable and produce results that can be replicated. Also, the proper use of feedback and collaborative mechanisms
allow them to increase their accuracy over time.
In the area of collaborative forecasting, Vendor Managed Inventory (VMI) and
Continuous Planning Forecasting Replenishment (CPFR) are among the best success
stories in the industry. By allowing members of the supply chain to connect electronically to share real-time
information, it is possible to improve inventory level accuracy throughout the entire supply chain.
Epner (2007) studied the adoption of collaborative technology by hard goods distributors. The results from a
survey of 150 companies revealed that the most compelling reason to adopt collaborative technology was a requirement
of a trading partner. Absent that request, even companies that had proved the value of collaboration
would not push to use it with other partners who did not ask for it.
The most accurate results come from forecasts that use data that leverages historical
data and then collaborative review from multiple supply chain constituents.
Although some businesses are resistant to abandoning their spreadsheets for fear of the
unknown, stand-alone and collaborative package software systems are available and work well.
It is not clear why companies avoid implementation of proven technologies. What is clear is that
companies that have embraced collaboration methods and software tools have better information to make
decisions, realize efficiencies, and are better able to manage their demand streams. Therefore, companies
that embrace these technologies have an advantage over their competitors.
Sophisticated formulas are being tested and made available to the market as software companies receive
marketplace feedback and improve their system capabilities. Software vendors are able to spread the cost
of research and development efforts over many users. This not only reduces the cost, it also gains input
and expertise from a wide variety of end users. These development efforts are rarely duplicated in individual
According to Mark Smith of American Air Filter International, (M. Smith, personal communication, July 31, 2007)
American Air Filter improved forecast accuracy by switching from spreadsheets to a forecasting program. Accuracy of
the spreadsheet was only 60 percent at best. Measuring results from the more sophisticated packaged software yielded
results that approached a 90 percent accuracy rate.
Improvements in forecasting accuracy will reduce supply chain cost, increase service levels and greatly
increase profitability. Based on the limitations of spreadsheets highlighted in this paper and the ongoing
potential problems from undocumented, untested and uncontrolled spreadsheets, the continued use of spreadsheets
to manage mission critical business functions is an unacceptable risk for Twenty First Century businesses. In too
many businesses, spreadsheets may be sabotaging their profits. The stakeholders of every organization should insist
that corporate executives take greater responsibility for the output and use of spreadsheets that impact profit decisions.
Today, the requirements for the right inventory in the right place at the right time are being hampered
by spreadsheet forecasts that are based on improperly tested and maintained mathematics and undocumented or
inappropriate source data. Packaged forecasting software holds out the promise of a better controlled and trusted
environment. Adding in collaborative capabilities will give those supply chains that take advantage of the
technology a competitive advantage. Now that we know the old methods are broken, shame on us if we do not correct them.
Now that we know the old methods are broken, shame on us if we do not correct them.
Epner, S. (2007). Adoption of electronic invoicing by general line distributors.
Review of the
Electronic and Industrial Distribution Industries. The NEDA Education Foundation. 6(1), 52-73.
Klatch, W. (2007). How to use supply chain design to reduce forecast friction.
The Journal of Business
Forecasting. Spring 2007, 23-31.
Lee, H., Padmanabhan, V., & Whang, S. (1997, April). Information distortion in a supply chain: The bullwhip effect.
Management Science, 43(4), 546-558.
Panko, R. (2000).
Spreadsheet errors: What we know. What we think we can do.
the Spreadsheet Risk Symposium, European Spreadsheet Risks Interest Group, Greenwich, England.
Panko, R. (2005). Sarbanes-Oxley: What about all the spreadsheets?
European Spreadsheet Risks Interest
Group 2005, Greenwich, England.
Sanders, N & Manrodt, K. (2002). Forecasting software in practice: Use, satisfaction, and performance.
Retrieved from: http://www.forecastingprinciples.com/paperpdf/Sanders_and_Manrodt.pdf on August 23, 2007.
Wilson, N. (2001). Game plan for a successful collaborative forecasting process.
The Journal of Business Forecasting. Spring 2001, 3-6.
This document contains information which may be considered confidential. It is intended only for the
use of the individual or entity named on this cover sheet. The unauthorized disclosure, use or publication
of confidential information is prohibited.
Copyright © 2007 Brown, Smith, Wallace LLC and/or its affiliates or licensors. All rights reserved.
The Brown, Smith, Wallace LLC name and design marks are trademarks and/or registered trademarks of Brown,
Smith, Wallace LLC and/or one of its affiliates. All rights reserved. All other trademarks listed herein
are the property of their respective owners.
1050 N. Lindbergh Blvd.
St. Louis MO 63132
104 N. Main Street
St. Charles MO 63301
200 S. Wacker Dr.
31st Floor Chicago IL 60606