MG 758 Decision Modeling for Managers Final Term Project and Exam Monroe Enterprises is an iron ore supplier that has three sources of iron ore and five markets to be supplied. The annual availability of iron ore at sources 1. 2, and 3 is 15, 20, and 15 million tons, respectively. The amount that can be sold annually at markets 1, 2, 3, 4 and 5 is 11, 12, 9, 10, and 8 million tons, respectively. In the past, the company has shipped the ore by train. However, because of shipping costs have been increasing. the alternative of using ships to make some of the deliveries is being investigated. This alternative would require the company to invest in some ships. Except for investment costs, the shipping costs in thousands of dollars per million tons by rail and by water (when feasible) would be the following for each route. UlilCu-tbyllllltom Uli'lCu-tbySlblSlMl}hm Sm I. 2 3 4 5 1 2 3 4 5 1 61 72 45 55 66 31 3-3 24 ---- 35 2 69 78 60 49 56 36 43 28 24 31 3 5'9 66 63 61 4? .... 33 35 32 26 The capital investment [in thousands of dollars) in ships required for each million tons to be transported annually by ship along each route is given next. Unit mmmwsupsqsm1mm Sm 1 2 3 4 S 1 275 303 233 --- 285 2 293 3-13 270 250 265 3 ---- 23.3 275 263 24-0 Considering the expected useful life of the ships and the time value of money, the equivalent uniform annual cost of these investments is one-tenth the amount given in the table. The objective is to determine the overall shipping plan that minimizes the total equivalent uniform annual cost [including shipping costs). You are the analyst that has been assigned the task of determining this shipping plan for each of the three options listed next. Option 1: Continue shipping exclusively by rail. Option 2: Switch to shipping exclusively by water [except where only rail is feasible) Option 3: Ship by either rail or water, depending on which is less expensive for the particular route. (i) Create an excel spreadsheet LP model to present the results for each option. (if) Write a business report memo summarizing the results and your recommendations. Bonus w Extra Credit Finally, consider the fact that these results are based on current shipping and investment costs, so that the decision on the option to adopt now should take into account management's projection of how these costs are likely to change in the future. (iii) For each option using the sensitivity report(sj, describe a scenario of future cost changes that would justify adopting that option now