Social Icons

Friday, October 4, 2013

Variance Reporting - How to do it - Part III

In the Last series of Posts Here (Part I) and Here (Part II), we have talked about the basics of Variance Reporting (Part I) and Variance Reporting Across Time Periods (Part II). 

In this post, we will talk about variance reporting Across Scenarios (Actual, Plan, Forecast).

Most of the Organizations who have been using Oracle EPM set of tools as part of their financial process would have the below process setup
  1. Fetch the Actuals (transaction level data) from different source systems for their reporting & analysis. Most of the actual data comes from oracle GL, Oracle EBS, PeopleSoft or any type of an OLTP system or it could even come from flatfiles or excel or could be any readable format
  2. Have a Planning application where users can input their PLAN data. Most of the organizations have long-term goals and target and plan to achieve it. What are the steps that are involved to achieve their goals and targets is part of their planning. I am not going to talk in details of about different types of Planning.
  3. organizations do make some assumptions based on their current & Historical Actuals. This is called Forecasting and is mostly considered as a short-term process. You can also employ a Rolling Forecasting process based on your running actuals. 
    • You Forecast for Jan based on the last year actuals. When you move to Feb, You forecast again for Feb based on Jan Actuals. This is really advantageous as it will help you to consider any uncertainty that arise during the course of your current business operations.
  4. organizations do have What-If modelling that will help them to understand the consequences that might arise and take precautionary measure to prevent them. 
    • For Example, Let's say that you expect that due to increase in fuel prices you predict an increase in "Transportation Expenses". 
    • Reduction in Raw Material Cost will reduce your Production Cost. But, that does not mean that you have to reduce your product cost. You might treat this as part of your Assumption and Forecast & Plan accordingly.
  5. organizations do have a Budget process on how to allocate money across various departments. Based on the amount of Budget that has been allocated, companies plan in order to reduce the cost of their operations.
Based on the above points, we can categorize scenarios as Actual, Plan* , Forecast

*Most of the organizations have multiple plans. Based on the number of planning sessions, you can create one scenario for each plan session

Taking all the points above in to consideration, below would be the variance scenarios that you can build in your applications
  • AvP (Act Vs. Plan) , AvP% (Act Vs. Plan %)
  • AvF (Act Vs. Forecast), AvF% (Act Vs. Forecast %)
As you plan and forecast, you always want to maintain multiple versions (Working v1, Working v2, Revised Working, Final) before finalizing your plan. These can be maintained in versions dimension (Standard Dimension when you create a Hyperion Planning Application).

As per point [4] , you can build Variance Scenarios considering What-If Scenarios

Hope this information will help you in building Variance Scenarios that will help your organization in taking better decision, better analysis and do better planning

Happy Learning!!! 


  1. Hi, can you provide example MDX queries for the above ?

  2. Hi Keith. I have recently moved to London and didn't have much time to continue. I will surely post on MDX formulas. ASO cubes are bit complicated when it comes to variance reporting as most of the ASO applications tends to have more detail till date level. Will surely post

  3. Hi, incase organisation needs to do variance reporting across scenario and time periods then we need to create all the members as given in your post. Also i have seen organisation where they have created variance in year dimension rather than scenario.

    1. It's more of perception and simplicity.
      If you want to look at CY Vs. PY (CY - Current Year, PY - Prior Year) then it makes sense to have it in Year than Scenario as you will be looking at the same scenario

      If you want to combine "CY Vs. PY" with different scenarios Scenario then you can also have a Variance Scenario like "Act Vs. Bud"

  4. Hi , we need variance reporting across time periods and scenario both. So shall we create required members in scenario or year dimension.

  5. @Aamir
    You can always choose a dimension which is very standard and I prefer a scenario over Time periods here in your case.

    In my earlier project which is used mainly for Financial Planning & Budgeting, we had around 50+ variance scenarios with all different combinations of Year, Time Periods and Scenarios and also a combination of Sub Vars and functions like PRIOR, SHIFT, MDSHIFT