What if Analysis in Excel

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$.

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.

select Scenario Manager

The Scenario Manager dialog box will appear.

2. Add a scenario by clicking on Add.

Add

3. Type a name (60% Highest value), select cell B6 (% sold at the highest price) for the Changing cells and click on OK.

OK

4. Enter the corresponding value 0.6 and click on OK again (shown by default).

OK again

5. Now, add 3 more scenarios (50%, 70%, and 90%).

The Scenario Manager should be consistent with the figure below:

show

Scenario Summary

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.

D12

Output is shown in the figure below:

output

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.

Goal Seek

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.

goal seek

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.

Click OK

You need to sell 90% of the CDs for the highest price to obtain a total profit of exactly $95,000.

90

$95,000