Tips and tricks for improving the efficiency of Driver Based Planning models while reducing development and maintenance costs.
Driver based financial planning applications can be efficient tools for developing rational corporate budgets. Budget analysts can focus their time and energy on planning and estimating the key business drivers and understanding the relationships of these drivers to various aspects of the business. The emphasis is less on the total aggregate expense and more on the drivers and individual costs associated with each driver .
Driver Based Example
For example, instead of focusing on a total Employee Benefits budget of $1,000,000, we change the conversation to a benefit cost of $1,000 per employee for our 1000 employees. Now, in a standard planning model, if we increase the number of employees throughout the year due to anticipated growth, we will have to go back and update our Employee Benefits budget accordingly. In a driver based model, the Employee Benefit expense is automatically recalculated and is always rationally tied to our headcount.
However the benefits of driver based planning don’t come without some cost to the complexity of our planning application. Business rules must be written and maintained to perform all of the driver based math such as "Employee Benefits" = "Headcount" * "Employee Benefit Cost per Employee". Depending on the number of financial accounts and the nature of the business, these calculations can run from dozens to hundreds; or put another way, dozens to thousands of rows of “code”.
Take, for example, the potential expenses driven off of “headcount”. Here is a short list, but many companies have dozens of expenses that can be driven off of headcount.
Salary
Benefits
Desktop Software
Phone
Office Supplies
Since these accounts will be driver based, we also need an account to hold the rate associated with the given expense:
Avg Salary per HC
Avg Benefits per HC
Avg Desktop Software per HC
Avg Phone per HC
Avg Office Supplies per HC
Notice that we have doubled the number of Accounts needed (one for the expense dollars and one for the rate).
Our overly simplified business rule will look something like this:
FIX( dimension list )
"Salary" = "Avg Salary per HC" * "Headcount";
"Benefits" = "Avg Benefits per HC" * "Headcount";
"Desktop Software" = "Avg Desktop Software per HC" * "Headcount";
"Phone" = "Avg Phone per HC" * "Headcount";
"Office Supplies" = "Avg Office Supplies per HC" * "Headcount";
ENDFIX
If we were to add an expense Account later, we would need to add two members to our outline and then modify the business rule to include the math for the new Account. Now, in this overly simple example, this change process doesn’t appear to be too painful. However, in larger enterprises, the Account dimension is often managed by a data governance process and a maintained within a Master Data Management tool like DRM or EPMware. Further, these organizations may have strict software development lifecycle standards that make even a simple business rule change time consuming and costly.
By taking a different approach, we can create a dynamic and flexible driver base model.
By adding a utility dimension to our application, we can simplify the driver based math. Consider the dimension "Account Info":
With this Dimension, we can now make our Driver based model metadata driven rather than hard coded and Planning SmartLists help make this meta data readable and easy to follow:
Our business rule changes to:
FIX( dimension list )
IF("Plan Method" = 2) /* Smart List value for Driver */
"Base Value" = "Driver Value" * "Rate";
ENDIF
ENDFIX
Now, no matter how many Accounts we have—or add in the future—this business rule remains the same and never needs to change. We no longer have to add a separate Account to hold the rate used in the driver formula. What we do have to do is load the text list value (0, 1, 2) into the "Plan Method" intersection and then this math just works. Simple as that. Well, maybe not quite as simple as that. We still have a little bit of setup to do; but you should be able to see the benefit of this approach by now.
Our Account Info dimension needs a few formulas to help keep our code simple and dynamic.
We’ve added Account Info members to store the account member information in one precise intersection of all dimensions ("Jan"->"No Organization"->"No Geography"->"No Product"). The only dimension that varies is Account. Our original Plan Method, Driver and Rate Type have become dynamic formulas that use the cross dimensional method to retrieve the stored value no matter what intersection we find ourselves on.
We’ve also added a formula to the Driver Value member. This is the only place where things are hard coded. Notice, here, that we have used the cross-dimensional reference to the top of the Product dimension as Product level detail is not relevant to our expense calculations. We hard-coded references to all the potential drivers from our Drivers SmartList. With a little more advanced programming, we can even make this formula dynamic as well; but that is a topic for another blog.
Finally, it is important to note that we are now storing the results of the calculations in the "Base Value" member in the Account Info dimension.
We have kept this usage of the Account Info dimension simple for this example; however, this dimension can be used to support other enhancements such as Trending, adjustments to driver based or trended values or adjustments to the rates used in driver based calculations. For example:
Back to our simple example. Now that we have our Dynamic Driver based application setup, it’s time to see how a user form would be setup:
Comentarios