Model indexed contracts

[apeiron]

Hi all

i am trying to model revenues of a company whose most contracts are indexed to oil prices. Only 45% of the contracts are indexed, so the company is able to pass extra oil costs directly to customers. The remaining 55% is actually what gives me some headache: even if not indexed, contracts will be renegotiated every 2 years at the prevailing oil price.

I have attached an xls file.
Row 6: oil price development
Row 8: base input cost for the company
Row 9: increase in the input cost due to changes in the oil price.
Row 11: amount of the additional cost that the company is able to pass through directly in the same period (45%=indexed contracts)
Row 12: Not index part: the remainder. This is basically calculated with 2015 as a base, but part of it will disappear as contracts are renegotiated at higher/lower prices.

What I want to calculate is the value of oil increase that has to be deducted from row 12 because contracts have been renegotiated. Can someone help? I hope the above is clear enough --

thanks!!

[Ochimus]

apeiron,

I think you need the "rolling average" of the current and preceding year (because in Year 3 you will have renegotiated the Year 1 contract, so the "base price" for that will be reset to Zero?

So for Year 2 the formula is:

=AVERAGE(C8*(C6/$C$6-1)*0.55,D8*(D6/$C$6-1)*0.55)

2015 is the "Base" price of Zero.
2016 has a 28% increase in 55% of the contracts, so the combined impact is 23.6
2017 sees a further 22% increase, but the contracts from 2015 have been renegotiated at this new "baseline", so the nett impact is 18.3 (22% on 55% of the business)
2018 is the same rate as 2017, but the contracts from 2016 have been renegotiated at this new "baseline", so the nett impact is Zero
2019 actually sees a 27% fall in price, so the impact is -22.5

Hope that seems logical?

Ochimus