What is what-if analysis in Excel and how to use it? (2023)

What-If Analysis in Excel is an Excel tool that you can use to perform reverse calculations, sensitivity analyzes, and scenario comparisons.

Decision making is a crucial part of any business or professional role. When you can make data-driven decisions, you gain more control over the outcome of the deal, project, or task.

What if Excel was used by almost all data analysts, and especially mid-level and higher-level professionals, to make better, faster, and more accurate data-driven decisions?

3 parts of what-if analysis in Excel

• Achieve Goal - Inverse Calculations
• Data table: sensitivity analysis
• Scenario Manager - Scenario Comparison

Look for goals in what-if analysis

Let's consider a simple dataset where the invoice amount is Rs. 10,000 in which there is 9% CGST and 9% SGST giving a total of Rs. 11,800.

The customer is asking for a discount of Rs. 800 and hence the final amount should be Rs. 11,000.

The simple equation is now X + 18% = 11,000 where X is the invoice amount and 18% is the total GST.

To find out how much + 18% = 11,000, we use Goal Seek in the what-if analysis.

• Place the cursor in the Total cell.
• On the Data tab, click What-If Analysis and then click Goal Tracking.

• In Set Cell, B4 is automatically selected because you hover over it.
• In To Value, enter the desired value, in this case 11000.
• In "When changing cell", select the amount you want to change, in this case the invoice amount. Therefore, cell B1 is selected.
• pressure OK

Excel will reverse the calculation and immediately give you the Rs value. 9,322, which is +18% to exactly Rs. 11,000

This was a very simple example of using Goal Seek in what-if analysis. You can also use Goal Seek for more complex models, let's take an example of a car loan model.

The 'EMI' calculated Rs. 19,786 is the output value per month. The value is negative because the money is coming out of your pocket.

But it has a budget of just INR 17,000 per month. So how much can you pay as “car price”?

Set the target fetch values ​​as above and you will know the car price you can afford.

These were multi-step calculations that Goal Seek performed in the what-if analysis, as they needed to consider available funds, ROI and number of payouts to reverse the calculation and provide the answer.

That's how powerful it is.

Data table in the hypothetical analysis

The data table is used for sensitivity analysis. This basically means if 1 or 2 of the inputs in your model change, you want to know the output based on each change.

Let's take the same car loan example as before.

Now after using Goal Seek you know that you can buy a car worth Rs. 7,15,526 instead of INR 8,00,000.

Data table with 1 entry

Then go to car dealerships and search for more available cars. You will find 5 cars that you like. Want to know what the EMI would be for each of the cars?

Carro 1 - Rs. 5.54.000

Coche 2 - Rs. 5.96.000

Coche 3 - Rs. 6.24.000

Coche 4 - Rs. 7.36.000

Coche 5 - Rs. 7.94.000

Use the what-if analysis data table to find this.

As only 1 input changes, i. H. the price of the car, we use a 1-entry data table.

Make this structure in your excel spreadsheet next to your model.

In D3 you can write whatever you want, it doesn't matter.

Place next to it at E4 =B9. Basically, you are pointing to the formula used to calculate the EMI. So here you've told Excel that you want to calculate the resulting EMI for each security using the formula in B9.

Now select this structure you created and in what-if analysis under the Data tab go to "Data table".

Since our car price options are placed vertically in a column, we'll use the column's input cell. Select cell B1 to tell Excel that all 5 values ​​are car price values.

pressure OK

Excel has calculated for you the EMI for each change in the price of the car.

2 input data table

Likewise, you can have 2 inputs that vary and still get the respective outputs.

What if I change the loan term and compare these 5 cars?

Go to data table and select row input cell as “No. of payments in months' and column input cell as 'Car price'

You'll get the EMI value for each combination quickly, without much effort or complicated formulas.

Scenario manager in what-if analysis

Suppose you work at a car dealership in the sales department. You are tasked with planning sales for the next quarter. You need to create several scenarios and prepare a comparison of all scenarios.

You model as below and want to create multiple scenarios based on how many cars you can sell for each of the cars.

Go to the Scenario Manager under What-If Analysis.

Click "Add".

Let's start building our 1^{to connect}road map

• Scenario Name: Best Case
• Cell Shift - Select cells C2:C6 as this is the number of cars you can sell, basically the variable cells
• pressure OK
• Enter values ​​for each car

I entered the values ​​as above, you can enter whatever you like.

Similarly, add 1 more scenario and name it "Worst Case". Of course, changing cells remains the same.

I have given the values ​​below for the worst case.

You can create many other scenarios like this one.

Compare the scenarios

After creating your scenarios, let's compare them.

In the Scenario Manager window, click Summary.

You will now be asked to provide "result cells". Select Total Sales Amount, cell D8, as this is what you want to compare. If you want to compare more results, you can also select multiple cells here.

When you press OK, a new sheet is automatically created, offering you a comparison of the current values ​​of your sheet + the 2 scenarios you created.

Therefore, the total turnover exceeds Rs. 6 CR at best. The worst case is 3.77 CR.

Now you can make your trading decisions based on that result.

Diploma

Therefore, we can conclude that what-if analysis is an essential part of the tools that every data analyst or middle or senior management uses. All 3 what-if tools allow you to analyze data much faster than trying to do the same thing with formulas, allowing you to make faster and more accurate decisions.

• Achieve goal is for inverse calculations.
• The data table is for changing 1 or 2 inputs resulting in changes in the output.
• Scenario Manager is designed to compare various trading scenarios based on changing multiple inputs.

Many functions in different versions of Excel work differently, but what-if analysis in Excel 2010 works the same as what-if analysis in Excel 2013 and what-if analysis in 2007 or 2016.

lead toData analysis course with ExcelBecome a proficient data analyst.