Question

1. A manufacturing company wants to maximize its revenue from the car seats it sells. It produces three different kinds of automobile seats—leather, cloth, and vinyl. The leather seats cost $1800 to manufacture. The cloth seats cost $1200 and the vinyl costs $500. The company has $2,000,000 to spend on wholesale costs. The leather seats require 30 man hours of labor to produce. The cloth seats 28 and the vinyl seats 18. The company has 40,000 labor hours available each month. Because the company wants to move away from vinyl, it wants the vinyl seats to represent no more than half of its total sales. If the company makes $3300 on each leather seat, $2600 on each cloth seat, and $1600 on each vinyl seat, how should it distribute its manufacturing (assuming it can sell all it makes)?  If the company had 300 more labor hours each month, what would be the impact to revenue?

Linear Programming / Excel Solver

If Possible please show all equations/constraints

Answer 1

Let 'L', 'C', and 'V' be the number of leather, cloth, and vinyl seats to be produced.

Max Z = 3300L + 2600C + 1600V

Subject to,

1800L + 1200C + 500V <= 2000000 (Budget)

30L + 28C + 18V <= 40000 (Man-hour)

V <= 0.5*(L + C + V) or, -0.5L - 0.5C + 0.5V <= 0

L, C, V >= 0

--------------------------

Excel model:

Solver inputs:

Solution (Answer report)

Objective Cell (Max)
	Cell	Name	Original Value	Final Value
	$F$4	Profit	$0	$4,137,931
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No. of Leather seats	0	919.54	Contin
	$D$3	No. of Cloth seats	0	0.00	Contin
	$E$3	No. of Vinyl seats	0	689.66	Contin
Constraints
	Cell	Name	Cell Value	Formula	Status	Slack
	$F$6	Budget	2000000	$F$6<=$H$6	Binding	0
	$F$7	Man-hrs	40000	$F$7<=$H$7	Binding	0
	$F$8	Max V	-114.9425287	$F$8<=$H$8	Not Binding	114.9425287

Sensitivity report:

Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$C$3	No. of Leather seats	919.54	0.00	3300.00	2460.00	378.947
	$D$3	No. of Cloth seats	0.00	-165.52	2600.00	165.52	1E+30
	$E$3	No. of Vinyl seats	689.66	0.00	1600.00	380.00	200
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
	$F$6	Budget	2000000	0.655	2000000	400000.000	83333.333
	$F$7	Man-hrs	40000	70.690	40000	1739.130	6666.667
	$F$8	Max V	-114.943	0	0	1E+30	114.9425287

Note that the shadow price of the labor hour constraint is 70.69 up to an increase of 1739.13 hrs. So, an increase of 300 labor-hrs will cause the profit to increase by 300*70.69 = $21,207