By Mike Arnoldy
August 27th, 2015
Most people view HFM as just a financial consolidation tool (and it is an excellent tool for combining data from disparate sources and then consolidating it to produce financial statements), putting HFM into this “consolidation tool-only box” and never think about how to use its capabilities to solve other reporting challenges.
Well, guess what?
You can easily and effectively use HFM’s capabilities and features for other purposes, and in some cases, it is a better solution than other tools.
The application in question was for an insurance company and the challenge we faced was the calculation of commissions for insurance agents. This was an annual manual company effort where four to five people spent several months each year reviewing the data and manually calculate the commission due to the agents.
I admit that the first time I was presented with using HFM for something other than financial consolidations I was skeptical. This was new territory, so I didn’t reject the idea outright, but I did approach it cautiously. But as the discussion about the business problem and possible ways to solve it in HFM progressed, my interest grew.
We solved the problem by building an HFM application to perform the calculations. The application has Insurance Agents set up in the entity dimension organized into both Geographical and Sales Regions hierarchies. The account dimension had accounts representing data points such as premiums, claims, losses, expenses and various rates. The custom dimensions include additional details for the accounts such as thresholds for profit levels and payment rates. The application is loaded with a data file that contains the appropriate data for premiums and losses. When the consolidation is run, it is less about aggregating data than about running the rules for each agent to calculate the amount of their commissions based upon various criteria. The final step was that HFM created a file with the payment information that was loaded into their payable system to generate the checks.
Why was this application a good fit for HFM as opposed to Essbase, which they also owned? The answer is because this application really benefitted from the how dynamic and flexible HFM rules are. The member labels in the dimensions were designed so that they can be referenced in the rules. For example, Yearly Gross Profit level is used in the commission calculation with different rates applicable to different profit levels. The member names represent the various thresholds such as 10000, 20000, 30000, etc. These member labels can then be used in the rules to determine which rate to apply based upon the gross profit. If the thresholds change, the members only have to be updated with no changes to the rules. This concept was used throughout the application design to make the application dynamic and easy to change as the compensation structure for the agents changed.
The second feature of HFM that made this a good fit was the ability to use journals. For various reasons, the calculated amount of the commissions might be overridden, usually a result of a meeting between the agent and the CEO. Using journals to do the override provided the necessary audit trail the auditors were looking for.
I have been fortunate to be challenged by various clients to use HFM in some non-traditional ways. It encouraged me to change and expand my views about what HFM can do. The “box” I work in is certainly larger now and hopefully this will expand your views about what HFM can do.