...
Each line items discount is calculated as a "variance" (the discount includes Customer Group discounts and discounts applied to the total value of the order. If you want to exclude customer group discounts you will need to edit the discount formula using a Vlookup)
The variance will fall under 1 of the above discount tiers, and the commission is applied to that line item according to the tier.
...
In Tab Commissions you can edit the below formulas to Calculate the commission accordingly
- Cell J11 A11 you can enter a formula to calculate the commission
- Cell K11 B11 will have a formula to calculate your discount
- Cell L11 D11 you can enter an equation to have the Commission % in OR you can have this fill in from the Calculations tab (in the example below the heading is GP% Discount this can alternatively be discount)GP if you require)
If you do not want to include customer group discounts in your commissions you can use column I11, Create a Vlookup to lookup the discount group and discount % and minus the customer discount from your discount% in column B11.
*NB Disregard all other information on this Excel Tab prior to column J After D (this is imported to give an example on how the commission will calculate)
In Tab Calculations
- Cell C4 and down is where you have your Inventory Group Codes
...