# mathematical analyse of data

#### rhola

##### New Member
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

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

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

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

Replies
3
Views
268
Replies
1
Views
146
Replies
2
Views
272
Replies
14
Views
329
Replies
1
Views
339

1,196,100
Messages
6,013,452
Members
441,767
Latest member
Craigh4444

### 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.

### Which adblocker are you using?

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

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