- March 9, 2015
- Posted by: platformspecialists
- Category: Budgeting & Planning, Hyperion Planning
Should we ask our salespeople to sell more of Product X or Product Y?
Based on margin, should we push more of Service A or Service B?
Are our West Coast service lines more profitable than the service lines we offer on the East Coast?
These seem like simple questions, but getting to a fairly accurate answer isn’t always easy. Most companies with an interest in measuring financial performance at this level turn to an EPM (Enterprise Performance Management) tool like Oracle Hyperion Planning to help derive answers. Product profitability is important for almost all businesses to understand, and having been through various client EPM initiatives with this goal in mind, we tend to see three core considerations at play:
- Grouping and categorizing what a company is selling (products or services) in various “business views” is important to help improve insight
- Historical product sales and cost trends can provide a solid basis for forecasting future period gross margin data
- Expense data (particularly SG&A) is rarely directly attributable to a product or service level of detail; educated decisions need to be made on how
to fairly allocate this data to derive full product P&Ls
Now let’s dive a bit deeper into each consideration
Categorize and group products in innovative ways
for better analysis
At the core of most EPM systems (like Oracle Hyperion) is the concept of dimensionality. A dimension is basically a
hierarchy that allows you to categorize and summarize information. The
way a Product dimension is structured in a Hyperion forecasting application really drives the level of decision making power it can yield.
Most companies start with their “standard” view
of product categorization. For companies that sell physical
products, it’s typically a product family hierarchy. For
companies that sell service type offerings, individual services are usually
grouped by service lines.
(In our simple example below, we show individual Products and Services, but it’s certainly possible to analyze at more granular levels, such as Product SKUs.)
This is a great start, but we often push our
clients to think about alternate ways to look at their products. Do
you have products that are primarily targeted to specific
demographics or specific geographic regions? Are your products generally
sold in varying retail outlets (brick and mortar, web based,
etc.)? Adding additional views of product categorizations can provide some valuable insight when you’re able to see the profitability of
each on paper. In Hyperion Planning we can handle these alternate views
with alternative hierarchies or product attributes. We’re analyzing the same Products, just in new ways. This may ultimately help to show us that we should push more products to our E-Commerce channels if they are most profitable, for instance.
Product level Gross Margin data – historical
data is a great starting point
Once products are grouped in a way that helps set
the stage for value added analysis, we need to actually plan and track
financials for them. When talking about financial statements, there’s no
better place to start than the one everyone loves best – the P&L.
(Sorry balance sheet, you were a close second)
In a really basic P&L structure, we have
a Net Sales account section that is generally directly tracked or
associated with Product or Service information. Sales and COGS accounts,
for instance, are likely tracked by SKU or some sort of Product
or Service Tag in a GL system. In Hyperion Planning, we can use
this historical information, along with some assumptions on how
price, units, or COGS are expected to change in the future to derive
future Gross Margin and Gross Profit product data. This process for forecasting
future gross margin based on historical data can range from
simple to ridiculously complex. For our example though, we’re going to
keep it simple. We’re applying a Unit Growth Rate
and an Average Product Selling Price to calculate Gross Sales. Then we’re using Material Cost as a Percent of Sales to derive COGS.
Allocating Expense Data to get a full P&L
Nice! So far we grouped our products
in various ways to help drive insight, we planned next year’s gross
margin data based on our supremely accurate and never restated historical data,
and now we’re almost to the illusive Net Profit by Product.
In order to get down to Net Profit by Product,
we’ll need to work on forecasting our P&L expense
section. Expenses – what do we know about them? Management always
wants us to cut them, that’s for sure. Aside from
that basic truth, we also know that expenses aren’t typically tracked by
product. For example, we rarely associate a Corporate Accounting Manager’s salary with Product A or Product C. This is where
systematic allocations come into play. Usually after a series of design
sessions and requirements meetings, those familiar with operations can help to
provide a fair allocation methodology to round out our full Product
P&Ls. A few example approaches for expense to
product allocation logic are pictured below.
(Our “No Product” column above represents a pre-allocated expense. We then take that expense and allocate it to Products based on varying methodologies.)
And now, with
our product structures setup, gross margin data forecasted,
and future expenses allocated, we can reap the fruits
of our labor. Some really sweet reports!
Based on how we set up our product hierarchies, we are able to run a P&L at the product level, as well report profitability for our alternate product groupings, as shown below.
There are countless other considerations at
play when understanding product profitability (including the best way to
forecast expenses before they even get allocated to a product), but we hope
this post gave everyone a little foundational insight on how to understand
key considerations in the mix. We hope to see you soon…