Apr 14, 2014

Top Five Business Modeling Pitfalls

A model can be as simple as a two line regression in a Microsoft Excel spreadsheet or as complex as a multi-dimensional, super-computer-powered predictive analytics system. Whatever the size, usage or software in which it is built, a model either tries to predict the future or fill in an existing gap in real-world information.

Anyone who has built a model knows that each one is different. Modelers each have unique mindsets, techniques and aesthetics to their work, which drive large amounts of new thought and creativity within the space. While new thinking is critical to making a model great, many modelers fall into pitfalls that prevent their work from reaching its full potential. In this article we will discuss five common pitfalls and offer suggestions on how to avoid them.


In the world of modeling, spreadsheet applications (such as Microsoft Excel) reign supreme. However, while spreadsheets are great tools, they are not great for everything. Processing large datasets, creating sophisticated visualizations, running repeatable algorithms, pushing data to the web and many other tasks are not best suited for the likes of Excel. So, take it from us – do your homework before you start a new model. As long as you are willing to learn something new, you will be surprised how much faster, smarter and more effective your models can become.


Finding the right data is almost always the first part of building a model. Without accurate, timely, and usable data to support it, the model lacks value and credibility. Often, however, the data source dictates the design and structure of the model. More specifically, modelers often create models that rely almost exclusively on one or two datasets, put calculations in the same sheets as the data, or even use similar formatting as the data source.

Why is this dangerous? We have all experienced a situation in which data sources change. This could be for any number of reasons: the source has modified its format; the source is no longer available or has become cost-prohibitive; a better source has come to light, etc. This is why it is critical to ensure that the data is separate from the calculations. This can be as simple as making sure the data is in a different sheet or maybe even a different file. No matter what, when building the model, be sure to ask the question – what happens if I need to change the data source?

Structural rigidity is the Achilles’ heel of any model. As things change in the real world, you must be able to build a model that will allow for as many anticipated changes as possible. It is unlikely you will be able to anticipate every potential modification, and endeavoring to do so is probably a waste of time. However, doing a bit of risk/reward evaluation ahead of time can save you days of redesign in the future.

Some questions to ask include:
• What will you do if your boss asks you to add new markets, products or competitors?
• Does your model consolidate multiple categories of data into one? What happens if you need to break them back out?

We have all had panic attacks as a result of model additions and enhancements. So, while you are building your model, be sure to think about what might happen if an internal or external customer comes calling with a new request – you will be glad you did.

Models can snowball very quickly from something simple to something complex to something unwieldy. Growth is usually a key indicator of a successful model – one that has been used heavily by stakeholders and is modified and appended as a result. However,models can also become unmanageable as ownership transfers from one stakeholder to the next, with each adding features while not fully understanding how the model works. The result is a rapidly growing model that often is poorly documented, has inconsistent
design, and contains outdated sections that are no longer relevant to the model’s goal, but are retained because removal requires more effort and understanding than ignoring.

As a modeler, it is critical to create boundaries around your model’s scope and end goals. It is also important to understand the limitations of the tool you have chosen, and to be willing/able to pivot in the case of new specifications. Remember – a big model is not a bad thing as long as you keep in mind that you are the one running the model – not the other way around.

As a general rule, any model that is not clearly documented is more of a liability than an asset. The most dangerous kinds of models are those that rely on specific knowledge that only exists in the brain of one to three people. These dangers can come to fruition when creators leave the company, are tasked with new responsibilities or are even just on vacation. There are many excuses not to create documentation; models created on a tight deadline are rarely documented, and perhaps they are simple enough that their creators think that documentation is not necessary. 

Whether it is due to deadlines, short-sightedness or even just general laziness, model assumptions, data sources, calculations and results should be documented
at all times. From a creator’s standpoint, this allows for low-stress handovers to new analysts, more amicable separations and ensures no panicked phone calls while lying on a beach on vacation. As a manager, this documentation reduces transition time, improves continuity and cuts the stress associated with employee turnover. When building a model, make sure your timeline accounts for documentation – it will pay off in a big way.