Background The Texago Corporation is a large, fully integrated petroleum company based in the United States. The company produces most of its oil in its oil fields and then imports the rest of what it needs from the Middle East. An extensive distribution network is used to transport the oil to the company's refineries and then to transport the petroleum products from the refineries to Texagos distribution centers. The locations of these various facilities are given in Table 1. Texago is continuing to increase its market share for several of its major products. Therefore, management has decided to expand output by building an additional refinery and increasing imports of crude oil from the Middle East. The crucial remaining decision is where to locate the new refinery. The addition of the new refinery will have a great impact on the operation of the entire distribution system, including decisions on how much crude oil to transport from each of its sources to each refinery (including the new one) and how much finished product to ship from each refinery to each distribution center. Therefore, the three key factors for management's decision on the location of the new refinery are: 1. The cost of transporting the oil from its sources to all the refineries, including the new one. 2. The cost of transporting finished product from all the refineries, including the new one, to the distribution centers. 3. Operating costs for the new refinery, including labor costs, taxes, the cost of needed supplies (other than crude oil), energy costs, the cost of insurance, the effect of financial incentives provided by the state or city, and so forth. (Capital costs are not a factor since they would be essentially the same at any of the potential sites.) Management has set up a task force to study the issue of where to locate the new refinery. After considerable investigation, the task force has determined that there are three attractive potential sites. These sites and the main advantages of each are spelled out in Table 2. Other relevant factors, such as standard-of-living considerations for management and employees, are considered reasonably comparable at these sites. Gathering the Necessary Data The task force needs to gather a large amount of data, some of which require considerable digging, to perform the analysis requested by management. Management wants all the refineries, including the new one, to operate at full capacity. Therefore, the task force begins by determining how much crude oil each refinery would need to receive annually under these conditions. Using units of 1 million barrels, these needed amounts are shown on the left side of Table 3. The right side of the table shows the current annual output of crude oil from the various oil fields. These quantities are expected to remain stable for some years to come. Since the refineries need a total of 360 million barrels of crude oil, and the oil fields will produce a total of 240 million barrels, the difference of 120 million barrels will need to be imported from the Middle East. TABLE 1 Location of Texago's current facilities Type of Facility Locations Oil fields 1. Texas 2. California 3. Alaska Refineries 1. Near New Orleans, Louisiana 2. Near Charleston, South Carolina 3. Near Seattle, Washington Distribution centers 1. Pittsburgh, Pennsylvania 2. Atlanta, Georgia 3. Kansas City, Missouri 4. San Francisco, California TABLE 2 Potential sites for Texago's new refineries and their main advantages Potential Site Main Advantages Near Los Angeles, California 1. Near California oil fields 2. Ready access from Alaska oil fields 3. Fairly near San Francisco distribution center Near Galveston, Texas 1. Near Texas oil fields 2. Ready access from Middle East imports 3. Near corporate headquarters Near St. Louis, Missouri 1. Low operating costs 2. Centrally located for distribution centers 3. Ready access to crude oil via Mississippi River TABLE 3 Refinery New Orleans Charleston Seattle New one Total Production data for Texago Corp. Crude Oil Needed Annually (Million Barrels) 100 60 80 120 360 Oil Fields Crude Oil Produced Annually (Million Barrels) Texas 80 California 60 Alaska 100 Total 240 Needed imports = 360 - 240 = 120 TABLE 4 Cost data for shipping crude oil to a Texago refinery Cost per Unit Shipped (Millions of Dollars per Million Barrels) Refinery or Potential Refinery New Orleans Charleston Seattle Los Angeles Galveston Source Texas 2 5 3 1 California 5 5 3 1 3 Alaska 5 7 3 4 5 Middle East 2 3 5 4 St. Louis 1 4 4 7 4 3 8 TABLE 5 Cost data for shipping finished product to a distribution center Cost per Unit Shipped (Millions of Dollars) Distribution Center Pittsburgh Atlanta Kansas City San Francisco Refinery New Orleans 6,5 5.5 6 Charleston 5 4 Seattle 8 4 Potential Los Angeles 8 6 3 Refinery Galveston 5 4 3 St. Louis 4 3 1 5 Number of units needed 100 80 80 100 3 2 0 Since the amounts of crude oil produced or purchased will be the same regardless of which location is chosen for the new refinery, the task force concludes that the associated production or purchase costs (exclusive of shipping costs) are not relevant to the site selection decision. On the other hand, the costs for transporting the crude oil from its source to a refinery are very relevant. These costs are shown in Table 4 for both the three current refineries and the three potential sites for the new refinery Also, very relevant are the costs of shipping the finished product from a refinery to a distribution center. Letting one unit of the finished product correspond to the production of a refinery from 1 million barrels of crude oil, these costs are given in Table 5. The bottom row of the table shows the number of units of finished product needed by each distribution center. The final key body of data involves the operating costs for a refinery at each potential site. Estimating these costs requires site visits by several members of the task force to collect detailed information about local labor costs, taxes, and so forth. Comparisons then are made with the operating costs of the current refineries to help refine these data. In addition, the task force gathers information on one-time site costs for land, construction, and so forth, and amortizes these costs on an equivalent uniform annual cost basis. This process leads to the estimates shown in Table 6. Analysis (Six Applications of a Transportation Problem) Armed with these data, the task force now needs to develop the following key financial information for management: 1. Total shipping cost for crude oil with each potential choice of a site for the new refinery. 2. Total shipping cost for the finished product with each potential choice of a site for the new refinery. For both types of costs, once a site is selected, an optimal shipping plan will be determined and then followed. Therefore, to find either type of cost with a potential choice of a site, it is necessary to solve for the optimal shipping plan given that choice and then calculate the corresponding cost. TABLE 6 Site Los Angeles Galveston St. Louis Estimated operating costs for a Texago refinery at each potential site Annual Operating Cost (Millions of Dollars) 620 570 530 B. Follow the steps explained below to solve this problem 1) First, optimize the transportation from oil fields to refineries. Construct 3 separate Excel files to investigate the cost of transportation of the 3 potential refinery options. Then, solve the transportation problem using Excel and save the optimized cost for each potential refinery option. See figure 1 for an example. Figure 1, is an example of an Excel file that you need for part (1). 1 Texago Corp. Site-Selection Problem (Shipping to Refineries) E G H 1 New Orleans New Site Unit Cost ($millions) Texas Oil California Fields Alaska Middle East Refineries Charleston Seattle 4 5 5 3 7 3 3 5 5 5 5 2 2 3 4 4 5 6 7 8 9 9 10 11 12 13 14 15 16 17 18 19 20 Shipment Quantity (millions of barrels) Texas Oil California Fields Alaska Middle East Total Received New Orleans 0 0 0 Refineries Charleston Seattle 0 0 0 0 0 0 0 0 0 0 0 = 60 80 New Site 0 0 0 0 0 0 0 Total Shipped 0 0 0 0 0 Supply 80 60 100 120 0 0 = Demand 100 Total Cost (Smillions) 120 2) Similar to part (1), optimize the cost of transportation from the refineries to the distribution centers. You should prepare 3 Excel files for 3 potential refineries. See figure 2 for an example. Figure 2, is an example of an Excel file that you need for part (2). AT F 1 Texago Corp. Site-Selection Problem (Shipping to D.C.'s) B D G H H Pittsburgh Unit Cost (Smillions) New Orleans Refineries Charleston Seattle New Site 6.5 7 7 Distribution Center Atlanta Kansas City 5.5 5 8 4 San Francisco 8 7 3 1 2 3 4 5 6 7 8 8 9 10 11 12 13 14 15 16 17 18 19 20 = Shipment Quantity (millions of barrels) New Orleans Refineries Charleston Seattle New Site Total Received Pittsburgh 0 0 0 0 0 Distribution Center Atlanta Kansas City 0 0 0 0 0 0 0 0 0 0 San Francisco 0 0 0 0 0 Total Shipped 0 0 0 0 0 Supply 100 60 80 120 Demand Total Cost (Smillions) 100 80 80 100 3) Calculate the total cost, for each refinery option, considering transportation costs from part (A) and part (b), plus other incurred costs. 4) Which refinery option do you suggest the company to use? Background The Texago Corporation is a large, fully integrated petroleum company based in the United States. The company produces most of its oil in its oil fields and then imports the rest of what it needs from the Middle East. An extensive distribution network is used to transport the oil to the company's refineries and then to transport the petroleum products from the refineries to Texagos distribution centers. The locations of these various facilities are given in Table 1. Texago is continuing to increase its market share for several of its major products. Therefore, management has decided to expand output by building an additional refinery and increasing imports of crude oil from the Middle East. The crucial remaining decision is where to locate the new refinery. The addition of the new refinery will have a great impact on the operation of the entire distribution system, including decisions on how much crude oil to transport from each of its sources to each refinery (including the new one) and how much finished product to ship from each refinery to each distribution center. Therefore, the three key factors for management's decision on the location of the new refinery are: 1. The cost of transporting the oil from its sources to all the refineries, including the new one. 2. The cost of transporting finished product from all the refineries, including the new one, to the distribution centers. 3. Operating costs for the new refinery, including labor costs, taxes, the cost of needed supplies (other than crude oil), energy costs, the cost of insurance, the effect of financial incentives provided by the state or city, and so forth. (Capital costs are not a factor since they would be essentially the same at any of the potential sites.) Management has set up a task force to study the issue of where to locate the new refinery. After considerable investigation, the task force has determined that there are three attractive potential sites. These sites and the main advantages of each are spelled out in Table 2. Other relevant factors, such as standard-of-living considerations for management and employees, are considered reasonably comparable at these sites. Gathering the Necessary Data The task force needs to gather a large amount of data, some of which require considerable digging, to perform the analysis requested by management. Management wants all the refineries, including the new one, to operate at full capacity. Therefore, the task force begins by determining how much crude oil each refinery would need to receive annually under these conditions. Using units of 1 million barrels, these needed amounts are shown on the left side of Table 3. The right side of the table shows the current annual output of crude oil from the various oil fields. These quantities are expected to remain stable for some years to come. Since the refineries need a total of 360 million barrels of crude oil, and the oil fields will produce a total of 240 million barrels, the difference of 120 million barrels will need to be imported from the Middle East. TABLE 1 Location of Texago's current facilities Type of Facility Locations Oil fields 1. Texas 2. California 3. Alaska Refineries 1. Near New Orleans, Louisiana 2. Near Charleston, South Carolina 3. Near Seattle, Washington Distribution centers 1. Pittsburgh, Pennsylvania 2. Atlanta, Georgia 3. Kansas City, Missouri 4. San Francisco, California TABLE 2 Potential sites for Texago's new refineries and their main advantages Potential Site Main Advantages Near Los Angeles, California 1. Near California oil fields 2. Ready access from Alaska oil fields 3. Fairly near San Francisco distribution center Near Galveston, Texas 1. Near Texas oil fields 2. Ready access from Middle East imports 3. Near corporate headquarters Near St. Louis, Missouri 1. Low operating costs 2. Centrally located for distribution centers 3. Ready access to crude oil via Mississippi River TABLE 3 Refinery New Orleans Charleston Seattle New one Total Production data for Texago Corp. Crude Oil Needed Annually (Million Barrels) 100 60 80 120 360 Oil Fields Crude Oil Produced Annually (Million Barrels) Texas 80 California 60 Alaska 100 Total 240 Needed imports = 360 - 240 = 120 TABLE 4 Cost data for shipping crude oil to a Texago refinery Cost per Unit Shipped (Millions of Dollars per Million Barrels) Refinery or Potential Refinery New Orleans Charleston Seattle Los Angeles Galveston Source Texas 2 5 3 1 California 5 5 3 1 3 Alaska 5 7 3 4 5 Middle East 2 3 5 4 St. Louis 1 4 4 7 4 3 8 TABLE 5 Cost data for shipping finished product to a distribution center Cost per Unit Shipped (Millions of Dollars) Distribution Center Pittsburgh Atlanta Kansas City San Francisco Refinery New Orleans 6,5 5.5 6 Charleston 5 4 Seattle 8 4 Potential Los Angeles 8 6 3 Refinery Galveston 5 4 3 St. Louis 4 3 1 5 Number of units needed 100 80 80 100 3 2 0 Since the amounts of crude oil produced or purchased will be the same regardless of which location is chosen for the new refinery, the task force concludes that the associated production or purchase costs (exclusive of shipping costs) are not relevant to the site selection decision. On the other hand, the costs for transporting the crude oil from its source to a refinery are very relevant. These costs are shown in Table 4 for both the three current refineries and the three potential sites for the new refinery Also, very relevant are the costs of shipping the finished product from a refinery to a distribution center. Letting one unit of the finished product correspond to the production of a refinery from 1 million barrels of crude oil, these costs are given in Table 5. The bottom row of the table shows the number of units of finished product needed by each distribution center. The final key body of data involves the operating costs for a refinery at each potential site. Estimating these costs requires site visits by several members of the task force to collect detailed information about local labor costs, taxes, and so forth. Comparisons then are made with the operating costs of the current refineries to help refine these data. In addition, the task force gathers information on one-time site costs for land, construction, and so forth, and amortizes these costs on an equivalent uniform annual cost basis. This process leads to the estimates shown in Table 6. Analysis (Six Applications of a Transportation Problem) Armed with these data, the task force now needs to develop the following key financial information for management: 1. Total shipping cost for crude oil with each potential choice of a site for the new refinery. 2. Total shipping cost for the finished product with each potential choice of a site for the new refinery. For both types of costs, once a site is selected, an optimal shipping plan will be determined and then followed. Therefore, to find either type of cost with a potential choice of a site, it is necessary to solve for the optimal shipping plan given that choice and then calculate the corresponding cost. TABLE 6 Site Los Angeles Galveston St. Louis Estimated operating costs for a Texago refinery at each potential site Annual Operating Cost (Millions of Dollars) 620 570 530 B. Follow the steps explained below to solve this problem 1) First, optimize the transportation from oil fields to refineries. Construct 3 separate Excel files to investigate the cost of transportation of the 3 potential refinery options. Then, solve the transportation problem using Excel and save the optimized cost for each potential refinery option. See figure 1 for an example. Figure 1, is an example of an Excel file that you need for part (1). 1 Texago Corp. Site-Selection Problem (Shipping to Refineries) E G H 1 New Orleans New Site Unit Cost ($millions) Texas Oil California Fields Alaska Middle East Refineries Charleston Seattle 4 5 5 3 7 3 3 5 5 5 5 2 2 3 4 4 5 6 7 8 9 9 10 11 12 13 14 15 16 17 18 19 20 Shipment Quantity (millions of barrels) Texas Oil California Fields Alaska Middle East Total Received New Orleans 0 0 0 Refineries Charleston Seattle 0 0 0 0 0 0 0 0 0 0 0 = 60 80 New Site 0 0 0 0 0 0 0 Total Shipped 0 0 0 0 0 Supply 80 60 100 120 0 0 = Demand 100 Total Cost (Smillions) 120 2) Similar to part (1), optimize the cost of transportation from the refineries to the distribution centers. You should prepare 3 Excel files for 3 potential refineries. See figure 2 for an example. Figure 2, is an example of an Excel file that you need for part (2). AT F 1 Texago Corp. Site-Selection Problem (Shipping to D.C.'s) B D G H H Pittsburgh Unit Cost (Smillions) New Orleans Refineries Charleston Seattle New Site 6.5 7 7 Distribution Center Atlanta Kansas City 5.5 5 8 4 San Francisco 8 7 3 1 2 3 4 5 6 7 8 8 9 10 11 12 13 14 15 16 17 18 19 20 = Shipment Quantity (millions of barrels) New Orleans Refineries Charleston Seattle New Site Total Received Pittsburgh 0 0 0 0 0 Distribution Center Atlanta Kansas City 0 0 0 0 0 0 0 0 0 0 San Francisco 0 0 0 0 0 Total Shipped 0 0 0 0 0 Supply 100 60 80 120 Demand Total Cost (Smillions) 100 80 80 100 3) Calculate the total cost, for each refinery option, considering transportation costs from part (A) and part (b), plus other incurred costs. 4) Which refinery option do you suggest the company to use