Attend FREE Webinar on Digital Marketing for Career & Business Growth Register Now

Data Analytics Blog

Data Analytics Case Studies, WhyTos, HowTos, Interviews, News, Events, Jobs and more...

What is What-If-Analysis in Excel and how is it used

4 (80%) 1 vote

What-if-analysis in Excel is a tool in Excel that helps you run reverse calculations, sensitivity analysis and scenarios comparison.

Decision making is a crucial part of any business or job role. When you can take decisions, which are informed based on data, the outcome of the business or project or task is always more in control.

Thus, What if Excel is used by almost every data analyst and especially middle to higher management professionals, to make better, faster and more accurate decisions based on data.

3 parts of what-if-analysis in Excel

  • Goal Seek – Reverse calculations
  • Data Table – Sensitivity analysis
  • Scenario Manager – Comparison of scenarios

Goal Seek in What if analysis

Let’s consider a simple dataset, where the invoice amount is Rs. 10,000, on which there is 9% CGST and 9% SGST, which thus amounts to a total of Rs. 11,800.

The customer asks you for a discount of Rs. 800 and thus the final amount should be Rs. 11,000.

Now, the equation in simple terms is, X + 18% = 11000, where X is the invoice amount, 18% is the total GST.

To find out, how much + 18% = 11000, we will use Goal Seek in What if analysis.

  • Place your cursor on the ‘Total’ cell
  • Under the ‘Data’ tab, click on ‘What-If-Analysis’, then on ‘Goal Seek’

  • In ‘Set Cell’, B4 will automatically be selected as you had kept your cursor on it.
  • In ‘To value’, enter the desired value, 11000 in this case.
  • In ‘By changing cell’, choose the value that needs to be changed, invoice amount in this case. Thus cell B1 is selected.
  • Press Ok

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

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

The ‘EMI’ calculated Rs. 19,786 is the outgoing amount per month. The value is negative as money is going out of your pocket.

But, you have a budget of only Rs. 17,000 per month. So, how much can you afford as ‘Price of Car’?

Put the Goal Seek values as above and you will know the Price of Car that you can afford.

This was calculations at multiple levels that Goal Seek in What-if analysis did, as it had to consider Available funds, ROI, Number of payments to reverse calculate and give you the answer.

That’s how powerful it is.

Data Table in What-If analysis

Data Table is used for Sensitivity analysis. What this means is basically, either 1 or 2 of the inputs in your model are changing, you want to know output based on each change.

Let’s take the same Car loan example as earlier.

Now, after applying the Goal Seek, you know you can afford to buy a car worth Rs. 7,15,526 instead of Rs. 8,00,000.

1-input Data Table

Then you go to the Car showrooms and research on more cars available. You find out 5 cars that you like, you want to know what would be the EMI amount for each of the car?

Car 1 – Rs. 5,54,000

Car 2 – Rs. 5,96,000

Car 3 – Rs. 6,24,000

Car 4 – Rs. 7,36,000

Car 5 – Rs. 7,94,000

Use what if analysis data table to find this.

Since only 1 input is changing, that is, Price of Car, we will use 1-input data table.

Make this structure in your Excel sheet next to your model.

In D3, you can write anything you want, doesn’t matter.

Next to that, in E4, put =B9. Basically, you are pointing to the formula that is used to calculate the EMI. Thus, here you have informed Excel that you want to calculate the resulting EMI for each value, using the formula in B9.

Now select this structure you have created and go to ‘Data table’ under what-if analysis in Data tab.

Since our options of Prices of Cars are put vertically in a column, we will use Column input cell. Select cell B1 to inform Excel that the 5 values are Price of Car values.

Press OK

Excel has calculated for you, the EMI for each change in Price of Car.

2-input Data Table

Similarly, you can have 2 inputs varying and still get the respective outputs.

So now you think about what if I change the duration of the loan, and compare for all these 5 cars?

Go to Data Table and select Row input cell as ‘No. of payments in months’ and Column input cell as ‘Price of car’

You will get the EMI amount for each combination in no time, without much effort or any complicated formulas.

Scenario Manager in what if analysis

Let’s say you are working in a Car Showroom in the Sales department. You have been given the task to plan the sales for the next quarter. You must build multiple scenarios and prepare a comparison of all the scenarios.

You make a model as below and then want to create multiple scenarios based on number of cars that you will be able to sell for each of the cars.

Under What-if analysis, go to scenario manager.

Click on ‘Add’

Let’s start building our 1st scenario

  • Scenario Name – Best Case
  • Changing Cells – select cells C2:C6 as these are the No. of cars that you will be able to sell, basically the variable cells
  • Press OK
  • Enter values for each Car

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

Similarly add 1 more Scenario and name it as ‘Worst Case’. The changing cells will ofcourse remain the same.

I have put in the below values for Worst case.

You can create many more scenarios like this.

Compare the scenarios

Now that your scenarios are created, let’s compare them.

In the Scenario Manager window, click on Summary.

You will now be asked for ‘Result cells’. Choose the Total Sales Value, cell D8, as that’s what you want to compare. If you want to compare more outputs, you can choose multiple cells here too.

A new Sheet will be created automatically on pressing OK which will give you a comparison of the Current values in your Sheet + the 2 scenarios you created.

Thus, in best case, the Total Sales is over Rs. 6 CR. Worst Case is 3.77 CR.

Now you can take your business decisions based on this output.

Conclusion

Thus, we can conclude that what-if analysis is an integral part of the tools any data analyst or middle to senior management uses. Using the 3 tools in what if, you can analyze data much quickly than if you try to do the same using formulas, thereby allowing you to take faster and accurate decisions.

  • Goal seek is for Reverse calculations.
  • Data Table is for 1 or 2 inputs changing, resulting changes in output.
  • Scenario Manager is to compare multiple business scenarios based on multiple inputs changing.

Many features in different versions of excel work differently, but what if analysis in excel 2010 works same way as what if analysis in excel 2013 and what if analysis in 2007 or 2016.

Founder & CEO at DLTC.co and Blogger at YouTube.com/ExcelRush. Certified Microsoft Office Specialist in Excel. Trained 2000+ working professionals and management students. Strength is training in Excel’s Advanced features & functions, programming using VBA in Excel, also Power BI & SQL. Multifunctional work experience of 11+ years. Dancer at Heart.

  • Data-Analytics

  • Your Comment

    Your email address will not be published.