STATS – Kamm Industries

On the tab labeled "Case Update", I have provided the modeling of Case 4.3 beginning on Pages 184. Carefully read the case in the textbook and then review the model to familiarize yourself with the integration of the LP into Excel.On the "Case Update" tab, there are five questions. After loading the ASPE solver, solve the model, obtain the sensitivity report, and use the sensitivity report to answer Questions 1 though 5. Each question is worth 16 points: 8 points for correctly identifying the cell value(s) you used from the sensitivity report to justify your response and 8 points for a clear, concise, grammatically response correctly answering the question. Your responses should be typed in unbolded black font right after the statement of each question. (HINT: If you have loaded the ASPE solver correctly, Cell N22 should reflect a total cost between $1.9M and $2.0M to fill this order)Remember, cases are individual work. Good luck and as always…HAVE FUN!Kamm IndustriesCarpet123456789101112131415DemandMake(Yds)Dobbie Pantera14,0000na52,0000na44,0000na20,0000na77,50000109,50000120,0000060,000007,5000069,5000068,5000083,0000010,00000381,0000064,00000Buy000000000000000Total000000000000000DobbiePanteraYd/HrHr/YdYd/HrHr/Yd4.510 0.221729nana4.796 0.208507nana4.629 0.216029nana4.256 0.234962nana5.145 0.194363 5.4280.184233.806 0.262743 3.9350.254134.168 0.239923 4.316 0.2316965.251 0.19044 5.356 0.1867065.223 0.191461 5.277 0.1895025.216 0.191718 5.419 0.1845363.744 0.267094 3.835 0.2607564.157 0.240558 4.291 0.2330464.422 0.226142 4.558 0.2193945.281 0.189358 5.353 0.1868114.222 0.236855 4.288 0.233209Used0Used0Available32370 Available 172640Make CostDobbiePantera$2.66na$2.55na$2.64na$2.56na$1.61$1.60$1.62$1.61$1.64$1.61$1.48$1.47$1.50$1.50$1.44$1.42$1.64$1.64$1.57$1.56$1.49$1.48$1.31$1.30$1.51$1.50$0$0Total CostBuy Cost$2.77$2.73$2.85$2.73$1.76$1.76$1.76$1.59$1.71$1.63$1.80$1.78$1.63$1.44$1.69$0$01. (16 Points) Load the solver. To get you started, I have defined the objective cell and decisionvariables in the solver. From there, the LP can be modeled with three constraints: total demand foreach of the 15 types of carpet, max hours on Dobbie machines, and max hours on Panteramachines. What is the cost required to meet demand?2. (16 Points) In the sensitivity report, refer to the Constraints section and to the informationprovided for cell K21 which is associated to the numer of Pantera hours used. What would happento the total cost if one of the Pantera machines broke and could not be used during the 13-weekproduction period?3. (16 Points) In the sensitivity report, refer to the Constraints section and to the informationprovided for cell I21 which is associated to the numer of Dobbie hours used. What would happento the total cost if an additional Dobbie machine was added for the 13-week production period?4. (16 Points) In the Shadow Price column of the Constraints section, format the cells to showthese values to two decmial places. Explain the shadow prices and the values in the AllowableIncrease column of the Sensitivity Report for the products that are being outsourced.5. (16 Points) If the carpets in Orders 5 through 15 all sell for the same amount, which type ofcarpet should Kamm encourage its sales force to sell more of? Why?