Build a spreadsheet model for the following case study. Table 1 The Shenn is a l
ID: 371556 • Letter: B
Question
Build a spreadsheet model for the following case study.
Table 1
The Shenn is a large petroleum company based in the United States. The company produces most of its oil in its own 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 Shenn’s distribution centers. The locations of these various facilities are given in Table 1.
Type of Facility
Locations
Oil fields
North Dakota
California
The Gulf of Mexico
Refineries
Near Port Arthur, Texas
Near Grayville, Louisiana
Near Robinson, Illinois
Distribution centers
Atlanta, Georgia
San Francisco, California
Indianapolis, Indiana
Pittsburgh, Pennsylvania
The management has made the decision to expand output by building an additional refinery. 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:
The cost of transporting the oil from its sources to all the refineries, including the new one.
The cost of transporting finished product from all the refineries, including the new one, to the distribution centers.
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.
Three potential locations have been identified. These sites and the main advantages of each are given in Table 2.
Potential Site
Main Advantages
Near Los Angeles, California
Near California oil fields
Near corporate headquarters
Near California Distribution Center
Near Houston, Texas
Near Gulf of Mexico oil fields
Ready to access from Middle East imports
Near Port Arthur oil fields
Near Chesterfield, Missouri
Low operating costs
Centrally located for distribution centers
Ready access to crude oil via Mississippi River
Table 2
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 re-main 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 3
Crude Oil Needed Annually (Million Barrels)
Oil Fields
Crude Oil Produced Annually (million Barrels)
Port Arthur
North Dakota
Near Grayville
California
Near Robinson
The Gulf of Mexico
New one
Total
Needed Imports
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.
Cost per Unit Shipped (Millions Dollars per Million Barrels)
Refinery or Potential Refinery
Port Arthur
Grayville
Robinson
Los Angeles
Houston
Chesterfield
North Dakota
2
4
5
3
1
1
California
5
5
3
1
3
4
The Gulf of Mexico
5
7
3
4
5
7
Middle East
2
3
5
4
3
4
Table 4
Table 5
Also very relevant are the costs of shipping the finished product from a refinery to a distribution center. Letting one unit of 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.
Cost per Unit Shipped (Millions Dollars)
Distribution Center
Atlanta
San Francisco
Indianapolis
Pittsburgh
Port Arthur
6.5
5.5
6
8
Grayville
7
5
4
7
Robinson
7
8
4
3
Los Angeles
8
6
3
2
Houston
5
4
3
6
Chesterfield
4
3
1
5
Number of units needed
100
80
80
100
Table 6
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. This process leads to the estimates shown in Table 6.
Site
Annual Operating Cost (Millions of Dollars)
Los Angeles
620
Houston
570
Chesterfield
530
Answer the following questions:
Where should the new refinery be located and why? (8 points)
Table 1
Explanation / Answer
Chesterfield is the best location for setting up a new refinery.
An assumption is made : All 3 oil fields has same capacity of output (80 million barrels) & All four refineries has same output capacity of 90 million barrels) because the data is not mentioned.
Reasons are as below :
1. Operating cost is the lowest among all three options
2. Distribution cost for any of the Distribution center is significantly low compared to all other options
3. Since 120 million barrels has to be imported from Gulf, Chesterfield is an ideal location for shipment via sea
4. Four major Distribution center ( Atlanta, Pittsbugh, Indiana Polis) with the cumulative requirement of 280 million barrels can be catered with proximity Advantage.
5. Cost of shipment from North Dakota to Chesterfield is very low in comparison with Grayville, Indiana Polis etc. Hence this option is the best.
6. As mentioned, proximity of River will be an added advantage for logistics purpose.
7.Refineries would require good waste disposal methods to avoid polluting environment. Rivers with close proximity to Sea will be ideal situation for waste disposals after treatment.
8. Since San Francisco Distribution center does not have any refinery to supply at efficient costs, Chesterfield is the best option for San Francisco to source from.
9. With establishment of refinery at Chesterfield, the company can achieve a good balance between proximity to distribution center and cost of logistics thereby maximising profits.
Please ping me in case of any clarifications.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.