Question

Can you please assist using the solver function in excel?

CarPro is an automobile dealer selling only new cars. CarPro sells three types of vehicles: sedans, SUVs and trucks. CarPro places orders to the car manufacturers only when customers have decided to purchase. The ordering cost (per unit) of sedan, SUV and truck are $18,000, $20,500 and $19,000, respectively. The sales price (per unit) of sedan, SUV and truck are $20,000, $23,000, and $21,500, respectively. The base salary for a sales person is $100/day. In addition, a sales person gets a commission of 5% on the selling price of cars he sells. Each sales person works 8 hours a day. A sales person spends two hours selling a sedan, three hours selling an SUV, and two-and-a-half hours selling a truck. CarPro can spend a maximum of $300,000 per day on ordering cars. How many of each type of car should CarPro sell to maximize profits?

Refer to the CarPro problem. At the optimal solution, how many trucks should be sold to maximize profits?

	a.	20
	b.	14
	c.	18
	d.	10

Refer to the CarPro problem. To maximize profits, CarPro should sell two SUVs per day.

True

False

Refer to the CarPro problem. At the optimal solution, the total ordering cost for all cars is:

	a.	$ 278,800
	b.	$ 276,908
	c.	$ 286,500
	d.	$ 289,700

Answer 1

Profit/Car = Sale Price/Car - (Commission/Car + Order Cost/Car)Answers are not matching with options given in the question.

We will assume that there are as many salesmen as required.

No of Salesman= RoundUp(Total Time/8,0)

Solver Objective Function and Constraints

1. None of the options. 13 Trucks should be sold. I don't see 10 as an option all combinations with 10 trucks are giving a lesser profit.

2. False. No SUVs should be sold.

3. Total Ordering Cost at optimal value= 299500