Oracle EPM Planning – Budget Adjustment Tracking

The Intro

In our field there are the need for the complex and the extravagant solutions that take weeks/months to build and test! They are fun and they are challenging and if they are very complex usually after many late nights and weekends you emerge with a solution made of a combination of clever thought process, collaboration and some kick ass development. This is when a solution developer is in his/her element and is both engaged and challenged (headphones on, music pumping, quirks happening and for me a pizza delivered with a nice win). However today, its not the complex that has my focus, its the exact opposite, its the simple. The simple solution that comes along and expresses its great value through effortless simplicity and elegance.

Today I will cover a rather simple solution for Budget Adjustment Tracking! This solution can also be used to also track forecast adjustments using the same principles/techniques. It is important to understand why this solution came about in order to understand the solution. Organisations will create budgets and some will adjust these budgets throughout the year (e.g. Initial Budget, Budget Adjustment 1, Budget Adjustment 2, etc…). The concepts equally apply for forecasts with organisations updating their forecasts multiple times in the year (e.g. Forecast 2+10, Forecast 4+8, Forecast 6+6, etc…).

How often adjustments / updates are made to budgets and forecasts is unique to each organisation so requirements will vary. Also not all organisations require tracking the changes they are making to these scenarios and reporting on the changes. For the ones that do this blog post is for you! I should make it clear that there are multiple solutions available for this and it all depends on the requirements.

This solution was aimed to deliver the functionality to track updates to budgets/forecasts by calculating the differences between the current and prior cuts of a budget or forecast. Again, I do stress that other solutions may be better positioned to handle more detailed requirements, for example, entry of just changes vs tracking (calculating the change) the difference as we do here. The benefits we get from this solution is that it keeps the budget and forecast process simple and is very simple to implement and maintain. This ultimately means that you can get a simple solution without a massive time investment to make it happen!

The How To

In our example/approach, the ability to track budget adjustments is delivered as a calculated amount between the current budget and previous budget. This is achieved using a dynamically calculated dimension member in the Version dimension called Calculated Adjustment (you can use another standard dimension to do this if that makes more sense in your application).

I do recommend that this logic is applied to locked scenarios, this means that the initial entry and adjustments are made in calculating scenarios and copied to the locked scenarios when updates are completed.
The instructions:

1. Create a hierarchy for each budget, this will make it easy to group all changes made for a particular budget and will be very useful for setting up the forms and reports to use this functionality (in the example below we use “Budget2019” as the parent to group the initial budget and all adjustments for that budget):
2. Create the initial budget, nothing special here. Once the budget is finalised copy from live scenario to this locked scenario member. Refer to “Budget2019_Initial” above.
3. Create a UDA called “Adjust” and apply to any adjustment scenario. Do not apply to initial budget.
4. For adjustments add scenario members relevant to that adjustment (refer to “Budget2019_Adj(x)” in the above). Tag this adjustment scenarios with the “Adjust” UDA. Ensure the adjust scenarios are right siblings of the initial budget, this is required for the logic.
5. Create the Calculated Adjustment dimension member and ensure is set to dynamic calc. Create logic similar to the below to calculate the differences in current and prior budgets and save.
6. Do not forget to Refresh Your Application with every new addition!
7. Realise how simple and useful this is!

See this In Action

This is the fun part, the output. In the below example we can see how the tracking works in action (not a real life example, just in case you are wondering!):

• There was an initial budget entry of 8M
• I then decided we needed an additional 2M so I added this
• The manager asked me to reduce the spend by 1M (I argued my case and lost)
• Manager realised I was right, but wasn’t ready to admit it fully so asked to increase by 500K
• Manager realised I was right and gave in to full amount so I then increased to by 500K

Smart View Example:

A few notes on the solution:

• Amounts are stored against version “Final”, column 3
• Commentary for changes are stored in a target version called Target, to enable level zero and summary based commentary, column 4
• Adjustments are back calculated using “Calculated Adjustment” version, column 5

A Message

As always I wish you answers to questions you seek! You are like a Jedi in search of knowledge and I wish the Force is with you on your journey.

Advertisement