Exp22_Excel_Ch06_HOE - Mortgage

Step

Instructions

Points Possible

1

Download and open the file named Exp19_Excel_Ch06_HOE_Mortgage.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

As you complete your mortgage calculations, you would like to use range names to make the formulas easier to understand. Create the range name PurchasePrice for cell B2 and DownPayment for cell B3.

3

3

You have decided to utilize an alternative method of creating range names for the remainder of the variables. Create range names for the range A9:B13 using the create from selection method.

6

4

You would like to edit the previously created named ranges. To do so, you will use the Name Manager. Delete the Monthly_Payment named range. Edit the DownPayment named range so it appears as Down_Payment and edit PurchasePrice to appear as Purchase_Price.

2

5

You would like to apply the newly created named ranges to the existing formulas in the worksheet. Apply defined names to the existing formulas on the Home Loan worksheet. Once completed, named ranges should appear in the cells B12 and B14.

5

6

You would like to document the named ranges by inserting them as a list on a new worksheet. Create a new worksheet named Range Names. Insert the previously created range names on the worksheet starting in cell A2. Set the width of columns A:B to AutoFit.

8

7

As you continue your analysis, you would like to create a one-variable data table to explore the impact of various interest rates on the payment, total interest, and total amount repaid. To complete the table, you will first create substitution values. On the Home Loan sheet, beginning in cell D4, enter substitution values from 4% to 6% increasing in .25% increments, completing the range D4:D12.

5

8

To continue the construction of the one-variable data table, you will add formulas to the table structure. Add a reference to the cell the contains the monthly payment (B12) in cell E3. Add a reference to the cell that contains the total to repay loan in cell F3 (B13), and add a reference to the cell that contains total interest paid (B14) in cell G3.

3

9

Complete the one-variable data table using the APR in cell B4 as the column input.

5

10

You would like to format the one variable data table to add clear labels that are easy to understand. Apply Accounting Number Format to the range E4:G12. Type APR in cell D3. Next, create the following custom formats for the range E3:G3. Cell E3 custom format = Payment Cell F3 custom format = Total Repaid Cell G3 custom format = Total Interest

3

11

You would like to create a two-variable data table to explore the impact of changing interest rates and home costs on the monthly payment. To begin creating the structure of the table, you will enter substitution values for the home cost and interest rate. Enter 150000, 175000, and 200000 in the range J3:L3. Format these values with Accounting Number Format with two decimals. Expand the column width as needed if pound signs (#) appear. Start in cell I4. Complete the series of substitution values ranging from 4% to 8% in increments of .25% vertically down, stopping in cell I20.

5

12

Enter a reference to the monthly payment (B12) in cell I3 and complete the two-variable data table using the cost of the home (cell B2) as the Row Input and the APR (cell B4) as the Column Input.

6

13

As your last step, you would like to create a custom number format for cell I3. In cell I3, apply a Custom number format to display APR. Center and bold the contents in cell I3.

3

14

To continue your analysis, you will use Goal seek to determine the optimal purchase price to obtain the desired monthly payment of $600 without changing the down payment or APR. Use Goal Seek to set the monthly payment (cell B12) to $ 600 by changing the purchase price (cell B2).

0

15

To enhance your analysis you will use Scenario Manager to create best, most likely, and worst case scenarios that can be documented in a scenario summary report. To begin this step, you will start with creating scenarios. Create the following scenarios using Scenario Manager. Best-Case Scenario Changing cells B2:B5 B2 = 150000 B3 = 10000 B4 = 2.25% B5 = 15 Worst-Case Scenario Changing cells B2:B5 B2 = 150000 B3 = 45000 B4 = 6% B5 = 45 Most Likely Scenario Changing cells B2:B5 B2 = 150000 B3 = 30000 B4 = 42.5% B5 = 30

6

16

After creating the three scenarios, you notice an error. You will edit the Most Likely Scenario to correct the mistake. Edit the Most Likely Scenario to reflect the correct interest rate of 4.25% in cell B4.

1

17

You would like to create a Scenario Summary report to document the three scenarios. Create a Scenario Summary report that reflects the range B12:B14.

5

18

You would like to format the Scenario Summary report using the formatting best practices discussed in the text. Delete Column A:B, Row 1, Current Values column. Enter the following values in the respective cells: A5 = Cost of Home A6 = Down Payment A7 = APR A8 = Years A10 = Monthly Payment A11 = Total to Repay Loan A12 = Total Interest Paid

4

19

Although Goal Seek and Scenario Manager were helpful in further analyzing your home purchase, you want to ensure the spreadsheet model imposes constraints on the situation. Therefore, you will continue your analysis by using Solver. Switch back to the Home Loan worksheet. If necessary, load the Solver Add-in. Set Solver to use cell B12 as the Objective and set it to the value of 1000.

3

20

Set Cost of home (B2), Down Payment (B3), APR (B4), and years financed (B5) as the Changing Variable cells, (B2:B5).

4

21

Set constraints that ensure the cost of the home (B2) is between $100,000 and $300,000 (greater than or equal to 100000 and less than or equal to 300000).

4

22

Set constraints to ensure the down payment (B3) is between $5,000 and $10,000 (greater than or equal to 5000 and less than or equal to 10000).

4

23

Set constraints to ensure the APR (B4) is between 4% and 6% (greater than or equal to 4% and less than or equal to 6%).

4

24

Set constraints to ensure the years financed (B5) are between 15 and 30 and are whole numbers.

6

25

Click or press Solve, retain the solution values, and generate an Answer Report.

5

26

Save and close Exp22_Excel_Ch06_HOE_Mortgage.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100