Question

Solve the following LP problem using the Solver in MS Excel.  

A company which manufactures canoes employs 120 employees, each of whom working 30 hours per week. Half of them work in the carpenter department, 20 persons in the plastics department, and the rest of them at the completion department. The company manufactures the simple canoes with net unit profit € 7 and the luxury canoes with corresponding profit € 10. A simple canoe requires 4.5 hours in the carpenter department and two hours in each of the other two departments. The working hours for each luxury canoe are 5, 1 and 4 at the carpenter department, plastics department and completion department respectively. Marketing calculations have shown that not less than 1/3 and not more than 2/3 of the total number of the canoes should be luxurious. How will the company maximize its overall net profit?

Answer 1

Available hours in carpentry = 60 employees x 30 hours per employee = 1800 hours
Available hours in plastics = 20 employees x 30 hours per employee = 600 hours
Available hours in completion = 40 employees x 30 hours per employee = 1200 hours

Formulation

Maximize Z = total profit = 7*Simple + 10*Luxury
Subject to,
4.5*Simple + 5.0*Luxury <= 1800 (carpentry hrs. limit)
2.0*Simple + 1.0*Luxury <= 600 (plastics hrs. limit)
2.0*Simple + 4.0*Luxury <= 1200 (completion hrs. limit)

Luxury >= 1/3*(Simple + Luxury) or, -(2/3)*Luxury + (1/3)*Simple <= 0 (min. of Luxury)
Luxury <= 2/3*(Simple + Luxury) or, (1/3)*Luxury - (2/3)*Simple <= 0 (max. of Luxury)

Luxury, Simple >= 0

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

Set Objective: Simple Luxu To: O Max Value Of: No. of Profit Subject to 4 7 10SUMPRODUCTISCS3:SD$3,C4:D4.__ By Changing Variable Cells: SCS3:SDS3 Carpentry 4.5 5 -SUMPRODUCT($CS3:SDS3,C6:D6) <-1800 Plastics 2 Completion 24 SUMPRODUCT(SC$3:SDS3,C8:D8)1200 Min Luxury -1/3 2/3 SUMPRODUCT(SC$3:$D$3,C9:D9) <0 Max Luxury2/3 -1/3 SUMPRODUCT(SC$3:SD$3,C10:D10) <0 SUMPRODUCT($C$3:SD$3,C7:D7)600 Subject to the Constraints: Add 10 Change Delete 12 13 14 15 16 17 18 19 Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options

Answer Report

Objective Cell (Max) Cell SEŞ4 Profit Original Value 0 Name Final Value 3,300 Variable Cells Cell $C$3 No. of Simple SD$3 No. of Luxury Original Value 0 0 Name Final Value Integer 150 Contin 225 Contin Constraints Cell $E$6 Carpentry SES7 Plastics SE$8 Completion SE$9 Min Luxu ŞE$10 Max Luxury Cell Value Status Slack 0 525 SE$7-SG$7 Not Binding 75 0 -100 SE$9< SG$9 Not Binding 100 25 SE$10-SG$10 Not Binding 25 Name Formula 1800 $E$6<=$G$6 Bindin 1200 ŞE$8< $G$8 Bindin

Answer 2

Add your answer here!