mathematical analyse of data

[rhola]

Say you have period 1 in which you sell to articles
A1 Qty = 5 - Price = 10
A2 Qty = 5 - Price = 5
Total value = 75 - Avg price = 75 / 10 = 7,5

In periode 2, you sell both articles again but in different volumes and at different prices:
A1 Qty = 10 - Price = 8
A2 Qty = 5 - Price = 10
Total value = 130 - Avg price = 130 / 15 = 8,66

The average price has increased with 1,16. Now we are looking for a formula to explain this increase. Two factors have influenced this increase: the change in price and volume. Does anyone know a formula to calculate the differences. What made the price go up: change in volume or price or both and what's their individual part in the general increase? We are looking in a way to say that by example 0,85 of 1,16 is due to price increase and 0,31 is due to change in volume which has effect on average price?

Does anyone can help me out with this??

Thx,

Rudi

 

[RichardS]

Not sure if this is what you want, but you could possible use the scenario manager. In your second period, leave the prices the same with the new quantities to see what impact that has on the price, then do it the other way, change the prices and leave the quantities the same. I'm no maths giant, so my theory may be completely up the creek. Just thought it might be worth a try.

In your exanple, the increase in average due to quantity increase is .83, the increase due to increased price is 1.5. The average of these two figures is 1.16, the total increase in average. 35.6% of the iincrease is due to quantity and 64.4% to price.

HTH

Richard


Edited to correct a figure

_________________

This message was edited by RichardS on 2002-10-15 03:09

 

[rudiholans]

Thx Richard, as i'm totally not familiar with scenario messanger, can you please add some comments to how you reached these values. I'm trying with simple calculations, but they don't match your results?

Rudi

 

[PaddyD]

haven't looked at the question in too much detail, but might this help?

http://216.92.17.166/board/viewtopic.php?topic=13287&forum=2

if not, post back with a bit more detail on where your data is, what format it takes etc...

paddy

 

[rudiholans]

Richard,

I've tried with ordinary calculations and it works, but now i do not understand why in many different scenarios, there is no change in volume avg, but in the example i gave it does.

Can i sent you by email my example?
Rudi

 

[RichardS]

Rudi,

Sorry for delay in replying, but we're on opposite sides of the planet.

You can send me the file if you like, but no guarantees. I'm no expert.

Richard