The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolate- coated nut clusters (Cluster), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is barely able to keep up with the increasing demand for these products; however, increasing raw material prices are forcing MNC to watch its margin to ensure it is operating in the most efficient manner possible. To meet marketing demand for the upcoming weck, MNC needs to produce at least 1,000 pounds of the whole product, between 400 and 500 pounds of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 200 pounds of the Roasted product. Each pound of the whole, Cluster, Crunch, and Roasted product contains, respectively, 60%, 40%, 20%, and 100% macadamia nuts, with the remaining weight made of chocolate coating. The company has 1.100 pounds of nuts and 800 pounds of chocolate available for use in the next week. The various products are made using four different machines that hull the nuts, roast the nuts, coat the nuts with chocolate (if needed), and package the products. Each machine can be operated 60 hours during the week. The required processing times, revenues, and variable costs for the products are shown below. Minutes Required per Pound Machine Whole Cluster Crunch Roasted Hulling 1.00 1.00 1.00 1.00 Roasting 2.00 1.50 1.00 1.75 1.00 0.70 0.20 0.00 Packaging 2.50 1.60 1.25 1.00 Coating Per Pound Revenue and Costs Whole Cluster Crunch Roasted Selling Price $7.50 $6.00 $4.80 $6.75 Variable Cost $4.73 $3.90 $3.24 $4.65 1) Provide a complete algebraic formulation to determine the product mix that maximizes next week's profit for MNC. Define the variables and write down the objective function and all constraints mathematically. 2) Create a spreadsheet model for this problem and solve it with Excel Solver. 3) What is the optimal solution? What is the optimal value? The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolate- coated nut clusters (Cluster), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is barely able to keep up with the increasing demand for these products; however, increasing raw material prices are forcing MNC to watch its margin to ensure it is operating in the most efficient manner possible. To meet marketing demand for the upcoming weck, MNC needs to produce at least 1,000 pounds of the whole product, between 400 and 500 pounds of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 200 pounds of the Roasted product. Each pound of the whole, Cluster, Crunch, and Roasted product contains, respectively, 60%, 40%, 20%, and 100% macadamia nuts, with the remaining weight made of chocolate coating. The company has 1.100 pounds of nuts and 800 pounds of chocolate available for use in the next week. The various products are made using four different machines that hull the nuts, roast the nuts, coat the nuts with chocolate (if needed), and package the products. Each machine can be operated 60 hours during the week. The required processing times, revenues, and variable costs for the products are shown below. Minutes Required per Pound Machine Whole Cluster Crunch Roasted Hulling 1.00 1.00 1.00 1.00 Roasting 2.00 1.50 1.00 1.75 1.00 0.70 0.20 0.00 Packaging 2.50 1.60 1.25 1.00 Coating Per Pound Revenue and Costs Whole Cluster Crunch Roasted Selling Price $7.50 $6.00 $4.80 $6.75 Variable Cost $4.73 $3.90 $3.24 $4.65 1) Provide a complete algebraic formulation to determine the product mix that maximizes next week's profit for MNC. Define the variables and write down the objective function and all constraints mathematically. 2) Create a spreadsheet model for this problem and solve it with Excel Solver. 3) What is the optimal solution? What is the optimal value
