The Acme car dealership would like you to build a car purchase and financing model. They have created a basic format as shown in the car finance spreadsheet. Variable Inputs include base price, additional equipment, trade-in allowance and amount owed on the trade-in vehicle. Tax is 3% of the purchase cost or $300, whichever is less. Fixed inputs are document fee ($399) and title fee ($69) The length of financing can vary from 3-6 years. APR depends on the purchaser's FICO score (as shown on the following table). Do not use cell names or range names. Include a spin button for the user to be able to select only 36, 48, 60 or 72 month finance options. The model should calculate APR from any valid FICO score entered by the user (input validation is not required.)

FICO Range

Rate

0 to 649

3%

650 to 719

2%

over 719

1%

Complete the model for Acme. Use the model to calculate the monthly payment for John, who has a trade-in worth $10,660 that he still owes $8,600 and he wants to apply toward the purchase of a new car with a base price of 20,000 and $1,550 worth of accessories. He has a FICO score of 670 and wants to finance the car over 5 years. Upload your model, showing John's case as inputs.

Using the above input values, single data table that shows the monthly payment and the total interest paid on the loan, as the base price of his desired car ranges from $18,000 to $24,000 in increments of $1,000

[Use the pmt() function for the monthly payment calculation; use the min() function for the tax calculation;use vlookup() to find APR; use a one-way data table with multiple outputs for the table of base price, monthly payment, total interest]

Used Vehicle Credit

Trade in allowance

Balance Owed

Net allowance

Amount to be Financed

Base price

Addt'l equip

Vehicle & equip

Used vehicle credit

Tax

Document Fee

Title Fee

Amount Financed

Financing

FICO Score 670

APR 2%

Number of Months

Monthly Payment

Total Interest on Loan