What If Analysis in Excel (Table of Contents)
Overview of What If Analysis in Excel
What-if analysis in Excel is used to test more than one value for a different formula on the basis of multiple scenarios. For this, we must have data of such kind where for a single parameter we would have 2 or more values for comparison. Go to the Data menu tab and click on the What-If Analysis option under the Forecast section. Select the scenario manager and give a scenario name and select the cell which contains the scenario value. By this, we can enter multiple scenarios. Now from the Goal Seek option from What-If Analysis select the value we want to compare.
What if analysis is available in the “forecast” section under the “Data” tab.
There are three different kinds of tolls in What if analysis. Those are:
1. Scenario manager
2. Goal Seek
3. Data table
We will see each one with related examples.
Examples of What If Analysis in Excel
Here are some examples of What if Analysis in Excel given below
Example #1 – Scenario Manager
Scenario manager helps to find the results for different scenarios. Let’s consider a company which wants to buy raw material for their organization needs. Due to the scarcity of funds the company wants to understand how much cost will happen for different possibilities of buying.
In these cases, we can use the scenario manager for applying different scenarios to understand the results and take the decision accordingly. Now consider Raw material X, Raw material Y, and Raw material Z. We know the price of each and we want to know how much amount need for different scenarios.
Now we need to design 3 scenarios like High volume purchase, Medium volume purchase, and Low volume purchase. For that click on What if analysis and select Scenario manager.
Once we select the scenario manager the following window will open.
As shown in the screenshot currently there were no scenarios, if we want to add scenarios we need to click on the “Add” option available.
Then it will ask for the Scenario name and changing cells. Give scenario name whatever you want as per your requirement. Here I am giving “High volume”
Changing cells is the range of cells that your scenario values for different scenarios. Suppose if we observe the below screenshot. No. of units will change in each scenario that is the reason in changing cells we used C2:C4 which means C2, C3, and C4.
Once you give the change values click on “OK” then it will ask for the changing values for High volume scenario. Input the values for high volume scenario and then click on “Add” to add another scenario “Medium Volume”
Give name as “Medium volume” and give the same range and click Ok then it will ask for values.
Again, click on “Add” and create one more scenario “Low volume” with low values like below.
Once all scenarios have done click on “Ok” You will find the below screen.
We can find all the scenarios in the “Scenarios” screen. Now we can click on each scenario and click on Show then you will find the results in excel otherwise we can view all the scenarios by clicking on the option “Summary”
If we click on the scenario wise the results will be changing in the excel as below.
Whenever you click on the scenario and show the results at the back will change. If we want to see all the scenarios at a time to compare with others, click on the summary the following screen will come.
Select ‘Scenario Summary” and give the “Results Cells” here the total results will be in D5 hence I given D5, click on ‘Ok’. Then a new tab will be created with the name “Scenario summary”
Here the columns in gray color are the changing values and column in white color is the current value which was the last selected scenario results.
Example #2 – Goal Seek
Goal seek helps to find the input for the known output or required output.
Suppose we will take a small example of product sale. Suppose we know that we want to sell the product at an additional price of 200 than product cost then we want to know what is the percentage we are earning the profit.
Observe the above screenshot product cost is 500 and I given the formula for finding the percentage profit which you can observe in the formula bar. In one more cell, I gave the formula for the additional price which we want to sell.
Now use Goal Seek to find the profit percentage of the different additional price on product cost and selling price of the product.
When we click on the “Goal seek” the above pop up will come. In the set, the cell gives the cell position where we are going to give the output value here the additional price amount we know which we are giving in cell C4.
The “To Value” is the value at what additional price we want to cell 150 rupees additional to the product cost and the changing cell is B2 where percentage changes. Click on “Ok” and see how much percentage profit if we sell an additional 150 rupees.
Profit percentage is 30 and the selling price should be 650. Similarly, we can check for different targeted values. This goal seek helps to find the EMI calculations etc.
Example #3 – Data Table in What If Analysis
Now we will see the Data table. We will consider a very small example to understand better. Suppose we want to know the 10%, 20%, 30%, 40% and 50% of 5000 similarly, we want to find the percentages for 6000, 7000, 8000, 9000 and 10000.
We have to get the percentages in each combination. In these situations, the Data table will help to find the output for a different combination of inputs. Here in Cell B3 should get 10% of 6000 and B4 10% of 7000 and so on. Now we will see how to achieve this. First, create a formula to perform this.
If we observe the above screenshot the part marked with a box is the example. In A3 we have the formula to find the percentage from A1 and A2. So inputs are A1 and A2. Now take the result of A3 to A1 as shown in the below screenshot.
Now select the entire table to apply Data table of What if Analysis as shown in the below screenshot.
Once selected click on the “Data” then “What If Analysis” from that dropdown select data table.
Once select “Data table” the below pop up will come.
In “Row Input cell” give the cell address where the row inputs should input that means here row inputs are 10, 20, 30,40 and 50. Similarly, give “Column input cell” as A2 here column inputs are 6000, 7000, 8000, 9000 and 10,000. Click on “Ok” then results will appear in the form of a table as shown below.
Things to Remember
- What if Analysis is available under the “Data” menu on the top.
- It will have 3 features 1. Scenario manager 2. Goal seeks and 3. Data table.
- Scenario manager helps to analyze different situations.
- Goal seek helps to know the right input value for the required output.
- Data table helps to get results of different inputs in row-wise and column wise.
Recommended Articles
This is a guide on What if Analysis in Excel. Here we discuss three different kinds of tools in What if Analysis along with the examples and downloadable excel template. You may also look at the following articles to learn more –
