Costing & FinanceFree worksheetPDF, Excel, Word exportEpicor Prophet 21Wholesale DistributionAll Epicor Prophet 21 tools

Customer Profitability Worksheet for Distributors

A customer with a 24 percent gross margin can still lose money after freight, rebates and the cost of handling 960 small orders. This worksheet makes the gap between gross and net visible, one customer at a time.

Free, optional. Reopen it on any device.

Download:

Gross margin	Gross margin percent	Net profit	Net profit percent
$54,000	25.5%	$45,620	21.5%
$87,000	17.9%	$41,380	8.5%
$25,500	26.6%	$23,244	24.2%
$38,700	30%	$24,300	18.8%

Total sales

$922,000

Total net profit

$134,544

Blended net profit percent

14.6%

Cost to process one order is an estimate: total order-handling labor and overhead divided by order count. Treat results as directional and agree the allocation method with finance.

Customer profitability from ERP data

An optional review of building this analysis directly from invoice and order tables.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the worksheet

Export net sales and cost of goods by customer for a trailing period, then add freight paid out, rebates and discounts, and the number of orders. Estimate a cost to process one order for your branch. Replace the sample rows, review the net profit percent, and sort by the lowest value. Export to Excel to take the list into a pricing or account review.

Use the same period for every column
Include rebate accruals, not just paid rebates
Use freight actually paid, not freight billed
Apply a lower order cost to counter and EDI orders

The calculation

Net profit is net sales minus cost of goods, freight, rebates and discounts, and orders times cost to process an order. Net profit percent divides that by sales. In the sample, Metro Building Services has a gross margin of about 18 percent, but after freight, rebates and 960 orders at 22 dollars it has a net near 3 percent, well below the others.

What to do with the result

Low net customers are not automatically bad. Options include raising minimum order values, moving to scheduled delivery, charging freight, changing the price matrix or shifting small orders to a web channel. Customers with high net margins deserve attention too, because protecting them matters more than chasing volume from the weak ones.

Common mistakes

Allocating overhead evenly by customer, which hides the effect of order count, and ignoring returns and credits are the big ones. Also beware of a short period: one large special order can distort a small customer. Review again after a full quarter.

Frequently Asked Questions

How do I estimate cost to process an order?

Divide the labor and overhead of order entry, picking, packing and invoicing by the number of orders in the period. Ask finance for the allocation. Counter or EDI orders cost less than phone orders, so use more than one rate if you can.

Should I include sales rep commission?

You can, as part of the rebates and discounts column or by editing the exported file. Include it if you want the view to represent true profit contribution.

Is this activity-based costing?

It is a light version. Full activity-based costing assigns many cost pools, this worksheet uses freight, rebates and order handling because those are available to most distributors and cause most of the gap.

What is a good net profit percent?

It depends on the industry and your cost structure. Compare customers to each other and to the blended result in the summary. Use the ranking to find outliers rather than to chase a universal target.

If you want this view generated from your ERP invoice and order history, with every number traceable to a query, we can discuss a read-only analysis.

Talk to an Expert Explore More Free Tools