Question

Problem with Commission calculation under Quote Entry – Worksheet Tab

  • 23 February 2024
  • 4 replies
  • 38 views
C

We have noticed a problem with the Commission calculation under Quote Entry, Worksheet Tab.

As commission is paid on the final sales price, the unit price to be quoted including commissioning (to ensure you get the desired mark-up or profit) should be:

(Cost of part with mark-up) / ( 1 - Commission Rate)

However it seems to be calculating as:

(Cost of part with mark-up) * ( 1 + Commission Rate)

So once the sales commission is paid, the amount of sales value that we receive is less than it should be.

The commission calculation should ensure that, after commission is paid, we receive the full sales value (which is the cost plus the mark-up).

See comparison of how Kinetic is calculating commission and how it should be calculated to ensure that we receive the full sales value (which is the cost plus the mark-up).

 

Carolina Pullen Systems Project Director

4 replies

C

See screen print of current calculation below:

 

Carolina Pullen Systems Project Director
T
Userlevel 3

Hi Carolina

Unless I’ve misunderstood I can’t see anything wrong with the Kinetic Commission calculation.

I’ve put my calculation below, if I've misunderstood anything in your explanation please correct me.

  • I have the calculation as Material + Markup = Total Price
  • In your example a 20% markup = Material * (1 + markup%) = Total Price → £9000 * 1.2 = £10800
  • It is applying a 10% commission to the Total Price. In this instance Total is £10800 * 10% = £1080
  • Total Price W/Commission (TPWC) is Total Price * ( 1 + commission%) →  £10800 * 1.1 = £11880

To Calculate Commission from Total Price w/commission the calc is:

  • TPWC - (TPWC / (1 + Commission%) → £11880 - (£11880  / ( 1 + 0.10) ) → £11880 - £10800 = £1080

In your example I can’t workout how your commission is being calculated as £1200

Are you also applying a markup to the commission?

C

Thanks for your response.

To clarify how the commission is calculates as £1200 it is using the formula provided:

(Cost of part with mark-up) / ( 1 - Commission Rate)

10,800 / (1 - 0.10) = 10,800 / 0.9 = 12,000

If we sell an order with a value of 12,000 and we pay a sales agent 10% commission, their commission will always be calculated as 10% of the “sales value”.

So the commission amount to be built into the quoted price needs to be based on the “Sales”, not on the “Total price w/commission” value.

If it is based on the “Total price w/commission” value it will be lower, which means that we won’t build enough into the quoted price to pay 10% commission on the sales value.

I hope this makes sense.

 

Carolina Pullen Systems Project Director
T
Userlevel 3

Yes it does make sense. It’s the difference between Markup and Margin. Usually commission is calculated using a markup which is what the Kinetic worksheet uses. However, I believe you are looking for the worksheet to use a margin calculation for the commission.

I’ve included additional info below and tried to summarise the differences should anyone else come across this in future and the explanation helps.

 

You are wanting to calculate a 10% Margin for the commission (based on the sales total- see my definition below). However, Commission is usually calculated using Markup. 

If we sell an order with a value of 12,000 and we pay a sales agent 10% commission, their commission will always be calculated as 10% of the “sales value”.

 

In my examples/below I've used the following definitions, which hopefully match your examples above:

  • Sale Total
    • Total Price of an order after markup/margin before any commissions
  • Sales Value:
    • Total of the sales chargeable to the customer after any commissions
  • Commission Value
    • The amount of commission payable

 

Whilst the Worksheet can use either margin (profit) or markup (using the markup or profit radio switch on the kinetic screen) against the costs of the product the commission calculation is always based on Markup.

You would need to customise this screen or replace it to use a margin calculation for the commission. 

 

So for your specific example 

Markup:

The markup calculation is as per the Kinetic worksheet.

  • Commission Value = Sale Total * Commission% → £10800 & 10% = £1080
  • Sales Value = Sale Total + Commission Value → £10800 + 1080 = £11880
  • Markup = 1 - ( Sales Value / Sale Total)
    • 1 - (11880 / 10800 ) = 10% Commission markup 

For example VAT uses a markup calculation.

  • Selling Price = Cost of goods + VAT 
  • £120 = £100 + £20
  • The total price is 120% of the Cost of Goods or 

Margin:

The Margin calculation is as your describe it:

  • Commission Value = ( Sale Total / (1 - commission margin)) - Sale Total
    • (£10800 / 0.9) - £10800 → £12000 - £10800 = £1200
  • Margin = ( Sales Value - Sale Total ) / Sales Value
    • (£12000 - £10800 ) / 12000 → £1200 / £12000 = 10% Commission Margin

So in summary a 10% Markup on the commission is different to a 10% Margin on the commission.

  • 10% Markup (9.09% Margin) on £10800 = £1080 Commission
  • 10% Margin (11.1% Markup) on £10800 = £1200 Commission

Reply


Terms & Conditions