Problem 1 (26 points)The J. Mehta Companyâ??s production manager is planning a series of one-month p

Problem 1 (26 points)The J. Mehta Company’s production manager is planning a series of one-month productionperiods for stainless steel sinks. The forecasted demand for the next four months is as follows:Month1234 Demand for Stainless Steel Sinks120160240100 The Mehta firm can normally produce 100 stainless steel sinks in a month. This is done duringregular production hours at a cost of $100 per sink. If demand in any one month cannot besatisfied by regular production, the production manager has three other choices:(1) he can produce up to 50 more sinks per month in overtime but at a cost of $130 per sink;(2) he can purchase a limited number of sinks from a friendly competitor for resale (themaximum number of outside purchases over the four-month period is 450 sinks, at acost of $150 each);(3) Or, he can fill the demand from his on-hand inventory (i.e. beginning inventory). Theinventory carrying cost is $10 per sink per month (i.e. the cost of holding a sink ininventory at the end of the month is $10 per sink).A constant workforce level is expected. Back orders are NOT permitted (e.g. order taken inperiod 3 to satisfy the demand in later period 2 is not permitted). Inventory on hand at thebeginning of month 1 is 40 sinks (i.e. beginning inventory at month 1 is 40 sinks)a. Set up and formulate algebraically the above “production scheduling” problem asa TRANSPORTATION Model to minimize cost. (16 points)b. SOLVE using Excel solver (Provide a printout of the corresponding “ExcelSpreadsheet” and the “Answer Report”). Also include a managerial statement thatdescribes verbally the results. (10 points)Note: This problem can be formulated as a multiperiod production scheduling LPproblem. However, DO NOT try to formulate it this way as the problem requirementis to formulate it as a transportation problem.