# Question: The fluctuating price of oil has a big impact on

The fluctuating price of oil has a big impact on the cost of a supply chain. As oil prices increase, so does the cost of transportation. Recent fluctuations in the price of oil have taught supply chain managers that they need to develop a robust supply chain strategy that performs well if the price of oil is high or low. Also, supply chain managers need to be able to project the amount they will spend on transportation as the price of oil changes. This is a question that gets the attention of the senior management within any firm. For businesses that ship mostly by truck, we can start to answer this question by understanding how the price of oil impacts the price of diesel fuel. (After we know this, we can approximate how much of our costs are driven by the price of diesel. For other modes of transportation, we can follow a similar methodology.) Figure 6.15 shows a scatter plot of the price of diesel versus oil. This data can be found on the book web site in the file Oil and Diesel Prices.xls. This represents weekly data from December 30, 2005, to March 30, 2012.

A regression analysis can help us determine the relationship.

a. Run a regression in Excel on this data with the price of diesel as the dependent variable. What is the regression equation that relates the price of diesel to the price of oil? What is the R-squared value? What is the p-value for the independent variable, the price of oil?

b. How much does this model predict that diesel will increase for every $10 increase in the price of oil?

c. Build a chart that shows the expected price of diesel for every $10 increment in the price of oil from $20 a barrel to $200 a barrel.

d. If oil is currently $100 a barrel and you expect it to increase by 40% to $140, what percent increase would you expect in the price of diesel fuel?

e. If oil is currently $40 a barrel and you expect it to increase by 100% to $80, what percent increase would you expect in the price of diesel fuel?

f. How would you use this information when running different network modeling scenarios?

A regression analysis can help us determine the relationship.

a. Run a regression in Excel on this data with the price of diesel as the dependent variable. What is the regression equation that relates the price of diesel to the price of oil? What is the R-squared value? What is the p-value for the independent variable, the price of oil?

b. How much does this model predict that diesel will increase for every $10 increase in the price of oil?

c. Build a chart that shows the expected price of diesel for every $10 increment in the price of oil from $20 a barrel to $200 a barrel.

d. If oil is currently $100 a barrel and you expect it to increase by 40% to $140, what percent increase would you expect in the price of diesel fuel?

e. If oil is currently $40 a barrel and you expect it to increase by 100% to $80, what percent increase would you expect in the price of diesel fuel?

f. How would you use this information when running different network modeling scenarios?

**View Solution:**## Answer to relevant Questions

Consider a problem in which you need to be able to deliver emergency pharmaceutical products to the drugstores in Chicago. That is, if a drugstore runs out of an item and they need to fill an order, your service can ...If you are working for a firm that ships with multi-stop truckloads, why might a cost of $0.22 per ton-mile be a good estimate? Assuming that it is a good estimate, why would you want to pick a slightly higher number, or a ...Open the file Accounting Allocations.xls (on the book web site) to see a list of sample accounts that a firm has for their various plants. Your team has already gone through these accounts and has determined whether the ...In the five-warehouse solution, even though the Columbus warehouse is very close to the plants, it still chooses to serve customers from additional warehouses. Why? In other words, why not save on inbound transportation and ...Value Grocers has decided to expand its model to include product lines such as packaged deli meats and cheeses, crackers, potato chips, canned nuts, yogurt, and milk. You can find the detailed storage and logistics ...Post your question