Exploring Excel Ch. 06 A1 Project What-If Analysis complete solutions correct answers key

Exploring Excel Ch. 06 A1 Project What-If Analysis complete solutions correct answers key

Project Description:

You are budgeting for the purchase of a new automobile. You are evaluating your expenses to determine the monetary resources available for a monthly payment. As part of the project you will perform What-IF Analysis and complete an amortization schedule.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Download and open the file named exploring_e06_grader_a1.xlsx, and then save the file as e06c2auto loan_LastFirst, replacing “LastFirst” with your name.

0

2

Use Goal Seek to set the monthly payment of the auto loan to $305.69 (15% of available capital) by changing the purchase price in cell E4.

Beginning in cell G3, complete the series of loan amount values ranging from $16,000 to $30,000 in $500 increments in cells G3:G31.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Goal Seek. On the HOME tab, in the Editing group, use the Fill tool to complete the substitution values.

5

3

Enter references for monthly payment, percent of total, and insurance premium in the cell H2, cell I2, cell J2 respectively for a one-variable data table.

Hint: Create linking formulas to cells E12, E15, and E17. Cell references are preceded by the = sign.

7

4

Complete the one-variable data table using Amount of Loan as the column input cell, and then ensure the results for monthly payment and insurance premium are formatted as Accounting Number Format with two decimal places. Format percent of available income as Percentage Number Format.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Data Table. On the HOME tab, in the Number group, click the Format Cells: Number Dialog Box Launcher to format the results.

4

5

Apply custom number formats to make the formula references appear as the following descriptive column headings: H2-Monthly Payment, I2-% of Available Income, J2-Insurance Premium.

Hint: On the HOME tab, in the Number group, click the Format Cells: Number Dialog Box Launcher. Enter text in quotation marks to create the custom number formats.

4

6

Copy the loan amount values from the one-variable table into the two-variable table starting in cell L3 (Hint: copy the loan amount values from G3:G31).

Hint: Copy the values in cells G3:G31 and paste them starting in cell L3.

5

7

Type 3.00% into cell M2. Complete the series from 3.00% to 4.25% in .25% increments.

Hint: On the HOME tab, in the Editing group, use the Fill tool to complete the substitution values.

5

8

Enter the reference to the monthly payment formula in the cell L2 for a two-variable data table.

Hint: Cell references are preceded by the = sign.

5

9

Complete the two-variable data table using cell E7 as the row input cell and E6 as the column input cell , and then format the results with Accounting Number Format with two decimal places.

Hint:  On the DATA tab, in the Data Tools group, click What-If Analysis, then click Data Table. On the HOME tab, in the Number group, click the Format Cells: Number Dialog Box Launcher to format the results.

10

10

Apply a custom number format to cell L2 to display Amount of Payment.

Hint: On the HOME tab, in the Number group, click the Format Cells: Number Dialog Box Launcher. Enter text in quotation marks to create the custom number formats.

5

11

Create a scenario named Current loan option, using amount of loan and interest rate as variable cells. Enter these values for the scenario: 25000, 4.25%.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Scenario Manager. Uncheck Prevent changes to change cell values.

5

12

Create a second scenario named Best loan option, using the same changing cells. Enter these values for the scenario: 16000, 3.00%.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Scenario Manager. Uncheck Prevent changes to change cell values.

5

13

Create a third scenario named Worst loan option, using the same changing cells. Enter these values for the scenario: 30000, 4.25%.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Scenario Manager. Uncheck Prevent changes to change cell values.

5

14

Generate a scenario summary report using the amount of loan, monthly payment, and insurance premium as the results.

Hint: On the DATA tab, in the Data Tools group, click What-If Analysis, then click Scenario Manager. Uncheck Prevent changes to change cell values.

5

15

Clean up the summary report by deleting the first column, row, and information in rows 11, 12 & 13 (in that order).

Hint: Delete column A, and rows 11, 12, & 13 as specified.

5

16

Load the Solver add-in if it is not already loaded. Set the objective cell for the lowest monthly payment possible.

Hint: With the Auto Loan sheet selected, on the FILE tab, click Options, then click Add-Ins to install the add-in. Solver will appear in the Analysis group on the DATA tab.

5

17

Use the purchase price and down payment as changing variable cells.

Hint: Enter the changing cells as specified.

5

18

Set a constraints for the purchase price and down payment. The purchase price must be at least $16000 but no more than $18000. The down payment must be at least $500 but no more than $5000.

Hint: Enter the constraint as $E$4<=18000. Enter the constraint as $E$4>=16000. Enter the constraint as $E$5<=5000. Enter the constraint as $E$5>=500.

5

19

Set a constraint for the interest rate, which must be at least .03.

Hint: Enter the constraint as $E$7 >= .03.

5

20

Solve the problem, generate an Answer Report, but keep the original values in the Auto Loan worksheet.

Hint: In the Solve Parameters dialog box, click Solve.

5

21

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Scenario Summary, Answer Report 1, Auto Loan.  Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

Total Points

100