How to calculate margin based commissions when your accounting system lacks inventory cost data

The more automated commission calculations are, the less opportunity there is for subjective amounts to be assigned to a sale, and the safer management can feel about the numbers. That said, it’s not uncommon for accounting systems to lack comprehensive cost data in the inventory module.

To calculate the commission on a transaction you first need to know the margin, and to know that you need to know the cost of the items being sold. In a more simple scenario, where products for sale have been received through a purchase order process its straight forward to ensure the inventory has costs associated with them in the system. However, what if the parts inside the products for sale have been salvaged from other products?

We faced this exact dilemma at our customer, refurbished computer retailer 2NDGEAR. We assessed a variety of options including literally trying to tag hundreds of thousands of inventory items in the ERP with valid cost data. Eventually we settled on a simple and practical solution.

2NDGEAR is a subsidiary of parent company Insight Investments, Inc. headquartered in Irvine, Orange County CA. Insight Investments is one of the largest computer hardware leasing businesses in the USA and when those computers come back off lease, the 2NDGEAR division salvages parts from some and upgrades certain models in order to sell them. You can see 2NDGEAR’s low cost refurbished computer offerings at www.2ndgear.com.

There were a number challenges to automate the commission calculations for 2ndgear sales orders. One model computer can have a whole host of parts options in terms of chip, memory, and hard drive. On top of this the computer might have parts swapped out and upgraded during the sales process. A good example of this would be the replacement of the hard drive with a solid state drive. Another challenge was presented with the requirement that sales reps working in Microsoft Dynamics 365 CRM were selling a mixture of new and refurbished products from a list of under 1000 offerings, while in the Dynamics GP ERP accounting system there were well over a hundred thousand inventory products and parts.

Utilizing our middleware portal technology our process engineers devised a simple solution to this seemingly impassable problem, We had already provided two key features that we were able to enhance to solve this problem:

1. We had created a screen with built in workflows which enabled sales administrators to pick options from the inventory in GP Dynamics and push them to Dynamics 365 CRM. For example a particular computer model from Dell with some of its possible configurations in terms of parts and accessories.

2. We had created another screen with workflows to enable the purchasing department (who would buy items not available off-lease) to create a list of items for sale and set current market prices.

With these features in place, our team devised a strategy to record fair market cost (FMC) for off-lease and reconfigured equipment.

We then modified the Dynamics 365 CRM to make API calls to our middleware portal during order entry in order to retrieve the FMC and give a sales rep a “preview” of the commission. This API call was enhanced to also pull actual cost data from Dynamics GP, when available for items that had been purchased. Upgrade items like solid state drives (SSD) also had a FMC associated with them.

In the refurbished computer business it is common for orders to be reallocated against other available inventory items and parts. Hence our solution had to use the same technique recalculate margins and commissions once the sales order was in Dynamics GP.

The net result was automated commission calculations for all 2NDGEAR sales orders, and vastly improved cost, margin and profit reporting.