Modelling commodity price uncertainty with brownian motion - time period impacts

background

I have two separate models of a metals resources company.

Each model produces a series of accounting and cashflows forecast for different assets, and consolidates these to a overall group view.

1. The first model is life of asset, it models the forecast entire life of the portfolio (asset by asset) in annual periods.
2. The second model is a funding model, it focuses on balance sheet liquidity. It forecasts a two year period broken into quarters.

While the models have a different design, they use the same data sources and they are aligned deterministically.

probabilistic modelling

For forecast and decision making, probabilistic analysis is used to take into account the relative correlations and historical volatilities between different commodity prices, foreign exchange rates and key cost drives.

Both models use geometric brownian motion with a mean reversion modification for commodity prices - as commodity prices (outside gold and perhaps silver) are widely accepted to mean revert over the longer term when hypothetically supply and demand reach equilbrium.

issue

For the same underlying assumptions, and consistent probabilistic parameters, the funding model produces a significantly smaller range of outcomes over the first two years than the life of asset model.

On face value the reason for this is logical:

   
The life of asset model produces a single price that is used over the entire period.
   
The funding model produces four different prices series over the first year, the price of the last quarter matches the price outcomes from the life of asset model.
   
So expressing this as an option model, the commodity price at expiry of the first annual period is the same for both models (regardless of how many steps the time period is broken into).
 
 In terms of profit forecasting, when looking at an extreme downside price outcome, the funding model has a significantly higher average price over the first year than the life of asset model, as prices in the funding model don't reach the same level as the life of asset model until the last quarter.

This at once appears deceptively simple but complex.

Intuitively before I looked at this, I expected both models to produce the same "average" price over a period, regardless of how many intervals that time period is divided into. But if this occurred, then there would be different prices in both models at the end of the first period. Which would be incorrect.

Have I missed something simple?
LVL 50
DaveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
As I understand it, the life cycle model prices match the funding model and the start and end of each year. The "average" price in the life cycle model would therefore be the average of the starting and ending prices. I assume that you would calculate financial performance using this average commodity price.

In the funding model, the prices will vary at quarterly intervals according to your Brownian motion model with reversion to the mean. If you fix the endpoints and did a Monte Carlo simulation of the problem, I'd expect the average of the five prices (year start plus end of each quarter) during a year to equal the average of year start and year end.

To test the above assertion, I built a spreadsheet covering prices over a 250 year period (obviously without inflation). The price formula was:
New price = Old price + Brownian factor * (RAND() - 0.5) - (Old price - Mean price) * Regression to mean factor

Even though the average of Y/Y prices equaled the average of five quarterly prices (when averaged over the 250 year period), there was invariably a discrepancy between the Y/Y and five quarter averages. The RMS amount of this discrepancy varied linearly with the Brownian factor and did not depend very much on the regression to the mean factor.

In the model I tested (Brownian factor = 0.1, Regression to mean factor = 0.2), the RMS discrepancy was about 14% of the Brownian factor.

If your models calculate prices the same way my spreadsheet did, then the discrepancy in average price between the life cycle and funding models appears to be statistically valid. The discrepancy is an inevitable result of the fact that one analyzes one interval per year while the other analyzes four. You could change the models to force the average prices to be the same, but then the year-ending prices would be different, and there might be other artifacts introduced (unintentionally) by such an approach. I think the better fix would be to change the life cycle model so it uses four intervals per year, and hence the same prices as the funding model.
CommodityPricesQ28711573.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveAuthor Commented:
Thx Brad,

Sorry for the long delay.

Yes, after further investigation we have moved to the quarterly model as being more accurate (stepping to months made little difference)

Regards

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Math / Science

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.