What-If-Analysis

Project Description:

Goal Seek, Data Tables, Scenario Manager. No Solver.

Steps to Perform:

Step	Instructions	Points Possible
1	You want to have a maximum payment of $1000. Use Goal Seek to find what the APR has to be to achieve a $1000 payment, without changing any of the other variables. Do not edit the APR cell after running Goal Seek.	10
2	You want to see a whole series of results as interest rate changes. You will make a 1 variable data table. First, start by putting 2% in E3. Then complete the series down the E column so the APR changes in increments of 0.5% and ends at 5%. Format the series as % with one decimal place.	5
3	Now, using the interest rates in the column, create a data table that shows you the Monthly payments, The total to Repay the Loan, and the Total Interest Paid, in that order from left to right. Format all the data table results as Accounting with 2 Decimal places.	15
4	Format the Cell references at the top of your data table to Show Payment, Total to Repay and Total Interest. Auto fit columns F, G and H.	5
5	Now you want a data table that shows you the Payment as APR changes as above in the column (Start the APR series in J3), and Total Budget changes from $30,000 to $40,000 in increments of $2,000 in the top row. Format the series of Budgets with Accounting and no decimal places.	20
6	Format the top left corner of the 2-variable data table to show APR/Budget. Format the data table results with Accounting no decimal places.	5
7	Make a scenario named Worst Case Scenario in which Budget is $40,000, Down payment is $2000 and APR is 5%.	15
8	Make a Scenario named Best Case Scenario in which Budget is $30,000 and Down Payment is $4000 and APR is 2.5%.	15
9	Generate a Scenario Summary report (not Pivot Table) that shows results for Payment, Total to Repay Loan, and Total Interest Paid. Do not edit the Scenario Summary Report.	10
10	Make sure the sheets are in the order left to right Scenario Summary Report then Sheet 1. Save the file and submit in MyLab.	0

Total Points

100