You are here
Home > Data Analytics > Building an effective What-If analysis model

Building an effective What-If analysis model

I’ve often heard this; not only heard but advocated it. To make sense of your numbers, you need to separate the signal from all that noise. After all, with content explosion, there’s just too much noise around us these days. One needs to separate the wheat from the chaff, so to speak, in order to make sense of the facts.

Measuring the numbers is important, but what’s equally important is presenting them in a way that can be easily understood. The craft is called data visualization.

Anyone who’s had anything to do with data analytics will tell you that there are a few, basic ways to show off your data, including Comparison & Relationship. One can go on to add 2D, Temporal, multi-dimensional, etc, to this.

But this post is not about picking the right type of data visualization chart or tool. Instead, today, we will be looking at what it takes to design an effective ‘What-If’ analysis, an example of the relationship data visualization technique.

A What-if analysis is the method of mathematically establishing the effect of a particular process on an organization, given a predefined situation. In layman’s terms, this type of analysis “juices up” analytics by using a, ‘if this, then what’, method.

Like analytics guru Avinash Kaushik has put it in one of his recent blog posts:

“…. if you create exploratory environments it can be exceedingly accretive to decision-making if we build in what-if type models. Rather than stopping at an end-point, provide an option of doing some type of sensitivity analysis with the goal of prodding the audience to take action.”

Where What-If Analysis Is Used

Many of you out there would be familiar with financial modelling. A robust financial model provides an analyst the way to manage a business’ operations, and analyze the results in time (historically) as well as cross-sectional – measuring the company’s performance against that of the competition. The analyst will input both, historical data and assumptions about future performance, in order to arrive at various ratio analyses and other performance metrics such as profit margins, inventory, cash in hand and so on.

Using the What-If analysis, a company’s management can toy around with different circumstances, using any two assumptions, to observe its effect on the financial model. A What-If analysis also adds more flexibility to one’s financial and valuation models when it comes to analysis and presentation. What’s more, this kind of analysis can be used to predict the outcome in almost all possible departments – Marketing, Sales, even HR.

To cut short all the mumbo jumbo, a What-If analysis helps an organization best-determine its next move in the market, based not on some hunch but on cold, historical facts.

Here’s an example: The head of a marketing team adds these two variables to understand the impact on the bottom line: (a) Add 20 marketing people (b) Over the next 6 months.  The question he thus seeks an answer to is: What if I employ 20 marketing people in the next 6 months? Will it boost revenue, and if so, by how much?

Conversely, companies can use a What-If analysis model to also reduce their business risks, inherent in all operations. Risks come in all forms, and can catch business operations unawares – disruption in supply chain, weather breaks, are some of the examples. By inputting variables and analyzing the outcome, a company can surely reduce its impact on overall business.

Essentially, as Avinash says, What-If adds that “extra” factor; there’s no need to make data visualizations with dead-ends.

Efficient What-If analysis

Now that you are all know what can be done with a What-If or “scenario” analysis, you need to also know that in order to make your team understand the outcome, you need to ensure your situational modelling has the following:

  • Simple exploration
  • Minimal variables
  • Easy to grasp findings

Most data analysts will tell you there’s a tendency of “overkill” or the use of complex variables in any form of analysis, let alone in a scenario model. The questions you ask of your data to forecast the future are also the key. Too many questions or too complicated a query will surely complicate things.

If you are drawing up such a sensitivity model using the traditional Excel sheet method for example, rather than entering variables or formulas individually to compare results, you can set up a data table with one or two variables at most. Data tables, by the way, are one of Excel’s ‘What-If Analysis’ features. They are a range of cells in which you can change values and come up with different answers to a problem. Using data tables, you may create one or two variable tables; it all depends on the number of variables and formulas up for testing, the output of which can be seen in the results cell.

Next: Whether you use Excel or charts or graphs, your audience or stakeholders also define the explanation of the outcome. You have to make sure that the data visualization is made in a way that your end-audience will understand it, or else, the whole effort is likely to do down the drain. The visualization model must clearly indicate the baseline (actuality), from where the analysis departs to talking of the future.

These are some of the few pointers for you to keep in mind while drawing up a Whats-If analysis if you want it to be an efficient one.

Example In Excel Of A Simple What-If Model

I am using Excel to demonstrate how a What-If analytic model works, because the tool’s the simplest, most easiest way to explain. In Excel, when you change the input values, it re-calculates the resultant output, creating an instant What-If analysis.

In Figure 2, below, when I changed the loan term to 120 months instead of the original 240 months (Figure 1), the loan amount, monthly payment + other results changed. Which means, using What-If in Excel, you can easily calculate what will happen if your loan tenure changes. In Figure 3, I’ve changed the down payment  to 10% from the original 12% (Figure 1), and immediately, things change once again, in the results cell. This is a What-If analysis in its simplest form.

(Quick tip: How to get going in Excel? Just go to ‘Data’ in the header, click on it, and on the extreme right hand side of the bar, you will see the, ‘What-If’ tab.)
















Before signing off, I leave you with one such interesting scenario analysis done on ‘The Future of Burgers’ by Katrina Scicluna and Felix Zappe, at the Edward de Bono Institute, University of Malta. The key drivers identified by the analysts in this What-If scenario include a change in the production pattern of burgers because of an infusion of technology, and a change in consumption patterns including the emergence of organic foods. An eye-opener, indeed.

Leave a Reply


Sign up for our Blog!

Enter your email and stay tuned to the world of data analytics.