What if Analysis in Excel
What-If Analysis is a scenario based analysis technique. In MS Excel, it allows you to try different values against the set scenario to ascertain possible outcomes. In this tutorial, you will learn how to apply what-if analysis quickly and easily.
Let’s assume you have CD Shop and have 1000 CDs in your stock. You have set a price range from 100$ to 50$.
If you sell 60% for the highest price, cell D12 calculates a total profit of 600 * $100 + 400 * $50 = $80,000.
Create Different Scenarios
But what if the scenario shifts. What if you have sold 50% CDs on highest and 50% on lowest prices? Or what if you have sold the CDs on 70:30 ratio? Each different percentage is a different scenario. So it would be better to use the Scenario Manager to cater these scenarios.
1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.
The Scenario Manager dialog box will appear.
2. Add a scenario by clicking on Add.
3. Type a name (60% Highest value), select cell B6 (% sold at the highest price) for the Changing cells and click on OK.
4. Enter the corresponding value 0.6 and click on OK again (shown by default).
5. Now, add 3 more scenarios (50%, 70%, and 90%).
The Scenario Manager should be consistent with the figure below:
To easily compare the results of these scenarios, execute the following steps.
1. Click the Summary button in the Scenario Manager.
2. Next, select cell D12 (Total) for the result cell and click on OK.
Output is shown in the figure below:
Conclusion: if you sell 60% for the highest price, you will obtain a total profit of $80,000, if you sell 50% for the highest price, you will obtain a total profit of $75,000 etc.
This is how you can get the advantage of What-if.
Let’s assume that you need to find out that how many CDs you need to sell at the highest price, to get a total profit of exactly $95,000? This can be achieved using Goal Seek feature.
1. On the Data tab, click What-If Analysis, and select Goal Seek from the drop-down list.
The Goal Seek dialog box will appear.
2. Select cell D12.
3. Click in the ‘To value’ box and type 95000.
4. Click in the ‘By changing cell’ box and select cell B6.
5. Click OK.
You need to sell 90% of the CDs for the highest price to obtain a total profit of exactly $95,000.