Reference no: EM133052515

The Hawaiian Nut Company (HNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolate-coated nut clusters (Clusters), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is barely able to keep up with demand for these products. However, increasing raw materials prices and foreign competition are forcing HNC to watch its margins to ensure it is operating in the most efficient manner possible. To meet marketing demands for the coming week, HNC needs to produce at least 900 pounds of the Whole product, between 200 and 500 pounds of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 250 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 up of chocolate coating. The company has 1,100 pounds of macadamia 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 product. 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 HNC's average weekly operations over the past quarter (next page). From this report, the controller is arguing that the company should cease producing its Cluster and Crunch products.

	Whole	Cluster	Crunch	Roasted	Total
Sales Revenue	$5,125.00	$1,800.00	$500.00	$930.00	$8,355.00
Variable Costs
Direct Materials	$1,330.00	$560.00	$122.50	$320.00	$2,332.50
Direct Labor	$1,081.50	$400.00	$95.00	$130.00	$1,706.50
Manufacturing	$330.00	$140.00	$45.00	$90.00	$605.00
Selling & Administrative	$550.00	$180.00	$65.00	$120.00	$915.00
Allocated Fixed Costs
Manufacturing Overhead	$659.72	$347.22	$104.17	$138.89	$1,250
S&A Overhead	$554.17	$291.67	$87.50	$116.67	$1,050
Net Profit	$620	($119)	($19)	$14	$496
Pounds Sold	950	500	150	200	1800
Net Profit Per Pound	$0.65	($0.24)	($0.13)	$0.07	$0.28

Tasks:

Perform the tasks outlined in the order provided below. You should have only one model worksheet in your Excel workbook. Include a worksheet tab labelled 'Answers' on which you will answer questions for Tasks 1 & 4. 

1. Before building the Linear Optimization model, do you agree with the controller's recommendation to cease production of the Cluster and Crunch products? Why or why not?

1. Build, and solve, your Linear Optimization model on a worksheet labeled 'Model'. Create the Sensitivity Report for the optimal solution (make sure the worksheet is labeled 'Sensitivity Report'). Three hints on building the model:

• Given that the Allocated Fixed Costs are assigned based on the proportion of pounds of each product compared total pounds produced, you should not consider these in your Optimization/Solver model as the values will change based on the optimal solution. Instead, your optimization model should be based only on the other values, which are constant regardless of production quantities. This is value is often called the Contribution (equal to Revenue minus Variable Costs).
• Therefore, you cannot use Profit directly in the model. You will need to perform some calculations to find key coefficient values for use in the model. However, for the sake of clarity, these calculations should NOT appear on your Optimization model spreadsheet but can be included on a separate worksheet within your workbook (labeled 'Calculations') and you can then copy/paste the values or use references in your model back to these calculations, but make sure the coefficients are shown on your 'Model' sheet.
• Make sure to include any upper or lower limit constraints in a manner that facilitates comprehensive analysis via the Sensitivity Report (as discussed in the Chapter 4 Part 2 video, you want to avoid direct Bound Constraints), or you may be unable to fully answer questions in Task 4.

2. After finding your optimal solution, at the bottom of the 'Model' worksheet, perform the following additional work so that you can compare your results with controller's information presented in the table above:

• Allocate the two Fixed Costs proportionally across the four products based on the optimal production quantities and total quantity (not original quantities).
• Calculate the new Net Profit for each of the four products, as well as the Total Net Profit.
• Calculate the new Net Profit Per Pound for each of the four products.

1. Answer the following questions. Your answers to parts a-e should be derived from the 'Sensitivity Report'. Be explicit and precise with quantities and numerical impacts.

1. If you could decrease the production of any product, which one? How much would each pound decrease change the Objective Function? By how many pounds would you decrease production?

2. If you could increase the production of any product, which one? How much would each pound increase change the Objective Function? By how many pounds would you increase production?

3. If you could acquire more of any raw material, which one would you acquire, how much would you acquire, and what is the value of these additional units of this raw material?

4. If you could add more time to any of the machines, to which machine would you add time, how much time would you add, and what is the value of this additional time?

5. Management is considering raising the price of the Whole product by $0.25. Will this change the current optimal solution?

Based on the optimal solution and your responses above, would your answer to the question in Task 1 be different now? Why