Variance Reporting - How to do it - Part II

In the last post here, we have talked about the basics of Variance Reporting. In this post, we will talk a bit detail about the variances scenarios that fall under two categories that we mentioned in the Part I of this series. 

Exert from the last post

You can categorize the Variance Reporting as

1. Variance Reporting across Time Periods
2. Variance Reporting across Scenarios (Actual, Plan, Forecast)

Variance Reporting across Time Periods

Most of the organizations often compare data across Time Periods to know where exactly their stand and how are they progressing.

For Example, a sales organization want to see 

• How much their sales has increased from Last Quarter/Month
• What is their revenue when compared to Last Quarter/Month
• what are the companies expenses when compared to Last Quarter/Month
• What is the profit margin when compared to Last Quarter/Month

When a company releases their Quarter Results, you might have heard that

• Our company profits have increased to 19% when compared to Last Quarter
• Our Sales have increased to 35% when compared to Last Quarter
• YoY we have an increase of 12% in profits

So, how do we implement in Essbase?

Most of the Essbase/Planning Application will have Scenario Dimension and Scenario is a good candidate to create Variance Reporting Scenarios

So, in general if we want to have all these done in Oracle Essbase, below are the list of comparisons that you would do

1. Current Month Vs Prior Month (Month On Month Variance)
2. Current Quarter Vs Prior Quarter (Qtr On Qtr Variance)
3. Current Year Vs Prior Year (Year On Year Variance)

These should be flexible enough to work at Q-T-D and Y-T-D level and they should also work on any Month/Qtr/Year for which you want to retrieve the data.
 At times, you might want to see just the variance of Current Year YTD with Previous Year Closing Balance mostly for Balance Sheet Accounts. 

Let's go a bit deep and see how your Formula would like for the Variance Scenarios
Make sure that you categorize your Hierarchies for better understanding. This is how my Scenario Dimension looks like

Scenario
    Actual Scenarios
    Plan Scenarios
    Variance Scenarios
        SCN_CM_Vs_PM (Curr Mth Vs Prior Mth) (~)
        SCN_CQ_Vs_PQ (Curr Qtr Vs Prior Qtr) (~)
        SCN_CY_Vs_PY (Curr Year Vs Prior Year) (~)

Current Month Vs Prior Month (Month On Month Variance)
Let's name the Scenario as SCN_CM_Vs_PM (Curr Mth Vs Prior Mth). This would be a Dynamic Calc member (In ASO, this member would fall under a Dynamic Hierarchy) with a Formula on it.

IF(@ISMBR("Jan"))
/* For Jan, You might want to take the last Year Dec as prior month */

      "Actual"->"Jan" - @PRIOR("Actual"->"Dec",1,"Years") 
ELSE
/* @LEVMBRS("Period",0) is an optional parameter. By Default, it will take level0 members of the Period Dimension (Dimension tagged as Time) */
      "Actual" - @PRIOR("Actual",1,@LEVMBRS("Period",0))
END

Current Quarter Vs Prior Quarter (Quarter On Quarter Variance)

Similar to "Month On Month Variance", you can write the similar formula for "Quarter On Quarter Variance"

Current Year Vs Prior Year (Year On Year Variance)

You may have lot of  years in your application but you may have data starting from some specific year / As a part of your archival strategy, you might archive your historical data to a separate application. In such cases, you can have a substitution variable that will take the first year of the application from where the data is available and below is how the formula would look like:
IF(@ISMBR(&FirstYear))
/* FirstYear is that year from which you have data in your application */
      "Actual"; 
ELSE
/* @LEVMBRS("Years",0) is an optional parameter. By Default, it will take level0 members of the Period Dimension (Dimension tagged as Time) */
      "Actual" - @PRIOR("Actual",1,@LEVMBRS("Years",0))
END

Note: Similar to the above variance Scenarios, you can also build Variance % Scenarios. 

Once you have all the scenarios in place, you can build a nice Smart View Retrieve template / Build an HFR Report and show it to your users. I hope they will love it.

We will talk about "Variance Reporting across Scenarios (Actual, Plan, Forecast)" in the next post. As an Add-on (At the END), we will also cover how these variance reporting scenarios will work in ASO.

Happy Learning!!!