Search Contextures Sites
Custom Search

Excel 2003 Scenarios -- Create and Show

How to create and show Scenarios to store and compare different versions of the data in a worksheet.

Introduction

Set up the Excel 2003 Scenarios Worksheet  

Create the first Excel 2003 Scenario  

Create the second Excel 2003 Scenario 

Show an Excel 2003 Scenario  

Download the Sample File

For Excel 2007/Excel 2010 instructions, see Excel Scenarios -- Create and Show

Introduction

You can use Scenarios in Excel 2003 to store and compare different versions of the data in a worksheet.

For example, when preparing the annual budget, the Marketing and Finance departments may have different forecasts for sales and expenses. Instead of creating different files, store each forecast as a Scenario, then print them separately, or compare the forecasts side-by-side.

Set up the Excel Scenarios Worksheet

Although Excel scenarios can be complex, a simple example is used here.

  1. Delete all sheets except Sheet1
  2. Rename Sheet1 as Budget
  3. On the Budget sheet, enter the Finance budget, as shown at right
  4. Name the following cells (there are Naming instructions here: Name a Range). Naming the cells is not required, but will make it easier to manage the scenarios, and read the reports:
    • Name cell B1 as Dept
    • Name cell B3 as Sales
    • Name cell B4 as Expenses
    • Name cell B6 as Profit
  5. In cell B6, enter the following formula:
          =Sales - Expenses
A
B
1
Budget 2006 Finance
2
   
3
Sales $ 3,500,000
4
Expenses $ 2,750,000
5
   
6
Profit =Sales - Expenses

Create the First Excel Scenario

  1. From the Tools menu, choose Scenarios.
  2. In the Scenario Manager, click the Add button
  3. Type name for the Scenario. For this example, use Finance.
  4. Press the Tab key, to move to the Changing cells box
  5. On the worksheet, select cells B1
  6. Hold the Ctrl key, and select cells B3:B4
    Note: There is a limit of 32 changing cells
  7. Press the Tab key, to move to the Comment box
  8. (optional) Enter a comment that describes the scenario.
  9. Click the OK button

  1. The Scenario Values dialog box opens, with a box for each changing cell.
  2. You could modify these values, but in this example they contain the values currently on the worksheet, and don't need to be changed.
  3. Click the OK button, to return to the Scenario Manager.
  4. Click the Close button, to return to the worksheet  

Create the Second Excel Scenario

  1. To prepare for the Marketing scenario, change the values in cells B1, B3 and B4, as shown at right
  2. From the Tools menu, choose Scenarios.
  3. In the Scenario Manager, click the Add button
  4. Type name for the second Scenario. For this example, use Marketing.
  5. Press the Tab key, to move to the Changing cells box
  6. On the worksheet, select cells B3:B4
  7. Press the Tab key, to move to the Comment box
  8. (optional) Enter a comment that describes the scenario.
  9. Click the OK button
  10. The Scenario Values dialog box opens, with a box for each changing cell.
  11. Click the OK button, to return to the Scenario Manager.
  12. Click the Close button, to return to the worksheet  
A
B
1
Budget 2006 Marketing
2
   
3
Sales $ 3,900,000
4
Expenses $ 2,450,000
5
   

Show an Excel Scenario

Once you have created Scenarios, you can show them. In this example, the Marketing scenario is currently visible. To change to a different scenario:

  1. Fom the Tools menu, choose Scenarios
  2. In the list of Scenarios, select Finance
  3. Click the Show button
  4. Click the Close button.

Download the Sample File

Download the zipped sample Excel 2003 Scenario file

More Tutorials

Scenario Summaries  

Automatically Show Scenarios

Scenarios -- Programming  

 

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: April 11, 2016 3:16 PM