Identifying Low Margin Products

Before you head over to see the data in the Pricing Waterfall, you first need to identify products where you have low margins. So let's head over to the Analytics menu and select a Scatter plot chart.

Select the Data Source, that contains product and transaction data and plot the Invoice Price on the X-Axis and Margin on the Y-Axis and Aggregate by Product ID to give us a view of all the products. Click Apply and Refresh.

And now you can see all the products and if you hover over them individually you will see all the product details, the margin, among other information.

But what you may want to see, is the Margin rather than as a sum, you may prefer as a percentage. Let's apply and refresh.

And you can see they are now more widely dispersed. You can now see a group of products that all sit at a margin of under 10%.

Now you may know that certain product groups attract similar margins, so let's band this by Product Group to see whether you have any that appear out of sync with others in their product group.

You do so and click apply and refresh and you can now see them banded by color. So now you can see that there are some outliers that you may want to focus on which are achieving less than a 10% Gross Margin that you're looking at.

The Blue Data Points, for the product group, Electrical Protection Control are banded relatively close together within a range from 7 to 12%, the dark green is fairly consistent between 8 and 11% but then you see the pink data points which refer to the Home Automation product group, and at the bottom, you can see, starting at 5% and working your way up, you can see right at the top here there's 21.59% so there is quite a difference between 5.36% and the 21.59%.

So let's focus on the Home Automation Category for this example.

In order to create the Waterfall chart, you'll need the Product ID's and the simplest way to get these in a way you can utilize them easily is to head over to Scatter Data, and if you do that you have all the data for all the products that appear in the chart. Search for "Home" in the Product Group column as a filter:

Now you're presented with only those products within the Home Automation Product Group.

You can sort the list in ascending order to see the information sorted by percentage of Gross Margin.

Excellent, so that gives you the information that you need. When you create the Waterfall chart you're going to need the Product ID to create a Waterfall per product, so you can now do a few things. 

Identifying Reasons for Margin Leakage

You could export the product data to Excel which means it's relatively easy for you to copy and paste, but let’s head over to Analytics, Data Analyzer and right-click on Waterfall then select the 'Open Link in a new Tab' option, and you can see on the top here that it gives you a new tab with a waterfall. And that's the default view that loads on your screen.

But you still have the other tab open for the Scatter Data and you can now copy this information out. Simply right-click on the Product Id number from the list and Copy it, so you don't need to switch between this and Excel.

Go back to your Waterfall chart and simply create a new Waterfall chart for this product, by pressing CTRL+V and pasting it on the Products filter area. Avoid pressing the Enter key yet, you just need to select it from the drop-down or available list. Click out of the field and now you can see it's there.

Make sure that your data source is correct and change the aggregate by Percentage.

You've selected the Pricing Date Year as 2020, but you could select 2019 or 2021, depending on what's available in your Data. So let's leave that default date and apply and refresh.

So that gives you an indication of where all of your Margin Leakage is located. You can see Volume Discount, Std Discount/Industry/Sales Channel Discount, Promotion Discount, you’ve got Competitor Discounts and Customer Negotiated Discounts and this is all at a percentage.

And the Percentage within the Pricing Waterfall is taken from the Invoice Price, so you can see the invoice price is at 100%. That's the price that you work from.

There is a specific tutorial on Different Chart Types and you can find more information on the Waterfall Chart there.

But for now, let’s look at things like Rebates, where you can see there's a 21% rebate there off the invoice price, and there is a payment term cost as well.

These are all areas that you can now look at in order to improve your margin.

You may also want to compare the weakest product from the Scatter Plot Chart, or do the same analysis for each of the products and get an indication of where the areas of Margin Leakages are.

Now, you can copy the product with the highest gross margin from the Scatter chart and go back to the Waterfall chart for the same analysis.

If you want to compare both, you could go and create a brand new Waterfall chart on a new tab by performing the same previous steps.

And this new Waterfall chart is for the product 6036, which you know is the best performing product in this product group.

So you can now see, just off the top of your head, the Rebates and Discounts here are significantly lower than you have on the other product.

And you can have a quick look at the other product here.

But it's tricky to go from one Waterfall chart to another one for comparisons.

Comparing Results

So what Pricefx offers, under Analytics again, Data Analyzer, below the Waterfall you actually have a Waterfall Comparison option:

Let's click on that option. And what that allows you to do is to create a chart with more than one series by clicking on the Add Series option.

So they will appear next to each other, and you can do a real side-by-side comparison.

So, you must go back and find your data in the Scatter chart tab. Let’s start by copying the worst-performing product.

Now, you're going back to your Waterfall Comparison tab and copying it into the Products filter. Make sure to update the Aggregation to Percentage again.

Remember to select the product from the drop-down once it is pasted. Apply and Refresh. And you can see that this is slightly different. You can see all the positive numbers are above the zero line and all the negatives are below the line.

And this is unique to the Waterfall Comparison chart. This is still only for the one product, the lower-performance one.

 So what you can do, at the bottom on the left-side area, use the Add Series option, which only works or appears on the Waterfall Comparison chart, it doesn't work on the standard Waterfall chart functionality.

You want to add a series, you'll call it Series 2, leave that as it is, it keeps it as a percentage. But you must change the product in the filter with the best-performance product.

So you must go back to your data from the Scatter tab and find the best performing product. Copy the details from there.

Bring the copied product back to the Waterfall Comparison tab and use CTRL+V, in this case, to paste it into the Series 2 Products filter.

Remember to select the product from the pop-up list to make sure is selected.  

Now you have the product that you want to compare with the same settings for the filters. Apply and Refresh. Now you can see the two products compared side by side. You can now see both Base Prices.

But the interesting pieces are the discounts, the rebates, and payment terms costs that you have here.

And if you look at this you can see immediately the volume discount here on both series, the first bars correspond to series one with the lower-performance product and the second bars correspond to series two with the best performance product. If you click on any one of those or just hover over it, you will see highlights of all the series one data associated.

And series two is there next to that one so you can see the difference here.

Volume discount for the first the worst performing product is at 10% just or almost 11% and if you look at a volume discount for the best performing product the discount there is just about 4.5% percent.

And as you move across you can see that in every instance the discount on the best performing product is significantly lower than the discount on the worst-performing product.

So that tells you there's something that you may want to look at there.

What you can also see just very briefly is that the Competitor Discount for the best performing product is actually a positive number, which is incorrect, that shouldn't be so that tells you that there may be something that you want to look at in your data source itself. 

So you may want to change that but even if you did that correctly it would still be a significantly lower discount than what you have in the series one for worst performing product

So that is where you find the analytics to begin working on identifying and stopping Margin leakage within your organization and while you've looked specifically at Margin Leakage for a product, you can use the same method to identify margin leakage across customers, account managers, or even countries and regions across your organization.

And that's all dependent on the filters you can see here

The pricing waterfall is widely used across Pricefx to ensure that you have this type of information on hand when you're creating quotes, running simulations, or looking to optimize your pricing at different stages of your pricing workflow.

So getting to know it is very important.