# Customer Profitability Worksheet for Distributors

> Source: https://netray.co/tools/distributor-customer-profitability-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable worksheet showing net profit by customer after freight, rebates and cost to serve, so the gross margin leaders are not mistaken for the profit leaders.

## Category

cost-finance

## Erp

Epicor Prophet 21

## Industry

Wholesale Distribution

## Audience

- Sales manager
- Controller
- Branch manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Customer

**Key:** customer

**Type:** text

#### Net sales

**Key:** sales

**Type:** number

**Format:** currency

#### Cost of goods

**Key:** cogs

**Type:** number

**Format:** currency

#### Freight out

**Key:** freight

**Type:** number

**Format:** currency

#### Rebates and discounts

**Key:** rebates

**Type:** number

**Format:** currency

#### Order count

**Key:** orders

**Type:** number

#### Cost to process one order

**Key:** cost_per_order

**Type:** number

**Format:** currency

#### Gross margin

**Key:** gross

**Type:** number

**Expr:** sales - cogs

**Format:** currency

#### Gross margin percent

**Key:** gross_pct

**Type:** number

**Expr:** if(gt(sales,0), (sales - cogs) / sales * 100, 0)

**Format:** percent

#### Net profit

**Key:** net

**Type:** number

**Expr:** sales - cogs - freight - rebates - orders * cost_per_order

**Format:** currency

#### Net profit percent

**Key:** net_pct

**Type:** number

**Expr:** if(gt(sales,0), (sales - cogs - freight - rebates - orders * cost_per_order) / sales * 100, 0)

**Format:** percent

**Rows:**

**Customer:** Brazos Fabrication LLC

**Sales:** 212000

**Cogs:** 158000

**Freight:** 3200

**Rebates:** 2100

**Orders:** 140

**Cost per order:** 22

**Customer:** Metro Building Services

**Sales:** 485000

**Cogs:** 398000

**Freight:** 14800

**Rebates:** 9700

**Orders:** 960

**Cost per order:** 22

**Customer:** Delta Mill Maintenance

**Sales:** 96000

**Cogs:** 70500

**Freight:** 1200

**Rebates:** 0

**Orders:** 48

**Cost per order:** 22

**Customer:** Walk-in counter

**Sales:** 129000

**Cogs:** 90300

**Freight:** 0

**Rebates:** 0

**Orders:** 2400

**Cost per order:** 6

**Totals:**

#### Total sales

**Key:** sales

**Agg:** sum

**Format:** currency

#### Total net profit

**Key:** net

**Agg:** sum

**Format:** currency

**Summary:**

#### Blended net profit percent

**Key:** blended_net

**Expr:** if(gt(sum_sales,0), sum_net / sum_sales * 100, 0)

**Format:** percent

**Primary:** true

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How do I estimate cost to process an order?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

customer profitability analysis excel, cost to serve worksheet distribution, net margin by customer, distributor customer margin template, customer profitability template, gross margin vs net margin customer, p21 customer profitability, profitable customers distributor

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json