Calculating waterfall chart components

user_7657585 0
Viewed 146

I’m trying to create a waterfall chart, but having trouble working out how to calculate the contributions from the components that gets you from budget to actual...

For example – I have a "budget" list of groceries, with a price and a quantity, and then I’ll have an "actual" grocery list with the final price and quantity… which looks like the below

Budget

Item Price Quantity
Apples 2.1 5
Oranges 3.4 7
Bananas 5.1 10
Mangos 15.3 3
Grapes 3.8 20
Total 4.6 45

Actual

Item Price Quantity
Apples 2.5 9
Oranges 3.7 6
Bananas 4.3 11
Mangos 13.3 4
Grapes 9.5 22
Total 6.8 52

So if the waterfall were to begin at the weighted average of $4.6 per grocery item, how do I calculate each grocery item's contribution to get to the actual weighted contribution to $6.8 per item? Is there a nice simple calculation to work this out, ensuring that it factors in changes to each item's price as well as changes in quantity...

hoping to achieve something like this waterfall:

waterfall

Thanks

math
1 Answers

Notations:

  • i : item's number (in the example: 1 for Apples, ..., 5 for Grapes)
  • n : number if items (in the example: 5)
  • Pb[i] : budget price of the i-th item
  • Qb[i] : budget quantity of the i-th item
  • Qb = Qb[1] + ... + Qb[n] : sum of budget quantities (in the example: 45)
  • Pb = (Pb[1] * Qb[1] + ... + Pb[n] * Qb[n]) / Qb : average budget price (in the example: 4.6)
  • Pa[i] : actual price of the i-th item
  • Qa[i] : actual quantity of the i-th item
  • Qa = Qa[1] + ... + Qa[n] : sum of actual quantities (in the example: 52)
  • Pa = (Pa[1] * Qa[1] + ... + Pa[n] * Qa[n]) / Qa : average actual price (in the example: 6.8)

You would like to calculate each item's contribution to the difference between the average actual price and the average budget price. This can be done by rearranging the difference:

Pa - Pb
  = (Pa[1] * Qa[1] + ... + Pa[n] * Qa[n]) / Qa - (Pb[1] * Qb[1] + ... + Pb[n] * Qb[n]) / Qb
  = (Pa[1] * Qa[1] / Qa - Pb[1] * Qb[1] / Qb) + ... + (Pa[n] * Qa[n] / Qa - Pb[n] * Qb[n] / Qb)

That is, the contribution of the i-ts item is Pa[i] * Qa[i] / Qa - Pb[i] * Qb[i] / Qb. For your example, the numbers are the following:

          Contribution
Apples     0.2
Oranges   -0.1
Bananas   -0.2
Mangos     0.0
Grapes     2.3

The sum of these contributions is 2.2, which equals to the difference between the average actual price (6.8) and the average budget price (4.6), as expected.

user_6001350
Related