Case study: Nut supplier The firm makes four different products from almond nuts grown locally in Australia: raw nuts (Raw), roasted mixed nuts (Roasted), roasted and salted mixed nuts (Salted), and chili-coated mixed nuts (Chili). The firm is barely able to keep up with the increasing demand for these products. However, increasing raw material prices and foreign competition are forcing the firm to watch its margins to ensure it is operating in-the most efficient manner possible. To meet marketing demands for the coming week, the firm needs to produce at least 1,500 kg of the Raw product, between 600 and 700 kg of the Roasted product, at least 500 kg of the Salted product, and no more than 400 kg of the Chili product. Each kilogram of the Raw, Roasted, Salted, and Chili product contains, respectively, 100%, 70%, 55%, and 40% almond nuts with the remaining weight made up of other nuts. The company has 3000 kg of almond nuts and 1000 kg of other nuts 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 chili (if needed), and package the products. The following table summarises the time required by each product on each machine. Each machine has 70 hours of time available in the coming week. Minutes required per kg Machine Raw Roasted Salted Chili Hulling 1.00 1.00 1.00 1.00 Roasting 0.00 1.00 1.75 1.50 Coating 0.00 0.00 0.00 1.00 Packaging 1.50 1.50 1.25 1.00 The controller recently presented management with the following financial summary of the firm’s average weekly operations over the past quarter. Product Raw Roasted Salted Chili Total Sales Revenue $5,412 $1,846 $623 $1,165 $9,046 Variable Costs Direct materials $1,331 $560 $144 $320 $2,355 Direct labour $1,092 $400 $96 $130 $1,718 Manufacturing overhead $333 $140 $36 $90 $599 Selling & Administrative $540 $180 $62 $120 $902 Allocated Fixed Costs Manufacturing overhead $688 $331 $99 $132 $1,250 Selling & Administrative $578 $278 $83 $111 $1,050 Units Sold 1040 500 150 200 1890 1. Formulate an LP model for this problem to maximise the total profit and briefly describe each equation. 2. Create a spreadsheet model for this problem and solve it using Solver. 3. What is the optimal solution? You must report inputs, output, and required resources.4. Create a sensitivity report for this solution, and answer the following questions: a. If the firm wanted to decrease the production on any product, which one would you recommend and why? b. If the firm wanted to increase the production of any product, which one would you recommend and why? c. Which resources are preventing the firm from making more money? If the firm could acquire more of this resource, how much should it acquire and how much should it be willing to pay to acquire this resource? d. If the marketing department wanted to decrease the price of the Roasted product by $1, would the optimal solution change and why?