How to add up data within a range of months with multiple variables that need to match.

[Rehmanx]

Hi Guys,

So i have got the file attached to do almost 90% of what's needed and struggling with the final part.

I need to calculate the impact of a price cut on the next 3 year's volume. Most of the price cuts are implemented in Nov so the formula's in Columns Q, R & S work perfectly fine if the Price cut is implemented in Nov as they add up the volume for Nov and Dec in the year price cut is effective from and then sums up the full year for the outer years.

However, I am struggling with the two points below.

1. I can't think of the correct formula if the price cut is implemented in a different month of the year. Let's say the price cut is effective from Feb 2021, then in Column Q I would want the volumes from Feb till Dec 2021 to be added up and multiplied by the difference in price.
2. In some cases the price cut come later than expected as is the case in Row 9 "Grape". In this case i have a small pricing help rather than a cost which again is not calculated by the current formulas.

I was thinking i might need a sum product do add up the months from the date price cut is effective till the end of the year.

Any Ideas?

[kvsrinivasamurthy]

May be this. In Q4 then copied down.

Please Login or Register  to view this content.

[Rehmanx]

Hi kvsrinivasamurthy,

Thanks alot for the above, that solves the issue in point 1.

Would you also have a solution for Point 2 above? in the case that the price cut is delayed by a few months vs plan then the formula should be calculating a pricing help for a few months.

Thanks in advance

[kvsrinivasamurthy]

I am not clear with point2. Pl explain with example.

[Rehmanx]

In Cell C9 you will see that the price cut was planned for 01/11/2021 however the latest input in Cell J9 indicates that the price cut will now be effective from 01/11/2022. Therefore you have 12 months of volume that was planned with a price cut of -8% which will now be sold at full price and so in Columns T & Q9 i would expect to benefit vs plan coming through as a positive variance.

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Rehmanx]

Hi FDibbins,

Thanks for your message. I have updated the title as you have suggested. Let me know if the change has come through ok.

Thanks

[kvsrinivasamurthy]

It is not yet clear. Pl explain in view of mathematics, what calculation is required.
Formula in Q has no relation to Column C.