Spreadsheets are holding you back

Spreadsheets are

holding you back

There’s a better way to plan, budget and forecast —

with less risk, less time and more accuracy.

Table of

Contents

Overview

Spreadsheet-based

planning: A “rough road”

plagued with potholes

Most common types of

spreadsheet errors

Three reasons

spreadsheets are holding

you back

The evolution of

spreadsheet planning: IBM

Planning Analytics

Conclusion

Overview

Companies large and small spend countless hours each year

developing the detailed plans, budgets, forecasts and reports

they need to drive strategic decision-making. It’s critical that the

information be timely and accurate, and that it can be easily updated

as market conditions change. However, most organizations rely on

spreadsheets to build these plans and reports – in fact – 58% of

midsize and large companies still use spreadsheets to manage their

planning and budgeting processes.

Although they’re a useful and

popular personal productivity tool, spreadsheets are poorly suited

for business planning and performance management. Errors are

common. And the broader the use of spreadsheets, the greater the

chance for a small error to be magniﬁed, potentially exposing the

organization to signiﬁcant risk.

IBM Watson Spreadsheets are holding you back 2

Spreadsheet-based planning:

A “rough road” plagued with

potholes

While spreadsheet technology has improved over the years,

serious problems persist, especially for those who rely primarily on

spreadsheets for planning and analysis. According to a 2020 study

by the Business Application Resource Center (BARC), only 8% of

Excel users experience “no signiﬁcant problems” using spreadsheets

for planning.

Many businesses who use spreadsheets have exposed themselves

to serious risks of spreadsheet error. And though in some cases large

companies can absorb these risks, small and medium businesses

usually cannot. Sad to say, the list of newsworthy episodes continues

to grow. Three examples from recent years illustrate the ongoing

hazards of reliance on spreadsheets.

– In early 2019, a large Canadian ﬁrm in the emerging legal cannabis

industry cited “spreadsheet error” as a cause of under-reporting

earnings. The company’s news release said “The correction was

made due to a formula error in the spreadsheet supporting the year-

to-date adjusted EBITDA loss calculation.”

– In the spring of 2018, a major liquor and wine retailer in the UK lost

60 percent of its market value – £500 million – in a matter of weeks,

due in part to an “arithmetic error” in a spreadsheet. The Times

of London commented, “Not for the ﬁrst time, human error with

spreadsheets has led to disaster.”

– In May of 2018, a large number of young doctors in the UK “had job

offers rescinded following an error in the administrative process.”

What happened? “A spreadsheet error was made in transferring

data from one system to another.” Besides embarrassing the

organization, the situation “caused ‘extreme anxiety’ for those who

have made life choices based upon these offers, including arranging

moves and putting deposits on new homes.”

When used for modern planning processes, spreadsheets and user

errors can cast serious doubt on the integrity of strategic plans. They

make plans and reports difﬁcult to maintain and often inhibit – rather

than facilitate – a collaborative, company-wide planning process. And

as business plans and analyses become larger and more complex, the

inadequacy of spreadsheet-based systems is only magniﬁed.

Most common types of

spreadsheet errors

According to experts and academics who have researched

spreadsheet effectiveness, three primary types of error typically

occur in spreadsheet models.

– The ﬁrst is mechanical error, which arises from mistakes in keying in

data, cutting and pasting, or other simple manual operations. While

a mechanical error may at ﬁrst appear minor, incorrectly entered

data can affect the integrity of an entire model. Furthermore,

planning models tend to grow in size and complexity as available

computing power increases. As the models grow, the errors created

within them increase in both volume and severity.

– The second type of error is logic error, where an inappropriate

algorithm is chosen or inapt formulas are created to implement

the algorithm. The resulting flawed calculations affect not only the

individual worksheet where the error appears, but the entire model,

as well.

– The third, and one of the most common types of error, is the error of

omission, where critical components are left out of a model entirely.

Errors of omission, of course, are very hard to spot. As a user labors

through multiple worksheets in a complex plan, the likelihood is

great that a critical item will simply not be inserted and its absence

will not be noticed. This type of error occurs because you can’t plan

at a granular level using spreadsheets, which means you’re forced to

combine data points.

Whether a given error is one of mechanics, logic or omission, the result

will be the same: a flawed model and inaccurate calculations, hence

an inaccurate or ineffective plan, forecast or report. While many large

corporate ﬁnance departments have adopted dedicated planning

solutions, at the department or line-of-business level, where many of

the most important daily decisions are made, the spreadsheet is still

the default planning tool.

41% of Excel users say spreadsheets cannot

handle their data volumes

IBM Watson Spreadsheets are holding you back 3

Three reasons spreadsheets

are holding you back

Aside from speciﬁc problems related to errors, spreadsheets present

other limitations and drawbacks when organizations try to use them

in performance management processes. Here are ﬁve of the most

serious issues with using the spreadsheet as a planning tool.

Failure of accuracy

– Data integrity and transparency: Disconnected spreadsheets offer

little in the way of data security or an audit trail to identify when,

where or why changes were made, all of which lead to multiple

versions of the truth. Consequently, conﬁdence in the numbers is

undermined. Poor version control can result in a consolidated plan

based upon inaccurate or incomplete data or—owing to a mismatch

of model structures—an inability to consolidate at all.

– Collaboration: Successful business planning depends to a large

extent on high levels of collaboration across teams. The greater

the cross-departmental input, the greater the accuracy delivered

in the plan. Due to error frequency and deployment difﬁculties,

spreadsheet-based planning engenders a constrained, siloed

process that represents only a small part of the organization.

Failure of agility

– Standardization: Spreadsheets, by design, are ad-hoc and

individual. Email substitutes for systematic workflow. And without

a guided, standard process, time is often wasted in “reinventing

the wheel” and waiting for others to contribute and review. It is a

laborious task for managers to check on the status of individual

contributions and ensure they are submitted in a timely manner.

The end result is a process that is limited by the pace of the

slowest participant.

– Speed: The business world is moving faster all the time. And to drive

fast decision-making in this competitive environment you need to

access and analyze large volumes of data and get answers quickly.

A spreadsheet-based planning and analysis process does not allow

organizations to alter plans, reforecast, or modify budgets in real

time. Making changes in a large, complex spreadsheet requires both

an inordinate amount of time and great care. The effort required to

consolidate hundreds of spreadsheets can inhibit quick reaction to

changes in markets or the actions of competitors.

– Aggregation and application maintenance: Even if individual

spreadsheets are error-free, the process of aggregating inputs from

multiple users is a major undertaking. A single person or task group

has to collect the numerous spreadsheets and consolidate them

into a single version, trying to maintain ﬁles that may be linked

together. If submitted models are not identical, the data will not

consolidate correctly.

Failure of scale

– Size: When a spreadsheet’s single data ﬁle is too large, it can make

the program run very slowly. Spreadsheets are simply poor at

dealing with large data volumes and merging multiple ﬁles. Users

spend more time on data collection and veriﬁcation than they do

on analysis.

– Granularity: Spreadsheets have a limited number of cells, which

prevents users from including all the granular data they need. If you

cannot look at data at a precise enough level (ie you can only see

product type, but not SKU), you will not be able accurately analyze

the data and plan appropriately.

– Capacity: Once you hit a certain threshold, spreadsheets can no

longer handle the amount of data and will crash. How many times

have you lost your work or had your entire system freeze because of

a spreadsheet with too many complex formulas?

IBM Watson Spreadsheets are holding you back 4

The evolution of spreadsheet

planning: IBM Planning Analytics

All these problems notwithstanding, the spreadsheet remains a

ubiquitous – and widely popular tool. Fortunately, there is a way to

overcome its limitations and leverage the capabilities of a modern

planning and analytics using your Microsoft Excel spreadsheets —

with IBM Planning Analytics.

This solution enables ﬁnance professionals, business analysts,

line-of-business managers and users on the front lines to create

more accurate and reliable budgets and plans. Users can either upload

their spreadsheets directly into Planning Analytics to get started, or

opt to use the IBM Planning Analytics for Microsoft Excel interface.

From there, explore and analyze data sourced from multidimensional

planning models including the IBM TM1 database.

Users can deﬁne, guide, optimize, automate and document the

planning process, ensuring that tasks are done on time, by the

right people. Planning users have access to a centrally managed

repository where data is shared, changes are tracked, and business

logic is protected, ensuring the “single version of the truth” that is so

essential for conﬁdent decision making.

IBM Planning Analytics enables organizations to harness the power of

collaboration with managed workflow. It provides data aggregation and

calculations, and allows users to review and analyze results and details

from thousands of data points in real time. Plus, users can store key

business logic and calculations in a secure application, where changes

are made only by designated administrators and are immediately

replicated to all participants, eliminating errors and conflicting data.

The IBM Planning Analytics for Excel interface helps organizations leverage

the existing spreadsheet skills of ﬁnance and business users.

In the BARC 2020 study, 2x the respondents

reported better quality planning results

when using a specialized software vs Excel

IBM Watson Spreadsheets are holding you back 5

Conclusion

The spreadsheet has been a useful personal productivity tool for

many years. But it lacks controls and auditability, and depends on

individual users to enter data accurately and generate complex

formulas and macros. These limitations make the spreadsheet

suboptimal for business planning.

That’s why the IBM approach is ideal. It enables users to explore

data, perform complex analysis and collaborate more easily

across the organization. With IBM Planning Analytics, users can

plan, budget, forecast and analyze, using familiar Excel tools and

techniques, and leveraging the software skills they already have.

Spreadsheets are and will likely remain a popular tool. They just

need a little help.

Save time and increase reporting accuracy: learn more about

IBM Planning Analytics

“Business beneﬁts regularly achieved with

Planning Analytics (and achieved more

frequently than with Excel) include increased

transparency of planning, improved

integration of different sub-budgets and

improved integration of planning with

reporting/analysis.”

BARC The Planning Survey 2020, IBM Planning

Analytics Highlights Report

IBM Watson Spreadsheets are holding you back 6

IBM Corporation

Route 100 Somers,

NY 10589

Produced in the

United States of America

August 2020

IBM, the IBM logo, ibm.com, IBM

Cloud, IBM Watson, OpenPages,

Promontory Financial Group,

and Watson are trademarks of

International Business Machines

Corp., registered in many jurisdictions

worldwide. Other product and service

names might be trademarks of IBM

or other companies. A current list of

IBM trademarks is available on the

Web at “Copyright and trademark

information” at www.ibm.com/legal/

copytrade.shtml

This document is current as of the

initial date of publication and may be

changed by IBM at any time. Not all

offerings are available in every country

in which IBM operates.

Provided “as is” without any warranty,

express or implied, including without

any warranties of merchantability,

ﬁtness for a particular purpose

and any warranty or condition of

non-infringement. IBM products are

warranted according to the terms and

conditions of the agreements under

which they are provided.

Statement of Good Security Practices:

IT system security involves protecting

systems and information through

prevention, detection and response

to improper access from within and

outside your enterprise. Improper

access can result in information being

altered, destroyed, misappropriated or

misused or can result in damage to or

misuse of your systems, including for

use in attacks on others.

No IT system or product should be

considered completely secure and

no single product, service or security

measure can be completely effective

in preventing improper use or access.

IBM systems, products and services

are designed to be part of a lawful,

comprehensive security approach,

which will necessarily involve

additional operational procedures, and

may require other systems, products

or services to be most effective. IBM

does not warrant that any systems,

products or services are immune from,

or will make your enterprise immune

from, the malicious or illegal conduct

of any party.

All statements regarding IBM’s future

direction and intent are subject to

change or withdrawal without notice,

and represent goals and objectives

only.

Endnotes

1. Ventana Research: Change in the

Ofﬁce of Finance, Evaluating Barriers to

Digital Transformation, October 2019

2, 6, 7. BARC’s The Planning Survey

2020 IBM Planning Analytics Highlights

Report

3. Patrick Hosking, Spreadsheets plus

human error can add up to disaster, The

Times, April 9, 2018

4. Matt Lamers, Cannabis giant Canopy

cites ‘spreadsheet error’ for under-

reporting adjusted EBITDA, Marijuana

Business Daily, February 21, 2019

5. Junior doctor jobs offers withdrawn

after admin blunder, National Health

Executive, May 8, 2018