Create an effective what-if analysis model (2023)

Building an Effective What-If Analysis Model: Applications, Types, Benefits, Examples

In order for companies to test a hypothesis, they must aHypothetical analysis scenario. This what-if scenario technique helps organizations anticipate the outcome of a planned strategic or tactical action.

Also known as sensitivity analysis,hypothetical modelsThey are also used to understand whether changing the inputs in this model will also change the results.

To test the effectiveness of a strategy, data-intensive simulations such as what-if analysis are used to discover all or part of the behavior of complex systems in the enterprise while developing pre-established hypotheses, known as “scenarios“.

Thus, by creating a hypothetical world with a formulated scenario, what-if analysis examines how changes in one independent variable affect a set of dependent variables, which the analyst can then examine further.

Table of contents

  1. Where what-if analysis is used
  2. How to use what-if analysis
  3. Efficient what-if analysis
  4. Excel examples of what-if model analysis

However, this post is not about choosing the right chart type or data visualization tool. Instead, today we'll look at what it takes to design an effective what-if analysis, an example of the relationship data visualization technique.

Want to dive into what-if analysis?Get in touchwith our experts.

What is a what-if analysis

What-if analysis is the method of mathematically determining the impact of a particular process on an organization in a given situation.Put simply, this type of analysis “drives” the analysis in an “if this, then what” manner.

As analytical guru Avinash Kaushik put it in one of his recent papersBlog Entries:

"... When creating exploratory environments, it can be extremely beneficial for decision making if we create what-if models. Instead of stopping at an endpoint, you offer the opportunity to do some kind of sensitivity analysis to encourage the audience to take action.”

Where what-if analysis is used

Where areWhat-if analysis moduleneeded? Maybe you know your way aroundfinancial modeling. A robust financial model provides the analyst with a way to manage a company's operations and analyze the results over time (historically) as well as cross-sectionally and measure the company's performance relative to its competitors. The analyst will input historical data and assumptions about future performance. From this, you can derive various metrics analysis and other key performance indicators such as profit margins, inventory levels, cash balance, etc.

What-if analysis allows a company's management to play with different circumstances using any two assumptions to see their impact on the financial model.What-if analysis also gives financial and valuation models more analysis and presentation flexibility. In addition, this type of analysis can be used to predict the outcome in almost any department: marketing, sales, even human resources.

Create an effective what-if analysis model (1)

How to use what-if analysis

To cut all the jargon short, what-if analysis helps a company better determine its next move in the market based not on a hunch but on cold historical facts.

Example of what-if analysis: The leader of a marketing team adds these two variables to understand the impact on results: (a) Add 20 marketers (b) In the next 6 months. The question you're looking for an answer to is: What if I hire 20 marketers in the next 6 months? Will this increase revenue, and if so, by how much?

On the other hand, companies can use what-if analysis modelers to also reduce the business risks inherent in all operations. Risk comes in all forms and can surprise business operations: supply chain and weather disruptions are some examples of what-if analysis. By entering variables and analyzing the result, a company can certainly reduce its impact on the company as a whole.

How does what-if analysis improve project management?


As Kaushik says, What-If essentially adds that "extra" factor; Don't have todata visualizationswith dead ends.

Efficient what-if analysis

Now that you know what you can do in what-if analysis, you need to know what it takes to understand it. In order for your team to understand the result, you need to make sure your situation model has the following:

  1. simple scanning
  2. Minimum Variables
  3. Findings that are easy to understand

Most data analysts will tell you that there is a tendency to overdo it with complex variables. This makes it much more difficult to decipher possible changes. The questions you ask your data to predict the future are also crucial. Too many questions or a very complicated query will surely complicate things.

For example, if you create a sensitivity model using the traditional Excel spreadsheet method. Instead of entering variables or formulas individually to compare results, you can set up a data table with one or at most two variables. By the way, data tables are one of Excel's "what-if" features. They are a series of cells set up so that you can plug and play with values ​​and find different answers to a problem. Using data tables, you can create one or two variable tables; everything depends on the number of variables and formulas to be tested, the result of which can be seen in the result cell.

Next: Whether you use Excel or spreadsheets or charts, your audience or stakeholders also define the explanation of the result. You need to make sure the data is visualized in a way that your audience understands; Otherwise all the effort will probably go to waste. The visualization model should clearly indicate the baseline (current) from which to start analysis to talk about the future.

Excel examples of what-if model analysis

We can easily demonstrate how a what-if analysis model works in Excel with simple steps.

In Figure 1 we see a simple mortgage loan calculator that we have designed. In the example, we see that the monthly mortgage payment is $1,698 using our initial variable. But what if we want to see how the change in credit terms affects our monthly payment?

Create an effective what-if analysis model (2)

In Figure 2, you can see that I changed the repayment term to 10 years (120 months). This recalculated the monthly payment to $2,326. But the total mortgage payment is reduced.

Create an effective what-if analysis model (3)

In Figure 3, we've modified the loan so that we only make a 10% down payment. Now the monthly payment is $1737, but in the long run we will pay more.

Create an effective what-if analysis model (4)

This is an example of what-if analysis in its simplest form.

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

Before I say goodbye, I leave you with an interesting analysis of the"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 factors for the change in burger culture. These two drivers were

  1. new technology that changes the production of hamburgers
  2. The popularization of organic food changed consumption patterns.

A revelation indeed.

3 types of what-if analysis in Excel

There are 3 types of what-if analysis tools that come with Excel:

  1. scenarios
  2. data tables
  3. find target

Scenarios and data tables capture sets of input values ​​and are intended to decide a likely outcome. Goal Seek differs from scenarios and data tables in that it takes an outcome and projects it back to find the likely input values ​​that make up that outcome. If you are familiar with the result you want from a formula, but are unsure what input value the formula needs to get that result, use the Goal Seek function type.

You can use Goal Seek to decide what interest rate to set to set your loan goal.

A data table is a series of cells where you can change values ​​in some cells and get different answers to a problem.

Each scenario can contain up to 32 variable values. If you want to look at more than 32 values ​​and the values ​​only show 1 or 2 variables, you can use data tables. While a data table sometimes consists of only 1 or 2 variables, it can contain any number of different variable values. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you like.

Today's successful companies are very proactive in their customer experience. If your organization wants to demonstrate the same success, it must harness the power of scenario analysis. Look no further to execute your "what if" scenarios. OurOyster Customer Data Platformis here to help

Learn more,Click here

Top Articles
Latest Posts
Article information

Author: Aron Pacocha

Last Updated: 02/19/2023

Views: 5839

Rating: 4.8 / 5 (48 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Aron Pacocha

Birthday: 1999-08-12

Address: 3808 Moen Corner, Gorczanyport, FL 67364-2074

Phone: +393457723392

Job: Retail Consultant

Hobby: Jewelry making, Cooking, Gaming, Reading, Juggling, Cabaret, Origami

Introduction: My name is Aron Pacocha, I am a happy, tasty, innocent, proud, talented, courageous, magnificent person who loves writing and wants to share my knowledge and understanding with you.