Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Usually a customer does not purchase the car at the manufacturer's suggested retail price ( MSRP ) . The dealer will offer incentives and discounts
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 to calculate the total number of new cars in stock. Copy this formula over to
cell to calculate the total number of used cars in stock.
Write an Excel formula in cell to calculate the cost to fill the fuel tank. Copy this formula down the column
and format the column as shown in Figure
Write an Excel formula in cell 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 by the total number of new cars in stock cell J and multiply it by the MSRP cell C 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 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 and copy it down and across to obtain the discount price of the respective
car.
Write a formula in cell 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 to display the car with the lowest price based on Total Number of New Cars in Stock.
Copy the formula to cell K to display the lowest price based on the Total Number of Used Cars in Stock.
Write a formula in cell J to display the car with the highest price based on Total Number of New Cars in Stock.
Copy the formula to cell K
Format the cells as the other cells in column J & K
PART 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 $ to $
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started