An Excel Pricing formula based on Lagged Index Prices?

phynicle

New Member
Joined
Jun 12, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Thanks in advance to all who might be willing to chime in.

I have customer pricing formulas to update and i want to automate them a bit.
Essentially, the customers base pricing works like this:
1) It is an average of eg 3 months of historical commodity price.
2) It is usually lagged by 1 month before it comes into effect. Because if the price change is to take effect on Apr'20 and customer needs advanced notification, then Mar'20's commodity price would not make it into the calc in time.
3) The customers price is valid for x amount of months fixed eg 3 months at $295/unit

So take Customer ABC for example:
1) the last time the price changed was in Apr'20
2) the base price is the average of 4 months of historical commodity price, further lagged by 1 month. In this case it was based on the average of Oct,Nov,Dec,Jan'20 periods. Does not include Feb'20
3) The average for this period is $262/unit and is fixed at this price for 3 months

My plea is, does anyone have a more eloquent solution rather than me manually calculating the averages each time?
So hopefully next customer i drop in, i can input the last time price was changed, the months of commodity pricing taken into account, the lag gap and how long their price is fixed for.




Book1
ABCDEFGHIJKLMNOPQRSTUV
1Apr'19May'19Jun'19July'19Aug'19Sep'19Oct'19Nov'19Dec'19Jan'20Feb'20Mar'20Apr'20May'20
2Commodity Price$/Unit210250295330310350345295218189176177169143
3
4
5Customer Pricing Base
6CustomerLast Price RollPeriod of average priceMonths Lag before price becomes validPrice Valid forJun'19July'19Aug'19Sep'19Oct'19Nov'19Dec'19Jan'20Feb'20Mar'20Apr'20May'20Jun'20Jul'20Aug'20Sep'20
7PeriodNo. of MonthsNo. of MonthsNo. of Months
8ABCApr'20413 296 296 296 325 325 325 262 262 262 166 166 166
9CBADec'20616 313 313 313 313 313 313 204 204 204 204
10AAAMar'20313 292 292 292 335 335 335 234 234 234 ? ? ? ?
Sheet1
Cell Formulas
RangeFormula
K8,T8,N8K8=AVERAGE(F2:I2)
L8,U8,Q8,O8L8=AVERAGE(F2:I2)
M8,V8,R8,P8M8=AVERAGE(F2:I2)
S8S8=AVERAGE(K2:N2)
M9:R9M9=AVERAGE($F$2:$K$2)
S9:V9S9=AVERAGE($L$2:$Q$2)
J10:L10J10=AVERAGE($F$2:$H$2)
M10:O10M10=AVERAGE($I$2:$K$2)
P10:R10P10=AVERAGE($L$2:$N$2)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I had to make a few adjustments for this to work, your dates were evaluating as text so I had to change them to a valid format.

The dates in B8:B10 are now the point at which the first pricing base should start, trying to work back from later dates was making the formulas too complex and was not working.
The formula does appear to fail if a date in B8:B10 is too early, in the sense that there is not enough history to get the correct results for the date entered. This is easiest observed by changing B10 to Jun-19
13-06-20.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20
2Commodity Price$/Unit210250295330310350345295218189176177169143
3
4
5Customer Pricing Base
6CustomerLast Price RollPeriod of average priceMonths LagPrice Valid forJun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20
7PeriodNo. of MonthsNo. of MonthsNo. of Months
8ABCOct-19413    296.25296.25296.25325.00325.00325.00219.50219.50219.50166.25166.25166.25
9CBADec-19616      313.33313.33313.33313.33313.33313.33204.00204.00204.00204.00
10AAASep-19313   291.67291.67291.67335.00335.00335.00234.00234.00234.00174.00174.00174.00 
Sheet2
Cell Formulas
RangeFormula
G8:V10G8=IFERROR(IF(G$6<$B8,"",IF(MOD(COUNT($F8:F8),$E8)=0,AVERAGE(INDEX($E$2:$R$2,MATCH(EDATE(G$6,-($C8+$D8)),$E$1:$R$1,0)):INDEX($E$2:$R$2,MATCH(EDATE(G$6,-($C8+$D8)+$C8-1),$E$1:$R$1,0))),F8)),"")
 
Upvote 0
Jason!!!!!

you saved me from doing 200 manual costings!

your an absolute godsend, thanks for your brilliance & patience,hopefully this benefits others as well.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top