01 October 2009
Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.
01 October 2009
A scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. This means that you could have a spreadsheet displaying numerical data that is relevant to a certain date, month, topic or whatever and using the Scenario Manager you can enter different values into the worksheet to forecast the outcome of the data. These values (or Scenarios) can be retained for future use and are stored in a hidden part of the workbook which can be retrieved by asking the Scenario Manager to show the Scenario that uses those specific values.
Remember, all that Scenarios are are just a different version of the same worksheet with each different version using a different set of input values in the nominated cells.
01 October 2009
For Scenarios to work correctly, you should first set up a base or default Scenario, on a worksheet in Excel. It is from this default Scenario that all other Scenarios are defined.
1. Go to Tools>Scenarios to activate the Scenario Manager. You will see a message telling you "No Scenarios are defined". 2. Choose Add to add your default Scenario. 3. Give your Scenario a name, Base, Original or Default or a name you can easily identify will be fine. 4. Click in the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here. 5. Click the collapse dialog button to collapse this dialog box up so that you can easily select the cells from the Worksheet that you wish to reference. 6. Click on the collapse dialog button again to expand the full Scenario Manager box. 7. If you wish to type a comment in, then click into the Comment: box and do so.
01 October 2009
There are two options at the bottom of this dialog box. They are Prevent changes and Hide, with Prevent changes being the default. If you select Prevent changes, then all your Scenarios will be locked and will be unable to be edited. It is important to note here that you MUST also protect your Worksheet via the Tools>Protection>Protect sheet option for this option to take effect. If you then with to edit your Scenarios, you must first unprotect your Worksheet, then de-select the Prevent changes option to proceed. The Hide option when selected will do exactly as said and Hide your Scenarios. This option also requires sheet protection for it to take effect.
8. Click the OK button. Once you click on the OK button the Scenario Values dialog box will appear. This is where you must enter values into the scenario cells. As the first scenario is your default Scenario, the values in the cells that we specified in the Changing Cells: box have been picked up so we need to make no changes here, we need only click the OK button. This will now take us back to the Scenario Manager and you will see the name of your Scenario.
There is no limit to the number of Scenarios that you can apply to your worksheet model. Adding a scenario is done in basically the same way as creating a default scenario.
1. Go to Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box. 2. Under Scenario name: give your new Scenario a name. You need make no changes to the Changing cells: box as the cells we nominated in your Default scenario show here. 3. Click OK. This will show the Scenario values dialog box and this time you will need to change the values. 4. Click OK.
Now you can display your Scenarios to show how they change the outcome of your data by asking the Scenario Manager to show a particular scenario.
1. Select Tools>Scenarios 2. Click on the Scenario name you want to see 3. Click Show. 4. Drag the dialog box out of the way and have a look at the values in the worksheet. One you have done this, click on the next Scenario name and then Show, and again peruse the values in the worksheet. 5. Click on Close when you no longer wish to view your Scenarios.
CAclubindia
