4 Replies Latest reply on Mar 26, 2017 12:23 AM by Morgan DUARTE

# Average selling price comparison (Delta price)

Hello,

Last year I've tried to come up with a dashboard to show the delta price of a product and a product line, but I did not succeed to get the right calculation. Hope you can help me there.

I have 2 years of data, with many lines of products (Material) and products lines (Activity) and I need to do the following formula:

(Average Selling Price of 2017 - Average Selling Price of 2016) * 2017 quantity   and all of it divided by total turnover of 2017.

And this calculation should work at the material level (product ABC) but also at the activity level (Server, desktop,...).

Attached an example.

For ABC, I should fing +4.8% and for DEF, -29.6%

And for the total Server, it should be -11.1% (The sum of ABC and DEF delta volume divided by the sum of ABC and DEF turnover)

Any idea how to do it?

Thank you

• ###### 1. Re: Average selling price comparison (Delta price)

Hi Morgan,

Hope this helps.

Yours,

Yuri

• ###### 2. Re: Average selling price comparison (Delta price)

Hi Yuri,

At the line level it is okay, but not at the activity level.

It works, until we have some material we sold in a year but not the other.

Attached a revised workbook where I have added 1 new product (Product Launch) with some numbers.

The result should be the same than your previous version in fact, meaning, if not matching both years, we cannot calculate the Delta Price.

I had a similar post 9 months ago that we just solved. (But a bit complex)

May be you know an easier way.

Thank you

• ###### 3. Re: Average selling price comparison (Delta price)

Hi Morgan,

so I just added rows for both new and discontinued products.

So you want to count for only those products

which are present in both 2016 and 2017 --

throwing both new and discontinued ones

out from the calculation? This could be done.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Average selling price comparison (Delta price)

Thanks Yuri !

I still find some differences there and there, but it is probably in my methodology (I remove the lines where the Turnover is 0 even if there is quantity).

But this is an old formula, that might need to be corrected from my side.

I'll double check and let you know if any further differences.

Thank you