Please setup and label steps in excel

A dealer buys and sells trucks and cars. As with many dealerships, the selling price of a vehicle type influences the number of customers willing to buy it. Assume the following equations characterize the relationship between the price set by the dealer and annual demand:

Truck Demand = 900 18 * (Truck Price/1,000)

Car Demand = 600 11 * (Car Price/1,000)

The dealers cost per vehicle is $45,000 for trucks and $40,000 for cars.

Each truck requires 3 hours of dealer prep labor. Each car requires 2 hours of dealer prep labor. The dealers present staffing has the capacity of 250 labor hours per year for dealer prep.

The dealers owner wants to determine the price of each vehicle that maximizes annual profit. It is fine if the number of each vehicle sold during the year is not an integer. The data is not accurate enough for such rounding to make a practical difference.

Create a spreadsheet tabs (Q2 and Q2-Report) to show your work for the following two situations. Ensure your spreadsheet tabs is well-organized and easy for others to use.

A. Base Case. Determine and show the optimal price of each vehicle and the resulting profit. Label your spreadsheet tab as Q2. Present your work so that the dealers owner will find it easy to understand and use.

B. Optimization What-If Analysis. Perform an optimization what-if analysisusing Solver Sensitivitywhere the available labor hours for dealer prep varies between 250 and 300 in increments of 10. For each value of available labor hours, determine the optimal price of each vehicle and the resulting total profit. Create a table with this information and a graph showing the profit versus the available labor hours. If you think it would be helpful, please include in the table any additional information you think the owner should know. Present your findings in a clear and organized manner. Put the resulting table and graph in tab Q2-Report.