Chapter 13 Discovery Project

Gas vs. Oil Prices

Managing expenses is often a critical job duty for Chief Financial Officers (CFOs). Sometimes costs are fairly easy to forecast. Items such as office supplies and staffing costs are generally more manageable costs to estimate for budgeting purposes. Other expenses such as raw materials and utility costs can be harder to forecast because the prices can fluctuate widely during a given time period.

Gasoline is another such expense. Gasoline prices are determined in part by the price per barrel of crude oil. There are two predominate types of crude oil used in the industry to determine the price of gas we see at the pump: West Texas Intermediate (WTI) and Brent Blend. WTI is the benchmark used in the United States and Brent Blend is mostly used in Europe and Africa. An interesting side note is that about $20$ gallons of gasoline are made from one barrel of crude oil (Source: Frequently Asked Questions (FAQs) – U.S. Energy Information Administration (EIA) .)

Industries that rely heavily on gasoline to provide their product or service can find it difficult to estimate the gasoline expense from month to month and sometimes even from day to day. Some examples of industries that have fluctuating gas price impacts are transportation, landscaping, and logistics. Being able to reliably forecast the gasoline expense is a critical component of estimating company profits.

Imagine you are tasked with determining reliable estimates of the price per gallon of gasoline to be used in forecasting company profits. Using the data file called Gas Prices vs. Oil Prices, answer the following questions to better understand the relationship between the two types of crude oil and their impact on gasoline prices. The data set includes monthly gasoline and crude oil prices between January 2000 and March 2021 ($255$ data points).

1. Download the data file and open it in Microsoft Excel.

2. Determine the mean, mode, median, maximum, minimum, range, standard deviation, and the coefficient of variation of the price per gallon (to $2$ decimal places) and briefly discuss the results. (Hint: These values can be quickly calculated using the Data Analysis Add-in: Descriptive Statistics in Excel).

3. Create scatterplots of each of the crude oils against the price per gallon.

4. Fit a simple linear regression (SLR) model for Brent Blend vs. Reg Gas Price per Gallon and WTI vs. Reg Gas Price per Gallon.

5. Obtain the residuals for each regression analysis and plot them.

6. Validate the assumptions — of linearity, independence, normality, constant variance.

7. Test the slopes for each crude oil model to see if they are statistically significant.

8. Obtain confidence intervals for the slopes using a $5\%$ significance level.

9. Make some predictions. Pick a price per gallon (or barrel) for Brent Blend and WTI and predict the price of a regular gallon of gas.

10. Which crude oil would you recommend as the best predictor of regular gasoline prices for your company to use to forecast expenses?