LESSON WORKBOOK: Level 3 Lesson 6 2007.xlsx
Scenarios
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.
Creating a Default Scenario
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 This Year. Spend a few minutes familiarizing yourself with it. Note the grey shaded cells are the cells that we will use in our Scenarios.
Go to the Data tab and under the Data Tools group click What If Analysis then 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." 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 the Scenario name: box type This Year as this is your "template" or "original" data.
Hit the tab key (or click with your left mouse button if you prefer) to get to the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook (or the cell you were clicked in before you began the Scenario Manager) 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 G16, then on B16.
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 Protect sheet option for this feature to take effect. If you then wish 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 and you will see the Scenario Values dialog box 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 This Year, the scenario we just created.
Adding Scenarios
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 Next Year Scenario and a Worst Case Next Year Scenario and apply them to our model to really get the hang of how scenarios can be used.
Go to the Data tab and under the Data Tools group click What If Analysis then Scenario Manager. In the Scenario name: box type Best Case Next Year. We need make no changes to the Changing cells: box as the cells we nominated in our This Year scenario show here. Move to the Comments: box and type in a short description if you wish, then select OK. This will show the Scenario values dialog box and this time we will change some values. Make the following changes:
Type 0.07 in 1
Type 275,000 in 2
Type 22,000 in 3
Type 6,000 in 4,
Type 7,500 in 5
Type 6,000 in 6 and Select OK
Now we have two scenarios available to us, our This Year Scenario and our Best Case Next Year Scenario.
Let's add the Worst Case Next Year Scenario.
Go to the Data tab and under the Data Tools group click What If Analysis then Scenario Manager. In the Scenario name: box type Best Case Next Year. We need make no changes to the Changing cells: box as the cells we nominated in our This Year scenario show here. Move to the Comments: box and type in a short description if you wish, then select OK. This will show the Scenario values dialog box and this time we will change some values. Make the following changes:
Type 0.02 in 1
Type 300,000 in 2
Type 30,000 in 3
Type 9,000 in 4,
Type 15,000 in 5
Type 14,000 in 6 and Select OK
Displaying Scenarios
At the beginning of this lesson we 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 What If Analysis then Scenario Manager under Data tools on the Data tab. Now ensure you are clicked on This Year then select the Show button. You won't notice any difference as this is our default or template Scenario. Select Best Case Next Year and then Show. You will see your figures change in cells B4:B7, G17 and M17 and also all the formulas that are dependent on these cells as well.
Using Names in Scenarios
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 the Formulas tab and under Defined Names options select Create from Selection.
Make sure that Left column is selected and click OK.
Click in cell G17 and select Define Name under Defined Names options and give it the name July_Improvements and click OK.
Click in cell M17 and select Define Name under Defined Names options and give it the name Dec_Improvements and click OK.
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 What If Analysis then Scenario Manager then click on any Scenario, then select Edit then OK to see the Scenario Values dialog box and the Names that we created.
Summary Reports
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 What If Analysis then Scenario Manager under Data tools on the Data tab. then click on Summary to display the Scenario Summary dialog box. Ensure that Scenario summary is selected under Report type then select OK. Excel will display the Result cells for you, change them to something that will give you more meaningful results (such as B20:M20). 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, again you will have to nominate the Result cells to glean more meaningful results. Select OK and your Summary PivotTable Report will be presented to you on a separate Worksheet within the Workbook.
Merging Scenarios
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 What If Analysis then Scenario Manager under Data tools on the Data tab and click 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 type Last Year, then click OK. 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.
Goal Seek
Goal Seek is the second "What-If" analysis tool that we will discuss in this lesson. There are many occasions in today's business world when we know the result of a formula, but we do not know the input value the formula needs to determine the result that we want to see. This is where Goal Seek comes in handy. Goal seek asks the question "What will the value need to be in a particular cell to arrive at a specific total? It then determines which part of your formula needs to be changed in order to achieve the desired result. As with Scenarios, we need to firstly set up a base model with which to perform the Goal Seek. This base model needs to be set up in a Worksheet with the data and formulas already in place and working. For Goal seek to work correctly, you must identify on your base model:
The cell that contains the formula that you want to settle. This is called the Set cell
The value you want the formula to change to. This is called To value
The part of the formula that you wish to change. This is called By Changing Cell
It is important to remember that the Set cell must always contain a formula or a function, whereas the Changing Cell must contain a value only. Not a formula or function.
Let's say that we wish to borrow money from the bank to modify and improve the existing canteen at our place of work. We will use Goal Seek to calculate how the term of the loan will be affected if we increase the Amount of our monthly loan repayments to $6,000.
Open the attached workbook and click on the Goal Seek worksheet. Note the formula =PMT(B5/12.B4.B3) in cell B7. This is calculating the monthly repayment of the loan and is the formula we will be using to run Goal Seek through our calculation.
It is always prudent to activate Goal Seek with your mouse pointer clicked on the Set cell this is because our Set cell will always contain the formula that we wish to settle. In our example, cell B7 is our Set cell, because it contains the formula calculating the monthly repayments of our loan.
With your mouse clicked in B7, select What If Analysis then Goal Seek under Data tools options on the Data tab to display the Goal Seek dialog box. Notice that as soon as the Goal Seek dialog box appears on your screen, you will see a marquee around the Set cell B7.
As we have already selected our Set cell, we need make no changes in the first box. Click or tab to the To value: box and type in -6,000.
Click or tab to the final box By changing cell: and select the collapse dialog button to the right of the box to collapse the dialog box.
Select cell B4 which is the term of the loan in months. Note here that as soon as you click on B4, it becomes absolute.
Collapse back through to your dialog box and click OK.
As soon as you select OK you will see that Goal Seek calculates a new term of the loan in the Worksheet. We have two options now, OK or Cancel. If we select OK the new term will be inserted into our Worksheet, of you select Cancel, the Goal Seek box will disappear, and your Worksheet will be in its original state. Let's click Cancel and perform a Goal Seek on our Worksheet to see what will happen to the Interest Rate if we wished to repay $6,000.
Select What If Analysis then Goal Seek under Data tools options to display the Goal Seek dialog box again, ensuring that B7 is still your active cell.
Click or tab to the To value: box and type in -6000.
Now click in or tab to the By changing cell: box. This time we will select cell B5, as it is the interest rate that we wish to change.
Click on OK to see the new interest rate that would be used if we repaid $6000 per month. T
This time, let's select OK to accept the Goal Seek solution into our Worksheet.
So as you can seek Goal Seek is another of the "What-If Analysis Tools" that can be performed on a Worksheet where you can actually specify the end result that you want, then using this very handy tool you can find out what the determining values need to be.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.