Question

The New England Cheese Company produces two cheese spreads by blending mild cheddar cheese with extra sharp cheddar cheese. The cheese spreads are packaged in 12-ounce containers, which are then sold to distributors throughout the Northeast. The Regular blend contains 75% mild cheddar and 25% extra sharp, and the Zesty blend contains 70% mild cheddar and 30% extra sharp. This year, a local dairy cooperative offered to provide up to 8,500 pounds of mild cheddar cheese for $1.40 per pound and up to 2,700 pounds of extra sharp cheddar cheese for $1.60 per pound. The cost to blend and package the cheese spreads, excluding the cost of the cheese, is $0.40 per container. If each container of Regular is sold for $2.20 and each container of Zesty is sold for $2.80, how many containers of Regular and Zesty should New England Cheese produce? Do not round intermediate calculations. If required, round your answers to the nearest whole number.

Let R = number of containers of Regular

Z = number of containers of Zesty

Optimal Solution: R=_____ , Z=_____ , profit = $_____

Answer 1

Algebraic formulation Decision Variable: Let R, Z be the number of containers of Regular and Zesty blend respectively that New England Cheese should produce Objective function: Maximise Profit: 0.713R + 1.305Z Subject to constraints: (0.75R +0.72)*12/16 < 8500 (0.25R +0.3Z)*12/16 2700 R, Z20

- 017 AB foc =SUMPRODUCT(D24:E24,D20:E20) D E F G H I J K L M N O P Q R Excel Spreadsheet and using excel solver to find optimal solution Maximise Profit 15,660 Solver Parameters Regular B Solve blend 0 Value of: 0 Number of containers of each blend Selling Price Blending and Packaging Cost Raw Material Cost Profit per container Close Set Target Cell: $D$17 Equal To: Max Min By Changing Cells: $D$20:$E$20 Zesty 12000 2.800 0.400 1.095 1.305 2.200 0.400 1.088 0.713 Guess Formulas D17=SUMPRODUCT(D24:E24,020:E20) D23 --SUMPRODUCT(D29:030,$F$ 29:$F$30)*12/16 E23 --SUMPRODUCT(E29:E30,$F$ 29:$F$30) *12/16 G29 =SUMPRODUCT(D29:E29,SD$ 20:$E$20)*12/16 G30 =SUMPRODUCT(D30:E30, $D$20:$E$20)*12/16 Subject to the Constraints: $G$29:$G$30 <= $I$29:$I$30 Options Add Change Reset All Delete Help Subject to constraints: Cheddar cheese Mild Cheddar Extra Sharp R 75% 25% Z 70% 30% Cost $1.40 $1.60 LHS 6300.0 2700.0 Supply 8500 2700 Optimal Solution: z = = 0 12000 containers containers Total Profit = 15,660

Let R = number of containers of Regular Z = number of containers of Zesty

Optimal Solution: R = 0, Z = 12000, profit = $ 15660.