 |
|
|
|
|
Download the associated
Workbook
for this lesson
In this lesson we are going to look at two of the tools that are specifically designed for use in "What-If analysis", and as such make up part of the "What-If Analysis Toolpak of Microsoft Excel". These tools are used to determine different outcomes of your data by changing different cells within a Worksheet model. The two tools we will discuss here are called Scenarios and Goal Seek. Both of these tools are used widely in financial, accounting and engineering businesses today, and once understood can be a huge aid in determining various outcomes and projections.
Scenarios are a very handy feature of Excel that are specifically designed for use in "What if" analysis. A scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. This means that we can have a spreadsheet displaying numerical data that is relevant to a certain date, month, topic or whatever and using the Scenario Manager we 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.
Let's use an example to explain fully the function of Scenarios. We may have a Worksheet that is predicting a Company budget for the next 12 months. Our Managing Director has asked us to give an overview on what would happen if company growth reached 15%, or decreased by 10% and what the outcome would be if the wholesale cost of goods increased by 7% or decreased by 5%. You can use Scenarios to "what if" test your Worksheet in a simple and logical way.
The very first thing that we must do is have an original or base model. This will obviously be a Worksheet. The next thing we must do is to set up a base Scenario. Think of it like a template or default Scenario. It is from this default Scenario that all other Scenarios are defined.
Open the attached Workbook and ensure you are on the Worksheet 2002 Scenarios. Spend a few minutes familiarizing yourself with it.
Go to Tools>Scenarios to activate the Scenario Manager. Notice that as we have done nothing at this stage the Scenario Manager is telling you that "No Scenarios are defined. Choose Add to add scenarios."
Let's do just as the Scenario Manager has instructed us to and click on Add. This will bring up the Add Scenario dialog box and your cursor will be flashing in the Scenario name: box to prompt you to firstly name your scenario. In this box type Default Case as this is your "template" or "original" data. Hit the tab key (or click with your left mouse button if you prefer) and you will jump to the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here. 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.
Highlight cells B4:B7 with your mouse, then holding down your Ctrl key, click on cell B11, then on E17. Click on the collapse dialog button again to expand the full Scenario Manager box. If you wish to type a comment in, then click into the Comment: box and do so now. This is a good point to mention the 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. We will leave the default as Prevent changes and make no other changes here.
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 this is our 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 Default scenario, the scenario we just created.
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. Let's add a Best Case Scenario and a Worst Case Scenario and apply them to our model to really get the hang of how scenarios can be used.
Select Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box. Under Scenario name: type in Best Case. We need make no changes to the Changing cells: box as the cells we nominated in our Default scenario show here. Move to the Comments: box and type in a short description such as Best case scenario Jan 2002, then select OK. This will show the Scenario values dialog box and this time we will change some values. Type 0.15 in 1, type 0.1 in 2, type 0.05 in 3, type 0.045 in 4, 40,000 in 5 and 30,000 in 6. Select OK. Now we have two scenarios available to us, our Default Scenario and our Best Case scenario.
Let's add the worst case scenario. Select Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box. Under Scenario name: type in Worst Case. We need make no changes to the Changing cells: box as the cells we nominated originally in our Default scenario should still be showing here. Move to the Comments: box and type in a short description like Worst case scenario Jan 2002, then select OK. This will show the Scenario values dialog box and we will add the following values. Type 0.05 in 1, type 0.06 in 2, type 0.025 in 3, type 0.020 in 4, type 30,000 in 5 and 20,000 in 6.
Before we display and test the outcome of our model using the three scenarios we have set up, let's take things a step further. The cells that we selected to change in each of our three scenarios appear as cell references in the Scenario values dialog box. It would be much easier for us and for any other users of our Workbook if we could easily give names to these cells and have them appear in lieu of cell addresses. This will make our scenarios easier to read and understand and is a simple process.
Highlight the range A4:B7. Go to Insert>Name>Create to access the Create Names dialog box. Make sure that Left column is selected and click OK. Click in cell B11 and select Insert>Name>Define and type in JanSales under Names in Workbook in the Define Name dialog box. Finally, let's click on cell E17 and go to Insert>Name>Define and type in MajorImprovements under Names in Workbook. Click on OK. Let's now make sure that these names appear in lieu of the cell addresses in the Scenario Values dialog box. Select Tools>Scenarios then click on any Scenario, then select Edit then OK to see the Scenario Values dialog box and the Names that we created.
Remember when we discussed Scenarios at the beginning of this lesson I stated that a scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. So in other words a scenario is just a version of your worksheet with a specific set of values in place. Now we have created three different scenarios, let's see how they change the outcome of our data by asking the Scenario Manager to show a particular scenario.
Select Tools>Scenarios click on Best Case, then click Show. 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 Worst Case then Show, again peruse the values in the worksheet, then finally select Default Case and Show to have a look at your default scenario. Click on Close when you no longer wish to view your scenarios.
Excel will allow you to create a Scenario Summary Report or a Scenario PivotTable Report of your scenarios with relative ease. The Summary Report is always created on a new Worksheet within the Workbook and will list all of the input cells and their values. Select Tools>Scenarios then click on Summary to display the Scenario Summary dialog box. Ensure that Scenario summary is selected under Report type then select OK. Excel may or may not display the Result cells for you, depending on which version of Excel you are using. You will be presented with a Scenario Summary on a separate Worksheet within your workbook.
Creating a Scenario PivotTable report works in the same way, except you MUST nominate your result cells for this type of report. As a tip, the quickest and easiest way to do this is to go to Tools>Scenarios>Edit, highlight any scenario, then select the cells under Changing Cells, select Ctrl + C, then select Cancel, then Summary then select Scenario PivotTable Report, click in Result Cells, then select Ctrl + V to paste in the values of the result cells. Select OK and your Summary PivotTable Report will be presented to you on a separate Worksheet within the Workbook.
If you wanted to, you could bring in similar scenarios that you have set up in different Workbooks into your current or active Worksheet. This will only work however if the input cells are exactly the same as those nominated in your current Worksheet. Merging scenarios can be great for recycling and analysing a previous years set of figures with the current year, or you may have different areas within a company that can make use of the same scenarios. This can be done easily by following the following steps.
Select Tools>Scenarios to again display the Scenario Manager. Click on the Merge button and the Merge Scenarios dialog box will appear. There are no changes to make in the first box as we wish to use the current Workbook in this case. Let's go to the second box Sheet: and highlight 2001 Scenarios. The next thing we do is click OK, but note before you do this that you have a message at the bottom of this dialog box telling you how many scenarios are in the source sheet that you selected. In this case there are 3. Once you have clicked OK you will be returned to the Scenario Manager and you should see the scenarios from the 2001 Scenarios worksheet added to the scenarios from the 2002 Scenarios Worksheet. All that remains to do now is to test them. So click on the various scenarios and click on Show to display them.
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. Remember, to make your "What-if" testing work correctly, you will need to remember to create your base model, or default scenario so you can return to your "original" figures whenever you need to.