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.
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 | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | Apr'19 | May'19 | Jun'19 | July'19 | Aug'19 | Sep'19 | Oct'19 | Nov'19 | Dec'19 | Jan'20 | Feb'20 | Mar'20 | Apr'20 | May'20 | ||||||||||
2 | Commodity Price | $/Unit | 210 | 250 | 295 | 330 | 310 | 350 | 345 | 295 | 218 | 189 | 176 | 177 | 169 | 143 | ||||||||
3 | ||||||||||||||||||||||||
4 | ||||||||||||||||||||||||
5 | Customer Pricing Base | |||||||||||||||||||||||
6 | Customer | Last Price Roll | Period of average price | Months Lag before price becomes valid | Price Valid for | Jun'19 | July'19 | Aug'19 | Sep'19 | Oct'19 | Nov'19 | Dec'19 | Jan'20 | Feb'20 | Mar'20 | Apr'20 | May'20 | Jun'20 | Jul'20 | Aug'20 | Sep'20 | |||
7 | Period | No. of Months | No. of Months | No. of Months | ||||||||||||||||||||
8 | ABC | Apr'20 | 4 | 1 | 3 | 296 | 296 | 296 | 325 | 325 | 325 | 262 | 262 | 262 | 166 | 166 | 166 | |||||||
9 | CBA | Dec'20 | 6 | 1 | 6 | 313 | 313 | 313 | 313 | 313 | 313 | 204 | 204 | 204 | 204 | |||||||||
10 | AAA | Mar'20 | 3 | 1 | 3 | 292 | 292 | 292 | 335 | 335 | 335 | 234 | 234 | 234 | ? | ? | ? | ? | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K8,T8,N8 | K8 | =AVERAGE(F2:I2) |
L8,U8,Q8,O8 | L8 | =AVERAGE(F2:I2) |
M8,V8,R8,P8 | M8 | =AVERAGE(F2:I2) |
S8 | S8 | =AVERAGE(K2:N2) |
M9:R9 | M9 | =AVERAGE($F$2:$K$2) |
S9:V9 | S9 | =AVERAGE($L$2:$Q$2) |
J10:L10 | J10 | =AVERAGE($F$2:$H$2) |
M10:O10 | M10 | =AVERAGE($I$2:$K$2) |
P10:R10 | P10 | =AVERAGE($L$2:$N$2) |