On the tab labeled "Original Problem", I 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 sentence(s) 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!
Question 1 (3 points). Managment has incorporated a new fee structure for its production process. They have decided to reduce all fixed costs by 10% (round all decimals up to the next whole dollar) and increase all variable costs by $4. What is the new optimal solution? HINT: This scenario requires you to make changes in the B and C columns then load the solver. When you change the
values in Column B, you should round them to the nearest whole dollar. The solver setup will be the same as shown on the Original Problem.
Question 2 (3 points). Machine 4 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 both machines (3 and 6) are used while Machine 4 is under repair. Management wants Machines 3 and 6 to produce at least 110 shoes per machine. What is the resulting optimal solution (give total cost only after re-solving 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.
Question 3 (4 points). With new car sales down due to the poor economy and as a supplier of brake shoes for older vehicles, Radford has received an order for 2,600 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 25% to 45% of the required total on machines not normally used (Machines 3 and 6). The remainder of the load (i.e., the difference between 2,600 and the total number of brake shoes made on Machines 3 and 6 combined) must be split over the machines such that each machine makes at least 10% of the difference. What is the optimal solution and cost of this strategy (give both only after re-solving the model). HINT: I would start this problem by changing Cell E15 to 2600. Next, I would create a new cell in the spreadsheet that computes 25% of the required total and another cell that computes 45% of the required total. A third new cell should compute the sum of shoes made by Machines 3 and 6 combined. Finally, in the spreadsheet, I would create another new cell that computes 10% of the difference between the required number of shoes and total made by Machines 3 and 6. From here, the solver setup will be the same as shown on the Original Problem tab but will also require additional constraint(s) to incorpoate this new production scenario. The new constraints will be comparisons of 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 optimal solution and total cost.
Question 1 (3 points). Managment has incorporated a new fee structure for its production process. They have decided to reduce all fixed costs by 10% (round all decimals up to the next whole dollar) and increase all variable costs by $4. What is the new optimal solution? HINT: This scenario requires you to make changes in the B and C columns then load the solver. When you change the
values in Column B, you should round them to the nearest whole dollar. The solver setup will be the same as shown on the Original Problem.
Question 2 (3 points). Machine 4 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 both machines (3 and 6) are used while Machine 4 is under repair. Management wants Machines 3 and 6 to produce at least 110 shoes per machine. What is the resulting optimal solution (give total cost only after re-solving 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.
Question 3 (4 points). With new car sales down due to the poor economy and as a supplier of brake shoes for older vehicles, Radford has received an order for 2,600 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 25% to 45% of the required total on machines not normally used (Machines 3 and 6). The remainder of the load (i.e., the difference between 2,600 and the total number of brake shoes made on Machines 3 and 6 combined) must be split over the machines such that each machine makes at least 10% of the difference. What is the optimal solution and cost of this strategy (give both only after re-solving the model). HINT: I would start this problem by changing Cell E15 to 2600. Next, I would create a new cell in the spreadsheet that computes 25% of the required total and another cell that computes 45% of the required total. A third new cell should compute the sum of shoes made by Machines 3 and 6 combined. Finally, in the spreadsheet, I would create another new cell that computes 10% of the difference between the required number of shoes and total made by Machines 3 and 6. From here, the solver setup will be the same as shown on the Original Problem tab but will also require additional constraint(s) to incorpoate this new production scenario. The new constraints will be comparisons of 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 optimal solution and total cost.
