Variance Analysis Using Calculated Measures – Exception Aggregation

[former_member805109]

Hello fellow Visionaries,

Based on a client request I discovered a new and innovative way to perform variance analysis using calculated measures within SAC. This was only made possible due to the New Model released earlier this year by SAP. The calculations shown in this blog were made so much easier based on the new features leveraged in the New Model.

A client wanted to perform earnings variance analysis (EVA) calculations consisting of volume/mix and rate within the profit and loss statement (P&L). The request was for a volume/mix calculation to be performed at certain leaf members (account types) and rate calculations to be performed at those same leaf members and a few additional members.

EVA calculations often look at the difference between actual and AOP data so we first created a variance measure based on account type. We named this measure “Actual_Vs_AOP”. Here is the calculation:

IF([d/ACCOUNT_TEST].[p/Account_Type]="INC" ,RESULTLOOKUP([Amount] ,[d/Version]="public.Actual" )-RESULTLOOKUP([Amount] ,[d/Version]="public.AOP" ) ,(IF([d/ACCOUNT_TEST].[p/Account_Type]="EXP" ,RESULTLOOKUP([Amount] ,[d/Version]="public.Actual" )-RESULTLOOKUP([Amount] ,[d/Version]="public.AOP" ) ,0 )) )

The volume mix calculation takes dollar per pound/volume. So next, dollar per pound needed to be calculated for every member. This calculation is straightforward:

[Amount] /RESULTLOOKUP([Amount] ,[d/ACCOUNT_TEST]="Volume (lbs)" )

We could then create the volume/mix calculation. This calculation was more complicated as it was only to be performed for certain leaf members. We accomplished this by creating a property in the account dimension and tagged the various accounts we wanted to calculate vol/mix for with “EVA”.We could then use an IF function to single out those exact lines. Also, if the volume is null, we want the entire Actual Vs AOP to appear in this column.  This is the calculation we used:

IF([d/ACCOUNT_TEST].[p/EVA_Calc]="EVA", IF(ISNULL(RESULTLOOKUP([Amount] ,[d/ACCOUNT_TEST]="Volume (lbs)" )) ,[Actual_VS_AOP],IF(RESULTLOOKUP([Amount] ,[d/ACCOUNT_TEST]="Volume (lbs)" )=0 , [Actual_VS_AOP],RESULTLOOKUP([Dollar_Per_Pound] ,[d/Version]="public.AOP" )*RESULTLOOKUP([Actual_VS_AOP] ,[d/ACCOUNT_TEST]="Volume (lbs)" ))))

Finally, we needed to create the ‘rate’ calculated measure which we accomplished like the calculation above. We tagged the additional accounts that needed the rate calculation with “Rate” in the property we created in the account dimension. Then we were able to calculate the rate on both the “EVA” accounts and the “Rate” accounts. Note: for the accounts tagged as rate, we just wanted these to return the Actual Vs AOP. These are fixed expense accounts so they do not get calculated the same. This is the calculation:

IF([d/ACCOUNT_TEST].[p/EVA_Calc]="EVA" ,(RESULTLOOKUP([Dollar_Per_Pound] ,[d/Version]="public.Actual" )-RESULTLOOKUP([Dollar_Per_Pound] ,[d/Version]="public.AOP" ))*RESULTLOOKUP([Amount] ,[d/ACCOUNT_TEST]="Volume (lbs)" ),IF([d/ACCOUNT_TEST].[p/EVA_Calc]="Rate" ,[Actual_VS_AOP]-[Vol_Mix] ) )

The last request the client had was to aggregate these totals through the hierarchies we created in the account dimension without the calculation being performed at the parent node levels. We were able to do this by leveraging the exception aggregation feature in SAC. We used sum for the ‘exception aggregation type’. See below for the details:

I hope you all enjoyed this blog post, and it helps you solve issues in the future. If you found any benefit, please give it a like! If you have any questions, please leave comments. Finally, if you are interested in further SAC content, give me a follow as I plan to post further about these topics in the future.

 

Brian Harris