Usually a customer does not purchase the car at the manufacturer's suggested retail price $($MSRP$).$ The dealer will offer

incentives and discounts to give the customer the illusion they are saving thousands of dollars. In this part of the lab,

you will use the criteria explained below to calculate a discount price of the car using, which is the actual dollar amount

the customer will pay for the car. You will also write formulas to total the new and used cars, calculate the cost to fill

the fuel tank based on the price of gasoline, etc.$)$

Write an Excel formula in cell J$3$ to calculate the total number of new cars in stock. Copy this formula over to

cell $K3$ to calculate the total number of used cars in stock.

Write an Excel formula in cell $G5$ to calculate the cost to fill the fuel tank. Copy this formula down the column

and format the column as shown in Figure $2.$

Write an Excel formula in cell $J5$ to calculate the discount price of the Ford Focus. To obtain the discount of

the Ford Focus based on the total number of new cars in stock, divide the number of new Ford Focus cars in

stock $($cell H$5)$ by the total number of new cars in stock $($cell J$3),$ and multiply it by the MSRP $($cell C$5).$ The

discount price will be MSRP $-$DISCOUNT. This is the MSRP value for a vehicle $($Not the named range, $"$MSRP$")$

minus the DISCOUNT. $($Round the number to the nearest $2$ decimal places.$)$ Copy the formula down to calculate

the discount price of the other cars and across to calculate the respective discount cost of each used car based

on the number of used cars in stock.

You should use one formula in $J5$ and copy it down and across to obtain the discount price of the respective

car.

Write a formula in cell $C11$ to display the average MSRP of all the vehicles $($pound to the nearest whole number$)$

a$.$ Use the named range, MSRP for the argument of the average function

i$.$ Center if needed

Write a formula in cell J$11$ to display the car with the lowest price based on Total Number of New Cars in Stock.

Copy the formula to cell K$11$ to display the lowest price based on the Total Number of Used Cars in Stock.

Write a formula in cell J$12$ to display the car with the highest price based on Total Number of New Cars in Stock.

Copy the formula to cell K$12.$

Format the cells as the other cells in column J & K$.$

PART $3-$ DISPLAYING THE SPREADSHEET IN FORMULA VIEW

Copy the information in the Additional Vehicles to a new worksheet.

Name the new worksheet, Formula View.

Add color to the tab. $($any color$).$

Change the cost of gas from $$2\mathrm{.}67$ to $$2\mathrm{.}84.$

Notice the cost to fill the tank of each vehicle changes based on the cost of gas. This is why you should always

use cell references in your formulas instead of hard coding the values. Although, sometimes it does make more