Problem 1: Transportation problem (25 points) The J. Mehta Company's production manager is planning a series of one-month production periods for stainless steel sinks. The forecasted demand for the next four months is as follows: Month 2 Demand for Stainless Steel Sinks 90 100 250 150 The Mehta firm can normally produce 75 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices: (1) he can produce up to 40 more sinks per month in overtime but at a cost of $150 per sink; (2) he can purchase a limited number of sinks from a friendly competitor for resale (the maximum number of outside purchases over the four-month period COMBINED is 200 sinks (NOT 200 units each month), at a cost of $200 each; (3) Or, he can fill the demand from his on-hand inventory. The ending inventory cost is $20 per sink per month A constant workforce level is expected. Back orders are NOT permitted (eg. order taken in period 3 to satisfy demand in later period 2 is not permitted). Inventory on hand at the beginning of month 1 is 40 sinks. a. Formulate algebraically the above problem as a TRANSPORTATION Linear Programming model. Define the decision variables, objective function, and constraints. (16 points) Hint: Draw the transportation network model that corresponds to the problem, to figure out how to formulate. b. Formulate this same problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report). Include "managerial statements" that communicate the results of the analyses. (9 points) Hint: Use the Excel spreadsheet formulation of a transportation problem (refer to the problem discussed in class). Problem 1: Transportation problem (25 points) The J. Mehta Company's production manager is planning a series of one-month production periods for stainless steel sinks. The forecasted demand for the next four months is as follows: Month 2 Demand for Stainless Steel Sinks 90 100 250 150 The Mehta firm can normally produce 75 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices: (1) he can produce up to 40 more sinks per month in overtime but at a cost of $150 per sink; (2) he can purchase a limited number of sinks from a friendly competitor for resale (the maximum number of outside purchases over the four-month period COMBINED is 200 sinks (NOT 200 units each month), at a cost of $200 each; (3) Or, he can fill the demand from his on-hand inventory. The ending inventory cost is $20 per sink per month A constant workforce level is expected. Back orders are NOT permitted (eg. order taken in period 3 to satisfy demand in later period 2 is not permitted). Inventory on hand at the beginning of month 1 is 40 sinks. a. Formulate algebraically the above problem as a TRANSPORTATION Linear Programming model. Define the decision variables, objective function, and constraints. (16 points) Hint: Draw the transportation network model that corresponds to the problem, to figure out how to formulate. b. Formulate this same problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report). Include "managerial statements" that communicate the results of the analyses. (9 points) Hint: Use the Excel spreadsheet formulation of a transportation problem (refer to the problem discussed in class)