Matt HawkinsPerfecting the art of allocations is much like successfully splitting the check when friends dine together (Remember those days?) Read on to see why.
When a business reports or plans profitability of its goods or services, it is vital to account for all aspects of costs associated to their production. This helps determine the value of certain programs and invest more in those that drive increased profits and reassign or react to investments that are under-performing. In this blog, I will review the process of cost allocations and the value of including this process as part of an organization’s financial analysis. I’ll demonstrate how SAP Analytics Cloud’s built-in structured allocation feature streamlines this process for a financial analyst.
What are cost allocations?
Let’s start with a personal example. Unfortunately, it has been a long time since I last enjoyed a large group dinner with friends at a restaurant. However, when we did go out as a group, we would typically split the bill – it’s just easier (for the diners and the wait staff). Total bill divided by number of credit cards. Is it fair? Sometimes yes, but usually no! However, it is not practical to go through the check line by line to assign specific costs to each diner, and what about that bottle of wine that was split unevenly?!
By taking the total cost and assigning an equal weight to each subset of the population (each couple, or family, for example) we performed a very simplistic allocation of costs.
Why are cost allocations important?
In the corporate world, the above scenario is complicated by thousands of variables. If a company wanted to better understand the margins of specific products or brands for example, we could take certain indirect costs and simply divide them by the number of products. This would assign the costs but, similar to the restaurant example, would not be a good indicator of “fairness” as a small product category would incur the same burden as a large one. Fortunately, there are a number of approaches to assign a more representative distribution of costs, based on a variety of methods.
For actual costs, these allocations may be performed within the ERP system, however not all scenarios may be accounted for. Or, they could be a requirement to model potential alternative scenarios. For plan/forecasts, it is common to enter these at a total level. Using a tool like Excel requires a large amount of source data to be loaded and resource intensive calculations to execute, neither of which are a good recipe for success.
Drivers
A key decision when creating allocation rules is to determine what “drives” the allocation. In the restaurant example, it is simply “total cost” — but to extend the analogy, we could have an allocation driver for entrees and another for alcohol, where the designated driver is not assigned any of the costs for alcohol. In the corporate world, these drivers could be revenue, subscribers, headcount, square footage of retail space or any combination of mixed drivers. These permutations make performing the allocation process via Excel impractical due to the number of drivers needed to fully allocate even a single account.
Fortunately, SAP Analytics Cloud (SAC) provides an effective way to create and manage these allocations. Any user simply selects the context of the allocation they would like to perform within the report they are viewing.
Example
Let’s take a closer look at a mutli-step allocation. In the context of profitability analysis, to determine the appropriate margin at a customer/product level, we need to assign a proportion of these costs to each customer and then assign each product the customer purchased a percentage of that cost, at the same time reducing the cost from the source. Remember, we have not generated any additional costs, just reassigned a portion to the appropriate profit center; therefore, we need to balance the equation by unloading the source. For example:
Company X spends $50,000 per month on advertising their line of organic health bars, which are sold exclusively to three health food stores for a total revenue figure of $400,000. We want to assign this cost proportionally to the store/product level.
Step 1: We allocate 100% of our $50,000 advertising expenses to our three stores, weighted by revenue.
Step 2: We take step one amounts and allocate to just two flavors of health bars.
Therefore, “Chocolate Healthy Bar” is assigned 60% of the $31,250, or $18,750. To calculate the profit margin on selling Chocolate Healthy Bars to Store A, we need to account for the $18,750 as related to advertising cost.
Of course, this is just one simple expense line for illustration. To perform this allocation across many expense lines, for potentially thousands of customers and tens of thousands of products, is a significant undertaking that a tool like Excel simply cannot manage. We could write these rules into a database and have it crunch the numbers, but that would require a significant investment in time and effort to ensure data consistency and, without even more engineering, becomes an inflexible and expensive to maintain solution.
How it works in SAP Analytics Cloud
SAP Analytics Cloud takes a novel approach to this financial function by providing a built-in allocation rule engine that is flexible, scalable and user driven. By pushing the number crunching to the underlying HANA cloud database, these complex scenarios can be executed in minutes by any financial analyst. The results are saved to private view and audited for clarity of cost movements over time — all without writing a single line of code.
We provide three inputs to the allocation rule:
- The source – Where is the aggregate value taken from?
- The driver – The denominator for the allocation
- The target – To where are we directing the allocation? (e.g. All products in the health sales channel
By creating multiple modular steps, we can build sophisticated allocation rules by simply loading these steps into a new rule. End users can selectively run these allocations — either as a whole or based on their current viewpoint in a report. This affords maximum flexibility to analyze multiple business scenarios.
This feature also allows forecasters to only enter values at a high-level and let the system calculate the spreading of these costs, saving huge amounts of time and effort, while providing a more detailed and realistic projection of future margin.
This is just one example of how SAP Analytics Cloud makes the role of a financial analyst easier, saving time — the most valuable asset during financial close and forecasting periods. SAC also eliminates error-prone manual calculations by leveraging the cloud to scale up the compute-intensive task of multi-dimensional allocations, while providing a consistent, secure user experience across the organization.
If you would like to learn more about how SAP Analytics Cloud can optimize the FP&A function, please contact me directly.
To learn more about our SAP capabilities, contact us or visit Protiviti’s SAP consulting services.
John Jones
Nathan Truhan
Don Loden
