By TopDown Team
January 12th, 2016
In this day in age it it’s always about the bottom line. Am I right? Everyone wants to know the how profitability of their products or stores, but specifically they want to make sure that the profitability is fully burdened. A fully burdened P&L by product or store will allow an organization with decision making by analyzing pricing, promotions, discontinuation, process changes, or expense control. In an ideal world fully burdening or allocating expenses to a product or store would not be necessary, but since we’re not in a utopia we have to ask why do we have to allocate in the first place and the different methods of allocating.
Why the need to allocate?
Some expenses cannot be easily associated to a product or store like nonmanufacturing overhead expenses. In other instances some expenses are not linked to a product or store due to data entry errors. With these expenses not associated to a product or store management cannot have a complete view of profitability.
How to begin the allocation discussion?
Below are 5 key drivers to profitability that will give you the ability to see how profitable each store or product truly is, allowing you to make the decision to discontinue the sale of that particular product or finding a solution to make it more profitable.
5 Ways to Achieve Success:
- Identify correct dimensionality for profitability. Are you looking for profitability by product, region, brands, stores, etc. Establishing the different way you would like to view profitability will be important in terms of analysis as well as volume of data which may have some technical issues.
- Establish a “DataType” dimension. This would be beneficial in order to have an audit trail of the original state of the data loaded from a source system, as well as adjustments and allocations.
- Associate appropriate drivers to each expense line that needs allocations. Some drivers might be gross sales, cost of goods sold, sell through, net sales at retail, headcount, machine hours, etc.
- List dependencies of different allocations and set order of operations for the allocation process. For example allocating channel data first before allocating store level detail of store expenses.
- Determine types of mediums for reporting profitability, for example standard financial reports, Excel or dashboards
Here is an example of an implementation of a total Hyperion EPM solution at a global cosmetic manufacturer and retailer company. The EPM solution consists of a global budgeting and forecasting system, financial consolidation system, an Oracle data mart with a fully integrated ETL process using ODI, and a financial and management reporting system which includes profitability by store or POS. During the implementation of the profitability database we ran into issues in trying to accomplish point number 4.
What we found during the implementation was that certain dimensions needed to be allocated out first before we could allocate down to a store. We needed to allocate expenses first, that were coded to no channel. Then we allocated to a store since a sizable amount of data was loaded to no channel. Not allocating the data from no channel would not yield the correct driver data like sales, COGS, or salary expense. Once the data was allocated out from a no channel member, based off of the post allocated values the drivers needed to be reestabilished. This was across the board for each of the brands in the company. The key was to allocate out the data from the no brand member to get the true driver data.
Another issue around dependencies occurred when dealing with a clustered market. Benelux is a combination of Belgium, Netherlands, and Luxemburg and allocations needed to take place at each individual country. We found that the driver that was used needed to be at the consolidated Benelux level. This required a driver to be established at the parent level. Using the Benelux driver was important so that the expenses can be allocated based off of a driver that looked at all countries that belonged to Benelux and would result into allocating expenses to all stores within Benelux treating it as a true clustered market.
At the end of the implementation, the requirement of having store profitability was met by executing the 5 steps above. Of course not all implementations go smoothly, but resolving the issues around the order of operations of allocations like the no channel or no brand or establishing the correct driver for a clustered market allowed the client to be successful. In this particular instance the client was able to oversee national advertising expenses for a new launch of a product and see how it impacted the overall profitability of a store, channel, brand or entity.