Whitepaper: You Can't Always Get from A to B

Abstract

The complexities of today’s business requirements have outpaced the capabilities of currently available two-dimensional (X and Y) spreadsheet software such as Microsoft Excel. Finance and data modeling professionals are challenged on a daily basis to deliver accurate and timely answers to business questions using a 25 year old paradigm. At stake is the viability of the business – the ability to make sound, informed decisions about budget planning, resource allocation, investments, expansion, or diversification. The financial community requires an easyto-use, “best-of-breed” software application that addresses the challenges of complex modeling tasks.

Discussion

Every day, business decisions are made that set the direction a company or organization will take in pursuit of its goals and objectives. By definition, decisions of this nature are forward looking and predictive. Obviously, these decisions are only as good as the data and information that supports them.

There is an oft-quoted saying from the software world, “Garbage In. Garbage out.” The same wisdom applies to the process of business decision-making. More explicitly: Quality decisions require quality information.

Professionals use modeling as a tool to assist them in making business decisions.

Modeling is the practice of distilling numerical data with predictive insights and business logic to derive answers to business questions. In essence, modeling is a process dependent upon two significant assets – the ability to hypothesize “what if” and, secondly, the tools to structure, manipulate and examine multiple variables in different combinations.

Modeling, as common practice within the financial community, has become increasingly burdensome. Financial professionals are being asked to examine business challenges in more detail and from more perspectives than ever before. Today’s business climate continues to become more and more complex. And yet today’s available software titles are not equipped to help with the dynamic needs of today’s financial models.

Existing software can be broken down into four general types:

enterprise resource planning (ERP)
server based business intelligence (BI) and reporting applications
stand alone desktop applications that serve job and/or industry specific purposes such as
tax preparation and business valuation
traditional spreadsheets

The core functionality of enterprise and BI/reporting applications is to automate business activities, process transactions, capture and manage historical data, and report on this data. The core functions of these applications do not support forecasting and planning. The “add-on” forecasting and budgeting features, which some of these systems do offer, have the following characteristics:

A “one size/shape fits all” approach to preparing budgets and forecasts
Rigid structure defined by existing accounting parameters
Implementation that often involves a significant business process re-engineering (BPR)
effort
Centrally managed architecture – End users often must make requests to a central group if they need changes or new reports

These applications do not support free form creation and modeling of data. In most cases, their functionality supports budgeting per accounting standards rather than true planning according to changing business dynamics.

The desk-top job and/or industry specific applications, such as tax preparation and business valuation software allow for the end user to setup, manipulate and access data directly. Many of these applications also offer some modeling functionality. However, these applications are narrow in focus, designed to address a specific tasks in a defined format. As soon as the business problem extends beyond the predefined conceptions addressed by the software, the user is left working with a traditional spreadsheet tool for its flexibility.

The scientific and mathematical communities have numerical and statistical modeling packages.
(e.g.., SAS and SPSS). These software products are oriented toward complex mathematical and
statistical tasks and require special training and/or experience to operate.

Because of the limitations of these specialized software products, financial professionals turn to the traditional two dimensional spreadsheet for significant modeling tasks – especially those involving scenarios that cannot be defined within the current accounting and financial systems.

The basic operation of the traditional spreadsheet application has not changed since its inception over twenty years ago. Yet, despite the lack of product improvements, spreadsheet usage has moved beyond a mere productivity enhancement. Spreadsheet modeling and an organization’s ability to harness its power is now a strategic advantage. Dr. Raymond R. Panko from the University of Hawaii has conducted extensive research on the use of spreadsheets and reports the following findings and statistics:

Spreadsheet usage is prolific – “Each year, tens of millions of managers and
professionals around the world create hundreds of millions of spreadsheets.”
These spreadsheets are quite large with one study finding the average model containing
6,000 cells.
And these spreadsheets are vital to business organizations. One study found that 42% of
surveyed companies’ indicated that the data resulting from spreadsheet models reached
the CEO.1

“These examples of rapid model change fundamentally alter the ability of firms to compete: they are of strategic importance. The spreadsheet in the hands of an end-user programmer can function as a strategic weapon.”2

Traditional Limitations

So what is it about traditional spreadsheets that make them unsuitable for complex modeling? Their limitations include:

Two-dimensional design – The basic spreadsheet was designed for creating a simple, known calculation, i.e., sales of several products over a three-year period. This is a twodimensional model. As soon as the professional decides to introduce other dimensions such as sales by region, grouping products into sub-categories, etc., the basic spreadsheet design begins to breakdown. The business professional must work within the confines of the two-dimensional design and the model is then developed based on the constraints of the software application rather than on the business needs.
Formulas written with arbitrary coordinates – Spreadsheets use the two-dimensional row and column coordinates to reference cells when writing formulas. This results in cryptic formulas that are difficult to write, understand and also maintain.
Logic is tied to the initial layout of the data – Because the formulas refer directly to cell positions, the logic of the model is inextricably tied to the presentation of the data within the spreadsheet’s grid of cells. It is therefore a time-consuming task to rearrange the presentation of the data to highlight or juxtapose particular numbers in the model. Similarly, when trying to present the data through graphs and charts, the user is constrained to work within the program’s “wizards”, which force the data to be displayed in the way it is depicted within the spreadsheet grid.
Lack of dynamism – For most business modeling, the middle two activities of the workflow described above is an iterative process. Indeed, that is one of the supposed benefits of
spreadsheets the ability to do “what if” analysis, to work through various scenarios. Therefore, the user oftentimes has only a vague idea of where they are headed when they begin to build their model; thus making changes, sometimes significant, is an inevitable part of the spreadsheet modeling process. However because of the cryptic
formulas and static layout of the data, changing models, especially large and complex ones, becomes a time-consuming task. Users must remember all the formula and cell
dependencies and make multiple insertions and deletions to ensure consistency. However, one must remember that the typical spreadsheet developer is a businessperson not a professional software engineer who is specifically trained in handling such programming details. Therefore, changing models also becomes a highly error prone task.
Replicated logic – The current spreadsheet application design necessitates that a formula reside in each cell that contains a calculated or dependent value as opposed to a “hardcoded” number. If a model needs to calculate monthly sales of hundreds of products over several years, each cell that represents one product’s monthly sales figure will contain a
formula, regardless of whether or not the formulas for all the cells are mathematically identical. This requires copying and pasting the same formula over hundreds, even thousands of cells and then maintain the resulting mass of formulas.
Row and Column limitations – The most widely used spreadsheet, limits the users to 65,536 rows and 256 columns. When initially designed, this was not considered a problem. However, as models have increased in complexity, this constraint is encountered more frequently.
File size – Due to the design of the traditional spreadsheet application that stores a formula in each calculated cell, models quickly become enormous files which are difficult to transfer with email or over a network.

Problems Caused

These issues cause several, often significantly detrimental, problems for an organization:

a. Errors – The cryptic formulas, the need to have a copy of a formula in each cell and the need to remember all cell and formula dependencies when making changes all lead to mistakes in the model. Dr. Raymond Panko has conducted the most extensive research on spreadsheet errors. His research reports that of the most recent (1997 and later) field audit studies of actual spreadsheets in organizations, 91% of the spreadsheets audited contained errors. This is especially alarming given that “most audits only reported substantive errors.” 3 Furthermore, his research also found that error rates occurred at this level regardless of who was doing the modeling; spreadsheet modelers with extensive experience produced just as many errors as those with little or no experience. 4 Given the critical importance of financial models and spreadsheet dependence in organizations today, the existence of such errors is startling. The ramifications of errors can be disastrous to organizations, especially in light of recent corporate accounting scandals and the resulting scrutiny on companies’ financial data. “A slip of the hand in a computer spreadsheet for bidding on electricity transmission contracts in New York will cost TransAlta Corp. $24-million (U.S.), wiping out 10 per cent of the company’s profit this year.” 5

b. Loss of productivity – Given the dynamics of today’s business environment, creating complex financial models and then maintaining and changing them is an inevitable part of a business professional’s job. However, because of the constraints imposed by the traditional spreadsheet program, most notably the two-dimensional design and the static layout of the data, spreadsheet modelers spend significant amounts of time trying to
“figure out” how to make their model work. Endless amounts of time are wasted trying to work around the limitations of the application. Given the average salary of these professionals, such a loss of productivity represents a significant cost to the organization. “A spreadsheet created in 3-months, full-time work by a highly-paid professional has a cost-accounting value in excess of $25,000.”

c. Lack of Auditability – Because the formulas are difficult to understand and the dependencies between cell values are hidden from easy viewing, it is difficult and timeconsuming for third parties to audit or verify the accuracy of the calculations. This exacerbates the problems of error rates and loss of productivity.

d. Author dependence – Because of the cryptic formulas and the transparent cell dependencies, models quickly become tied to one author. This prohibits collaborative model analysis and presents a particular challenge relating to staff turnover.

e. Lack of portability – The resulting large file size of most models limits their portability and their ability to be shared, as well as taxing valuable computer resources.

f. Limitations on business insight – As discussed previously, the ability to perform “what if” analysis using a spreadsheet can serve as a “strategic weapon” for a business. However, because of the time and difficulty in changing a model developed with a traditional
spreadsheet, many scenarios are not examined. The user is forced to decide which scenarios are worth the investment to analyze.

No Alternative?

In spite of all the problems, business professionals throughout the world use the basic spreadsheet package to accomplish their modeling tasks because there has been no product alternative.

The problem is two-fold:

Business professionals who are untrained in software design are required to create increasingly complex financial models that are vital to the decision making process within their organizations.
These models are being created using a technology that is not congruent with the current needs of the user.
Today’s financial professionals face ever increasing demands for more detailed, more
accurate, and more complex models of their businesses. The software that they have available to perform these tasks is based on 20 year old innovations which mapped the paper-ledger into software. The limitations of this software yield measurable performance and accuracy issues which are confronted on a daily basis by the financial community. Therefore, there is a void in professional software for an accessible, structured modeling tool that will increase the performance of financial professionals

Quantrix Modeler

Quantrix Modeler combines an innovative architectural approach. Separation of logic, structure and presentation – with a multidimensional calculation engine to deliver an elegant and powerful modeling tool designed for financial professionals.

With Quantrix Modeler, greater accuracy, transparency, flexibility and productivity combine to give better business insight, which ultimately leads to better business decisions.

Key features of the software that allow the professional to achieve this level of performance include:

Clear Logic – the conceptual logic behind the task shapes the model structure while clearly conveying its purpose.
Easy to Understand Models – plain English defines and expresses the formulas, making
models far easier to comprehend, trace and audit.
Write Once Formulas – individual formulas that appear many times throughout a model structure need only be defined once. In an equivalent two dimensional spreadsheet, this reduces reentering the same formula by as much as 100 to one.
Structural integrity. Variable flexibility – tables, dimensions and items within a model can be dynamically linked to assumption variables. Introduce or modify variables without changing the structure or rewriting formulas.
Multiple – Dimensional modeling – with drag and drop ease, configure, arrange and view the data in multiple combinations simultaneously. Quantrix Modeler is a desktop application and requires little or no infrastructure or maintenance support. And, its price point is significantly less than the more complex applications. More importantly, the product can improve productivity resulting in measurable cost savings. And it will improve financial professionals’ work products in terms of accuracy and business insights that lead ultimately to better business decisions.

Quantrix Modeler is a desktop application and requires little or no infrastructure or maintenance support. And, its price point is significantly less than the more complex applications. More importantly, the product can improve productivity resulting in measurable cost savings. And it will improve financial professionals’ work products in terms of accuracy and business insights that lead ultimately to better business decisions.

Request a Free Trial Book a Demo