Question

Hello! Could anyone show me how to solve these questions in excel using the "solver" tool?

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, the total ordering cost for all cars is:

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

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, how many trucks should be sold to maximize profits?

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

Refer to the CarPro problem. At the optimal solution, the total profit per day that can be made by CarPro is:

	a.	$ 21,800
	b.	$ 29,000
	c.	$ 27,500
	d.	$ 20,800

Refer to the CarPro problem. At the optimal solution, how many sales persons should be hired?

	a.	5
	b.	6
	c.	3
	d.	1

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. 15 Trucks will be bought.

2. False. No SUV should be sold.

3. Total Profit that can be made=20875.

4. At Optimal Solution you need 5 salesman