Question and Answer: Making the Leap from Spreadsheets to Models
When spreadsheets become cumbersome and aren't flexible enough to work with you, not against you, then it's time to move up to work with a modeling tool.
- By James E. Powell
- August 12, 2009
Spreadsheets are readily available, but are they the best tool available for creating forward-looking business models such as forecasts, budgets, and strategic plans? Chris Houle, CEO of Quantrix, explains the difference between spreadsheet and modeling tools, the pros and cons of each toolset, and how business users can determine when it’s time to make the leap.
BI This Week: What is the difference between using spreadsheets and models?
Chris Houle: Spreadsheets are great for simple, two-dimensional analyses such as comparing the sales of products from one year to the next. In a spreadsheet, you can add a third column and compare the growth between the two years -- it’s easy. However, as soon as you find you are replicating structure multiple times, you are modeling. For example, many companies develop profit-and-loss statements in spreadsheets, with one tab for corporate data, another tab for data from the Americas, another tab for data from Asia, etc. All of the sudden, they have gone from a two-dimensional problem to one that contains multiple dimensions, and they need to replicate the basic structure of the P&L -- tracking data across a 12-month time period, with the same line items, subtotals, and formulas -- across multiple worksheets.
That is the definition of a structured model, and spreadsheets are not designed for that. They may be terrific for calculating expense and mileage reports, and other two-dimensional analyses, but they are not the right choice for a structured, multi-dimensional model.
How can business users know when spreadsheets are no longer adequate for their needs? What concrete indicators should they be on the lookout for?
The first indicator is time: When you find that you are spending more time maintaining the spreadsheet than you are doing actual work, it’s time to move to a modeling tool. Fixing formulas, inserting rows and columns -- all of that takes too much time, especially when those actions need to be taken across multiple worksheets and tabs. People who have been hired to make important business decisions should not be copying and pasting formulas; that is a huge waste of talent.
The second indicator is paralysis: If you are refraining from taking the next step in your analysis because the complexity of changing your spreadsheet is too daunting, that is another sign that you need a modeling tool. Do you want to expand your analysis from one to three years? Add new categories to your expenses to get a better handle on incremental vs. direct costs? Break down your sales analysis from a broad overview into individual channels? Those types of activities are valuable, and can make your company smarter, but many don’t make the leap because it involves too much time and effort to change their spreadsheets. That’s a problem.
The third indicator is information confidence: Spreadsheets contain a lot of errors, in part because users need to copy and paste information and formulas in multiple places. One misplaced formula can result in bad information, and the errors just get replicated over time. There are costs associated with those inaccuracies, and while few people like to admit that their spreadsheets have problems, the reality is that most of them do. How confident are you in your spreadsheets? On a scale of one to 10, with 10 being totally confident, if you are at less than seven, then you should not be using the spreadsheet to make important business decisions. It’s time to look at a modeling tool.
What role does business intelligence play in business modeling, and how should they fit together in the business decision-making process?
There are three important components that need to intersect in a good decision-making model: business intelligence, which is useful analytics of internal enterprise data; external market data, including competitive and economic information; and domain knowledge, which includes information your in-house experts have about your business and your future plans. Business intelligence is important, but it needs to be combined with the other two elements for business modeling.
The structure of your BI analytics often matches the structure of your decision-making process. For example, in a structured model, you will connect your internal data on products, business groups, and sales with the external business drivers and business knowledge into an analytic process. A structured modeling environment enables companies to feed their historical business intelligence directly into an intelligent decision making model that enables them to factor in information such as what new products you plan to introduce, and external market factors. Information on sales history during the past 10 years is important, but is not enough to develop a sales forecast for next year. So business intelligence is an important piece, but ultimately it is only once piece of the puzzle.
The best business modeling tools provide a dynamic connection to enterprise data. Time is wasted when business professionals need to re-query the enterprise database and re-populate a model every time they need updated information. In addition, that is another opportunity for errors to be introduced into a model, compounding the problem.
What are the biggest mistakes users make in pushing spreadsheet tools beyond their limits? What are the costs associated with those mistakes?
There are two categories of mistakes made when spreadsheets are pushed beyond their limits: computational errors and construction boundaries.
Computational errors crop up when formulas are not pasted into the proper cells, or when flawed formulas are copied throughout an entire spreadsheet. These types of errors are widespread enough that every business should question the viability of spreadsheets for business decision-making. The costs are obvious -- when companies base business decisions on flawed data, it eventually impacts the bottom line.
Construction boundaries occur when businesses stop developing their models because of the limitations of their tools -- for example, when their spreadsheet can no longer accommodate the additional dimensions required, or the users fear that the entire model will break if it’s pushed or expanded any more. In my opinion, this is the worst type of mistake that can happen when businesses push spreadsheets beyond their limits, and the most costly. Companies should never let their decision models become static and antiquated simply because they have become too difficult to change, especially since flexible and robust tools are readily available. The cost of having a stagnant business model has become even higher in today’s economic environment, where businesses need to make rapid decisions using the best information possible.
What are the pros and cons of making the change to modeling tools? How difficult is it to make the transition?
One of the cons of switching off of spreadsheets is overcoming inertia -- everyone has grown up with spreadsheets, they are relatively simple to use, and they are ubiquitous within an organization. There definitely is a resistance to change in any organization, and replacing spreadsheets is no different. In addition to overcoming the cultural issues, you need to change your approach to problem solving. With spreadsheets, you can dive right in. With modeling tools, users need to step back, and think through the problem more structurally before they start setting it up. Although, changing the thinking process up front might actually be considered more of a positive from a business perspective!
Among the top benefits of making the change to modeling tools is that you can have your smart people focused on business problems, not spreadsheet problems. You can’t underestimate the value of that. With modeling tools, you can create more robust decision models that enable your company to make better decisions, faster. You can have a seamless, direct, and dynamic connection with the data in your enterprise, which enables you to build analysis based on current data. With spreadsheets, it’s far too easy for users to go off on tangents and create a mess of disconnected worksheets and analyses.
The difficulty of making the switch depends on the user. We’ve found that people who have experience working with both databases and spreadsheets tend to find it easier to make the leap to modeling tools. People do need to be able to think abstractly. And attitude certainly helps -- if you feel that you could never give up your spreadsheets, chances are you probably won’t. We find that some people “get it” within moments, and others require more training. But the motivational factor is that once users realize that they get to spend more of their time focusing on solving problems rather than manipulating or fixing their spreadsheets, they have a breakthrough. Modeling tools enable people to free up their intellectual capacity.
What are the costs of staying on spreadsheet platform vs. the costs of moving to modeling tools? How can users measure the improvement from one platform to the other? Provide some real-life examples.
The biggest costs of staying on a spreadsheet platform are the restrictions that the tool places on your ability to make accurate, forward-focused, and rapid business decisions. Other costs are around the inappropriate deployment of company resources toward spreadsheet maintenance instead of business decision-making. Added to those are the costs incurred due to errors in the spreadsheet itself.
Users can measure the improvements gained via modeling tools in a number of ways, including freeing up resource time and energy, and enabling enhanced decision-making. Here are some examples:
One of our customers in the global insurance industry consolidated more than 70 individual spreadsheets into one Quantrix model to streamline its financial forecasting and budgeting process. The consolidated model contains information on 30 companies, each with approximately 2,500 general ledger accounts. It includes 19 departments operating in eight currencies, providing information across 15 quarters of data. The new model calculates across 44 million cells using only 359 formulas -- the same model in a spreadsheet requires 20 million formulas. That is a huge savings in time just for calculations alone. In addition, the company’s financial professionals can quickly and easily reply to board queries that were all but impossible to answer using its spreadsheet-based tools.
Another customer in the global energy industry needed to forecast a broad range of financial domains including energy sales, revenues, and energy purchasing costs in a highly volatile market. In migrating to a Quantrix model, the company has a dynamic risk management platform for identifying and mitigating risk exposure, and its cycle for updating a forecast was reduced from weeks to only hours. That gave the company an edge in making better decisions, and it improved its profitability. The company says it is much more efficient, and can do more, much faster, with fewer resources and at significantly less cost.
What is Quantrix’s approach to business modeling and analytics, and who is the target user?
Our approach to modeling and analytics is to provide a seamless transition between enterprise data and modeling, with a platform that is approachable for business users. Rather than relying on IT programmers to write queries and design models, Quantrix gives the business user the power to ask and answer questions themselves. Frankly, it is the technical complexity of many enterprise budgeting and planning solutions that drives business users back to spreadsheets. We give them a tool that they can directly use in conjunction with their business intelligence, business acumen, and business expertise -- they are a company’s most intelligent, useful resource for business decision making. Our tool eases the process and speeds the time required to get good answers.
Our target companies are involved in fast-paced markets that need tools that support dynamic decision-making. Our target user is someone on the front line of business decision making who needs to use real-time enterprise business intelligence combined with their own business acumen and expertise to get answers to the questions that will drive business success.