O&G Financial Model Help

[Thomas Gabriel]

I'm making a model with a goal of showing the relation between historical oil prices, NG prices, and Exxon's earnings per barrel.

I'd like to end up visualizing the data with a 3d surface chart. My goal is to show what price levels represent a net loss, as well as the probability that those scenarios could happen.

My first idea was to just calculate NG price/BOE and earnings/BOE, and oil price and then run a regression. It's a good fit, but it doesn't take into account the production volume (i.e. since NG production in BOEs is about 75% of oil, it shouldn't have as much of an effect on earnings).

What should I do?

I can use Excel and Matlab.

[M.alex]

Originally posted by Thomas Gabriel
It's a good fit, but it doesn't take into account the production volume

uhhhh, include production volume as a variable then? :confused:

[dimi]

Well you can do a multiple regression in excel and see which variables correlate best with each other. :dunno:

If this is for a class and something introductory include a shitload of variables, people will get impressed, who cares if the whole model is flawed. LoL. If not just use the variables that you think are the most crucial.

As for 3D charts for the most part I've found them to be quite confusing for the audience , that is the ones in excel.

Good Lucks!!!

[ExtraSlow]

Originally posted by dimi
As for 3D charts for the most part I've found them to be quite confusing for the audience , that is the ones in excel.
Yeah, if this is for class, and you were specifically asked to use 3d charts, that's fine. If this is for work, or a presentation where you actually want the audience to understand, stay away from 3d charts, they are confusing to 97% of people, and 100% of managers.

[flipstah]

Originally posted by ExtraSlow

Yeah, if this is for class, and you were specifically asked to use 3d charts, that's fine. If this is for work, or a presentation where you actually want the audience to understand, stay away from 3d charts, they are confusing to 97% of people, and 100% of managers.

QFT. 3D graphs are useless IMO.

[Thomas Gabriel]

I should clarify, this is for a class. I talked to the prof and he said do a regression for the each price vs earnings and then each production vs earnings and then plot them together but that doesn't make sense to me because none of those regressions are good fits.

I can't include production in the regression because production and price are pretty strongly correlated especially for NG. And even if they weren't, my goal is to isolate the prices so I can figure out the minimum prices for positive earnings at current cost levels. Then I can hopefully end up with the probability that they will make money.

[M.alex]

If the model isn't a good fit you have to ask yourself why

- is the predictor variables just the wrong ones
- is there autocorrelation and you need time series forecasting
- are the residuals of the plot non-random and you need to transform the data

[Celica TVS3]

If you're using historical quarterly earnings with a reasonable number of data points for your analysis, the cost structure will have changed materially over the time period. Therefore, I don't think you'll be able to actually calculate the crude/oil gas price required to generate positive earnings. Regardless this is a bad way to predict break-even pricing.

Having said that I would start with...

WTI ($/bbl) vs. Earnings/BOE
HH ($/BOE @ 6:1) vs. Earnings/BOE

These two will likely have a similar R2 given WTI & HH have historically been fairly well correlated. I'm not sure if this is too simplistic for your course or not.

You adjust for production levels by converting to BOE. Earnings a $/BOE won't likely change much when absolute production numbers change as they are likely adding volumes with similar sales prices and costs as existing volumes.

I don't know if this is an option but, why not do something topical. Try a multi variable analysis on U.S. consumer sentiment vs. U.S. Unemployment, Jobless Claims, Average Income & Retail Gas Prices. Hint: You'll find the highest correlation to gas prices.

[Thomas Gabriel]

Ended up doing a stepwise regression and dropping production variables

Ya I did both prices in BOE and $/BOE for the regression (R2=0.99). Everything adjusted for inflation.

With the regression I generated 100k normally distributed random numbers for O & G, with O being 10 years x 100k with an increasing mean as inflation-adjusted O prices have a pretty significant linear trend. Then I ran the random numbers through the regression, and found the probability $/BOE could be <= zero. It was 0.00x% and decreasing.

Good model? What else could have been changed? I know it was kind of simplistic but it was for academic purposes.