he 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 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.

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 Coating 1.00 0.70 0.20 0.00 Packaging 2.50 1.60 1.25 1.00 The controller recently presented management with the following financial summary of MNCs average weekly operations over the past quarter. From this report, the controller is arguing that the company should cease producing its Cluster and Crunch products.

Product Whole Cluster Crunch Roasted Total Sales Revenue $5,304.00 $1,800.00 $510.00 $925.00 $8,539.00 Variable Costs Direct materials $1,331.00 $560.00 $144.00 $320.00 $2,355.00 Direct labor $1,092.00 $400.00 $96.00 $130.00 $1,718.00 Manufacturing overhead $333.00 $140.00 $36.00 $90.00 $599.00 Selling & Administrative $540.00 $180.00 $62.00 $120.00 $902.00 Allocated Fixed Costs Manufacturing overhead $687.83 $330.69 $99.21 $132.28 $1,250.01 Selling & Administrative $577.78 $277.78 $83.33 $111.11 $1,050.00 Net Profit $742.39 $88.47 $10.54 $21.61 $664.99 Units Sold 1040 500 150 200 1890 Net Profit Per Unit $0.7138 $0.1769 $0.0703 $0.1081 $0.3518 Do you agree with the controllers recommendation? Why or why not?

Formulate an LP model for this problem.

Create a spreadsheet model for this problem and solve it using Solver.

What is the optimal solution?

Create a sensitivity report for this solution and answer the following questions.

Is the solution degenerate?

Is the solution unique?

If MNC wanted to decrease the production on any product which one would you recommend and why?

If MNC wanted to increase the production of any product which one would you recommend and why?

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?

How much should MNC be willing to pay to acquire more chocolate?

If the marketing department wanted to decrease the price of the Whole product by $0.25 would the optimal solution change?

Create a Spider plot showing the impact on net profit of changing each products required time in the packaging process from between 70% to 130% of their original values in 5% increments. Interpret the information in the resulting chart.

Create a Spider plot showing the impact on net profit of changing the availability of nuts and chocolate from between 70% to 100% of their original values in 5% increments. Interpret the information in the resulting chart.