Case 1 - What-If-Analysis

WHAT-IF Scenarios - Create Different Scenarios

BOOK STORE                                 

"Assume you own a book store and have 1000 books in storage. You sell a certain % for the highest price of 2500 and a certain % for the lower price of 100."                                            

1.    If you sell 60% for the highest price, revenue will be:-------------                                    

2.    WHAT-IF you sell 70% of highest priced book?                   

3.    WHAT-IF you sell 80% of the highest priced book?             

You can simply type in a different percentage (books sold) into cell XX (E9) to see the corresponding result of a scenario (revenues) in cell YY(D14). OR, use what-if analysis to easily compare the results of different scenarios.

WHAT-IF Analysis

1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.

2. Add a scenario by clicking on Add

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

4. Enter the corresponding value 0.6 and click on OK again.

5. ADD other scenarios – 70%, 80%, 90% by repeating steps 3 to 5

Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.

SUMMARY

Step 6:  Click the Summary button in the Scenario Manager.

Step 7: Select cell YY (D14:  total revenues) for the result cell and click on OK.

Step 8: RESULTS

Conclusion: if you sell 70% for the highest price, you obtain total revenue of: 1780000.

If you sell 80% for the highest price, you obtain total revenue of: 2020000

Excel's Goal Seek

What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly 1060000?

1.    On the Data tab, click What-If Analysis, Goal Seek.

The Goal Seek dialog box appears.

2.    Select cell E9 (YY)

3.    Click in the 'To value' box and type 1060000

4.    Click in the 'By changing cell' box and select cell D14 (XX).

5.    Click OK.

RESULT:  You need to sell 40% of the books for the highest price to obtain a total profit of exactly 1060000.