MIS 204 Hands-On Activity 6 | Complete Solution

 MIS 204 Hands-On Activity 6:

Scenario Manager and Goal-Seek Tools

Introduction 

After putting together your decision support model for Willy Wonka, you wonder if there is a more efficient way of viewing the different possible outputs of the model.  You’re in luck!  EXCEL provides two tools – the Scenario Manager and the Goal-Seek tool— to do just that! 

Using the Scenario Manager 

Step 1:  Open the HOA6 Data Excel File 

This file should look familiar to you:  it is the file you created for Hands-On Activity 4.  As you can see, there are two worksheets in this workbook – the DSS Model and the Income and Cash Flow models.  Save the file to your computer with the following naming convention:  last name + first initial + HO6; e.g., doejHO6.xlsx

Step 2:  Using the Scenario Manager 

In this model there are four possible output scenarios:

As you should know from doing HOA4, you can see these different scenarios by manually entering in the appropriate combinations on the DSS model in cells C8 and C9.  However, that method is not the most intuitive.  Worse, when you make a change you lose what was listed in the prior scenario, making it impossible to directly compare results.  Luckily EXCEL’s Scenario Manager eliminates that problem. 

1. Click on the Data tab.
2.  

Click on the What-If Analysis icon and select Scenario Manager.  You will get this dialog box:
 

Click the Add button.  You will get a new dialog box
 

1. For this scenario, type ‘Recession-High Inflation’ in the Scenario name field.
    
2.  

Click in the Changing cells field, delete the value that is in there.  Then highlight cells C8:C9.  Click OK.  You will get a third dialog box:
 

1. If, as in the screenshot, the values for 1 and 2 are already R and H, then just click OK.  Otherwise, change the values to R and H.  Then click OK.
    
2. You should now be back at the Scenario Manager, only now your first Scenario is listed.
    
3.  

Now repeat the process to add the remaining three scenarios: Recession—Low Inflation, Boom – High Inflation, and Boom – Low Inflation.  When done, your Scenario Manager dialog box should look like this:
 

1. Now comes the payoff:  click the Summary button.
    
2. Leave the Summary setting checked, and for the Results Cells field, highlight cells C12:C15.  Click OK.
    
3.  

 You should now have a new Scenario Summary worksheet in your workbook; click on its tab.
 

1. Your final task is to clean up the formatting a little bit.  Do so by clicking on the cell C6 where it currently displays the value $C$8.  This is the cell address of the cell that is changing with the scenario.  But that information is not particularly useful to Willy Wonka because he does not know what that address represents.  Create a formula that makes the value of the cell equal to the descriptive header on the DSS Model worksheet (cell A8).
    
2. Now repeat the process for the rest of the header cells.
    
3. Resize Column C to fit.
    
4. Highlight all the cells with R and H in them and align right. Here is your final report:

As you can see, the Scenario Manager makes it a lot easier to see the different outputs of the model, thus making it easier to compare them.

Step 4: Save your File

Using the Goal-Seek Analysis Tool

The Scenario Manager is useful for when you want to see what the outcome of a change will be when you make a change in your model.  The Goal-Seek works differently: it tells you what change will be required to achieve a certain goal.

Step 1: Create a Goal-Seek Worksheet

Create a new worksheet and rename it Goal-Seek Exercise.
 

1. Create the following table in EXCEL:

1. Format the cells holding the values for Cost of Expansion, Cash Investment, Loan Amount and Monthly Payment as Currency (two decimal places).
    
2. Format Interest Rate as Percentage  (two decimal places)
    
3. Format Term as Number (no decimal places).
    
4. Create a formula for the cell holding the Loan Amount value:  Loan Amount = Cost of Expansion  – Cash Investment.  In the cell holding the Monthly Payment value, use the PMT() formula.
    
5. Populate the table with the following data:
   • Cost of Expansion:   $1,500,000
   • Cash Investment:   $250,000
   • Interest Rate:  5.00%
   •  

Years:  5

Done correctly your table should look like this:

Step 2: Using the Goal Seek

Let’s say that your boss feels that $23,589.04 is too high of a monthly payment for the firm to afford.  She wants a monthly payment of $10,000.  There are multiple factors that influence the size of the monthly payment: down payment (cash investment), interest rate, and term length.  Let’s use Goal Seek to evaluate the cash investment and interest rate that would give us a $10,000 monthly payment.

1. Click in the cell holding the Monthly Payment value.
    
2.  

Click on the Data tab on the ribbon, click on the What-If Analysis icon and choose Goal Seek.  You should see the following dialog box appear:

NOTE:the value in the Set cell: field may vary from what is pictured depending on where you created the table!
 

1. Click in the To value:  field and type -10,000 (because we’re using accounting format the Monthly Payment value is a negative).
    
2. Click in the By changing cell: field and then click in the cell holding the Cash Investment value.
    
3. Click OK.
    
4. Answer the following question on your spreadsheet below your table:  How much does the company have to put down to have a $10,000 monthly payment?
    
5. Now reset the table to the original values and use Goal-Seek to find the term length to get a $10,000 monthly payment.
    
6. Answer the following question on your spreadsheet below your table:  What term length will give you a $10,000 monthly payment?

Step 3: Save Your File and Upload to the Appropriate Drop Box.

Make sure your file is saved correctly and upload it to the Activity 6 Drop Box. Your assignment will be graded using the following  rubric: