Analytical Modeling

“War is ninety percent information.”

– Napoleon Bonaparte

Employees make dozens of decisions a day. How much should I buy? Where can I cut costs? How does pricing affect my sales? What is the cost-benefit of this investment? On and on.

Creating an analytic model is often the best tool to inform a better understanding of the situation, dynamics, options, and decision-making. I’ve created hundreds of models, some spanning just a few lines of an Excel spreadsheet, others that took months to build with hundreds of Excel pages, and drove the strategic direction of multi-billion companies.

The final product, beyond the recommendations, of many strategy projects, is a robust model that represented the abstraction of the scope of reality that was the focal of the problem solving. A core competency for strategic leaders is understanding how to utilize analytic models properly. We’ll go over some of the best practices they teach in the top business schools and strategy firms.

What is an analytical model?

An analytical model is always an imperfect abstraction of reality. Though, by abstracting reality, pulling apart the variables of a system, and freely manipulating and observing the dynamics of those variables, in the petri dish of a model, you can often generate more insights than through any other type of problem solving.

An analytical model is a data representation of a situation or system. Analytical models are primarily used to better understand a situation and inform decisions. There are four main elements to any analytical model, which include:

1. Output

Models are typically created to answer a particular question, and the answer to this question is the output of the model.

2. Parameters

The base assumptions of a model make up the parameters of a model. Assumptions are typically based on some sort of historical data or informed by facts. A model is only as good as the parameters and assumptions that drive it.

3. Decisions

Most analytical models are created to help make decisions, and potential decisions should be represented as the main part of any model.

4. Calculations

The calculations of a model are performed on inputs (i.e., parameters and decisions), to create the output of the model.

Below is a simple example of a model to better understand the future profit of a business.

In the above model, the parameters, decisions, outputs, and calculations are separated. This is a key attribute of good models since it makes the model much easier to follow, change, review, and think through. In this example, the parameters include the actual 2013 sales, gross margin percentage, and fixed costs, along with assumptions about future sales growth and gross margin percentages. The main decision is the fixed cost growth rate. The output of the model is the expected profit, given the parameters and decisions. And, the calculations are pretty simple, made up of the sales forecast, gross margin dollars, fixed costs, and profit.

As Steve Powell and Ken Baker, my fantastic MBA decision sciences professors, outline in their book Management Sciences, there are five main types of analysis that are conducted using models, including:

1. Base case analysis

Analyzing the current situation, under the most likely parameters and assumptions, and potentially projecting that current situation into the future.

2. What-if analysis

Evaluating the changes in outputs, given different scenarios and changes in parameters and decisions.

3. Breakeven analysis

Understanding the assumptions and decisions necessary to break even in profit, cost-benefit, or any other financial or investment situation.

4. Optimization

Determining the decision variables that will create the optimal value in the outputs.

5. Risk or simulation analysis

Analyzing the change in outputs given the uncertainty and probabilistic changes in parameters and potentially decisions.

In the above model, you can imagine performing each one of these analyses. For the different analyses, there are matching tools in Excel. For base case analysis, use regression tools to project historical trends into the future. For what-if analysis, use scenarios of parameters, decisions, and tornado charts. For breakeven analysis use the Excel goal seek tool. For optimization, use the Excel solver add-in. And, for risk or simulation analysis download a Monte Carlo 3rd party add-in.

What are the best practices in building models?

There are a lot of best practices in building models. We won’t get into Excel tutorials, but we will give you tips for building great models. Here they are:

First, Determine the Output

Define the problem you are trying to solve with the model and then define the output of the model. Streamline the process of making a model by working backward from what you are trying to solve.

Sketch it Out

Instead of jumping right into the data and building the model, take some time to sketch out the model. Given the output, what data do you need, and what are the main parameters and decisions? What is the general flow of calculations? What is the time dimension of the model? Sketching out and thinking through the main questions and blocks of a model will cut down the overall time of creating a great model.

Understand the Major Drivers

Almost every model has some select variables that are the main drivers of the output of the model, and these variables are the sensitivity variables. Understanding which variables are the sensitivity variables will give you a lot of insight into the dynamics of and the reality you are abstracting with the model.

Separate the Elements

Keep parameters, decisions, outputs, and calculations separated. It is one of the hardest, but one of the most important things to engrain into analysts. And, never hardcode assumptions within calculations.

Keep it Simple

There should never be more than 3-4 elements in an equation. If you find long, dense equations in your models, you should focus on staging the equations and creating new columns and rows to separate the staged equations, which will help in quality checking, auditing, making edits, updating, and taking people through your logic. Having long equations is like creating a knotted ball of yarn in your model.

Sanity Check

Once you create a model, spend some time changing parameters and decisions to check to see if the calculations and output make sense. Go through the calculations to ensure the logic and quality is sound. You can use the audit formulas in Excel to trace the variables in equations and check for errors.

DOWNLOAD A STARTER EXCEL MODEL

To get you going on modeling, download the free and editable starter Excel model.

NEXT SECTION: CHARTS

MCKINSEY PROBLEM SOLVING COURSE - LEARN MORE & ENROLL

Learn more about the best self-paced course on McKinsey Problem Solving

DOWNLOAD STRATEGY PRESENTATION TEMPLATES

THE $150 VALUE PACK - 600 SLIDES
168-PAGE COMPENDIUM OF STRATEGY FRAMEWORKS & TEMPLATES
186-PAGE HR & ORG STRATEGY PRESENTATION
100-PAGE SALES PLAN PRESENTATION
121-PAGE STRATEGIC PLAN & COMPANY OVERVIEW PRESENTATION
114-PAGE MARKET & COMPETITIVE ANALYSIS PRESENTATION
18-PAGE BUSINESS MODEL TEMPLATE

JOE NEWSUM COACHING

Learn more about Joe Newsum, the author of all this free content and a McKinsey Alum. I provide a suite of coaching and training services to realize the potential in you, your team, and your business. Learn more about me and my coaching philosophy.