# How much has price and quantity increased from year 1 to year 2?

• Aug 1st 2011, 08:26 AM
MrExcel
How much has price and quantity increased from year 1 to year 2?
We sell two products.

Product A:
---------------------------
price y1 = 10
quantity y1 = 2
sales y1 = 20 (price*quantity)

price y2 = 10
quantity y2 = 1
sales y2 = 10 (price*quantity)

Product B:
---------------------------
(p) price y1 = 5
(q) quantity y1 = 2
(s) sales y1 = 10

(p) price y2 = 1
(q) quantity y2 = 2
(s) sales y2 = 2

Analysis:
---------------------------
I want to know by how much our prices and quantities have increased for all products in total (or maybe rather on average) from year 1 to year 2.

Let's use capital letters for the total profit, quantity and sales per year: S, P, Q.

I know that S = P * Q.

I want to measure the price and quantity change. So let's set the capital letters to ratios of year 1 values: S1=1, P1=1 and Q1=1. I know that S2 = 12/30.

What are P2 and Q2?
• Aug 1st 2011, 01:58 PM
pickslides
Re: How much has price and quantity increased from year 1 to year 2?
Hi there, I'm not following this too well, but i'll give it a shot.

Consider quantity alone. The sum of products in the 1st year was 2+2=4 and in the second 1+2=3.

The change therefore in volume of product is -1 and in percentage is a decrease by 25%, as (4-3)/4 = 0.25

How is that?
• Aug 1st 2011, 11:13 PM
MrExcel
Re: How much has price and quantity increased from year 1 to year 2?
That's good. But I'd like the % change to be volume weighted. So that decreases in quantity influence the change in quantity level more from year 1 to year 2 when the quantity decreases for a high priced product than when it decreases for a low priced product.

If the quantity of product A (price y1 = 10) increases that should have a more significant impact on sales (S=P*Q) than if the quantity of product B (price y1 = 5) increases with the same amount.

I tried to solve it like this:

Price level Article A Year 1 = 10/10 = 1
Price level Article B Year 1 = 5/5 = 1
Price level Article A Year 2 = 10/10 = 1
Price level Article B Year 2 = 1/5 = 0.2
Sales Article A Year 1 = 20
Sales Article B Year 1 = 10
Sales Article A Year 2 = 10
Sales Article B Year 2 = 2

I then calculated the volume weighted price level for year 2 for all articles combined as:
P2 = (1*20+0.2*10)/(20+10) = 0.7333323 [(relative price level article A * sales article A + relative price level article B * sales article A)/(sum sales)]

I calculated the volume weighted quantity level for year 2 for all articles combined the same way:
P2 = (0,5*20+1*10)/(20+10) = 0,6666667

S2 = 12/30 = 0.4

Now I expect P2*Q2=S2,

but S2 = 0.4 and P2*Q2=0.4888889

So if the price decreases of a product I want it to affect the "price level" more if that product is sold in high quantities. If the quantity decreases of a product I want it to affect the "quantity level" more if that product is sold at high prices.

Is this doable?