Case Study: An Auto Parts Retailer Configured Dynamic Pricing for 40 Supplier Price Lists

An auto parts retailer selling brake pads, oil filters, spark plugs, timing belts, and tires operated with 40 active supplier relationships, each with different cost structures, invoice currencies, and margin requirements. They were managing retail pricing for approximately 18,000 SKUs using a 12-tab Excel workbook with VLOOKUP formulas. The spreadsheet had last been audited 14 months before this case study, and no one on the team was confident it was still producing correct margins on every product.

The Challenge

The pricing problem had three distinct dimensions that compounded each other.

The first was supplier cost variability. The 40 suppliers issued updated price lists at different intervals — some quarterly, some monthly, two suppliers issued revisions at irregular intervals with as little as 48 hours notice before the new prices took effect. When a supplier updated costs, the retailer needed to recalculate retail prices for every affected SKU before the new supplier invoice arrived, otherwise they would be selling products at margins calculated against outdated costs.

The second dimension was supplier-specific margin requirements. Each of the 40 suppliers had different margin targets based on category, volume commitments, and contract terms. Tire suppliers operated on 18-22% margins due to high logistics costs. Filtration products ran at 35-42% because of lower supplier costs and less competitive pricing pressure. Branded parts from OEM-aligned suppliers had minimum advertised price (MAP) constraints that placed a floor on retail prices regardless of cost changes. Tracking these rules in a 12-tab spreadsheet meant each tab represented one supplier’s pricing logic — a maintenance structure that required the original author to understand, which was a problem when the original author left the company.

The third dimension was the consequence of pricing errors. Under-pricing products below their cost basis (a margin calculation error caused by stale cost data) produced negative-margin sales that were indistinguishable from regular orders in the storefront. Over-pricing above MAP on branded parts carried contractual penalty risks with those suppliers. Both types of errors were happening, but they were only being caught during monthly margin reports — weeks after the incorrect prices had been live.

A full audit of the spreadsheet conducted over 3 days found 847 SKUs with retail prices that did not match the current margin targets when calculated against the most recent supplier costs. Of those, 214 SKUs were priced below their actual cost basis. The audit also identified that the VLOOKUP formulas on three tabs had broken references after a spreadsheet restructuring six months earlier, meaning pricing for those supplier catalogs had been calculated from outdated cost data for half a year.

What They Tried First

The initial response to discovering the audit findings was to rebuild the spreadsheet from scratch, this time with better documentation and error-checking formulas. A senior analyst spent two weeks rebuilding the workbook with named ranges instead of cell references, input validation on cost fields, and a summary dashboard showing current margin percentages across all suppliers.

The rebuilt spreadsheet worked correctly for the first month. The problem was that it was still a spreadsheet — a document that required manual input of updated supplier cost data whenever a price list changed. When three suppliers updated their prices in the same week, the cost update process required importing each price list file, cross-referencing the SKU codes against the spreadsheet, updating the cost fields, and regenerating the retail price calculations. This took approximately 4 to 6 hours per supplier price list update. Three simultaneous updates meant 12 to 18 hours of spreadsheet maintenance in a single week.

There was also a version control problem. The master pricing spreadsheet was stored on a shared network drive. Multiple team members had opened and saved versions. On two occasions, partial edits from one team member had overwritten cost updates entered by another, and the error had not been caught immediately. One instance of overwritten data had resulted in 312 products being priced from incorrect costs for 11 days before the discrepancy was discovered during a manual spot check.

The team also evaluated two mid-market pricing tools designed for multi-supplier retail. Both required custom development work to import the supplier price list formats in use, which the supplier base used inconsistently — some supplied CSV, others used Excel with different column structures, two suppliers still sent price lists as PDF documents. The custom development quotes for both tools exceeded the budget allocated for the project.

The Solution

The retailer implemented MicroPIM’s product pricing rules engine. The approach replaced the spreadsheet’s per-supplier tab logic with configurable rule sets that define how retail prices are calculated from supplier costs, with automatic recalculation triggered whenever cost data is updated.

Step 1: Structuring the Supplier Cost Data

Before pricing rules could be configured, the supplier cost data needed to be centralized in MicroPIM’s product catalog as structured attributes. The team created a cost field structure with three fields per product: base supplier cost, supplier identifier (mapped to a supplier record), and cost currency.

Importing the supplier cost data for all 18,000 SKUs was done via CSV import over three sessions, using MicroPIM’s import field mapping to handle the different column structures from different supplier exports. The field mapping was saved per supplier as a reusable template, so subsequent price list updates could be imported without reconfiguring the mapping.

Total import time for the initial cost data load: 14 hours across 3 days, mostly spent cleaning and standardizing the supplier CSV files before import. Going forward, each supplier price list update takes 15 to 45 minutes to import depending on file quality.

[SCREENSHOT: MicroPIM product pricing rules configuration panel showing a supplier rule set for a filtration product supplier, with base cost field, margin percentage, MAP floor, and calculated retail price output]

Step 2: Configuring Pricing Rules per Supplier Group

With cost data in the catalog, the team configured pricing rule sets for each supplier group. MicroPIM’s pricing rules engine allows rule sets to be applied to products by supplier tag, category, or explicit product selection. Each rule set defines:

• Margin calculation method: Either a fixed markup percentage (e.g., cost × 1.42 for filtration products) or a margin percentage (e.g., a gross margin target of 38%).
• Rounding rules: Retail prices rounded to the nearest €0.99 or €0.49 for consumer-facing prices, rounded to €0.50 for wholesale/B2B channel prices.
• MAP floor: A minimum retail price value that overrides the calculated price if the margin calculation would produce a price below the MAP threshold. Configured for the 6 suppliers with MAP requirements.
• Margin floor: A hard minimum margin percentage that prevents retail prices from falling below cost even if a supplier cost update and the margin calculation would produce a negative result. Set at 5% as a fail-safe across all rules.

Configuring the 40 supplier rule sets took approximately 11 hours across two sessions. This included mapping the existing margin targets from the spreadsheet tabs to the rule set configuration, which required reviewing the spreadsheet logic for each supplier and translating it into MicroPIM’s rule format.

Step 3: Testing Rule Outputs Against the Existing Spreadsheet

Before activating the pricing rules on the live catalog, the team ran a comparison between MicroPIM’s calculated prices and the rebuilt spreadsheet’s outputs for a sample of 500 SKUs across all 40 suppliers.

The comparison found 23 discrepancies between the two outputs. Investigating each discrepancy identified the source in every case: 14 were cases where the spreadsheet was using outdated costs (pre-dating the most recent supplier price list update) while MicroPIM was using the current costs imported during the initial load. The remaining 9 discrepancies were rounding differences where the spreadsheet used a different rounding convention than specified in the MicroPIM rules. The rounding rules were adjusted, and all 9 aligned.

The 14 cost discrepancies were confirmed as spreadsheet errors — products where the spreadsheet had not been updated with the most recent supplier costs. This was an expected finding given the audit results from earlier.

[SCREENSHOT: MicroPIM pricing rule test comparison view showing a sample of 500 SKUs with calculated prices from the rule engine and a flagged discrepancy column showing 23 products with price differences, reason column indicating stale cost data vs. rounding difference]

Step 4: Activating Rules and Establishing the Update Workflow

With the rule comparison validated, the team activated the pricing rules on the full catalog. The activation applied the calculated retail prices to all 18,000 SKUs in a single batch operation. Products where the new calculated price differed from the current live price by more than 15% were flagged for manual review before the new price was applied — a safety check to catch any anomalies before they went live. 47 products were flagged, of which 38 were confirmed as legitimate price corrections and 9 required additional investigation.

The ongoing workflow for supplier price list updates was standardized:

1. Receive supplier price list (CSV, Excel, or PDF).
2. Import into MicroPIM using the saved supplier-specific field mapping template.
3. Review the import summary for anomalies (products where cost changed by more than 20% vs. the previous import are automatically flagged).
4. Approve the import to update cost fields.
5. The pricing rules engine automatically recalculates retail prices for all affected products.
6. Review products where the new retail price crosses a MAP threshold or triggers the margin floor protection.
7. Publish updated prices to the connected storefronts.

Total time per supplier price list update under the new workflow: 20 to 40 minutes. Previously: 4 to 6 hours.

[SCREENSHOT: MicroPIM supplier price list import summary showing a filtration supplier’s Q2 update — 847 products updated, 12 products flagged for cost increase over 20% threshold, automatic pricing rule recalculation in progress]

The Results

Pricing accuracy: The first full audit under the new system, conducted 90 days after activation, found zero SKUs priced below cost basis. The previous audit had found 214. Products outside their target margin range (due to factors like MAP constraints or market pricing pressure) were reduced from 847 to 31, and all 31 were documented with a business justification.

Time savings on supplier price list updates: The average time to process a supplier cost update dropped from 4.7 hours to 28 minutes. With 40 suppliers issuing price list updates at varying intervals, this amounted to approximately 180 hours of analyst time recovered per year.

Elimination of version control incidents: In the 6 months following migration to MicroPIM, there were zero instances of pricing errors caused by version conflicts or overwritten data. The single source of truth for cost and pricing data was no longer a shared spreadsheet file.

MAP compliance: The margin floor and MAP enforcement rules in the pricing rule configuration eliminated MAP violations. In the 12 months prior to implementation, the retailer had received 3 formal notices from MAP-enforcing suppliers. In the 12 months following, they received zero.

Revenue impact: Correcting the 214 negative-margin SKUs recovered an estimated €38,000 in margin that had been lost per month due to under-pricing. Based on the sales volume of those products at the time of the audit, the pricing correction was worth approximately €456,000 in annual gross margin recovery.

Key Takeaways

• A spreadsheet-based pricing system is not a cost management tool — it is a risk accumulation system. Every time a supplier updates their price list and the spreadsheet is not updated immediately, the gap between actual costs and calculated margins grows.
• Product pricing rules in a catalog management system enforce margin logic at the database level, not the document level. The rules apply whenever cost data changes, without requiring manual recalculation.
• The initial rule configuration investment (11 hours for 40 supplier rule sets) compares favorably to the ongoing maintenance burden of a spreadsheet system maintained indefinitely.
• MAP enforcement and margin floor protection are most valuable as automatic constraints rather than manual review steps. A pricing rule that cannot produce a price below a floor does not require a human to check whether each price is above the floor.
• Version control incidents in shared pricing spreadsheets are not preventable through process alone — they require a system that does not expose pricing data as an editable document.

If your retail pricing is managed in a spreadsheet with per-supplier tabs and you have more than 5 active suppliers, the version control and update latency risks in that system are already costing you money — either through margin erosion on under-priced products or through compliance exposure on MAP-regulated lines. MicroPIM’s pricing rules can be configured for your first supplier group the same day you create an account. Start at app.micropim.net/register.

Related Reading

• Bulk Edit 10,000 Products in Minutes
• Case Study: Supplement Brand Tiered Pricing
• Case Study: Medical Device Pricing Tiers

Frequently Asked Questions

Can MicroPIM’s pricing rules handle currency conversion for suppliers invoiced in different currencies?

Yes. Pricing rules can include a currency conversion step that applies a configurable exchange rate to cost values before the margin calculation. The exchange rate can be set manually (updated periodically) or, with an API integration, updated automatically from a live FX data source. For the auto parts retailer in this case study, three suppliers invoiced in USD and two in GBP against a base currency of EUR. The pricing rules for those suppliers included a conversion factor that was updated weekly from a manually entered rate. An automatic FX update integration is available for catalogs where currency fluctuation has a material impact on margins.

What happens when a supplier’s price list includes products not currently in the catalog?

New SKUs in an imported price list that do not match existing catalog records are flagged during the import review step rather than being automatically added to the catalog or silently ignored. The catalog manager can then decide whether to create new product records for the flagged SKUs, skip them, or map them to existing records with different SKU codes. This prevents phantom products from appearing in the catalog while still ensuring that new supplier offerings are visible for consideration during the review.

How granular can product pricing rules be — can you set different margins for different product categories within the same supplier?

Yes. Pricing rule sets can be scoped to intersections of supplier and category — for example, a rule that applies a 35% margin to all oil filters from Supplier X but a 22% margin to all tires from the same supplier. This is configured by applying multiple rule sets with different filter criteria, where the more specific rule (supplier + category) takes precedence over the more general rule (supplier only). The auto parts retailer configured 67 distinct rule configurations across their 40 suppliers to handle category-specific margin targets.

Is there an approval workflow before new calculated prices go live on the storefront?

MicroPIM supports a publish/stage model where calculated prices can be reviewed in a staged state before being pushed to connected storefronts. The retailer configured the workflow so that any price change exceeding 10% automatically requires explicit approval before publishing. Smaller changes (below 10%) publish automatically on the configured sync schedule. This prevents large pricing anomalies from reaching the storefront immediately while allowing routine cost adjustments to flow through without manual intervention on every update.