Pricing analysis to identify the cause of margin leakage

Context

Our client was a US-based manufacturing company that supplies to the commercial construction industry, with annual revenue of around $120M. Its European subsidiary, based in Germany, had been unprofitable for three years despite growing sales. The management team wanted to fix this situation as its European subsidiary used to contribute a significant amount of EBITDA to the overall business.

The US executives believed that pricing was the key driver of the issue, but the local team pushed back against this view, because their prices were all set by the pricing calculator module in the ERP and they adhered to these prices (discounts were used but these were also determined by the calculator).

The US team wanted objective evidence to support or disprove their hypothesis, but the analysis required was not straightforward for several reasons: 1) since the products are all made to order, the concept of SKU does not exist so no standard pricing could be used for easy benchmarking; 2) finance does not track project-level profitability; 3) the pricing calculator is bit of a black box, custom coded as a module in the local ERP.

Objectives

The leadership team in the US asked us to investigate how pricing was done in Europe, and figure out a way to assess if it was indeed a major driver of the unprofitability issue there; and if it was, suggest fixes.

Project delivery

We started with multiple exploratory sessions with both the US and European teams to understand exactly how their pricing worked. Because the client's products are all custom-built to the exact specs of each project, pricing is different every time. The pricing calculator takes as inputs the product line, dimensions of the construction site, finishing materials, special components required, and so on, to calculate the COGS of the project then adds a margin on top.

Given that the local team followed the calculator prices, we suspected that the calculator likely underestimated some cost components. So our approach was to compare each cost component from the calculator, against the actual amount. Then we could identify if and where the calculator underestimated the costs.

We discovered as part of this exploration that the pricing calculator stored all historical quotes in the ERP's database, containing all cost components such as direct material, direct labor, freight, installation, commission, etc., but scattered across multiple tables. We leveraged the data warehouse that we had set up for this client (see this case study) and extracted three years of historical data out of the ERP, then wrote a SQL algorithm to join all those tables together using multiple layers of logic as there was no single key to link them all together. The output of this was a table with one row for each project, and a field for each cost component as well as the margin.

Finally, since individual projects' actual profitabilities were not tracked, we conducted our analysis at the annual level. For each cost component, we compared its total annual amount in the calculator, against its total annual amount in the general ledger (we worked with the local finance team to map the general ledger accounts to the same groupings that were used in the calculator, i.e. direct material, direct labor, etc.). Once these steps were completed, we were able to see exactly how the calculator costs compared to the actuals.

Outcome

After we completed the steps of our methodology, we found that the calculator had a gross margin that was 8% higher than the actual gross margin, driven by the underestimation of direct labor cost and direct material cost. After we benchmarked these two cost components in Europe against other subsidiaries, we found that the direct labor cost had been underestimated for four years, and direct material cost had been underestimated for two years.

When we brought these findings to the local team, we learned that while they updated the hourly wages in the pricing calculator every year, the last time they performed a time study in their factory was around 7 years ago, likely leading to outdated standards in the calculator for direct labor. The underestimation of direct material costs also turned out to be driven by a process breakdown due to employee churn, and the last time material costs were updated into the calculator was two years ago.

With these insights, we recommended making some immediate stop-gap adjustments to the pricing calculator so that new quotes would have higher margins; then fixing the root causes of the issues by performing another time study in the factory, and re-establishing the material costs update process. These were turned into management action items.

We worked with this client for many additional projects after this so we were kept in the loop on its European business. By the end of the following fiscal year (bookings typically stay in the backlog for 6-9 months so annual margin impact would only fully show up after 18-21 months), the P&L gross margin of this subsidiary had increased by 7.8%. It became profitable and stayed so since, generating €900K in EBITDA for the business every year.