0n the tab labeled "Original Problem", l have provided the modeling of Problem #17 on Page 307. Carefully read the statement of the problem in the textbook and then review the model and solver set up to familiarize yourself with the integration of the LP into Excel. Cells that are highlighted in RED should not be changed by students throughout the exercise [though some will change automatically as the problem is re-solved); however. you are free to change any of the GREEN cells as necessary to answer the questions related to the problem. There are three questions; each question has a corresponding tab in this file. Each question should be viewed independently from the others [i.e._. each of the three questions should be treated as a stand alone change from the original problem. So, please make the required changes and solve the updated model for each question]. To answer each question, you will be required to change information in the respective spreadsheet and then load the solver from scratch similar to what you observed in the ASPE videos. In your response to each question, you should begin with a written sentencels] that explains how you manipulated the original problem followed by the resulting optimal solution after re-solving the model. Questions 1 and 2 are worth 3 points; Question 3 is worth 4 points. Questions should be answered on the respective tabs and typed in unbolded black font right after the statement of the question. Good luck, and as always...HAVE FUN! Linking consume-adds ned coats formachhea used during prudsclian Manhlna leadcmt V'lahla Gail: -: Ilade Bins VH'IIIIIE- Question 1 {3 points]. Managment has Incorporated a new fee structure for its production process. They hm decided to reduce all xed costs by 5% and increase all variable costs by :3. What is the new optimal solution? HINT: This scenario requires you to malts changes in the B and :1: columns than load the solver. When you change the values in Column B, you should round them to lite nearest whole dollar. The solver setup will be the same as shown on the Original Problem. Linking cansh-aintsdda xed costs 'lnrmachhaa used during pruduion tted Coil V'Iahla- Gail: Question 2 {3 points]. Machine 2 suffered a catastrophic failure injuring an employee. The machine cannot be used for the next six weeks until parts are received and the machine is fixed. In light of this issue and given that Machines 3 and 6 do not contribute to the optimal solution in the original problem. management wants to ensure bout machines {3 and 6:: are used while Machine 2 is under repair. Management wants Machines 3 and ii to produce at least 100 shoes per machine. What is the resulting optimal solution [give total cost only after re-solvlng the problem]. HINT: This scenario does not require any change in the spreadsheet. The solver setup will be the same as shown on the Original Problem tab but will also require additional constraints to incorpoate this new production scenario. Once you have made this change and loaded the solver. solve the problem and report your updated solution. Linking Gunsueint-edds xed costs for machines mad daring prediction Machine Fixadcml Vu'lahlacm -: ladle Bins Unlabla Question 3 (4 points]. IHliith new car sales down due to the poor economy and as a supplier of bralte shoes for older vehicles, Redford has received an order for 2,500 new brake shoes [this is the new required value). To ensure their best opportunity for success in meeting this new requirement, they plan to produce 3051'. to m of the required total on machines not normally used [Machines 3 and a). The remainder of the load (i.e., the difference between 2,500 and the total number of brake shoes made on lilachines 3 and 5 combined) must be split over the machines such that each machine makes at least 1051'. of the difference. What is the optimal solution for this strategy {give total cost only after re-solying the model). HINT: I would start this problem by changing Cell :15 to 2500. Idem1 I would create a new call in thespreadsheet that computes 30% of the required total and another cell that computes 40% or the required total. A third new cell should compute the sum oi shoes made by Machines 3 and 6 combined. Finally, in the spreadsheet. I would create another new cell that computes 10% ot the difference between the required number at shoes and total made by Illachines 3 and 6. From here. the solver setup will be the same as shoum on the Original Problem tab but will also require additional constraint[a) to incorpoate this new production scenario. The new constraints will be comparisons oi the decision variables with the new cells created per my hint. Once you have made this change and loaded the solver, solve the problem and report your updated solution