1.

To begin, start Excel 2003 and open the file Practice 4 in your Excel 2003 Practice folder.  File->Open

You will see a worksheet with sales and expense data for a chain of stores. The goal in this exercise is to create a scenario based on a projected increase in sales for some stores, and a projected decrease in rent for some stores.

2.

First, you should create a scenario with the current data that you can always return to as a baseline.

Choose Tools->Scenarios from the menu bar to invoke the Scenario Manager.

You can see that no other scenarios have been defined as of yet.

Click the Add button to create a current or baseline scenario.

Type CurrentActualData in the Scenario name text box, and then use the Tab key to move your cursor to the Changing cells text box.

 Next, use your mouse to select the cell range C2:D11 from the worksheet. (This includes the sales and rent columns.)

In the comment box, type This is the original worksheet data.

Click OK to move to the next step.

3.

In the Scenario values box, do not enter any new values.

Remember, this will be a scenario containing the current (original) data, so just click the OK button to create the scenario.

The Scenario Manager will now contain the scenario you just created for the original data. You can load the original data into the worksheet at any time by clicking the Show button.

The next step is to create a scenario with the future sales and rent projections.

4.

To begin, click the Add button in the Scenario Manager dialogue box. If the Scenario Manager is not visible, choose Tools->Scenarios to display it.

In the Add Scenario box, type Projected sales and rent in the Scenario name text box.

Check that the changing cells are C2:D11, (they should be entered by default based on the first scenario), and then type the following comment in the comment text box, This is a scenario based on projected changes in sales and rent.

When you are finished, click OK.

5.

In the Scenario Values box, Type 150000 for the projected sales for store 1 (cell C2) in the first text box, and type 3500 for the projected rent for store 1 (cell D2) in the second text box. Look at the cell references beside the text boxes to make sure you are entering your changes in the right place. Type 125000 in the third text box (cell C3) and type 3500 in the fourth text box (cell D3). Finally, type 130000 in the fifth textbox representing the projected sales for store 3. Remember, you can move from one text box to the next by using the Tab key. When you are finished, click the OK button to create the scenario.

Now both of the scenarios you created should be listed in the Scenario Manager.

6.

Close the Scenario manager and click on cell H12 in the worksheet. Use the auto sum button on the standard toolbar to enter the sum of the profit figures in cell H12.

The total profit should be 522530.

Choose Tools->Scenarios to display the Scenario manager.

In the Scenario manager select the Projected sales and rent scenario from the list, and click the Show button.

7.

You should notice that the sales and rent have been changed for the first two stores, and that the sales have been changed in Store 3. The total profit has also been recalculated for this scenario as 629828. To return to the original data, select the CurrentActualData scenario and click the Show button in the Scenario manager.

Save your work and exit Excel.