Chapter: Linear Programming (LP) Question 1 – Production problem Winkler
Furniture manufactures two different types of china cabinets: a French Provincial
model and a Danish Modern model. Each cabinet produced must go through
three departments: carpentry, painting, and finishing. The table below contains all
relevant information concerning production times per cabinet produced and
production capacities for each operation per day, along with net revenue per unit
produced. The firm has a contract with an Indiana distributor to produce a
minimum of 300 of each cabinet per week (or 60 cabinets per day). Owner Bob
Winkler would like to determine a product mix to maximize his daily revenue.
Cabinet Style Carpentry (Hours/Cabinet) Painting (Hours/Cabinet) Finishing
(Hours/Cabinet) Net Revenue / Cabinet ($) French Provincial 3 1.5 0.75 28 Danish
Modern 2 1 0.75 25 Department Capacity (Hours) 360 200 125 (a) Formulate as a
Linear Programming problem. (Objective function and constraints must be clearly
shown) (b) Solve this problem using Excel solver. Based on your findings, what
would you suggest to Bob Winkler to solve his problem? (c) If the available
painting hours are increased to 400 hours, will your answers in (b) change?
Please explain your new findings using Excel solver. Note: Hello! If you are not
sure how to fulfill all the requirements needed, please do not attempt the
question. Microsoft excel and excel solver are needed to solve these questions.
Please send me the answers in an excel file if possible. Your help is very much
appreciated. Thank you.
(a)
Let,
X1 = Number of French Provincial model to be produced
X2 = Number of Danish Modern model to be produced
Objective function
Maximize Z = total revenue = 28 X1 + 25 X2
Subject to,
Constraints:
3.0 X1 + 2.0 X2 <= 360 (Carpentry hours) 1.5 X1 + 1.0 X2 <= 200 (Painting hours) 0.75
X1 + 0.75 X2 <= 125 (Finishing hours) X1 >= 60
X2 >= 60
X1, X2 >= 0
(b)
Excel formulation screenshot
[Link]
[Link]
[Link]
[Link]
Solution (Answer report)
Objective Cell (Max)
Cell Name Original Value Final Value
$E$4 Revenue 0 3930
Variable Cells
Cell Name Original Value Final Value Integer
$C$3 Value of X1 0 60 Contin
$D$3 Value of X2 0 90 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$E$6 Carpentry 360 $E$6<=$G$6 Binding 0 $E$7 Painting 180 $E$7<=$G$7 Not
Binding 20 $E$8 Finishing 112.5 $E$8<=$G$8 Not Binding 12.5 $E$9 Min X1 60
$E$9>=$G$9 Binding 0
$E$10 Min X2 90 $E$10>=$G$10 Not Binding 30
Recommendation
Produce French Provincial model 60 units per day and Danish Modern model 90 per
day to attain the maximum revenue per day of $3,930.
(c)
Answer report:
Objective Cell (Max)
Cell Name Original Value Final Value
$E$4 Revenue 0 3930
Variable Cells
Cell Name Original Value Final Value Integer
$C$3 Value of X1 0 60 Contin
$D$3 Value of X2 0 90 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$E$6 Carpentry 360 $E$6<=$G$6 Binding 0 $E$7 Painting 180 $E$7<=$G$7 Not
Binding 20 $E$8 Finishing 112.5 $E$8<=$G$8 Not Binding 12.5 $E$9 Min X1 60
$E$9>=$G$9 Binding 0
$E$10 Min X2 90 $E$10>=$G$10 Not Binding 30
Note carefully in the answer report that the utilized painting hours is only 180 against an
available capacity of 200 hours. So, a 20 hours slack capacity is available. So, painting
is a non-binding constraint. Therefore, additional capacity in painting will have no impact
on the optimal solution.
Looking for a Similar Assignment? Our Experts can help. Use the coupon code SAVE30 to get
your first order at 30% off!
[Link]
problemwinkler-furniture-manufactures-two-different-types-of-china-cabinets-a-french-provincial-
model-and-a-danish-modern-model-each-cabin/
