Q1. Refer to Problem 8 Assignment I to answer the following questions a) Give the full formulation and clearly define the decision variables, constraints, and the objective function (2 marks). b) Solve the above problem using EXCEL SOLVER. You must provide the followings: 1. Write down the optimal values of the objective function and the decision variables (0.5 mark). 2. A screen shot of the formulation sheet before solving the problem (0.5 mark). 3. A screen shot of the parameters' insertion window (0.5 mark). 4. A screen shot of the answer report (0.5 mark). 5. A screen shot of the formulation sheet after solving the problem (0.5 mark). 6. A screen shot of the sensitivity report (0.5 mark) c) Use the answer report to answer the following questions: 1. How much tonnages are transported from the third lumber company? (0.5 mark). 2. Does the supply from the second lumber company to the third plant reach the maximum capacity? (0.5 mark) d) Use the sensitivity report to answer the following questions 1. What is the feasibility range of the total tonnages transported from the third lumber company? (0.5 mark) 2. What is the optimality range of the tonnages transported from the second lumber company to the second plant? (0.5 mark) 3. What would be the optimal total cost if the supply capacity of the third lumber becomes 700? (0.5 mark) 4. What would be the optimal total cost if the supply capacity from the third lumber company to the first plant is reduced to 100? (0.5 mark) Problem 8 A furniture manufacturer has three plants that need 500, 700, and 600 tons of lumber weekly. The manufacturer may purchase the lumber from three lumber companies. The first two lumber manufacturers virtually have an unlimited supply and, because of other commitments, the third manufacturer cannot ship more than 500 tons weekly. The first lumber manufacturer uses rail for transportation and there is no limit on the tonnage that can be shipped to the furniture facilities. On the other hand, the last two lumber companies use trucks that limit the maximum tonnage that can be shipped to any of the furniture companies to 200 tons. The following table gives the transportation cost from the lumber companies to the furniture manufacturers ($ per ton). Formulate the problem as a linear program. LUMBER FURNITURE FACILITY COMPANY 1 2 3 1 1 3 5 2 3.5 4 4.8 3 3.6 3.2 NO