Consider the case study presented in Section 15.5 involving the Texago Corp. site selection problem. Texago management has tentatively chosen St. Louis as the site of the new refinery. However, management now is addressing the question of what the capacity of the new refinery should be. While analyzing the site selection problem, the task force was told to assume that the new refinery would have the capacity to process 120 million barrels of crude oil per year. As indicated in Table 15.16, this then would increase the total capacity of all the corporation's refineries from 240 million barrels to 360 million barrels. According to marketing forecasts, Texago will be able to sell all its finished product once this new capacity becomes available, but no more. Therefore, the choice of 120 million barrels as the capacity of the new refinery would enable all the corporation's refineries to operate at full capacity while also fully meeting the forecasted demand for Texago's products. However, to prepare for possible future increases in demand beyond the current forecasts, management now wants to also consider the option of enlarging the plans for the new refinery so that it would have the capacity to process 150 million barrels of crude oil annually. Although this would force the corporation's refineries collectively to operate below full capacity by 30 million barrels for a while, the extra capacity then would be available later if Texago were to continue to increase its market share. This might be well worthwhile since the capital and operating costs incurred by enlarging the plans for the new refinery would be far less (perhaps 40 percent less) than constructing and operating another refinery later to process only 30 million barrels of crude oil per year. Furthermore, management feels that this extra capacity might be needed within a few years. The extra capital costs needed to increase the capacity of the new refinery by 30 million barrels is estimated to be $1.2 billion. The cost of carrying this extra capital would be about $100 million per year, depending on future interest rates. If some of this extra capacity were used at the new refinery, the total operating cost for the refinery would be somewhat larger than the amount shown in Table 15.19, but decreasing the production rate by the same amount at another refinery would decrease its total operating cost by a comparable amount. Since the operating cost per million barrels of crude oil processed is roughly the same at all the refineries, including the new one, the total operating cost for processing 360 million barrels should not be substantially affected by the allocation of this work to the refineries. However, management feels that having some flexibility for where to allocate this work might permit a substantial reduction in the cost of shipping crude oil and finished product. Since Table 15.20 indicates that the total annual shipping cost for crude oil and finished product would be $2.92 billion with St. Louis as the site for the refinery, management hopes that substantial reductions can be achieved in this way. Figures 15.13 and 15.17 shows the optimal shipping plans for crude oil and finished product, respectively, when the new refinery is in St. Louis and has a capacity of processing 120 million barrels of crude oil per year. Management now is asking the task force to analyze the situation under the option of increasing this capacity to 150 million barrels. In particular, management wants the following questions addressed. Under the new option, how should the shipping plan for crude oil in Figure 15.13 change and how much reduction in the total shipping cost would be achieved? How should the shipping plan for finished product in Figure 15.17 change and how much reduction in the total shipping cost would be achieved? Finally, assuming that the differences in operating costs shown in Table 15.19 would continue to apply under the new option, would the financial comparison of the three sites given in Table 15.20 be altered substantially if this option were to be adopted? As the head of the task force, you have decided to lead the way in executing the following steps with the new option.
Formulate and solve a spreadsheet model to find an optimal plan for shipping 360 million barrels of crude oil per year from the oil fields to the refineries, including the new one in St. Louis, where the amount of crude oil each refinery will receive (up to its capacity) is based on minimizing the total annual cost for these shipments. (Hint: You can save some time in this and subsequent parts by using the live spreadsheets for the Texago case study in this chapter's Excel files as a starting point.) Compare the resulting total annual cost for these shipments with the results obtained in Figure 15.13 under the original assumption of a smaller refinery in St. Louis.
b. Assume that the plan found in part a (including its specification of how much crude oil each refinery will receive) will be used. On this basis, formulate and solve a spreadsheet model to find an optimal plan for shipping finished product from the refineries to the distribution centers. Compare the resulting total annual cost for these shipments with the results obtained in Figure 15.17. Also calculate the total annual cost of shipping both crude oil and finished product under this plan and compare it with the corresponding total of $2.92 billion obtained from Table 15.20.
c. You realize that the cost of shipping final product tends to be somewhat larger than the cost of shipping crude oil. Therefore, rather than having the decisions regarding the amount of crude oil each refinery will receive and process be dictated by minimizing the total annual cost of shipping crude oil (as in parts a and b), you decide to check what would happen if these decisions were based on minimizing the total annual cost of shipping final product instead. Formulate and solve a spreadsheet model to find an optimal plan for shipping final product from the refineries (including the new one in St. Louis) to the distribution centers, where the allocation of the 360 million barrels of crude oil per year to the refineries is based on minimizing the total annual cost for these shipments. Compare the resulting total annual cost for these shipments with the results obtained in part b and in Figure 15.17.
d. Assume that the plan found in part c (including its specification regarding how much crude oil each refinery will receive and process) will be used. On this basis, formulate and solve a spreadsheet model to find an optimal plan for shipping crude oil from the oil fields to the refineries. Compare the resulting total annual cost for these shipments with the results obtained in part a and in Figure 15.13. Also calculate the total annual cost of shipping both crude oil and finished product under this plan and compare it with the corresponding total obtained in part b and in Table 15.20.
e. You realize that, so far, you have been sub optimizing the overall problem by optimizing only one part of the problem at a time, so now it is time to get down to serious business. Formulate a single spreadsheet model that simultaneously considers the shipping of 360 million barrels of crude oil per year from the oil fields to the refineries (including the new one in St. Louis) and the shipping of final product from the refineries to the distribution centers. Use the objective of minimizing the grand total of all these shipping costs. Since the refineries collectively have a capacity of processing 390 million barrels of crude oil per year, the decisions regarding the amount of crude oil each refinery will receive and process (up to each refinery's capacity) also are to be based on this same objective. Solve the model and compare the resulting total of all the shipping costs with the corresponding total calculated in parts b and d and in Table 15.20.
f. Repeat part e if the new refinery (with a capacity of processing 150 million barrels of crude oil per year) were to be placed in Los Angeles instead of St. Louis. Then repeat it again if Galveston were to be selected as the site instead of St. Louis. Using the operating costs given in Table 15.19 for the three sites, construct a table like Table 15.20 to show the new financial comparison between the sites. (Although the operating costs will be larger than given in Table 15.19 if the new refinery processes more than 120 million barrels of crude oil per year, management has instructed the task force to assume that the differences in operating costs shown in Table 15.19 would continue to apply, so the differences in the total variable costs in the table being constructed would still be valid.)
g. You now are ready to submit all your results (including your spreadsheets) to management. Write an accompanying memorandum that presents your