Calculate the registration fee for each vehicle.

a$.$

Enter a formula in cell $C13$ to look up the registration fee for the first

vehicle. Use the vehicle type in cell C$4$ as the Lookup$\_$value argument. Use

the RegistrationFees named range as the Table$\_$array argument. The

registration fees are located in column $2$ of the data table. Require an

exact match.

b$.$

Copy the formula to the appropriate cells for the other vehicles.

Determine whether or not you will need a loan for each potential purchase.

a$.$

In cell C$15,$ enter a formula using an IF function to determine if you

need a loan. Your available cash is located on the Data sheet in cell A$3.$ If

the price of the car is less than or equal to your available cash, display

$"$no$".$ If the price of the car is more than your available, cash, display

"yes". Use absolute references where appropriate$-$you will be copying

this formula across the row.

b$.$

Copy the formula to the appropriate cells for the other vehicles.

Calculate how much you would need to borrow for each purchase.

a$.$

In cell C$16,$ enter a formula to calculate the price of the vehicle minus

your available cash $($from cell A$3$ in the Data worksheet$).$ Use absolute

references where appropriate$-$you will be copying this formula across

the row.

b$.$

Copy the formula to the appropriate cells for the other vehicles.

Calculate the monthly payment amount for each loan.

a$.$

In cell C$20,$ enter a formula using the PMT function to calculate the

monthly loan payment for the first vehicle. Hint: Divide the interest rate by

$12$ in the Rate argument to reflect monthly payments.

b$.$

$$ Hint: Multiply the number of years by $12$ in the Nper argument to

reflect the number of monthly payments during the life of the loan. Hint:

Use a negative value for the loan amount in the $Pv$ argument so the

payment amount is expressed as a positive number.

c$.$

Copy the formula to the appropriate cells for the other vehicles.

Compute the monthly cost of gas.

a$.$ In cell C$21,$ enter a formula to calculate the number of miles you expect

to drive each month. Divide the value of number of miles $($cell A$5$ from

the Data sheet$)$ by the average MPG for the vehicle multiplied by the

price of a gallon of gas $($cell A$6$ from the Data sheet$).$

b$.$

Copy the formula to the appropriate cells for the other vehicles.

c$.$

If cells D$21$:F$21$ display an error or a value of $0,$ display formulas and

check for errors.