provide mathematical models in detail for each scenario to receive full credit. • Must provide the excel output using Excel Solver or Data Analysis.
Scenario 2 California Pacific is a lumber company that has three sources of wood and five markets to be supplied. The annual availability of wood at sources 1, 2, and 3 is 15, 20, and 15 million board feet, respectively. The amount that can be sold annually at markets 1, 2, 3, and 4 and 5 is 11, 12, 9, 10 and 8 million board feet, respectively. In the past, the company has shipped the wood by train. However, because 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 these investment costs, the shipping costs in thousands of dollars per million board feet by rail and by water (when feasible) would be the following for each route. Unit Cost by Rail ($1,000s) to market Unit Cost by Ships ($1,000s) to market Source 1 2 3 4 5 1 2 3 4 5 1 2 3 61 69 59 72 78 66 45 60 63 55 49 61 66 56 47 31 36
38
43 33 24 28 36 -24 32 35 31 26 The capital investment (in thousands of dollars) in ships required for each million board feet to be transported annually by ship along each route is given next.
Unit Investment for Ships ($1,000s) to Market Source 1 2 3 4 5 1 2 3 275 293 – 303 318 283 238 270 275 -250 268 285 265 240
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 head of the management science team 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) Present your results for each option and compare.
