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?