A local soft drinks manufacturing company operates a plant in the northern part of the country. The plant produces soft drinks in three types of packages that include returnable glass bottles, aluminum cans, and nonreturnable plastic bottles. In response to the continued growth in demand, the company wants to build another plant. The anticipated demand for the soft drinks" three types of packages (expressed in cases) over the next 3 years is shown in Table 1 below. Also, the planned production capacities for the existing plant extrapolated over the same 3-year horizon are given in Table 2 below. Table 1. Demand for the three types of packages over the next 3 years Year 2 2450 Package Returnables Cans Nonreturnables 1 2400 1750 490 2000 550 3 2600 2300 600 Table 2. Planned production capacity for the existing plant over the next 3 years Year 2 1400 Package Returnables Cans Nonreturnables Warehouse NI N2 CI C2 SI S2 1 1800 1250 350 The company owns six distribution warehouses: NI and N2 are located in the north, CI and C2 located in the central section, S/ and S2 which are located in the south of the country. The share of sales by each warehouse within its zone is given in Table 3. Approximately 60% of the overall sales occur in the north, 15% in the central section, and 25% in the south. Warehouse NI N2 CI C2 SI $2 1350 380 Table 3. Sales share for each warehouse within its zone Sales percentage 0.80 1.20 1.50 1.60 1.90 2.10 85 15 60 40 80 20 The company wants to construct the new plant either in the central section or in the south. The transportation cost per case of returnable bottles is given in Table 4 below. It is estimated that the transportation costs per case of cans and per case of non-returnables are, respectively, 60% and 70% of that of the returnable bottles. 3 1900 1400 400 Table 4. Transportation cost per case (S) of the returnable bottles Existing plant Central plant 1.30 1.90 1.05 0.80 1.50 1.70 South plant 1.90 2.90 1.20 1.60 0.90 0.80 You have been hired by the company to assist them with the strategic decision of whether the new plant is to be located in the central or the southern section of the country. Conduct the necessary analysis and provide the management with a report that addresses the questions to follow. Managerial Report a) Using your own wordings, describe the problem at hand and clearly list what the objective(s) of the analysis is. b) For each potential location of the new plant (e.g. central and southern part of the country). formulate the problem as a linear program that determines the optimal shipping schedule when considering each location separately. Clearly define the decision variables and provide a verbal description of the objective function as well as each of the constraints. c) How many decision variables and functional constraints do the models developed in part (b) include? d) Solve the models developed in part (b) using Excel solver. e) Solve the models developed in part (b) using Lingo. For each potential location, use a tabular format to clearly summarize the optimal solution found (i.e. the optimal values for all decision variables), as well as the associated optimal objective function value? g) For each potential location, is the optimal solution obtained degenerate? Why? h) For each potential location, is the optimal solution obtained unique? Why? i) In light of the optimal solutions obtained, should the new plant be located in the central or the southern part of the country? Answer the following questions ONLY for the optimal location chosen in part (i) (i.e. not for both potential locations): j) Using a tabular format, discuss the range of optimality for the objective function coefficients and provide its interpretation. k) Using a tabular format, discuss the range of feasibility for the constraints' right hand sides and provide its interpretation. 1) Present clearly the shadow prices for all the constraints along with their interpretation. m) If the demand for the returnable bottles in year 2 increases from 2450 to 2600, is the shadow price associated with that constraint still valid to assess what impact this increase in the right hand side would have on the objective function value? Why? n) What is the maximum that the company should be willing to pay in order to increase the available production capacity of the non-returnables at the new plant by one unit in year 1 of operation (assuming such an increase is possible)? o) Assess the impact of changing the following transportation costs, one at a time, on the current optimal solution, by just indicating whether the current optimal solution remains optimal or not, and why: 1) Increasing the per case transportation cost from the existing plant to CI by 15%. 2) Reducing the per case transportation cost from the new plant to N2 by 10%. 3) Increasing the per case transportation cost from the new plant to S2 by 20%. In the following, assess the impact of the provided changes in each question one at a time: p) Assume that, due to unforeseen events, the production capacity of the cans in the existing plant drops in year 3 from 1400 down to 1200. Adjust the mathematical model accordingly and report the revised optimal solution obtained. Which of the warehouses will face shortages in this case? q) Now, assume that the company is keen on protecting the environment and accordingly it launches a marketing campaign at the start of year 3 where customer rebates are offered in exchange of every glass bottle returned. Such marketing campaign is expected to boost the sales of those returnables by 10% in year 3. In which of the existing two plants, or possibly in both of them, would the production capacity have to be increased in order to cope with the increasing demand and avoid having any shortages? What is the revised optimal shipping policy for the returnables in this case? r) If a new competitor enters the market, which would reduce the demand of all types of packages equivalently by 10% in all three years, what would be the contingent optimal shipping policy in this case? Which of the existing two plants would end up having a surplus production capacity and from which type of packaging?