Building an effective What-If analysis model

| 02 Nov 2017

Building an effective What-If analysis model (what if analysis model). ‘To make sense of your numbers, you need to separate the signal from all that noise.’

I’ve often heard this; I’ve even advocated it. After all, with the explosion of content, there’s just too much noise around us these days. One needs to separate the wheat from the chaff, so to speak, 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, also known as 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 & relationships. 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.

Want to dive deeper into scenario analysis? Get in touch with our experts.

What is a What-If Analysis (what if analysis)? 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

You might be familiar with financial modeling. A robust financial model provides an analyst a way to manage a business’s operations, and analyze the results over 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. From there, you can derive various ratio analyses and other performance metrics such as profit margins, inventory, cash in hand, etc.

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.

How What-If analysis is used

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 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 the supply chain, weather breaks, are some of the examples. By inputting variables and analyzing the outcome, a company can surely reduce its impact on the overall business.

Essentially, as Kaushik 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 know what can be done in What-If analysis, you also need to know what goes into understanding it. To make your team understand the outcome, you need to ensure your situational modeling has:

  • Simple exploration
  • Minimal variables
  • Easy to grasp findings

Most data analysts will tell you there’s a tendency to go overboard with complex variables. This makes figuring out possible changes much harder. 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 set up so you can plug and play with 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 so that your audience will understand it; otherwise, 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.

Example In Excel Of A Simple What-If Model

We can easily demonstrate how a What-If analytic model works in Excel.

In figure 1, we can see a basic Mortage Loan calculator we have designed. In the example, we see the monthly payment of a mortgage using our initial variables will be $1698. But what if we want to see how changing the terms of the loan affects our monthly payment?

In Figure 2, you can see I changed the loan term to 10 years (120 months). This recalculated the monthly payment to $2326. But, the Total Payment for the mortgage is reduced.

In Figure 3, we change the loan so we only pay 10% as a down payment. Now, the monthly payment is $1737, but we end up paying more in the long term.

This is What-If Analysis in its simplest form.

(Quick tip: Want to get going quickly in Excel? Select the ‘Data’ header tab, then select the ‘What-If’ tab on the far right side of the toolbar.)


 What-If analysis model


Before signing off, I leave you with the interesting scenario analysis of ‘The Future of Burgers’ by Katrina Scicluna and Felix Zappe, at the Edward de Bono Institute, University of Malta. Scicluna and Zappe were able to find two key drivers to how burger culture was changing. These two drivers were

  • new technology changing the production of burgers
  • the popularization of organic foods was changing consumer patterns.

An eye-opener, indeed.

Today’s successful companies are highly proactive in customer experience. If your business wants to taste the same degree of success, it needs to tap into the power of scenario analysis. To run your ‘What-if’ scenarios, you need to look no further. Our customer data platform Oyster is here to help.

To know more, click here

Express Analytics offers a wide range of services to customers. From Marketing mix analysis to RoI optimization.

Contact Us

Liked This Article?

Gain more insights, case studies, information on our product, customer data platform

Leave a comment

Your email address will not be published. Required fields are marked *

Copy link
Powered by Social Snap
  • Contact us

    Contact us