The Sunshine Food Processing Company produces three commercial grade canned fruit products - mixed fruit, fruit cocktail

[answerhappygod]

The Sunshine Food Processing Company Produces Three Commercial Grade Canned Fruit Products Mixed Fruit Fruit Cocktail 1 (75.44 KiB) Viewed 39 times

The Sunshine Food Processing Company Produces Three Commercial Grade Canned Fruit Products Mixed Fruit Fruit Cocktail 2 (69.42 KiB) Viewed 39 times

The Sunshine Food Processing Company Produces Three Commercial Grade Canned Fruit Products Mixed Fruit Fruit Cocktail 3 (86.33 KiB) Viewed 39 times

The Sunshine Food Processing Company produces three commercial grade canned fruit products - mixed fruit, fruit cocktail, and fruit delight. The main ingredients in each of the three products are pears and peaches. Each product is produced in lots and must go through three processes mixing, canning, and packaging. The company can easily sell all that it can make. The company uses linear programming to plan its quarterly production. The company has a contract with Lightshine Company to supply at least 15,000 cans of fruit delight. The resource requirements and profit margin for all three products are shown below. Any cost not listed or mentioned in the problem can be considered sunk. Product Resources Profit ($/can) Pears Peaches Mixing Canning Packaging (hrs) (lbs) (lbs) (hrs) (hrs) Mixed fruit 20 10 1 1 2 10 Fruit cocktail 10 20 2 1 1 6 Fruit delight 16 16 2 1 1 8 Availability 320,000 400,000 43,000 60,000 40,000 Let X1, X2, X3 be the number of cans (in 1000s) of the three products to be produced during the next quarter. Max 10 X1 + 6 X2 + 8 X3 (profit in 1000s of $) st 20 X1 + 10 X2 + 16 X3 < 320 (pears in 1000s of lbs) 10 X1 + 20 X2 + 16 X3 < 400 (peaches in 1000s of lbs) X1 + 2 X2 + 2 X3 < 43 (mixing in 1000s of hrs) X1 + X2 + X3 < 60 (canning in 1000s of hrs) 2 X1 + X2 + X3 <40 (packaging in 1000s of hrs) X3 > 15 (Lightshine demand in 1000s of cans) X1, X2, X3 > 0 (Nonnegativity)
Using the attached Excel output to answer the following questions. No need to run the Solver, a) What are the units of shadow prices of the pears and packaging constraint? b) Which of the resources constrains the total contribution margin? c) What is the maximum price the company would be willing to pay for additional lbs of peaches? How much should be purchased? Justify d) A fruit grower is offering to sell pears at $0.467/lb. Will you buy additional quantities? If so, how much? Justify your decision. e) The company can purchase a new mixing machine that would increase the available mixing time from 43,000 to 45,000 hours. How much is this additional capacity worth? f) The company can also purchase a new packaging machine that would increase the available packaging time from 40,000 to 50,000 hours. Would this impact the optimal solution? g) Due to a problem with the fruit supplier, only 320,000 pounds of peaches will be available. Will this impact the optimal product mix? h) If the manager were to attempt to secure additional units of only one of the resources, what should it be? Justify. i) If the company revised the selling price of fruit cocktail so that the profit margin increased from $6 to $10, would the optimal solution change? j) There is an opportunity to renegotiate the contract of fruit delight with lightsbine Company. What is your recommendation?
DECISION VARIABLES Product Production Qty (1000s of cans). OBJECTIVE COEFFICIENT Sunshine Food Company Mixed Fruit Fruit Cocktail Fruit Delight 1.00 6.00 15.00 10 8 Resource Usage Unit Profit Margin (S). CONSTRAINTS 1 Pears (1000s of lbs) Peaches (1000s of lbs) Mixing (1000s of hours) Canning (1000s of hours) Packaging (1000s of hours) Lightshine Demand (1000s of cans) 1 2 0 Microsoft Excel 15.0 Answer Report Objective Cell (Max) Cell Name SES8 Unit Profit Margin (5) Total Profit (1000s of 5) Vanable Cells Cell Name $B$5 Production Qty (1000s of cans), Mixed Fruit SCS5 Production Qty (1000s of cans) Fruit Cocktail SDS5 Production Qty (1000s of cans) Fruit Delight Constraints Cell Name SES11 Pears (1000s of lbs) LHS SE$12 Peaches (1000s of lbs) LHS SE513 Mixing (1000s of hours) LHS SES14 Canning (1000s of hours) LHS SES15 Packaging (1000s of hours) LHS SES16 Lightshine Demand (1000s of cans) LHS Microsoft Excel 15.0 Sensitivity Report Variable Cells Cell Name SBS5 Production Qty (1000s of cans). Mixed Fruit SCS5 Production Qty (1000s of cans). Fruit Cocktail SDS5 Production Qty (1000s of cans) Fruit Delight Constraints Cell Name SES11 Pears (1000s of lbs) LHS SES12 Peaches (1000s of lbs) LHS SES13 Mixing (1000s of hours) LHS SES14 Canning (1000s of hours) LHS $E$15 Packaging (1000s of hours) LHS SES16 Lightshine Demand (1000s of cans) LHS 20 10 16.00 16.00 2 200 1 1.00 1 1,00 10 1.00 Original Value Final Value 0 166 Original Value Final Value 0.00 0.00 0.00 Final Value 1 6 10 20 15 Final Value Cell Value Total Profit (1000s of $) 166 LHS RHS 320.00< 320 370,00 400 43.00 € 43 22.00 € 60 23.00 € 15.00 OOO 1.00 Contin 6.00 Contin 15.00 Contin Formula Status 320.00 $E$11<=$G$11 Binding 370.00 $E$12<=$G$12 Not Binding 43.00 SE$13 $GS13 Binding 22.00 SE$14<-SGS14 Not Binding 23.00 SE$15<=$G$15 Not Binding 15.00 SE$16 $G$S16 Binding Reduced Cost 0 Slack 0 30 0 38 17 0.00 Objective Allowable Allowable Coefficient Increase Decrease 0 10 12 2 14 1 8 08 1E+30 Shadow Constraint Allowable Allowable Increase Decrease 170 15 1E+30 30 3 9 1E+30 1E+30 2.5 0 Integer E968 588 2 320 Price RH. Side 320 0.466666667 370 43 0.666666667 0 400 43 22 60 23 0 40 15 -0.8 15 40 15 38 17 7.5