6 Replies Latest reply: Sep 8, 2016 6:26 AM by Peter Rieper RSS

    Volume- and Price-Variance with multiple unitcosts

    Peter Rieper

      Dears,

       

      I am not quite sure, whether there might be a solution for my problem, but there is so much knowledge out there ....

       

      Here we go:

      I am supposed to calculate Volume- and Price-variance. For simplicity we may concentrate on the volume-variance only, with formula (Q2-Q1)*P1 [Q2 stands for new quantity, Q1 for old quantity, P1 for the old unitprice]. The volume-effect is supposed to be summarized.

      Problem is that I do not have unit-info (thus not unitprice) for certain items.

      Rules are:

      1. If there are units recorded for the specific item, take them.
      2. If there no units recorded for the item, take the sum of all units from this group.
      3. If there are no units recorded for the group, take the sum of all units.

      So looking at the calculation for T3 for 2016, it will take 500 and divide by the sum of T1 and T2 (=25). Same for 2015. The volume-effect then would calculate (25-22) * 18.18 = 54.54 (~55 shown above).

      Needless to say, that number of Item, groups etc is not fixed ...

      Is there a way to cover the calculation into one formula, allowing to collapse all dimensions and still showing the above +332?

       

      So far my formula is (works, if all lines have volumes resp. unitprices):

      =SUM(AGGR(SUM((Q2-Q1)*P1), Month, City, Item))

       

      Have not managed to solve it as per the above example (think, that there are different granularities to create the sums).

       

      Any ideas?

       

      Edith has attached an example

      Edith has replaced the screenshot with a correct one.

       

      Regards

      Peter

       

      Message was edited by: Peter attachment