Add new product first then solve it by Exel.
The Molokai Nut Company (MNC) case The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolatecoated 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 and foreign competition are forcing MNC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, 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 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 up of chocolate coating. The company has 1100 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 in chocolate (if needed), and package the products. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. Minute per und Machine Whale Cluster Crunch Halling 1 10 Mig 2015 1.005 100 03 The controller recently presented management with the following financial summary of MNC's average weekly operations over the past quarter. Whale SM SUND 33 Sie 100 SINI Siles Die Dente Mandag Selling to Mangal Selling A Net SUS SIN SM S2 S - So 150 Net 05 Vete De Det er Sing A Allocated Mandaring at SA US Noteine 1. Formulate an LP model for this problem. (10 points) 2. Create a spreadsheet model for this problem and solve it using Solver. (6 points) 3. What is the optimal solution? - (2 points) 4. Create a sensitivity report for this solution and answer the following questions. (2 points) 5. If MNC wanted to decrease the production on any product, which one would you recommend and why? The company could increase profit by making fewer pounds of the Cluster product (it has a negative reduced cost). (2 points) 6. If MNC wanted to increase the production of any product, which one would you recommend and why? Each additional unit of the Roasted product adds about $0.55 to profit. (2 points) 7. Which resources are preventing MNS from making more money? If they could acquire more of this resource how much should they acquire & how much should they be willing to pay to acquire it? 8. (2 points) Packaging is the only binding constraint (aside from the upper and lower variable bounds). Each additional unit of packaging material is worth $0.772 (above and beyond its normal variable cost). 9. How much should MNC be willing to pay to acquire more chocolate? (2 points) SO. It is a non-binding resource. 10. If the marketing department wanted to decrease the price of the whole product by $0.25, would the optimal solution change? (2 points) No, it could decrease by $0.3057 without changing the optimal solution. The Molokai Nut Company (MNC) case The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolatecoated 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 and foreign competition are forcing MNC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, 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 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 up of chocolate coating. The company has 1100 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 in chocolate (if needed), and package the products. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. whole 1.00 Machine Hulling Roasting Coating Packaging Minutes Required per Pound Cluster Crunch Roasted 1.00 1.00 1.00 1.50 1.00 1.75 0.70 0.20 0.00 1.60 1.25 1.00 2.00 1.00 2.50 The controller recently presented management with the following financial summary of MNC's average weekly operations over the past quarter. Whole $5,304 Total Product Crunch 5510 Cluster $1,800 Roasted S925 58,539 $1,331 51,092 5333 5540 5560 $400 5140 $180 5144 596 536 562 5320 5130 S90 $120 S2,355 $1,718 5599 5902 Sales Revenue Variable Costs Direct materials Direct labor Manufacturing overhead Selling & Administrative Allocated Fixed Costs Manufacturing overhead Selling & Administrative Net Profit Units Sold Net Profit Per Unit 5688 5578 $742 1040 S0.71 5331 S278 -S88 500 -0.18 599 583 -S11 150 -S0.07 5132 S111 S22 200 s0.11 $1,250 $1,050 5665 1890 $0,35 1. Formulate an LP model for this problem. (10 points) 2. Create a spreadsheet model for this problem and solve it using Solver. (6 points) 3. What is the optimal solution?. (2 points) 4. Create a sensitivity report for this solution and answer the following questions. (2 points) 5. If MNC wanted to decrease the production on any product, which one would you recommend and why? The company could increase profit by making fewer pounds of the Cluster product (it has a negative reduced cost). (2 points) 6. If MNC wanted to increase the production of any product, which one would you recommend and why? Each additional unit of the Roasted product adds about $0.55 to profit. (2 points) 7. Which resources are preventing MNS from making more money? If they could acquire more of this resource how much should they acquire & how much should they be willing to pay to acquire it? 8. (2 points) Packaging is the only binding constraint (aside from the upper and lower variable bounds). Each additional unit of packaging material is worth $0.772 (above and beyond its normal variable cost). 9. How much should MNC be willing to pay to acquire more chocolate? (2 points) $0. It is a non-binding resource. 10. If the marketing department wanted to decrease the price of the whole product by $0.25, would the optimal solution change? (2 points) No, it could decrease by $0.3057 without changing the optimal solution. The Molokai Nut Company (MNC) case The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolatecoated 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 and foreign competition are forcing MNC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, 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 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 up of chocolate coating. The company has 1100 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 in chocolate (if needed), and package the products. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. Minute per und Machine Whale Cluster Crunch Halling 1 10 Mig 2015 1.005 100 03 The controller recently presented management with the following financial summary of MNC's average weekly operations over the past quarter. Whale SM SUND 33 Sie 100 SINI Siles Die Dente Mandag Selling to Mangal Selling A Net SUS SIN SM S2 S - So 150 Net 05 Vete De Det er Sing A Allocated Mandaring at SA US Noteine 1. Formulate an LP model for this problem. (10 points) 2. Create a spreadsheet model for this problem and solve it using Solver. (6 points) 3. What is the optimal solution? - (2 points) 4. Create a sensitivity report for this solution and answer the following questions. (2 points) 5. If MNC wanted to decrease the production on any product, which one would you recommend and why? The company could increase profit by making fewer pounds of the Cluster product (it has a negative reduced cost). (2 points) 6. If MNC wanted to increase the production of any product, which one would you recommend and why? Each additional unit of the Roasted product adds about $0.55 to profit. (2 points) 7. Which resources are preventing MNS from making more money? If they could acquire more of this resource how much should they acquire & how much should they be willing to pay to acquire it? 8. (2 points) Packaging is the only binding constraint (aside from the upper and lower variable bounds). Each additional unit of packaging material is worth $0.772 (above and beyond its normal variable cost). 9. How much should MNC be willing to pay to acquire more chocolate? (2 points) SO. It is a non-binding resource. 10. If the marketing department wanted to decrease the price of the whole product by $0.25, would the optimal solution change? (2 points) No, it could decrease by $0.3057 without changing the optimal solution. The Molokai Nut Company (MNC) case The Molokai Nut Company (MNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolatecoated 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 and foreign competition are forcing MNC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, 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 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 up of chocolate coating. The company has 1100 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 in chocolate (if needed), and package the products. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. whole 1.00 Machine Hulling Roasting Coating Packaging Minutes Required per Pound Cluster Crunch Roasted 1.00 1.00 1.00 1.50 1.00 1.75 0.70 0.20 0.00 1.60 1.25 1.00 2.00 1.00 2.50 The controller recently presented management with the following financial summary of MNC's average weekly operations over the past quarter. Whole $5,304 Total Product Crunch 5510 Cluster $1,800 Roasted S925 58,539 $1,331 51,092 5333 5540 5560 $400 5140 $180 5144 596 536 562 5320 5130 S90 $120 S2,355 $1,718 5599 5902 Sales Revenue Variable Costs Direct materials Direct labor Manufacturing overhead Selling & Administrative Allocated Fixed Costs Manufacturing overhead Selling & Administrative Net Profit Units Sold Net Profit Per Unit 5688 5578 $742 1040 S0.71 5331 S278 -S88 500 -0.18 599 583 -S11 150 -S0.07 5132 S111 S22 200 s0.11 $1,250 $1,050 5665 1890 $0,35 1. Formulate an LP model for this problem. (10 points) 2. Create a spreadsheet model for this problem and solve it using Solver. (6 points) 3. What is the optimal solution?. (2 points) 4. Create a sensitivity report for this solution and answer the following questions. (2 points) 5. If MNC wanted to decrease the production on any product, which one would you recommend and why? The company could increase profit by making fewer pounds of the Cluster product (it has a negative reduced cost). (2 points) 6. If MNC wanted to increase the production of any product, which one would you recommend and why? Each additional unit of the Roasted product adds about $0.55 to profit. (2 points) 7. Which resources are preventing MNS from making more money? If they could acquire more of this resource how much should they acquire & how much should they be willing to pay to acquire it? 8. (2 points) Packaging is the only binding constraint (aside from the upper and lower variable bounds). Each additional unit of packaging material is worth $0.772 (above and beyond its normal variable cost). 9. How much should MNC be willing to pay to acquire more chocolate? (2 points) $0. It is a non-binding resource. 10. If the marketing department wanted to decrease the price of the whole product by $0.25, would the optimal solution change? (2 points) No, it could decrease by $0.3057 without changing the optimal solution
