# D365 Trade Agreement Price and Margin Worksheet

> Source: https://netray.co/tools/d365-fo-trade-agreement-price-margin-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Plan sales price lines and trade discounts per item, see the net price, extended revenue and margin, and export the worksheet to Excel.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics 365 Finance and Supply Chain Management

## Audience

- Sales operations
- Pricing analyst
- D365 consultant

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Item number

**Key:** item

**Type:** text

#### Price group

**Key:** group

**Type:** select

**Options:**

- Retail
- Wholesale
- OEM
- Distributor
- Key account

#### Expected annual qty

**Key:** qty

**Type:** number

#### List price

**Key:** list

**Type:** number

**Format:** currency

#### Line discount %

**Key:** disc

**Type:** number

**Unit:** %

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### Net price

**Key:** net

**Type:** number

**Expr:** list * (1 - disc / 100)

**Format:** currency

#### Margin %

**Key:** margin

**Type:** number

**Expr:** (net - cost) / net * 100

**Format:** percent

#### Net revenue

**Key:** ext_net

**Type:** number

**Expr:** qty * net

**Format:** currency

#### Total cost

**Key:** ext_cost

**Type:** number

**Expr:** qty * cost

**Format:** currency

#### Valid from

**Key:** valid

**Type:** date

**Rows:**

**Item:** BRK-1001

**Group:** OEM

**Qty:** 12000

**List:** 14.9

**Disc:** 12

**Cost:** 8.4

**Valid:** 2026-11-01

**Item:** BRK-1002

**Group:** Distributor

**Qty:** 8000

**List:** 22.5

**Disc:** 18

**Cost:** 13.1

**Valid:** 2026-11-01

**Item:** HSG-2040

**Group:** Key account

**Qty:** 3000

**List:** 61

**Disc:** 10

**Cost:** 38.2

**Valid:** 2027-01-01

**Item:** SEN-3300

**Group:** Wholesale

**Qty:** 5000

**List:** 35.75

**Disc:** 15

**Cost:** 22.9

**Valid:** 2026-12-01

**Item:** KIT-9000

**Group:** Retail

**Qty:** 900

**List:** 149

**Disc:** 0

**Cost:** 96.5

**Valid:** 2026-11-15

**Totals:**

#### Total net revenue

**Key:** ext_net

**Agg:** sum

**Format:** currency

#### Total cost

**Key:** ext_cost

**Agg:** sum

**Format:** currency

#### Lowest line margin

**Key:** margin

**Agg:** min

**Format:** percent

**Summary:**

#### Gross margin

**Key:** gm

**Expr:** sum_ext_net - sum_ext_cost

**Format:** currency

**Primary:** true

#### Blended gross margin %

**Key:** gm_pct

**Expr:** gm / max(sum_ext_net, 0.0001) * 100

**Format:** percent

**Primary:** true

#### Lowest line margin %

**Key:** min_m

**Expr:** min_margin

**Format:** percent

**Notes:** Planning aid only. In D365 you enter prices through trade agreement journals under Sales and marketing, then post them. Check price group, currency, unit and date validity there.

## Content

**Intro:** Before keying lines into a price/discount agreement journal, check what they do to margin. This worksheet takes list price, discount and unit cost per item and returns net price, margin and the blended result for the whole set.

**Sections:**

#### How to use it

**Body:** Add one row per item and price group, with the list price you intend to enter in the sales price agreement and the line discount from the discount agreement. Add unit cost, ideally the current standard or average cost from the item. Margin updates for each row, and the bottom summary shows the blended gross margin weighted by expected quantity. Export to Excel to share with sales leadership before posting journals.

#### Calculations

**Body:** Net price equals list price times one minus the discount percentage. Margin percent is net minus cost, divided by net, not by cost. That is margin, not markup, and mixing the two is a classic pricing error. Net revenue and cost are quantity times net price and cost, and the blended margin divides total gross margin by total net revenue.

**Bullets:**

- Net = list x (1 - discount)
- Margin % = (net - cost) / net
- Blended margin = total gross margin / total net revenue

#### Reading the output

**Body:** Sort by margin to find lines that dilute the average, then check whether quantity justifies the discount. A small number of low-margin lines carrying large volume often drives the blended figure. If a line goes below your floor, adjust the discount or list price before it reaches the journal. Remember that rebates and freight can erode margin further and are not included here.

#### Common mistakes

**Body:** Do not compare cost at standard against a price in another currency without converting. Avoid stacking multiple discounts without checking how D365 applies them, since line discount, multiline discount and total discount agreements each have their own rules. Set valid-from dates deliberately, because overlapping agreements and missing price groups are the most common reasons a price does not appear on an order.

**Cta:** If you want automated checks that flag D365 price lines below target margin before journals are posted, Netray can build that with your D365 partner.

**Faq:**

#### Does this load into D365?

**Answer:** No. It is a planning worksheet that exports to Excel. You can use the export as input for a price/discount journal through the Excel add-in or the data management framework, after validating the columns.

#### Margin or markup?

**Answer:** This tool reports margin on net price. A 40 percent markup on cost equals about a 28.6 percent margin. Confirm which basis your company uses for targets so everyone reads the numbers the same way.

#### How do I handle several price groups?

**Answer:** Add a row per item and group combination. The blended margin then reflects your expected mix. If groups share a cost, enter the same cost on each row.

#### Why is blended margin different from the average of line margins?

**Answer:** Because it is weighted by revenue. Lines with higher volume influence the result more than small ones, which is the figure finance actually reports.

## Search terms

d365 trade agreement price template, d365 price discount agreement journal worksheet, d365 sales price margin calculation, dynamics 365 trade agreements excel, d365 customer price group discount, d365 f&o pricing worksheet, d365 price list margin check

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