Scenario Manager

Excel allows you to create and save several versions of a worksheet based on what-if
data. Excel scenarios are a perfect when you need to analyze information from the
past like sales figures and try to forecast future outcomes. Part of the planning process
is considering different possibilities. Scenario manager allows you to create several
versions of your data, based on what-if models such as, best case, probable case and
worst case scenarios for your company.

Creating a New Scenario

You can create a scenario for any worksheet. Start off by identifying the specific cells
you will need in your scenario.

1. Click Tools  Scenarios.

2. In the Scenario Manager dialog box, click the Add button., to create a new
scenario.
3. In the Add Scenario dialog box, type in a descriptive name for your scenario.
4. In the Changing Cells box, type or select the cells you plan to change.

5. Click OK.

 The Scenario Values dialog box appears.

6. Set the values of all the cells you want to change in your scenario.

7. Click OK.
8. If you want to create more scenarios, repeat steps 4 to 8.
9. Display a Scenario

When you display a scenario, you change the values of the cells saved as part of that
scenario.

1. Click Tools  Scenarios.

2. Click the name of the scenario you want to display.
3. Click Show.

Edit a scenario

1. Click Tools  Scenarios.

2. Click the name of the scenario you want to edit, and then click Edit.

3. Make the changes you want.

4. In the Scenario Values dialog box, type the values you want for the changing
cells.
5. Do one of the following:

 To save the changes, click OK

 To return to the Scenario Manager dialog box without changing the current
scenario, click Cancel.
Delete a scenario

1. Click Tools  Scenarios.

2. Click the Name of the scenario you want to delete, and then click Delete.

Prevent Changes to a scenario

1. Click Tools  Scenarios.

2. In the Scenarios box, click the name of the scenario.
3. Click Edit.

 Do one of the following:

 To prevent others from making changes to your scenarios, select the Prevent
changes check box.

 To remove a scenario from the list in the Scenario Manager dialog box,
select the Hide check box.

4. Click OK.
5. In the Scenario Values dialog box, click OK, and then click Close.
6. On the Tools menu, point to Protection, and then click Protect Sheet.
7. Select the Protect worksheet and contents of locked cells check box, and make
sure the Edit scenarios check box is clear.

Create a scenario summary report

1. Click Tools  Scenarios.

2. Click Summary.
3. Click Scenario Summary or Scenario PivotTable.

4. In the Result cells box, enter the references for the cells that refer to cells whose
values are changed by the scenarios. Separate multiple references with commas.
Merge scenarios from another worksheet

It is easier to merge scenarios when all what-if models on the worksheets are

identical. All changing cells on the source worksheet must refer to the corresponding
changing cells on the active worksheet. Microsoft Excel copies all scenarios on the
source sheet to the active worksheet.

1. Open all of the workbooks that contain the scenarios you want to merge.
2. Switch to the worksheet where you want to merge the scenarios.
3. Click Tools  Scenarios.
4. Click Merge.
5. In the Book box, click a workbook name.
6. In the Sheet box, click the name of a worksheet that contains the scenarios you
want to merge, and then click OK.
7. Repeat this process if you want to merge scenarios from more worksheets.

Exercise 1 – Scenario Ex 1 Scenarios Ex 1

1. Start Excel.
2. From the CD provided, open up the file Scenarios Ex 1, found in the CD:\
Excel Textbook\Section 5\Scenario
3. Create a scenario using the default data given. Call this default scenario Normal.
4. Create a ‘Very_Good’ scenario by changing Monthly Investment to 3000 and
Interest Rate to 11%.
5. Create a ‘OK_Retirement’ scenario by changing Monthly Investment to 1500
and Interest Rate to 8%.
6. Create a Summary Report and place it on Sheet 2.
7. SAVE your file as Scenarios Ex 1. In your Student Folder.
Exercise 2 – Scenario Ex 2

8. Start Excel.
9. From the CD provided, open up the file Scenarios Ex 2, found in the CD:\
Excel Textbook\Section 5\Scenario
10. Create a scenario using the default data given. Call this default scenario Normal.
11. Create a ‘Best’ scenario by changing Mr. Pengu’s income to 18000 and
Mrs. Pengu’s income to 3000.
12. Create a ‘Worst’ scenario by changing Mr. Pengu’s income to 16000,
Mrs. Pengu’s income to 0 and Food expense to 500.
13. Create a ‘Excellent’ scenario by changing Mr. Pengu’s income to 19000 and
Mrs. Pengu’s income to 4000.
14. Display the Best scenario.
15. Create a Summary Report and place it on Sheet 2.
16. SAVE your file as Scenarios Ex 2. In your Student Folder.
Exercise 3 – Scenario Ex 3

Desert Safari Tours runs a small business taking tourist out to the desert for a day of
dune bashing and barbeque dinner. They would like to have an idea of what their
possible Revenues an d Expenses might be for next year. The problem is that it all
depends on the weather they get that year. They have asked you to help them come up
with several different scenarios given the various weather possibilities.

17. Start Excel.

18. From the CD provided, open up the file Scenarios Ex 3, found in the CD:\
Excel Textbook\Section 5\Scenario
19. Create a scenario using the default data given. Call this default scenario
Past_Year.

When imputing a long list of variables in a Scenario, it is often

easier if you name each cell you will be changing so that you know
the label of the data you will be typing in.

20. Create a ‘Good Weather’ scenario by changing:

 Length Of Season 190

 Avg Number of Customers 32
 Fee Per Customer 110
 Insurance 13000
 SUV Maintenance 60000
 Salary and Benefits 13000
 Admin and Marketing 30000

21. Create a ‘Very Good Weather’ scenario by changing:

 Length Of Season 200

 Avg Number of Customers 35
 Fee Per Customer 110
 Insurance 15000
 SUV Maintenance 70000
 Salary and Benefits 145000
 Admin and Marketing 35000

22. Create a ‘Excellent Weather’ scenario by changing:

 Length Of Season 220

  Avg Number of Customers 37
 Fee Per Customer 110
 Insurance 17000
 SUV Maintenance 75000
 Salary and Benefits 160000
 Admin and Marketing 40000

23. Create a ‘Very Hot’ scenario by changing:

 Length Of Season 160

 Avg Number of Customers 25
 Fee Per Customer 110
 Insurance 13000
 SUV Maintenance 55000
 Salary and Benefits 130000
 Admin and Marketing 30000

24. Display the ‘Very Good Weather’ scenario.

25. Create a Summary Report and place it on Sheet 2.
26. SAVE your file as Scenarios Ex 3. In your Student Folder.
Exercise 4 – Scenario Ex 4
Desert Safari Tours operates in three different countries, UAE, Oman and Bahrain. It
is trying to make a forecast of its next year’s Income in each of the three countries it
operates in. The owner of Desert Safari Tours Mr. Khalid has asked each of the
managers from the three countries to come up with three different scenarios ( normal,
best and worst ) on what they predict the amount of their cliental and expenses based
on possible weather conditions. Mr. Khalid then will take the scenarios from each of
the three managers and create a Merged Scenario master worksheet.
27. Start Excel.
28. From the CD provided, open up the file Scenarios Ex 4, found in the CD:\
Excel Textbook\Section 5\Exercise
29. Create a scenario for the UAE worksheet, using the default data given. Call this
default scenario Normal_UAE.
30. Create a ‘Best_UAE’ scenario by changing:

 Length Of Season 200

 Avg Number of Customers 32
 Fee Per Customer 110
 Insurance 13000
 SUV Maintenance 60000
 Salary and Benefits 13000
 Admin and Marketing 30000
31. Create a ‘Worst_UAE’ scenario by changing:

 Length Of Season 160

 Avg Number of Customers 25
 Fee Per Customer 110
 Insurance 13000
 SUV Maintenance 55000
 Salary and Benefits 130000
 Admin and Marketing 30000
32. Display the ‘Normal_UAE’ scenario.
33. Create a scenario for the Oman worksheet, using the default data given. Call this
default scenario Normal_Oman.
34. Create a ‘Best_Oman’ scenario by changing:

 Length Of Season 210

 Avg Number of Customers 40
 Fee Per Customer 120
  Insurance 15000
 SUV Maintenance 75000
 Salary and Benefits 150000
 Admin and Marketing 35000
35. Create a ‘Worst_Oman’ scenario by changing:

 Length Of Season 170

 Avg Number of Customers 30
 Fee Per Customer 100
 Insurance 12000
 SUV Maintenance 65000
 Salary and Benefits 140000
 Admin and Marketing 32000
36. Display the ‘Normal_Oman’ scenario.
37. Create a scenario for the UAE worksheet, using the default data given. Call this
default scenario Normal_Bahrain.
38. Create a ‘Best_Bahrain’ scenario by changing:

 Length Of Season 220

 Avg Number of Customers 35
 Fee Per Customer 115
 Insurance 14000
 SUV Maintenance 75000
 Salary and Benefits 140000
 Admin and Marketing 40000
39. Create a ‘Worst_Bahrain’ scenario by changing:

 Length Of Season 170

 Avg Number of Customers 27
 Fee Per Customer 100
 Insurance 14000
 SUV Maintenance 65000
 Salary and Benefits 135000
 Admin and Marketing 38000
40. Display the ‘Normal_Bahrain’ scenario.
41. Click on the Total worksheet.
42. Merge the 3 scenarios from each of the three worksheets onto the Total worksheet.
43. Create a Summary Report and place it on Total worksheet.
44. SAVE your file as Scenarios Ex 4. In your Student Folder.