How to: build an SKU Profitability model

November 10, 2016 3:28 pm
View All Posts

Did this morning’s production run actually make any money?

Do you know which products in your business are actually making money? Do you know what factors could make these products more profitable, or conversely, stop them from being profitable? Many companies rely too heavily on their standard costings and do not have visibility to the true operational margins of their products.

During a recent analysis for a client, we found that over 60% of the company’s SKU’s were not at their target profit margins, and 20% were in fact, unprofitable. With operating environments in a state of constant flux, it is important to invest time and resources to build a tool that can present an understanding of true SKU performance, herein referred to as an SKU profitability model.

An SKU profitability model is one of the most powerful tools for your business. It provides:

  • A leading indication of the margin performance for each SKU and SKU group
  • Ability to cost new products and enable decisions prior to introduction
  • Information as to which products to sell or promote
  • Focus areas for operational improvement projects
  • Visibility to operational performance against costed standards
  • Understanding of which SKU’s should be rationalised in your portfolio

Fortunately, building a SKU profitability model does not have to be a complicated exercise. It does not require investment into sophisticated software. All of the information you need is sitting within your business.

Building the Model

Let’s assume you are in the cheese manufacturing business, Say Cheese Enterprises. One of your key customers, Cheese Master grocer, has suggested you cut the sales price of Camembert cases down by $1.00. You know that your company’s target profit margin for each SKU needs to be at least 20%, and the standard costing currently gives you a margin of 22%. However, a senior operator on the Camembert line retired several months ago, and the line has been running slower than before and producing more waste than usual. There are still eight months to go before your business refreshes costed standards, and your customer wants a response within the next fortnight. You decide to build an SKU profitability model to help you answer the question.

Assuming this model will eventually contain every SKU which is produced at Say Cheese Enterprises, think about how you would like to see the results. Do you want to see the margin by gram, unit, or case? Do you need visibility to profit by customer, brand, and grouping? Do you need to see margin by line and room? Be sure to collect and include as much basic information about each SKU so that you can view profitability in a way that makes sense for your business.

Line 1 currently produces the Camembert SKU in question. This SKU is 100g per pack and is sold as a private label to Cheese Masters grocer in cases which hold 10 packs. The primary information for your model is set up as follows:

Product Code Product Description Line Label Customer Pack Weight (g) Case Weight (g)
CAM1 Camembert Line 1 Private Cheese Masters 100 1,000


Now it’s time to add in the costs required to make our Camembert SKU. We will need to look at the following elements to be able to build the model:

  1. Sales Price
  2. Direct Material Costs
  3. Direct Labour Costs
  4. Overhead Costs

Sales Price

Say Cheese Enterprises currently sells cases of Camembert for $50.00 to Cheese Masters grocer, which includes a 5% trade discount. Let’s add this information into the model+:

Product Code Sales Price ($/case) Trade Discount Actual Sales Price ($/case)
CAM1 $50.00 5% $47.50

+all columns except product code truncated from previous table for simplicity

It is important to include any customer discounts in the model, as these ultimately affect your company profit margins.

Direct Material Costs

Direct materials are raw materials that are directly traceable to your product. Remember, we are not building standard costings and must use shop floor data to understand actual operations when constructing this model. Operational data that affects direct material costs includes waste and giveaway.

The total cost of raw materials in our Camembert SKU Bill of Materials (BOM) is costed at $1.65. This includes ingredients, packaging, and a 1% waste allowance. At Say Cheese Enterprises, the operations team measures and records waste and giveaway on a daily basis, which has averaged at 5% and 2% in recent months. Our direct material costs are added to the model+ as follows:

Product Code Standard Material Cost ($/unit) Standard Material Cost ($/case) Waste (%) Waste ($/Case) Giveaway (%) Giveaway ($/Case) Total Direct Material Cost ($/Case)
CAM1 $1.65 $16.5 4% $0.66 2% $0.33 $17.49

+all columns except product code truncated from previous table for simplicity

As the BOM includes a 1% waste allowance at standard costing, the shop floor waste data is stepped down by a percentage. All parameters are converted to $/case to determine a total direct material cost.

Direct Labour Costs

Direct labour is the amount of time taken by those employees in the production area to produce a SKU. The BOM will have a standard labour rate that outlines the cost for the amount of time it should take to make a certain product. The shop floor will have data about the amount of time it actually takes to make that product. Both of these need to be factored into our profitability mode.

The direct labour cost in our Camembert SKU BOM is $1.12 at a standard speed of 10 cases per hour, which is the fastest the line can operate without significant capital upgrades. The standard crew on Line 1 is 5 employees who all get paid $22.40 an hour. As we know, this line recently lost a senior operator and has not been running as efficiently as before, with shop floor measurements averaging 8 cases per hour. Our direct labour costs are added to the model+ as follows:

Product Code Standard Labour Cost ($/unit) Standard Labour Cost ($/case) Standard Rate (cases / hr) Standard Crew Standard Rate ($/hr) Actual Rate (cases / hr) Total Direct Labour Cost ($/Case)
CAM1 $1.12 $11.20 10 5 $22.40 8 $14.00

+all columns except product code truncated from previous table for simplicity

The total direct labour cost was calculated by determining the standard hourly cost of Line 1 (standard crew x standard rate), and converting this to $/case, based on actual rate as measured by the shop floor. In this example, there is a labour inefficiency cost of $2.80 that is in addition to what is specified in the standard costings.

Overhead Costs

Overhead costs are other costs within the manufacturing facility that are not considered direct material or labour costs. They are broken into fixed and variable: fixed costs are paid regardless of factory output (i.e. rent), whereas variable costs fluctuate depending on the amount of product output (i.e. electricity). Filtering these costs down to an SKU level requires an allocation method that is representative of the overhead in question. This exercise is complex and requires the input of several key departments to ensure its accuracy.

As an example, when building the model at Say Cheese Enterprises, we can allocate the fixed overhead cost of rent to the percentage of floor space the Camembert line occupies within the factory. The variable overhead cost of electricity is assigned to the number of hours the Camembert SKU is run on Line 1.

A similar methodology will need to be completed for all of your business’ overhead costs. For simplicity in our example, we will assume our Camembert SKU has the following total fixed and variable overhead costs+:

Product Code Total Fixed Overhead Costs ($/case) Total Variable Overhead Costs ($/Case)
CAM1 $8.00 $3.00

+all columns except product code truncated from previous table for simplicity

Analysing the Results

Now that we have all of the required inputs, we can determine what the total cost of our Camembert SKU is and thus, its profitability margin+:

Product Code Actual Sales Price ($/case) Total Cost ($/case) Profit ($/case) Profit Margin
CAM1 $47.50 $42.49 $5.01 10.6%

+all columns except product code truncated from previous table for simplicity

The total cost of each case of Camembert was determined by summing the aforementioned total direct material, direct labour, and overhead costs. The profit margin is a percentage from total profit over actual sales price.

By building the SKU profitability model we have found that our Camembert SKU makes significantly less than Say Cheese Enterprises’ target profit margin and the sales price per case should not be reduced by $1.00.

On the back of your findings, you advise the Managing Director of the SKU’s lack of profitability, to which they ask a series of questions such as What happens if run time is improved on Line 1? and What happens if the giveaway of this SKU is reduced? These are all very good questions, and now we have a tool to be able to model these scenarios!

The above example illustrates a brief overview of the steps required to build an SKU profitability model for a single SKU in the perfect circumstances. But in reality, the data which is required can be difficult to find, or is often not in a format which is easy to interpret. Allocating overheads can become a convoluted exercise, shopfloor studies need to be completed and documented, and educated assumptions must be made.

Next Steps

It is imperative that you do not use your SKU profitability model in isolation. The tool should act as an instrument to supplement your company’s overarching SKU management program. After building the model, some immediate next steps will be to:

  • Reconcile collected information with historical data to ensure accuracy in approach when constructing the model
  • Measure key operational parameters on a daily basis (exc. giveaway) if these are not currently measured
  • Define a process for feeding daily operational information into the model
  • Review negative and low margin SKU’s with a multi-disciplinary team consisting of colleagues from divisions such as finance, operations, sales and marketing in order to establish a logical path forward
  • Define SKU rationalisation requirements

Determining the profitability of your SKU’s is essential to ensuring your business remains competitive. Building this model will not only help you assess the margin of every SKU in your business, it will set the foundation for your company SKU management program.

Written by Karen Starr, Coriolis Consulting Pty Ltd


Standard Costing Explanation (