Data Phasing and Allocation

Today, I'd like to discuss about techniques I've used to implement rules for cost allocation and data phasing.  Sticking to the "no hard-coding" mantra, here are some of the methods that were used in my PBCS projects.

The examples in this blog rely on a PBCS application with the following dimensions and members:

• Entity
• Cost Centre
• Account
• Measure
• Period
• Scenario
• Version
• Cycle
• Cycle_Line

All the dimensions contain members you would usually find in any PBCS application.  The exceptions are in the Measure, Cycle, and Cycle_Line dimensions which I'll discuss in more detail in sections below.

Phasing of Plan Data

Introduction

More often than not, you'd find yourself getting requests to build planning models that is quick to prepare and the most requested function I found has been the phasing of the budget or forecast data in the outer years based on trends derived from Actuals.  In this instance, planners would have entered their annual planned numbers and will rely on the business rule to phase their plans across the months for the outer years based on historical trends that he/she has selected.  This is to ensure that the plans relfect the actual trend.  One such implementation of mine, the client has requested for a phasing model that allows them to assign one of the following phasing methods to each of the account that they wish to phase:

• Last Year Actual
• Last 2 Years Actual Average
• Last 3 Years Actual Average

Here's a sample of how the phasing works:

In the example above, the Annual Amount of $20,000 for "Acc001" in FY19 is phased based on "Last Year's Actual" while "Acc004" is using the "Last 2-Years Actual Average" as the phasing basis (third row of "Acc004").  So, with "Acc001" the calculation is simply taking the "Amount to be Phased" and then multiplying with the monthly last year actual number for "Acc001" before being divided by the total last year actual amount for "Acc001".  The formula is shown below:

While "Acc004" uses the Average of Last 2 Years' Actual to phase the annual data.

Here's what I did:

Tagging of Phasing Method

A webform was created to expose the relevant accounts that are subjected to the phasing logic with a measure (associated with a Phasing_Method smartlist) displayed in the column.  The Phasing_Method smartlist contains the 3 methods mentioned above.  This form provides the users the flexibility to vary the phasing method by account.

Another form was defined to capture the annual planned amount and this amount is captured against the "BegBalance" period member.

The Rule

The phasing of the data is done through a business rule where it will evaluate the phasing method associated to each of the accounts and then apply the necessary logic to spread the annual plan data across the periods using the user-selected basis.  Here's a snapshot of the business rule:

Inter & Intra-Company Cost Allocation

Introduction

Another use case for the above implementation is for cost allocation.  I realised that I have written similar post about cost allocation when discussing about reverse-mapping techniques.  You can read the post here.  In this blog, I have expanded the description to include some examples to further illustrate how the allocation model will work.  So, read on.

In building such a model for one of my clients, I was determined to build them one that they have full control over how the allocation is performed.  By this, I mean to develop one that allows the users to determine the source of costs to be allocated and the targets for the allocated costs.  The model will also accept the allocation ratio for the source/target combination.  The spreadsheet below illustrates the allocation of costs from Entity A Cost Centre 001 to Entity A Cost Centre 002, 003 & 004.

And here's a sample of the Inter-Company allocation where I will allocate part of the cost of Entity A to entities B, C, & D.  Note that 40% of the cost from Entity A is retained within the same entity.

Now, let's examine the building blocks for this model:

Dimensions & Members

As mentioned earlier, I'll focus on the Measure, Cycle, and Cycle_Line dimensions in this section.  T

Measure Dimension

The main function of this dimension is to enable us to further break an account down further to delineate the origin of a piece of data.  Consider the following hierarchy in the Measure dimension:

• Measure
• Total_PL
• Total_PL_Before_Alloc
• Loaded_Amount
• Direct_Amount
• Phased_Amount
• Workforce_Amount
• Project_Amount
• Allocation_Amount
• Alloc_In
• Alloc_Out
• Allocation_Drivers
• Phasing_Method
• Cycle_Description
• Source_Entity
• Source_Cost_Centre
• Source_Account
• Target_Entity
• Target_Cost_Centre
• Target_Account
• Alloc_Ratio

The "Loaded_Amount" member is used to store the data that is loaded from external data sources (e.g. Actuals from ERP).  "Direct_Amount" member is an input member capturing inputs from users during the planning process while the "Workforce_Amount" and "Project_Amount" members will store that data coming from the Workforce and Project models respectively.  The "Allocation_Amount" member which is further divided into "Alloc_In" and "Alloc_Out" will be used in the Allocation model.  All these members will roll-up to "Total_PL" to arrive at the total P&L number.

Extending on the example discussed in the introduction, I am adding the Measure dimension to the Intra-Company allocation table:

The measure "Total_PL_Before_Alloc" is a roll-up of amounts from "Loaded_Amount", "Direct_Amount", Workforce_Amount", and "Project_Amount".  The example above shows that the last 3 members ("Direct_Amount", Workforce_Amount", and "Project_Amount") contain values of $400, $300, and $300, and therefore,"Total_PL_Before_Alloc" being the parent will hold the sum of $1,000.  We will use this roll up as our source for allocation.  

As a result of the allocation process, the model will allocate to the target Cost Centres (002, 003, & 004) based upon the user-defined ratios and the allocated numbers are stored in the "Alloc_In" measure.  The whole amount from the source "Total_PL_Before_Alloc" will also be reversed and posted to the source Entity/Cost Centre/Account combination and the reversal (-$1,000 in this example) is stored in the "Alloc_Out" measure.  So, when reported at "Total_PL" level, the Overheads from the source Entity/Cost Centre" combination (Entity A, Cost Centre 001) will be fully allocated to the respective targets because the sum of "Total_PL_Before_Alloc" ($1,000) and "Allocation_Amount" (-$1,000) will result in zero.

Cycle & Cycle_Line Dimension

These dimensions allow users to define different allocation rules (in Cycle) and multiple lines per allocation rule (Cycle_Line).  The members in both the dimensions are just running sequential numbers.

• Cycle
• C001
• C002
• C003
• ...

• Cycle_Line
• CL001
• CL002
• CL003
• ...

Source/Target & Ratios Definition

As mentioned in the introduction, this model serves to allocate cost within the company as well as inter-company allocation.  The mapping table that I've defined via a webform capture not only the source and target accounts, but also the source/target Entity & Cost Centre combination as well.  Here's an illustration of the webform defined to capture the source/target combinations:

The top part of the diagram shows the form to define the Allocation Cycle while the bottom part of the diagram shows the detail allocation lines for each allocation cycle.  Notice that both forms are capturing the combinations against the NA members for Entity, Cost Centre, and Account.  You might be wondering how do I then capture the different combinations of source and target Entity, Cost Centre, and Account combinations?  The answer lies in Smartlist (see next section for details on smartlists).

Columns 3 through to 8 of the Allocation Line form are measures with smartlists tagged to each of the member.  This enables users to then pick the source/target entity, cost centre, and account of their choice from their associated smartlist.  Column 9 is an input column allowing users to enter the allocation ratio for each allocation line.  The above definition is  reflective of the Intra-Company Allocation example that I have mentioned in the introduction earlier.

Smartlists

The following heirarchy-driven smartlists are required to support the allocation model.  The smartlists will be assigned to Allocation_Driver members in the Measure dimension:

• Source Entity
• Source Cost Centre
• Source Account

If you have Target Entities, Cost Centres, or Accounts that are different from the source, you'd need to also define Target smartlists from them:

• Target Entity
• Target Cost Centre
• Target Account

A sample of a hierarchy-driven smartlist is shown below.  Please refer to my blog on Hierarchy-Driven Smartlists here for more in-depth description on the process to build such smartlist.

The Business Rule

To code the allocation logic, I have decided to use the @XWRITE function.  The cool thing about the model is that the rule is able to create the necessary target blocks based on what the users have selected for their source/target combinations via the webform created.  This helps minimise any chance of a block explosion where blocks are created unnecessarily which will in turn adversely affect the performance of the cube in general.  Keep a look out for my post on block creation which is still a "work in progress".  Also, the fact that all the source/target combinations are captured against Entity_NA, Cost_Centre_NA, and Account_NA means that the rule will rule faster as it does not need to cycle through all the different entity, cost centre, and account base members.  My rule is fixed to the NA members only and using @XWRITE to create the necessary blocks and perform the required allocation.  Here's the rule:

Notice that I've used the @XWRITE function to both create the blocks and perform the allocation.  The first part of the rule posts the reversal amount to the Source Entity, Cost Centre, and Account combination and to the "Alloc_Out" measure.  The value of "Total_PL_Before_Alloc" is multiplied by -1 before posting the results.

The second section of the rule deals with the allocation of the "Total_PL_Before_Alloc" amount from the Source Entity, Cost Centre, and Account combination to the Target Entity, Cost Centre, and Account combination and to the "Alloc_In" measure.  The value of "Total_PL_Before_Alloc" is multiplied by "Alloc_Ratio" before posting the results to the targets.

That's it folks for this blog.  Hope you've enjoyed reading this as much I have enjoyed writing it.  Do leave a comment and let me know what else you want me to cover.

Until then, sayonara for now.

To Hard-Code or Not To-Hard-Code - The Sequel

Keeping to the theme from my previous post, I thought of sharing more tips and tricks on how to avoid hard coding your business rules.  In this post, I'll share with you the following techniques that I have used in the various projects that I've worked with previously:

Reverse-mapping using Smartlist

This is essentially relying on dimension-based smartlists to resolve to the intended member.  This option gives the users the ability to maintain the mappings via a webform without having the underlying business rules hard-coding to any target members.  Use case for this method can be applied to any allocation-type business rules.  Details of this techniques can be found in this post and this one.

User Variables & Planning Expressions

The User Variables feature in Planning can be a useful tool to give users more control over what they wish to view in a webform.  Forms configured to use these variables will give users the ability to dynamically select a member at runtime (e.g. a parent member of a hierarchy) which could then be used to display the descendants of the selected parent.  To take it one step further, we can use the user variables in business rules as well.  This helps us to limit the calculation to only the selected member(s), thus, making the rule run faster.

Let's see how this can be achieved in the business rule.  The trick here is to use a Planning Expression to resolve to the selected member of the User Variable.  The Planing Expression in question here is the [[PlanningFunctions.getUserVarValue("User_Variable")]].


Consider this.  I have defined the following User Variables:

And I wish to limit my calculation scope to level 0 members of the selected Entity and Cost Centre parents.  Here's how I would code my rule:

The "PlanningFunction.getUserVarValue" Planning Expression will resolve to the base members of  entity and cost centre that the user has selected at runtime.  You could also use other Planning Expressions to further your quest in avoiding any hard-coding in your rules.  Here are some other examples of Planning Expressions that you could possible use:

Periods

The following is a list of Period-related Planning Expressions:

I have personally not used any of the above Planning Expressions except for [[Period("FIRST_PERIOD")]] & [[Period("LAST_PERIOD")]].  Here's an example of how I've used them in my rule to control how I get the prior period value for an account.

Scenarios

Here's a list of Planning Expressions linked to Scenarios:

The last I used the above expressions, they require the Scenario Name to be a string (i.e. hard-coded) but the latest reference guide from Oracle shows that the expressions now accepts variables such as Runtime Prompts (RTP).  Try it out and let me know we can now use variables.  Would be awesome if this is true.

Here's an example code I've lifted from the Oracle's guide.  Looks like it accepts RTPs.

That's all folks for this blogpost.  Let me know if you find this post useful and any other feedback is also welcomed.  Thank you and see you in my next post.

To hard-code or not to hard-code?

I'm not a fan of hard-coding my business rules as I find that more often than not, those hard-coded beasts will turn around and bite you.  You'd struggle to remember which hard-coded rules need updating when new hierarchies are added to your planning application.  Sure, hard-coded scripts tend to be easier to read but I'd still steer away from having hard-coding where possible.

Enough said, let's get down to business... This post, I'd like to share with you a technique I often used to resolve an assumption that is set globally for a group of common parent members (e.g. Entities).  Normally, one would create an "NA" member to hold the global assumptions for these members as shown below:

I could write a rule that hard-codes to ANZGROUP_NA to resolve the global assumption like this:

Or, I could create a rule that will dynamically resolve to ANZGROUP_NA. 

The code will resolve to ANZGROUP_NA by first resolving to the ANZGROUP node using the @ANCEST("Entity",2) function and followed by concatenating the resulting member with a suffix of "_NA".  The @MEMBER will then convert the concatenated string to a member before used in the crossdim with "Annual_Salary".  The series of functions will result in the following crossdim:

"Monthly Salary" = "Annual_Salary"->"ANZGROUP_NA" / 12;

This of course, is a very simple example with the entity dimension having just 3 generations (Entity, Group and Individual Level 0 entities) and you might ask what's the big deal with hard-coding the rule to point to ANZGROUP_NA?  You're right, this isn't a big deal to have it hard-coded in this instance as the chances of you adding another NA member in this hierarchy is quite minimal.  However, it will be a different conversation if we had an entity structure like the one shown below:

Each of the country entities (i.e. AU and NZ) having their respective NA members to capture their country-specific assumptions.  The hard-coded rule needs to be updated to evaluate if the current entity being calculated is part of the AU or the NZ hierarchy and then apply the correct country-specific assumption to the rule.

Any subsequent addition to the entity dimension (i.e. adding of new countries), will require the rule to be amended to also consider the newly added countries.  Now, compare it with the non-hard coded rule.  You would update the code to use Generation 3 that will resolve to either "AU" or "NZ" before concatenating with the "_NA" suffix.

With just 3 lines of code, the rule will resolve to AU_NA for all entities under the AU hierarchy and to NZ_NA for all NZ entities.

With the above code, any subsequent addition of siblings to the AU or NZ nodes will NOT require you to amend your rules as it will work perfectly as long as you maintain the same hierarchy structure as we have for AU or NZ.

There! A short and sweet post.  Hope you found it useful and keep a look out for more tips and tricks on how to avoid hard-coding in your rules.  Till then, adios!

Getting your way around a Planning Application using Navigation Flow

I'm back!  In this blog I we will not discuss about SmartLists but another cool feature within PBCS.  Gone are the days when Tasklists are the only way to go to define the planning flow in the Planning application.  Don't get me wrong, Tasklists get the work done pretty well but lacks the visual appeal that one is used to now with the icon-based navigation in all our mobile gadgets.

Make way for "Navigation Flow" (affectionately known as Nav Flow, well... at least to me).

So, what's this Nav Flow and does it stack up to our dear old friend the Tasklists?  For one, it is visually more appealing and intuitive to end-users.

Instead of defining them as a list of tasks, the planning models can be grouped and represented by icons.  The screenshot above shows that the application has models for Revenue, Expenses, Workforce, Capital, and Income Statement, all with their distinct icons.  Going from left to right, one will be able to complete their planning activities in the sequence that is intended by the designer of this application.

Drilling into the Revenue model reveals individual revenue sub-models.

The vertical tabs on the left represent the different revenue sub-models such as Property Management Income, Property Sales Income, etc.  The tabs across the top of the form list forms/reports/dashboards within each sub-model.  What's cool about this set up is that a user can have direct access to all the models and sub-models easily instead of the sequential access via a task list.

Now you must be wondering how do I control access to the models as not all users should have access to all the models in the application.  This is pretty awesome, the access rights set in the individual artifact (i.e. forms, reports, dashboards, rules) are honoured by the Nav Flow.  What this means that a user having access to the Revenue and Workforce models will only see these 2 icons in addition to the system icons (i.e. User Variables, Reports, Rules, and Jobs).  Cool, huh?  To take it one step further, assuming that this user has access to only the Property Management Numbers form within the Property Management Income model, he will only get this form shown and the other 4 forms are hidden from him.  All the other revenue sub-models are also hidden from this user.

To make the application more self-service oriented, you can also embed instructions to each of the form attached to this Nav Flow.  Just click in the "Information" icon beside the form name to reveal the instructions.

Now, let's get on to the nitty-gritty details of how we set this up but before that, I'd like to give you some context about the navigation flow discussed in this blog.

The Application
Our discussion is based on an application that contains the following models:

1. Revenue
• Property Management
• Property Sales
• Ancillary Income
• Agent Plus Income
• Maintenance Income
2. Expenses
• Office Rentals
• Management Fees
3. Workforce
4. Capital
5. Income Statement

User Groups and Security Access
As mentioned earlier, the Nav Flow uses the access rights for each user to render the relevant icons that he or she has access to.  Here's what I've done:

• Create groups for Admins and All Users and the relevant built-in user roles to groups

• Create groups for each model and assign individual users to the relevant Functional User groups.  

• Go ahead to then assign the Functional Groups to the relevant Forms, Reports, Dashboards, and Rules.

Navigation Flow
Now it's time to define the Nav Flow and I've chosen to build one for Administrators and the other for the rest of the user community.  Since the Nav Flow honours the security access rights that we've established in all the artifacts, we should only need a single Nav Flow that contains all the models and relevant system icons.  However, I've decided against that simply because I'd like to hide the Rules icon from the end users and rely on the rules to run automatically each time a form is saved.

So, in this example I have 2 Nav Flows defined while the "Default" came with PBCS:

• 10_All_Admins
• 20_All_Users

You'd ask why I have named the Nav Flow with a numerical prefix?  Well, that to help me control which Nav Flow gets resolved first.  You see, the system will evaluate the Nav Flow in the order in which they appear in the Navigation Flow page to resolve to the correct Nav Flow that a user has access to.  So, by having them named with a numerical prefix, I am ensured that they get evaluated first before landing on the Default Nav Flow.

With the above set up, PBCS will first check if the user has access to the first Nav Flow and if he is an Administrator of this application, he will be shown the 10_All_Admins Nav Flow and will use the second Nav Flow (20_All_Users) if he is a planner.  In the rare event if a user wasn't assigned to either the ALL_Admins or the ALL_Users group, he will gain access to the Default Nav Flow.

Now, let's see what under the hood of 10_All_Admins Nav Flow.  I have the User Variables as the first icon so that I can guide the users to first ensure that they have the necessary Users Variables set up prior to accessing any of the models.  Subsequent 5 icons are all the models that exist in this application while the remaining icons are 3 other system icons that I wish to expose to the users (i.e. Reports, Rules (only for Admins), and Jobs.

I will now show you how the sub models are defined in the Nav Flow and we'll use the Revenue as the example.

The Revenue model has 5 sub models and they appear in the vertical tabs on the left of the form:

1. Property Management
2. Property Sales
3. Ancillary Income
4. Agent Plus
5. Maintenance

Clicking on "Property Management" will reveal the horizontal tab settings as shown below.  They will appear as tabs across the top of the form:

Each of the items in the horizontal tab is linked to either a Form, Report, or a Dashboard.  Clicking on the "Property Management Assumptions" item will reveal the definition for this item and in this case is a reference to the "Property Management Assumptions" form.  Don't be fooled by the grey-coloured field in Artifact.  It does not mean that the content cannot be edited.  Click on the search icon beside the Artifact field to associate an artifact to the item.

When you are done defining all models and sub-models, click "Save and Close" and then activate the Nav Flow.  Just so you know, the Nav Flow needs to be in an "Inactive" state for you to be able to modify it and it has to be in an "Active" state for users to be able to access it.  Click on the status to toggle between Active and Inactive state.

Another cool feature that I'd like to tell you about the Nav Flow is that the models you've defined in the Nav Flow can be re-used in another Nav Flow thus, eliminating the need to repeat you definitions and makes updating them much easier where you need to only make the updates in the main Nav Flow and they will be applied to all other flows that refer to the main one.  In my case, 10_All_Admins is the main Nav Flow while 20_All_Users is the secondary one that reuses the definitions that were done in 10_All_Admins.

Notice that the models are referring to 10_All_Admins.  To reuse a definition from another Nav Flow you would click on "Add Existing Card/Cluster" instead of "Add Card" and then choose a card or cluster from an existing Nav Flow.

That's it!  We're done.

What's missing from the Nav Flow?
Now that I have sang praises about this neat feature, I'd certainly like to see additional features added to the Nav Flow.  One main one that is missing from here is the ability to link a specific rule to a card within the Nav Flow.  That was the reason why I had to expose the Rules system icon but limit it to the Administrators only.

Hope you have enjoyed reading my blog post and hope to post the next one real soon.  Until then, sayonara till we meet again.

Hierarchy-Driven SmartLists: And then there were two....

Apa khabar (that's "how are you" in Malay in case you're wondering)?  Hope you've enjoyed reading my maiden blogpost (Reverse Mapping using Hierarchy-Driven SmartLists).  Keeping to the theme, thought I'd share another use of a Hierarchy-Driver Smartlist; this time in an allocation process.

More often than not, you'll find yourself building some sort of an allocation logic for your planning application and as you know, there's more than one way to skin a cat.  My latest go-to method is using @XWRITE in conjunction with a Hierarchy-Driven Smartlist.  Not only does it help in addressing the ever-annoying block creation issue (we had our fair share of rants on this matter don't we, Pete?), using a SmartList provides the ability to avoid any kind of hard-coding in the business rules.

Interested?  Read on...

Background

A client of mine has this 4-tiered allocation process that they want implemented in their PBCS application.  Our solution uses a mapping table established to define the relationships between the following sources and targets:

• Entity
• Profit Centre
• Function Code
• Account
• Allocation %

The UV_Assessment_Cycle (in the POV) allows the client to maintain multiple cycles (or tiers) of allocation while the rows provides the ability to have multiple lines of allocation per cycle.  For instance, L001 defines a 100% allocation of cost

• From
• Entity: 4650
• Profit Centre: RAM RNY
• Function Code: 590 - COMPUTER SYSTEMS
• Account: OH2
• To
• Entity: 4550
• Profit Centre: MGR RAM
• Function Code: 590 - COMPUTER SYSTEMS
• Account: Overheads Pooling

Having this mapping table frees us from having to hard-code our allocation business rules.

The SmartLists
Define a hierarchy-driven smartlist for each of the following:

• Sender/Receiver Entity

• Sender/Receiver Profit Centre

• Sender/Receiver Function Code

• Sender Account

• Receiver Account

Accounts / Measures
Create the following accounts or measures and attach the above smartlists:

• Sender_Entity - SL_Ent
• Sender_Account - Cycle_Sender_Acc
• Sender_Profit_Centre - SL_PC
• Sender_Function_Code - SL_FC
• Receiver_Entity - SL_Ent
• Receiver_Account - Cycle_Receiver_Acc
• Receiver_Profit_Centre - SL_PC
• Receiver_Function_Code - SL_FC

Business Rule

The business rule will cycle through all the Assessment Cycles and Assessment Cycle Lines to the post a negative entry to the sender Entity/Profit Centre/Function Code/Account combinations and posts a positive entry to the receiver combinations.  A sample of the results is illustrated in the table below:

This is the full allocation code:

Say what?  Okay, okay.  Let's examine the section where we used the Hierarchy-Driven Smartists to first create the required block and then post the allocated numbers.  We'll start with the posting to the source combinations:

Notice that the rule uses the XWRITE function to post the value from the Sender Entity, Profit Centre, Function Code, and SenderAccount combination (section 1) to the Sender Entity, Profit Centre, Function Code, and Receiver Account combination (section 2).  What's cool about this is that the XWRITE will first create the block for the Sender Entity, Profit Centre, Function Code, and Receiver Account combination if it does not exist before the posting happens.

Also note that we have used the method of concatenating "HSP_ID_" with the value from the Hierarchy-Driven SmartList to resolve to the intended member. 

Here's the code snippet for the receiving entry and with similar method used when pushing the Sender Entry:

The last part of the rule applies the allocation ratio to both the sender and receiver entries and flips the sign for the sender entry:

Recap
This solution allows users to define their allocation mapping without having to rely on a hard-coded business rule (all thanks to the Hierarchy-Driven Smartlists).  The rule will cycle through 2 dimensions (i.e. the Assessment Cycle and the Assessment Cycle lines) when allocating costs between sender and receiver Entity, Profit Centre, Function Code, and Account combinations.  An example of the allocation result is illustrated in the table below:

Hope you have enjoyed this blog and stay tuned for my next blogpost.  Till then, terima kasih!