When we engage with retail prospects for our services, the first thing I usually ask is, “Are you setting prices in a spreadsheet?” If the answer is yes, likely you are experiencing challenges with your ERP or Host Merchandising system that were insurmountable and lead you to externalizing price setting through a spreadsheet. Often it is not just a single spreadsheet, but many spreadsheets that must work in unison to deliver prices.
Spreadsheets aren’t the only cause of pricing problems. In one case, we saw a retailer that has an optimization tool, but can’t reliably get prices down to stores. This is a price execution problem rather than price setting. Regardless of the reason, prices have to be set and sent to where customers can see them and problems can arise along the way that ultimately impact your profit.
If you suspect you might have pricing problems, how do you find out? We typically start analyzing where errors are the most obvious. At the highest level, commercial companies sell products for a price, customers buy the products, and you can check if the price for which you sold the product is the same as what you expected. The specific points where errors occur differs by industry but for retail, you can typically start with:
Store operations. Store operations will typically be notified by sales associates when prices are wrong. Track how many wrong prices are reported per week and which stores have errors.
Take a sample of orders and re-price them. A random sample will give you an indicator of if there are pricing errors.
You can then quantify the financial impact of the price errors. Products can be overpriced or underpriced and you can calculate the difference from the actual price. If it is overpriced you risk losing a sale. You can also undermine customer satisfaction but that is tougher to quantify. An underpriced product will impact profit and is easily calculated. Secondly, when you identify pricing errors it takes time and effort to correct the price. This has a cost in that an employee must analyze the error, correct it and move it through the systems to eCommerce or the POS. For example, you can review the analysis from the metals company we worked with years ago. Even though it has been 15 years, many companies still manually set prices in spreadsheets and experience similar error rates.
Once you realize how much price errors cost, you can figure out how to correct them by reviewing the process. The metals producer mentioned above found they had a 5% error rate on all invoices. This error rate is not uncommon when there are manual steps involved. They analyzed the process from start to finish. We used a similar approach and mapped it to fashion customers where we’ve seen a multi-step process that includes:
Merchant sets initial regular price
Merchant sets a calendar for promotions
Merchant defines promotions and a pricing administrative team executes them
Merchant sets mark down cadence for the season and pricing administrative team executes
Prices are sent to store
Store associate moves items and tags them
Where can this process go wrong? It’s best to do a thorough review of the process. What we’ve done in the past is to look at each step, interview the people doing the task and map out the steps and tools. For fashion, here are typical areas of opportunity:
Merchant sets initial regular price. When a product is introduced, the merchant sets the regular price. They typically define price points to target and then assign specific styles to each price point. A style is broken down into style-color-size combinations. This explosion of permutations is where the process gets cumbersome. For the most part, to make it easier, a style is generally priced the same but there are exceptions for size and color. Then, if you’re dealing with multiple currencies the process expands for each of the countries you’re dealing with. Price errors can occur when products aren’t mapped correctly or while converting prices for different countries.
Merchant sets calendar for promotions. The promotion calendar is built for the season but initially specific promotions are only defined at a high level. I’m calling out this step because merchants use it for planning but they’re not assigning the specific promotion yet.
Merchant sets promotion. When the promotional event is closer, the merchant will set the promotions. This can be specific discounts for a category, price points for a set of items, buy X get Y, or anything else a merchant can dream up. Usually, a merchant will define these in as much detail they can within a spreadsheet. Then, they hand it off to a pricing administrative team for execution. The interpretation between what the merchant wants and what the administrator enters can be a source of errors. For example, the merchant may inadvertently copy a style from a previous promotion or make an error while assigning a given item to a promotion. The pricing administrator may be able to catch the errors, but some errors will slip through.
Merchant sets mark down cadence. Depending on how a product is selling and how much inventory is left, merchants will set mark down cadence. These are hard marks geared towards optimally selling through the inventory by the end of the season. The use of separate systems for planning and executing these markdowns can lead to errors. Individual styles are marked down based on manual analysis or an optimization algorithm. If there is no systematic hand off between setting the prices and executing on them, problems can occur.
Prices are sent to the store. Once prices are set, they must be transmitted to the stores. Given that each store has their own point of sale system and might have different prices, promotions, or markdowns, most stores get their own set of prices and rules. For a large retailer, there can be 400 or more individual systems. Each system is a potential failure point given that the POS must receive the prices, load them, and pull in the rules.
Store personnel moves items and tags them. In parallel, promotional sheets are provided to the stores for product placement, promotional signage, and prices. Any number of issues can occur here. Tight coordination is required at each store to insure the prices are correct on the tags and the products are in the right spot for the given promotion.
Reviewing this process can reveal areas of opportunity to plug the holes. In this example, an up front system that allows the merchant to directly enter promotions or price changes directly would eliminate the opportunity for confusion with the price administrator. A system to quickly and reliably transmit the calculated prices to the POS might be needed. Alternatively, the POS could call out to a central pricing system which would eliminate the need to transmit the price data. The promotional and placement sheets that store personnel use could be generated out of the price execution system rather than being created manually. In some cases, electronic tags could be used. Regardless, once we’ve identified the most egregious spots we tackle them first, then move to the next ones. Typically, the solution relies on systems that can keep all the relationships in sync. It usually includes better processes as well. We look forward to learning about your specific processes and how we can help improve them.
Pricing errors leak profit
Pricing errors leak profits and they could be dramatically reduced with some effort. Companies that have straightforward list prices are much easier to manage then when companies negotiate complex contracts. Pricing errors are common place when companies negotiate regularly because there are so many exceptions to prices and conditions that sales people agree to which must first be put into a contract and second either executed by sales reps taking orders or automated into a system. This exception process leads to a lot of errors.
Our customers cut across many different industries and these issues are prolific whether you are in metals, high tech, insurance, or other business to business situations. One of our customers in the metals industry performed an extensive Six Sigma study prior to engaging us. The study found their sales process and inter-communication caused thousands of problems a year. The quantifiable errors cost them $1.4M a year and the upside was most likely $5M – $7M a year.
In Six Sigma, it is critical to have a well defined business case that outlines the purpose of the project as well as a goal statement that addresses the business case. For the customer, the business case was obvious:
$750 million in sales, 60 thousand invoices, 3 thousand discrepancies.
No system in place to verify accuracy of contract and pricing for orders.
Loss of revenue, customer confidence, control of pricing in marketplace.
And the resulting goal statement was defined as:
Improve competitive pricing and invoice system for quarterly savings of $350K.
The customer’s first priority was reducing pricing errors in accordance with the business case and goal statement. Today’s market requires more extensive information on invoices than in prior years including the price and how the final price is derived. Their price components were base price, alloy surcharges, scrap surcharges, freight, freight equalization, and fuel surcharges. Each one of these components were either contract values or tied to a monthly index average. The steps for calculating and looking up values produced significant errors as shown in Table 1.
Total Invoices
Price Errors
Input Errors
Retro Price
Late Price Sheet
Incorrect Price Sheet
Plant 1
561
225
5
21
93
217
Plant 2
39
31
2
0
0
6
Plant 3
489
220
2
31
103
133
Plant 4
411
248
8
4
45
106
Plant 5
221
29
27
4
6
155
Total
1721
753
44
60
247
617
% of Reasons
43.80%
2.60%
3.50%
14.40%
35.90%
% of Invoices
4.20%
1.80%
0.10%
0.10%
0.60%
1.50%
Avg cost/yr to correct
$348,503.00
$152,483.00
$8,910.00
$12,150.00
$50,018.00
$124,943.00
Table 1
This table looks at invoice discrepancies only when payments received did not match the invoiced amount. The number of invoice discrepancies were 4.2% of the total 60,000 invoices per year. Errors were broken down into five categories including:
Price Errors, which were mis-calculations or incorrect lookups of price components.
Input Errors, which occured when transposing prices from spreadsheet calculations to the invoicing system.
Retro Errors, which were caught after the fact and changed.
Late Price Sheet, which occured when sales people failed to get price changes in on time before invoices were set out.
Incorrect Price Sheets, which were spreadsheets maintained by sales people that had errors or were interpreted incorrectly.
We worked with them to eliminate all of these errors by using a centralized pricing structure and automatically generating price sheets. Sales people used the tool which had a similar interface as a spreadsheet which they were familiar with and prices were automatically stored in the central system. So, when invoicing needed prices, they were always up to date and interpretation disappeared.
It was interesting to note in the study that more often than not, customers notified them when they thought they were over-billed, but customer reported under-billing was almost non-existent as shown in Table 2.
January
February
Location
Over
Under
Over
Under
Plant 1
$39,867.13
$0.00
$6,965.65
-$232.35
Plant 2
$20,895.40
-$2,171.66
$5,842.22
$0.00
Plant 3
$29,933.82
$0.00
$3,282.29
$0.00
Plant 4
$13,555.53
$0.00
$8,392.71
$0.00
Plant 5
$1,700.74
$0.00
$87.73
$0.00
Monthly Total
$108,124.28
-$2,171.66
$24,803.08
-$232.35
Running Total
$108,124.28
-$2,171.66
$132,927.36
-$2,404.14
Annualized Rate
$1,297,491.36
-$26,059.92
$797,564.16
-$14,424.84
Table 2
Statistically, the over and under billing errors should have been similar. In the customer’s case, only one customer actually called back when he was under-billed.
As mentioned previously, with the new system the prices were correct the first time. When we piloted the system, they checked 5 customer’s prices for a month period and found over $77,000 in under-billing that would have previously gone unnoticed.
The Six Sigma study also took an in depth look at why errors occurred. The source of errors fell into the six bins shown in Illustration 2. The main source of errors was the loose price sheet document, which they called a CPF. This document was managed by sales people, but with little structure. Sales is a creative process so sales people needed flexibility in managing customers, but the loose form significantly contributed to errors.
Market Environment
CPF Document
Pricing System Errors
CPF Interpretation
Time of Order vs Time of Shipping
1. No time for detailed price agreement with customer
1. No requirement for documenting agreement with customer
1. Same person not always available to price
1. Long learning curve for interpreting CPFs
1. Lack of need for documented agreement with customer for specific delivered price
2. Customer’s system is not compatable with CPF
2. No time for detailed price agreement
2. Input error prone
2. Complexity of CPF, agreement, non-standard info sources
2. No comparison of pricing to customer PO
3. Information not received in a timely fashion
3. CPF constructed several days after agreement
3. Illegible, handwritten info
3. Inconsistent interpretation of CPF and add-ons
3. Price is optional part of order entry
4. Amendments not received
4. Pricing using wrong sheet
4. Calculation errors
4. Same person not always available
4. Price at order entry not transferred to pricing
5. CPF not updated
5. Information not received in a timely fashion
5. Transposition errors
5. Didn’t capture info correctly from CPF
5. No checks on previous billing history
6. Information not available on time
6. Amendments not received
6. Employees time constrained
6. Missed charging for extras
7. No verification process with customer
7. CPF not updated
7. Documents hard to read
7. Misread the CPF
8. Format of CPF not compatible with pricing needs
8. Information not available on time
8. Working from the wrong sheet, line, or column
9. Information not available on time
9. No controls to ensure correct info is being used
9. No controls on using wrong infor from CPF
10. Price protected orders/shipments hard to ID
11. No controls on what ammendments are used for pricing
12. No controls on effective dates
We were able to address the majority of the error sources discussed above without interfering with the creativity of the sales people. By centralizing pricing, standardizing calculation methods, and tables, we brought structure to the process but allowed sales people to customize price sheets according to individual needs.
Metals pricing is particularly complicated and if you’re going to tackle your own pricing errors, you don’t necessarily need to do a Six Sigma study before you start. It certainly helps to unearth the estimated savings, but you probably already have a gut feel for where the leaks are coming from. The first thing to do is get a handle on all the contracts floating around and the special conditions. You can segment these, put them in spreadsheets and start from there. Once you have the the special conditions isolated, you can put together a framework for exceptions that captures the majority of the conditions. Then, you can offer those exceptions as the only exceptions you’ll allow from sales reps. Giving reps some freedom within a process gives them some negotiating latitude that you can put into a system to cut down on errors. It’s not simple, but it can be done and will help increase profit.
Pricing errors take time to fix
One of our customers, a global clothing and accessory retailer, was looking for a more effective way to manage their prices. Competitive threats precipitated the need to change prices frequently which stressed their existing process. Their merchandising and pricing teams struggled with correcting price mistakes quickly and identifying where errors occurred. Their process was caught in a cumbersome coordination between their host merchandising, spreadsheets, eCommerce, and Point of Sale systems. The system we implemented made the process more effective, improved the speed at which they could respond to price mistakes, and gave them visibility to where the errors were happening. Below is a review the benefits they received and how we helped them.
Business Case. It is critical to have a well-defined business case that outlines the purpose of the project as well as a goal statement that addresses the business case. In this case, the objectives were clear:
Ability to react quickly and flexibly to local market conditions
Correct mistakes faster through direct integration into downstream systems
Identify problems faster with better visibility into where the errors occurred
Consolidate pricing activities into a single system of record
Better flexibility in local markets. As the competitive landscape changed, our customer needed the ability to change prices easily across local markets. While price changes were possible in their previous process, a lot of manual effort was required. Through the new tool and process we implemented, merchandisers were given the flexibility to change hard marks, sale, clearance, and promotional prices for any product and store combination. This laid the foundation to rapidly change prices. All prices are managed centrally and then individual files are generated for each store or the eCommerce site. In the future, they may take advantage of real time APIs which would allow systems to immediately receive price updates without any delay.
Correct mistakes faster. Correcting mistakes faster was a top priority in accordance with their business case. Today’s retailers must have accurate pricing and be able to react quickly to errors. The previous process would take about 2 to 2.5 hours to update mistakes or simply send out midday updates. With the new solution the time was slashed to 20 minutes. The previous process went through several steps with intermediary systems. Now, they are able to generate the price change directly for the given stores and distribute the files immediately which are then transferred to the POS.
Gaining visibility to pricing outcomes. Prices were buried in spreadsheets and often it was difficult to determine the actual effective price given overlapping hard marks, promotions, and stackable coupons. In many organizations different people are responsible for merchandising and marketing and the ultimate margin is estimated until sales data is returned. With the new tool, users are able to see how the prices were built, who created the promotion or coupon, and when it is effective. The price administrators are able to search across the time horizon to see if a future price change will affect their expected margins. Prior to the new process when a store recognized a price was wrong, they would notify the business who would then go through a flurry of emails to figure out where the error occurred. Now, the pricing team is able to look up the item, find out exactly which promotions are applied, and correct the error quickly.
Consolidating pricing activities. In the previous pricing process, activities were split between the host merchandising system, spreadsheets, and a separate system for multi-item deals. Our customer wanted to consolidate those functions to have a single system for hard marks, clearance, sale, promotions, and deals. They were able to do that through the new system which allows them to manage their prices and then distribute to their various channels.
Future considerations. Looking into the future, our customer will be able to move price entry into the hands of the merchants rather than having a dedicated team for price entry. This will allow the pricing team to focus on more strategic initiatives. The next area of focus is store communications. They manually create a document for store managers that tells them price changes and product placement. With the addition of product placement information, the new solution will automatically generate this document. This will streamline the process the merchandisers do to get information to the stores. Finally, they are considering an Asia Pacific rollout and real time connections to systems to cut the response time further.
We were able to address the issues discussed above working with a cross functional team of merchants, eCommerce, IT staff, and pricing managers. Working with these teams, we identified the critical issues with the process and implemented new capabilities that ultimately saved them time and money.
Preparing for an optimization opportunity
Pricing optimization is one of the best tools you have at your disposal to increase your profit. Studies have shown base price optimization can yield an increase of 2% – 5% in margin, promotional optimization can yield 5% – 20% and mark down can lead to a 6% – 10% improvement. That is too great of an opportunity to ignore. If you are not using science and have a good amount of transaction data, then you could almost certainly benefit from using optimization. If you are considering optimization, you can take steps to make sure you are fully prepared to take advantage of the solutions.
First, a brief explanation. Products go through different lifecycles which closely tracks with what types of algorithms you can use to optimize prices. Many products adhere to a lifecycle where the product is introduced, then sales increase, eventually even out, and finally decreases at the end of life as inventory is sold through. Each stage in the product lifecycle requires different optimization techniques. The initial and day-to-day price is established at introduction and you monitor performance for a period of time before using promotions to increase sales and profit. The initial price can be optimized but is typically bounded by constraints and business rules you have which limits optimization. Promotions allow more freedom in using elasticity to understand what the best price is and mark down optimizes your sell through.
The general barometer mentioned above is valid in most cases and you can do some high level analysis to determine what benefits you can achieve, but truthfully if there is an opportunity you won’t be able to realize the benefit unless you can actually do the optimization. So, instead of discussing a process for estimating the opportunity, we’ll discuss how you can figure out if you can unlock the opportunity.
For optimization to work, you must have enough data and price variation. The data elements needed depends on the type of optimization you are doing but you always need base data like products, location, and sales history. You might also need things like inventory positions, marketing instruments, cost, and promotions. Below we explain what each element is and how it relates to the specific optimization:
Sale price. It is important to have the price the customer sees when they make a purchase. As simple as this sounds, its sometimes difficult for companies to get this price. For example, if you’re a manufacturer, distributor or any other entity that does not have control over the final price the customer sees, it may be difficult to get it. Retailers have the transaction data, but in many cases the data isn’t clean and needs to be fixed.
Number of units sold. The transaction data will also include the number of units sold per location. Number of units sold and the sale price are the foundation of your historical data which is used in the forecast. If you can’t get the number of units sold, you can possibly get the number of units shipped to a given location. This isn’t ideal, but it’s better than nothing.
Price variation. Sometimes it is difficult to get enough data to build an accurate demand curve. But you can get it good enough then use basic analysis to set your price. Price variation can come from many different sources such as discounts, coupons, and price errors. It is essential to know the regular price, the promotional price, and the date range when the price was in effect.
Cost. When optimizing for profit, you’ll need to know how much you paid for it.
Marketing instruments. The marketing instrument used can influence the effectiveness of the promotion significantly. When capturing the price variation, it is important to know exactly what instrument was used because not all instruments are created equal.
Competitive prices. If you are in competitive markets, you’ll need the prices these competitors and the proximity to your stores. The same is true for your eCommerce channel. This data is tied to business rules which drive day to day pricing.
Inventory. If you’re trying to do mark down optimization, you’ll need inventory positions at each location including stores or distribution centers. Inventory would also include any future buys that have been made already.
Not all of this data is necessary to get started with optimization and you can add new data streams after your initial dip into the optimization pool. The basis for optimization is the forecast. If you don’t have enough price variation or data, you may need to substitute similar products, aggregate at a higher level, or use other forecasting techniques to get an accurate picture of demand. When you are trying to evaluate whether or not you can do optimization, the data is analyzed to see if there is enough to feel statistically confident.
When you’ve verified you can do optimization, what is all this data used for? For day-to-day pricing, a lot of the prices are dictated by business rules. These typically restrict the prices in a narrow band based on competitive products, target price points, and other factors. After that there is a small amount of room to maneuver using price elasticity. Promotions have more latitude in using price elasticity and also consider cannibalization and halo effect from other products. Finally, mark downs are constrained by available inventory and try to maximize your sell throughs based on your business goals.
If you pass the litmus test for having the data, you have an opportunity. The next step is to go through the process of collecting, cleansing, and preparing the data for an optimization tool so that you can unlock the potential benefits. In a future article, we will discuss how you use this data in each of the different types of optimization.
Home / Insights / Pricing In-Depth / Topic 2
Ryan Kinzy |
02
Understanding if you have a pricing problem
When we engage with retail prospects for our services, the first thing I usually ask is, “Are you setting prices in a spreadsheet?” If the answer is yes, likely you are experiencing challenges with your ERP or Host Merchandising system that were insurmountable and lead you to externalizing price setting through a spreadsheet. Often it is not just a single spreadsheet, but many spreadsheets that must work in unison to deliver prices.
Spreadsheets aren’t the only cause of pricing problems. In one case, we saw a retailer that has an optimization tool, but can’t reliably get prices down to stores. This is a price execution problem rather than price setting. Regardless of the reason, prices have to be set and sent to where customers can see them and problems can arise along the way that ultimately impact your profit.
If you suspect you might have pricing problems, how do you find out? We typically start analyzing where errors are the most obvious. At the highest level, commercial companies sell products for a price, customers buy the products, and you can check if the price for which you sold the product is the same as what you expected. The specific points where errors occur differs by industry but for retail, you can typically start with:
You can then quantify the financial impact of the price errors. Products can be overpriced or underpriced and you can calculate the difference from the actual price. If it is overpriced you risk losing a sale. You can also undermine customer satisfaction but that is tougher to quantify. An underpriced product will impact profit and is easily calculated. Secondly, when you identify pricing errors it takes time and effort to correct the price. This has a cost in that an employee must analyze the error, correct it and move it through the systems to eCommerce or the POS. For example, you can review the analysis from the metals company we worked with years ago. Even though it has been 15 years, many companies still manually set prices in spreadsheets and experience similar error rates.
Once you realize how much price errors cost, you can figure out how to correct them by reviewing the process. The metals producer mentioned above found they had a 5% error rate on all invoices. This error rate is not uncommon when there are manual steps involved. They analyzed the process from start to finish. We used a similar approach and mapped it to fashion customers where we’ve seen a multi-step process that includes:
Where can this process go wrong? It’s best to do a thorough review of the process. What we’ve done in the past is to look at each step, interview the people doing the task and map out the steps and tools. For fashion, here are typical areas of opportunity:
Merchant sets initial regular price. When a product is introduced, the merchant sets the regular price. They typically define price points to target and then assign specific styles to each price point. A style is broken down into style-color-size combinations. This explosion of permutations is where the process gets cumbersome. For the most part, to make it easier, a style is generally priced the same but there are exceptions for size and color. Then, if you’re dealing with multiple currencies the process expands for each of the countries you’re dealing with. Price errors can occur when products aren’t mapped correctly or while converting prices for different countries.
Merchant sets calendar for promotions. The promotion calendar is built for the season but initially specific promotions are only defined at a high level. I’m calling out this step because merchants use it for planning but they’re not assigning the specific promotion yet.
Merchant sets promotion. When the promotional event is closer, the merchant will set the promotions. This can be specific discounts for a category, price points for a set of items, buy X get Y, or anything else a merchant can dream up. Usually, a merchant will define these in as much detail they can within a spreadsheet. Then, they hand it off to a pricing administrative team for execution. The interpretation between what the merchant wants and what the administrator enters can be a source of errors. For example, the merchant may inadvertently copy a style from a previous promotion or make an error while assigning a given item to a promotion. The pricing administrator may be able to catch the errors, but some errors will slip through.
Merchant sets mark down cadence. Depending on how a product is selling and how much inventory is left, merchants will set mark down cadence. These are hard marks geared towards optimally selling through the inventory by the end of the season. The use of separate systems for planning and executing these markdowns can lead to errors. Individual styles are marked down based on manual analysis or an optimization algorithm. If there is no systematic hand off between setting the prices and executing on them, problems can occur.
Prices are sent to the store. Once prices are set, they must be transmitted to the stores. Given that each store has their own point of sale system and might have different prices, promotions, or markdowns, most stores get their own set of prices and rules. For a large retailer, there can be 400 or more individual systems. Each system is a potential failure point given that the POS must receive the prices, load them, and pull in the rules.
Store personnel moves items and tags them. In parallel, promotional sheets are provided to the stores for product placement, promotional signage, and prices. Any number of issues can occur here. Tight coordination is required at each store to insure the prices are correct on the tags and the products are in the right spot for the given promotion.
Reviewing this process can reveal areas of opportunity to plug the holes. In this example, an up front system that allows the merchant to directly enter promotions or price changes directly would eliminate the opportunity for confusion with the price administrator. A system to quickly and reliably transmit the calculated prices to the POS might be needed. Alternatively, the POS could call out to a central pricing system which would eliminate the need to transmit the price data. The promotional and placement sheets that store personnel use could be generated out of the price execution system rather than being created manually. In some cases, electronic tags could be used. Regardless, once we’ve identified the most egregious spots we tackle them first, then move to the next ones. Typically, the solution relies on systems that can keep all the relationships in sync. It usually includes better processes as well. We look forward to learning about your specific processes and how we can help improve them.
Pricing errors leak profit
Pricing errors leak profits and they could be dramatically reduced with some effort. Companies that have straightforward list prices are much easier to manage then when companies negotiate complex contracts. Pricing errors are common place when companies negotiate regularly because there are so many exceptions to prices and conditions that sales people agree to which must first be put into a contract and second either executed by sales reps taking orders or automated into a system. This exception process leads to a lot of errors.
Our customers cut across many different industries and these issues are prolific whether you are in metals, high tech, insurance, or other business to business situations. One of our customers in the metals industry performed an extensive Six Sigma study prior to engaging us. The study found their sales process and inter-communication caused thousands of problems a year. The quantifiable errors cost them $1.4M a year and the upside was most likely $5M – $7M a year.
In Six Sigma, it is critical to have a well defined business case that outlines the purpose of the project as well as a goal statement that addresses the business case. For the customer, the business case was obvious:
And the resulting goal statement was defined as:
The customer’s first priority was reducing pricing errors in accordance with the business case and goal statement. Today’s market requires more extensive information on invoices than in prior years including the price and how the final price is derived. Their price components were base price, alloy surcharges, scrap surcharges, freight, freight equalization, and fuel surcharges. Each one of these components were either contract values or tied to a monthly index average. The steps for calculating and looking up values produced significant errors as shown in Table 1.
Table 1
This table looks at invoice discrepancies only when payments received did not match the invoiced amount. The number of invoice discrepancies were 4.2% of the total 60,000 invoices per year. Errors were broken down into five categories including:
We worked with them to eliminate all of these errors by using a centralized pricing structure and automatically generating price sheets. Sales people used the tool which had a similar interface as a spreadsheet which they were familiar with and prices were automatically stored in the central system. So, when invoicing needed prices, they were always up to date and interpretation disappeared.
It was interesting to note in the study that more often than not, customers notified them when they thought they were over-billed, but customer reported under-billing was almost non-existent as shown in Table 2.
Table 2
Statistically, the over and under billing errors should have been similar. In the customer’s case, only one customer actually called back when he was under-billed.
As mentioned previously, with the new system the prices were correct the first time. When we piloted the system, they checked 5 customer’s prices for a month period and found over $77,000 in under-billing that would have previously gone unnoticed.
The Six Sigma study also took an in depth look at why errors occurred. The source of errors fell into the six bins shown in Illustration 2. The main source of errors was the loose price sheet document, which they called a CPF. This document was managed by sales people, but with little structure. Sales is a creative process so sales people needed flexibility in managing customers, but the loose form significantly contributed to errors.
We were able to address the majority of the error sources discussed above without interfering with the creativity of the sales people. By centralizing pricing, standardizing calculation methods, and tables, we brought structure to the process but allowed sales people to customize price sheets according to individual needs.
Metals pricing is particularly complicated and if you’re going to tackle your own pricing errors, you don’t necessarily need to do a Six Sigma study before you start. It certainly helps to unearth the estimated savings, but you probably already have a gut feel for where the leaks are coming from. The first thing to do is get a handle on all the contracts floating around and the special conditions. You can segment these, put them in spreadsheets and start from there. Once you have the the special conditions isolated, you can put together a framework for exceptions that captures the majority of the conditions. Then, you can offer those exceptions as the only exceptions you’ll allow from sales reps. Giving reps some freedom within a process gives them some negotiating latitude that you can put into a system to cut down on errors. It’s not simple, but it can be done and will help increase profit.
Pricing errors take time to fix
One of our customers, a global clothing and accessory retailer, was looking for a more effective way to manage their prices. Competitive threats precipitated the need to change prices frequently which stressed their existing process. Their merchandising and pricing teams struggled with correcting price mistakes quickly and identifying where errors occurred. Their process was caught in a cumbersome coordination between their host merchandising, spreadsheets, eCommerce, and Point of Sale systems. The system we implemented made the process more effective, improved the speed at which they could respond to price mistakes, and gave them visibility to where the errors were happening. Below is a review the benefits they received and how we helped them.
Business Case. It is critical to have a well-defined business case that outlines the purpose of the project as well as a goal statement that addresses the business case. In this case, the objectives were clear:
Better flexibility in local markets. As the competitive landscape changed, our customer needed the ability to change prices easily across local markets. While price changes were possible in their previous process, a lot of manual effort was required. Through the new tool and process we implemented, merchandisers were given the flexibility to change hard marks, sale, clearance, and promotional prices for any product and store combination. This laid the foundation to rapidly change prices. All prices are managed centrally and then individual files are generated for each store or the eCommerce site. In the future, they may take advantage of real time APIs which would allow systems to immediately receive price updates without any delay.
Correct mistakes faster. Correcting mistakes faster was a top priority in accordance with their business case. Today’s retailers must have accurate pricing and be able to react quickly to errors. The previous process would take about 2 to 2.5 hours to update mistakes or simply send out midday updates. With the new solution the time was slashed to 20 minutes. The previous process went through several steps with intermediary systems. Now, they are able to generate the price change directly for the given stores and distribute the files immediately which are then transferred to the POS.
Gaining visibility to pricing outcomes. Prices were buried in spreadsheets and often it was difficult to determine the actual effective price given overlapping hard marks, promotions, and stackable coupons. In many organizations different people are responsible for merchandising and marketing and the ultimate margin is estimated until sales data is returned. With the new tool, users are able to see how the prices were built, who created the promotion or coupon, and when it is effective. The price administrators are able to search across the time horizon to see if a future price change will affect their expected margins. Prior to the new process when a store recognized a price was wrong, they would notify the business who would then go through a flurry of emails to figure out where the error occurred. Now, the pricing team is able to look up the item, find out exactly which promotions are applied, and correct the error quickly.
Consolidating pricing activities. In the previous pricing process, activities were split between the host merchandising system, spreadsheets, and a separate system for multi-item deals. Our customer wanted to consolidate those functions to have a single system for hard marks, clearance, sale, promotions, and deals. They were able to do that through the new system which allows them to manage their prices and then distribute to their various channels.
Future considerations. Looking into the future, our customer will be able to move price entry into the hands of the merchants rather than having a dedicated team for price entry. This will allow the pricing team to focus on more strategic initiatives. The next area of focus is store communications. They manually create a document for store managers that tells them price changes and product placement. With the addition of product placement information, the new solution will automatically generate this document. This will streamline the process the merchandisers do to get information to the stores. Finally, they are considering an Asia Pacific rollout and real time connections to systems to cut the response time further.
We were able to address the issues discussed above working with a cross functional team of merchants, eCommerce, IT staff, and pricing managers. Working with these teams, we identified the critical issues with the process and implemented new capabilities that ultimately saved them time and money.
Preparing for an optimization opportunity
Pricing optimization is one of the best tools you have at your disposal to increase your profit. Studies have shown base price optimization can yield an increase of 2% – 5% in margin, promotional optimization can yield 5% – 20% and mark down can lead to a 6% – 10% improvement. That is too great of an opportunity to ignore. If you are not using science and have a good amount of transaction data, then you could almost certainly benefit from using optimization. If you are considering optimization, you can take steps to make sure you are fully prepared to take advantage of the solutions.
First, a brief explanation. Products go through different lifecycles which closely tracks with what types of algorithms you can use to optimize prices. Many products adhere to a lifecycle where the product is introduced, then sales increase, eventually even out, and finally decreases at the end of life as inventory is sold through. Each stage in the product lifecycle requires different optimization techniques. The initial and day-to-day price is established at introduction and you monitor performance for a period of time before using promotions to increase sales and profit. The initial price can be optimized but is typically bounded by constraints and business rules you have which limits optimization. Promotions allow more freedom in using elasticity to understand what the best price is and mark down optimizes your sell through.
The general barometer mentioned above is valid in most cases and you can do some high level analysis to determine what benefits you can achieve, but truthfully if there is an opportunity you won’t be able to realize the benefit unless you can actually do the optimization. So, instead of discussing a process for estimating the opportunity, we’ll discuss how you can figure out if you can unlock the opportunity.
For optimization to work, you must have enough data and price variation. The data elements needed depends on the type of optimization you are doing but you always need base data like products, location, and sales history. You might also need things like inventory positions, marketing instruments, cost, and promotions. Below we explain what each element is and how it relates to the specific optimization:
Sale price. It is important to have the price the customer sees when they make a purchase. As simple as this sounds, its sometimes difficult for companies to get this price. For example, if you’re a manufacturer, distributor or any other entity that does not have control over the final price the customer sees, it may be difficult to get it. Retailers have the transaction data, but in many cases the data isn’t clean and needs to be fixed.
Number of units sold. The transaction data will also include the number of units sold per location. Number of units sold and the sale price are the foundation of your historical data which is used in the forecast. If you can’t get the number of units sold, you can possibly get the number of units shipped to a given location. This isn’t ideal, but it’s better than nothing.
Price variation. Sometimes it is difficult to get enough data to build an accurate demand curve. But you can get it good enough then use basic analysis to set your price. Price variation can come from many different sources such as discounts, coupons, and price errors. It is essential to know the regular price, the promotional price, and the date range when the price was in effect.
Cost. When optimizing for profit, you’ll need to know how much you paid for it.
Marketing instruments. The marketing instrument used can influence the effectiveness of the promotion significantly. When capturing the price variation, it is important to know exactly what instrument was used because not all instruments are created equal.
Competitive prices. If you are in competitive markets, you’ll need the prices these competitors and the proximity to your stores. The same is true for your eCommerce channel. This data is tied to business rules which drive day to day pricing.
Inventory. If you’re trying to do mark down optimization, you’ll need inventory positions at each location including stores or distribution centers. Inventory would also include any future buys that have been made already.
Not all of this data is necessary to get started with optimization and you can add new data streams after your initial dip into the optimization pool. The basis for optimization is the forecast. If you don’t have enough price variation or data, you may need to substitute similar products, aggregate at a higher level, or use other forecasting techniques to get an accurate picture of demand. When you are trying to evaluate whether or not you can do optimization, the data is analyzed to see if there is enough to feel statistically confident.
When you’ve verified you can do optimization, what is all this data used for? For day-to-day pricing, a lot of the prices are dictated by business rules. These typically restrict the prices in a narrow band based on competitive products, target price points, and other factors. After that there is a small amount of room to maneuver using price elasticity. Promotions have more latitude in using price elasticity and also consider cannibalization and halo effect from other products. Finally, mark downs are constrained by available inventory and try to maximize your sell throughs based on your business goals.
If you pass the litmus test for having the data, you have an opportunity. The next step is to go through the process of collecting, cleansing, and preparing the data for an optimization tool so that you can unlock the potential benefits. In a future article, we will discuss how you use this data in each of the different types of optimization.