a. Open e06_exam_chap_data and save as LastnameFirstnameE6. Use Goal Seek to achieve a $400 monthly payment by changing the budget.

b. Complete the substitution values in .5% increments for 3% to 6% starting in D4 and format the results with two decimal places. Enter references to the Payment, Total Repaid, and Total Interest.

c. Complete the one-variable data table and format the results with Accounting Number Format. Apply custom number formats to make formula references descriptive column headings.

d. Create a two-variable data table next to the one-variable data table starting in I3. Enter substitution rates for the interest rate in .5% increments for 3% to 6% with two decimal places and the down payment from $500 to $2000, with $500 increments. Enter the reference to the monthly payment in the correct location. Complete the two-variable data table to see the results formatting the results with Accounting Number Format. Apply a custom number format to cell I3.

e. Create a scenario named 3.5% interest entering the values for the scenario 3.5% interest, $1500 down payment, 60 payments, and 22,000 budget. Create a second scenario named 4% interest entering the values 4%, $1800, 60 payments, and 22,000. Create a third scenario named 4.5% interest with 4.5%, $1800, 48 payments, and 20,000. Finally, create a fourth scenario named 5% interest with 5%, $2000, 60 payments, and 19,500. Generate a scenario summary report. Clean up the summary.

f. Load the Solver add-in, if necessary. Set the objective to calculate the shortest loan time frame possible. Use the interest rate and down payment for the changing variable cells. Set a $22,000 constraint for the loan amount. Solve the problem but keep the original values in the worksheet. Generate an Answer Report.

Input Area

One-Variable Data Table: APR

Two-Variable Data Table: APR and Down Payment

Budget for Car

$ 24,000.00

Calculated Results for Each APR

Monthly Payment by APR/Down payment

Down Payment

$ 2,000.00

APR

3%

No. Years for Loan

5

Number of Payments

12

Output Area

Amount of loan

$ 22,000.00

Periodic Interest Rate

0.25%

Number of Payments

60

Monthly Payment

$395.31

Total to Repay the Loan

$ 23,718.67

Total Interest Paid

$ 1,718.67