Prophet 21 Pricing Library Design Worksheet
A pricing library grows by accident: one customer exception here, one contract there, until nobody knows which rule wins. This worksheet forces the design onto one page before you key it into Prophet 21, so every rule has an owner, an audience and a margin you can defend.
| Price page / rule name | Applies to | Product group | Price basis | Sample unit cost | Multiplier on cost | Sample sell price | Gross margin | Effective date | Owner | |
|---|---|---|---|---|---|---|---|---|---|---|
$6 | 31% | |||||||||
$5 | 23.1% | |||||||||
$23 | 18% | |||||||||
$42 | 25.9% | |||||||||
$15 | 37.5% |
Margin is calculated as (multiplier minus 1) divided by multiplier, which is gross margin on sell price. Sample costs are only for checking the shape of the library. Confirm how your own system applies price pages, precedence and rounding before loading anything.
Pricing rule overlap review
Optional: share an export of your price pages and we will outline overlaps and expired rules for your team.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Add one row per price rule you intend to create. Name it, say whether it applies to a single customer, a customer class, a contract or everyone, and name the product group it covers. Choose the price basis and enter a representative cost and multiplier. The sheet calculates a sample sell price and gross margin so you can spot rules that undercut your floor. Sort by margin and challenge anything under your threshold. Export to Excel and circulate it to sales and finance before build.
Designing for precedence
Most distribution systems resolve price by looking for the most specific rule first, such as a customer and item match, then falling back to broader ones like a class and product group, and finally a base rule. Design your library the same way: a small base layer, a class layer, and a thin exception layer. Document the order you expect rules to win. Every row in the exception layer should have an owner and an expiry, because stale contract prices are the largest source of silent margin leakage.
- Keep the base layer small and boring
- Prefer classes over single customer rules
- Give every exception an owner and review date
- Test with real orders before go-live
Reading the margin column
A multiplier of 1.25 on cost is a 20 percent gross margin, not 25. This is the single most common mistake in distributor pricing conversations, because markup and margin sound alike. The worksheet shows margin on sell price. The summary shows the spread between your highest and lowest margin rules. A very wide spread is not automatically wrong, since contract and commodity lines legitimately differ, but it should be deliberate and explainable.
Common mistakes
Teams often copy the old system's rules one for one, including years of dead exceptions. Use the migration or redesign as a chance to prune. Another mistake is basing multipliers on replacement cost in some places and average cost in others without saying so. State the cost basis in the rule name or notes. Finally, do not forget rounding and minimum price behavior, which can erase a thin margin on low cost items.
Frequently Asked Questions
What is the difference between markup and margin?
Markup is profit as a percent of cost, margin is profit as a percent of sell price. A 1.25 multiplier on cost is a 25 percent markup but only a 20 percent gross margin. Always state which one a target refers to.
Should I price by customer or by class?
Prefer classes wherever customers behave similarly, because they are easier to maintain and audit. Reserve single-customer rules for true contracts and negotiated accounts, and give each one an owner and expiry so it does not linger.
How many price rules is too many?
There is no fixed number, but if nobody can explain why a given customer gets a given price, you have too many. A useful test is whether a new pricing analyst could trace any order line to one winning rule in a few minutes.
Does this load into Prophet 21?
No. It is a planning worksheet that exports to Excel. You still need to build the rules through your normal P21 setup or import process, following your own configuration and testing standards.
If you are cleaning up a large pricing library, we can build a read-only analysis that finds overlapping, expired and below-floor rules in your exported data for your pricing team to review.
Related Tools
Distributor Rebate Tracking Worksheet
Track vendor rebate programs by period: purchases, rebate rate, amount earned, claimed and received, and what is still outstanding.
EpicorProphet 21 Inventory Replenishment Calculator
Work out order point, safety stock, EOQ and a max level for a distribution item, then compare against what your P21 replenishment parameters say.
EpicorProphet 21 Data Cleanup Checklist
A checklist for cleaning item, customer, vendor, pricing and inventory data in Prophet 21 before an upgrade, migration or reporting project.
Supply Chain & InventoryDistribution ERP Fill Rate Calculator
Calculate line, unit and complete-order fill rate from your ERP counts and see the revenue and gross margin put at risk by unfilled lines.
More Epicor tools
See all- Prophet 21 Upgrade Checklist
- Prophet 21 Special Order Request Form Template
- Prophet 21 Vendor Return Authorization Request Template
- P21 Dead Stock Hold vs Liquidate Calculator
- P21 Price Matrix Multiplier and Margin Calculator
- Prophet 21 Inventory by Location SQL Generator
- Prophet 21 Open Order and Backorder SQL Generator
- Prophet 21 Order to Cash Go-Live Test Checklist
- Epicor Kinetic Job Status Reference