MIS204 Hands On Activity 6 Complete Solution
MIS 204 Hands-On Activity 6:
Scenario Manager and Goal-Seek Tools
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!
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
In this model there are four possible output scenarios:
Economic Outlook |
Inflation Outlook |
Recession |
High |
Recession |
Low |
Boom |
High |
Boom |
Low |
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.
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
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:
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:
You should now have a new Scenario Summary worksheet in your workbook; click on its tab.
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.
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.
Create a new worksheet and rename it Goal-Seek Exercise.
Years: 5
Done correctly your table should look like this:
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.
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!
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:
Category |
Points |
All four scenarios created properly using Scenario Manager tool. |
10 |
Goal-Seek Worksheet created correctly
|
10 |
Scenario Summary created and formatted correctly. |
5 |
Total: |
25pts |
MIS204 Hands-On Activity 6 Complete Solution
Cost of expansion $1,500,000.00
Cash investment...
A+ - Thank you!
Thanks for the positive feedback!