ULearnOffice.com

Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Excel
Office button
Home
Insert
Page Layout
Formulas
Data
Review
View
Developer

Excercises
Autosum
Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Goal Seek
Pivot table
Scenario Manager
Solver

Tutorials

Word
Excel 2003
Excel
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Share this page

AddThis Social Bookmark Button




Excel 2010 - Exercise on Scenario Manager

Introduction
Before we start
Create a scenario
View the sumary table
The View option and it's trap

Introduction

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.

CAUTION:
This command is only useful if you have a complete and operational model. Also, all variables must be on the same worksheet.

Before we start

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.

Create a scenario

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.
Product 100: B3
Product 200: B4
Product 300: B5

 

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.

Excel 2007 : Gestionnaire de scénarios

*Go to the Data tab.
*Under the button What-If Analysis, select the Scenario Manager option.

Excel 2010 - Scenario Manager Window

it's form this Scenario manager windows that you can create, change and delete scenarios. You can also merge scenarios that are located in other worksheets. The summary option will generate a new worksheet that will able you to compare the results of these scenarios on the important values in your model. The Show option allows you to view the impact of the scenario on your entire model. But don't forget to cancel this options. Or you won't be able to return to your original values.

Adding the scenarios

*From the scenario manager window, press the Add button.

Excel 2007 : Ajouter un scénario

The first step in creating a scenario is to name the scenario and determine what are the variable cells. These are the cells you want to change the values. For the first scenario, these are the cells B21 and B5.

*In the Scenario name box, enter the text : Scenario 1.
*In the box Changing cells, enter the cells B21; B5.

There are two ways to select multiple cells for a scenario.

There are two ways to select multiple cells for a scenario.

*In the Changing cells box, enter each cell address, separating them with a ";".
OR
*First click in the Changing cells box.
*Click the first cell you wish for the scenario.
*While pressing the Ctrl key, click on other cells that you need for the scenario.

*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 values​for each scenario in the boxes.

Excel 2010 - Add values to a scenario

*In the box B21, enter the value 0,2 or 20%.
*In the box B5, enter the value 50000.
*Press the Add button.

Enter the informations for the second scenario.

*In the Scenario name box, enter the text : Scenario 2.
*In the Changing cells box, enter the cells B21; B4.
*Press the OK button.
*In the B21 box, enter the value 0,3 or 30%.
*In the B4 box, enter the value 50000.
*Press the Add button.

Enter the informations for the third scenario.

*In the Scenario name box, enter the text : Scenario 3.
*In the Changing cells box, enter the cells B21; B3.
*Press the OK button.
*In the B21 box, enter the value 0,4 or 40%.
*In the B3 box, enter the value 95000.
*Press the OK button.

View summary table

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.

Excel 2010 - Scenario summary window

*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.
*Press the OK button.

Excel 2010 - Scenario summary worksheet

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 values​are 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's 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.

The Show option and it's trap

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.
*Under the What-If Analysis button, select Scenario Manager.
*Select one of the available scenarios.
*Press the Show button.

You can navigate through your model to see the changes made by this scenario. It's 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 Office 2007 : Bouton Annuller button.
OR
*Press on CTRL and Z.

 

You like what you read? Share it with your friends.





This site is hosted by 1&1.com