D365 Trade Agreement Price and Margin Worksheet
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.
| Item number | Price group | Expected annual qty | List price | Line discount % (%) | Unit cost | Net price | Margin % | Net revenue | Total cost | Valid from | |
|---|---|---|---|---|---|---|---|---|---|---|---|
$13 | 35.9% | $157,344 | $100,800 | ||||||||
$18 | 29% | $147,600 | $104,800 | ||||||||
$55 | 30.4% | $164,700 | $114,600 | ||||||||
$30 | 24.6% | $151,938 | $114,500 | ||||||||
$149 | 35.2% | $134,100 | $86,850 |
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.
Pricing exception checks
Optional: an expert sets up read-only reports that flag D365 price lines below your margin floor.
Optional. The tool and every export work without this. No spam.
How to use it
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
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.
- Net = list x (1 - discount)
- Margin % = (net - cost) / net
- Blended margin = total gross margin / total net revenue
Reading the output
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
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.
Frequently Asked Questions
Does this load into D365?
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?
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?
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?
Because it is weighted by revenue. Lines with higher volume influence the result more than small ones, which is the figure finance actually reports.
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.
Related Tools
D365 Standard Cost Rollup Calculator
Check a standard cost for one manufactured item, from material and scrap through setup, run time and overhead, and see how lot size moves it.
Microsoft DynamicsD365 Production Order Cost Variance Calculator
Break a production order variance into material price, material usage, labor rate and labor efficiency so you can see the real driver.
Microsoft DynamicsD365 Finance and Supply Chain License Cost Calculator
Estimate monthly, annual and term cost for D365 Finance and Supply Chain users, including attach licenses, team members and discount.
Microsoft DynamicsD365 Fit-Gap Analysis Document Template
A fill-in fit-gap document for D365 projects covering scope, requirement rows, fit classification, gap approach, effort and sign-off.
More Microsoft Dynamics tools
See all- D365 Batch Job Window and Runtime Calculator
- D365 Item Coverage Min Max and Safety Stock Calculator
- D365 Data Migration Cutover Duration Calculator
- D365 F&O Manufacturing Data Entities Reference
- D365 F&O OData URL Builder
- D365 Production Order Status Reference
- D365 Data Management Import Worksheet Template
- D365 Batch Job Checklist
- D365 Security Role Design Worksheet