Allocation of Price and Volume changes to a change in Rate

Assume you have three or more products {A, B, C, ...} and each has a price (P) and volume (V). There's a set of prices and volumes in period 1 that results in an average price across all products (AvgP1). Then the product prices and volumes move independently in period 2. This results in a change in the average price (AvgP2).

I want to allocate the price and volume movements for each product so that the sum of all the allocations add up to the total change. Preferably, I'd like to do this without "normalizing" an initial set of answers. I also want to be able to calculate the Price and Volume impact for each product with only knowing that product's Price and Volume and the total portfolio average Price and total Volume -- in other words, I don't want to have to iterate through all products to determine to impact from a single product.

Note that this is different from the typical Price/Volume impact analysis since it's broken into the components of Price and NOT Revenue. Also, if a low price product decreases in volume, we would expect it to have a positive Volume Impact on the Avg Price, and vice versa. If any product decreases in Price, the Price Impact of that product would be negative, and vice versa.

Example data:

Product Period 1 Price Period 1 Volume Period 2 Price Period 2 Volume Product Price Impact Product Volume Impact
A $2.00 2000 $2.50 3000 Pa Va
B $1.00 1000 $0.90 2000 Pb Vb
C $0.50 3000 $0.60 500 Pc Vc
Avg/Total AvgP1: $1.083 6000 AvgP2: $1.745 5500 Pttl Vttl

Solve for Pa through Vc. In the example above:
  • Pa+Va+Pb+Vb+Pc+Vc = Pttl + Vttl = (1.745 - 1.083) = $0.662
  • Pa is positive, Pb is negative, Pc is positive, Va is positive, Vb is negative, Vc is positive
  • AvgP1 + Pa + Va + Pb + Vb + Pc + Vc = AvgP2
I would like a formula I can plug in for each of Pa through Vc. Pa, Pb, and Pc should use the same formula (with different inputs). Va, Vb, and Vc should use the same formula. I should be able to solve for Pa or Va with only seeing the information for Product A and the Totals/Averages.

Answer

Answers can only be viewed under the following conditions:
  1. The questioner was satisfied with and accepted the answer, or
  2. The answer was evaluated as being 100% correct by the judge.
View the answer
  • Appears to work exactly as expected. Great solution. Thank you!

The answer is accepted.
Join Matchmaticians Affiliate Marketing Program to earn up to a 50% commission on every question that your affiliated users ask or answer.