Excel 2010 - Exercise on Scenario Manager
You have created a model that meets your needs. We offer several options to improve your performance. But which is better? You could try them in your model one after another. However, the scenario manager allows you to quickly compare several hypotheses, or scenarios for using the term of Excel, and generate a summary table with the results.
To follow this exercise, you can use the file excel2007-2010-exercises.xlsx on the demonstrations files web page.
Select the worksheet named Completed Model.
Here are three proprositions to increase the profitability for the quarter (cell D19)
Scenario 1: Growth Rate of 20%, but sales of the product 300 to start at $ 50 000.
Scenario 2: Growth Rate of 30%, but sales of the product 200 to start at $ 50 000.
Scenario 3: Growth Rate of 40%, but sales of the product 100 to start at $ 95 000.
The growth rate is in cell B21.
Which proposition is more interesting for the company? In this case, we want to know which scenario will give the highest cummulative profit that is in cell D19.
From the scenario manager window, press the Add button.
There are two ways to select multiple cells for a scenario.
In the Changing cells box, enter each cell address, separating them with a ";".
Press the OK button.
CAUTION : Always make sure that the cells that you select for the changing cells must be values; never formulas. Otherwise, the scenario manager will overwrite your formulas in preparation of the summary report and your model will no longer be valid.
We must now enter values for each scenario. Enter the appropriate valuesfor each scenario in the boxes.
Enter the informations for the second scenario.
In the Scenario name box, enter the text : Scenario 2.
Enter the informations for the third scenario.
In the Scenario name box, enter the text : Scenario 3.
The three scenarios have been entered. We must now generate a summary table. This table will show you a summary of the changing cells as well as the results of the cells you wanted to see.
From the Scenario manager window, press the Summary button.
We must now determine what cells you want to see the result in the synthesis report. These cells must always be cells with formulas.
For this exercise, select the cells D19, B25, B26 and B27.
Don't forget to select the first cell, press and hold the CTRL key and then select the other cells you wish to see the results.
These cells contain the cumulative profit for the quarter (D19), the minimum (B25), the maximum (B26) and the average (B27) profit for the quarter. All the cells that you select must contain a formula.
Make sure you choose the option Scenario summary.
A new worksheet will be generated with the scenario results.
The table above shows the results of the scenarios. The first column shows the cells you used for the scenarios and the cells you want to see the result in synthesis. The cells in gray are those whose valuesare different from the current values in the model. The report shows that the first scenario is most profitable for the company.
There is one more thing you should know about the scenario summary worksheet. It is not dynamic. If you change the values in the scenarios, this summary will not ajust it's values. You will have to delete this worksheet and regenerate a new one with the newest values.
This option has the advantage of allowing you to see the impact of a scenario over your entire model. But don't forget to cancel the Show option afterward. Otherwise, you will be stuck with the values of the scenario instead of those of your model.
Before we start, save the workbook.
This is an important safety measure. You can always return to this point if you forget to cancel the scenario viewing.
Go to Data tab.
You can navigate through your model to see the changes made by this scenario. It is important not to change numbers or to create formulas. You should just look your model as if the scenario was applied. Remember that you must cancel this view or you wont be able to return to your original data.
To cancel the scenario's view and return to your original model.
From the Quick Access Toolbar, press the Cancel button.
|You like what you read?
Share it with your friends.
This site is hosted by 1&1.com