Have you ever wondered what would happen to gas prices if oil prices skyrocketed to $200 as Goldman Sachs had predicated last year, or if oil prices plummeted to $20? It’s not easy to discern the oil-gas price correlation since there are other factors at play ranging from refining and distribution to taxes. Therefore, the oil-gas price curve is not linear and it is not a 1:1 correlation. However, with some nifty excel tabulations, you can extrapolate what gas prices would look like under various scenarios based on some reasonable historical perspective and some neat Excel tricks.
Energy Price Data and Approach
I looked at hundreds of data points of weekly oil prices vs. weekly gas prices ranging from 1991 to present. I then offset the results by a few days given the fact that gasoline prices tend to rise shortly after oil price moves and not instantaneously. They also tend to decline slowly as well (definitely not instantaneously!).
Oil Gas Price Chart #1 – some Perspective
This first chart just gives some historical perspective for where oil and gas prices have been over the past two decades. The blue line represents the oil price in dollars per barrel and the black area is the average retail gasoline price over time.
Oil Gas Price Chart #2 – Linear Equation?
For this chart, I plotted oil and gas prices on the x and y axes, respectively so that you can develop a linear equation to represent the relationship between the two and visually express over a broad range of values. The most simplistic estimation is to run a linear regression. While the R2 value was relatively high at .96 (meaning there’s a very high assurance that the model accurately represents the data set), note that at higher oil prices, the gasoline prices are very much skewed to the right of the trendline. Since oil prices peaked at ~$140/barrel last year, and we see that gasoline prices topped out at about $4, it’s tough to discern what would happen during an oil superspike scenario since the energy price correlation tends to break down at these higher levels. Therefore, the linear regression has its drawbacks but within typical historical norms, you can reasonably predict gas prices for a given oil price.
Oil Gas Price Chart #3 – Polynomial (2nd Order)
So, for this oil-gas price chart, I used the same underlying data, but instead of running a linear regression, I tried out a 2nd order polynomial equation. This is simply the next level of complexity, expressed in standard algebraic terms as:
y = -0.0078×2 + 3.6791x + 43.54
In this case, x is your oil price. To see how this model would predict a future gas price for a given oil price, note that with both a higher R2 value and the line passing right through those high points in the upper right, you can first test out last year’s highs to double check that this equation is legit, then see how it looks for an oil superspike, or worse, for those peak oil advocates out there, a sustained 3 (or 4) figure oil price! Let’s check out last year’s $140 oil. Perform the following calculation:
y (gas price) = -0.0078*(140)*(140) + 3.6791*(140) + 43.54 = $4.05/gallon gas
If you recall, this exactly matches last year’s high for oil and gas. Not bad! If you double check last week’s prices using $72 oil, you also get $2.67 gas, which is about where prices are at now. So, this model works well.
So, here’s what a $200 oil superspike would look on the wallet:
y (gas price) = -0.0078*(200)*(200) + 3.6791*(200) + 43.54 = $4.67/gallon gas
You can do the same for any crazy oil price spike you can think of. If you are genuinely concerned about energy prices and want to protect your personal finances against the prospect of rising prices, make sure to check out this article on How to Hedge your Own Energy Prices. Additionally, note that Petroleum Engineering is the hottest career right now (starting at over $83K per year!) given the most recent salary survey for college graduates.
If you enjoyed this article and want to stay on top of others like, make sure to Subscribe for Free to future articles.
If you enjoyed this post, you can get free updates through RSS Feed or via Email whenever a new post is published. Rest assured that you can unsubscribe at any time via the automated system and your information will not be sold, archived or utilized for any other "nefarious" purposes.