Alister Harris

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

Management likes automated commission calculations

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.

Are margin and commission calculations a challenge for your business?

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?

Our process engineers architected and delivered a practical solution in just 2 months!

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 sells refurbished off-lease refurbished computers

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

How much is an off-lease computer or computer part worth?

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.
How did we automate these commission calculations?

How did we automate these commission calculations?

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.

Recent Framework Blog Posts

Cast and Crew Union Rules Payroll Software

With union payroll rules notoriously complicated to adhere to, and cast and crew union payroll rules for the film industry likely the most difficult of all, we set out to design a cloud-based system that would automate away the complexity.

Lease Transaction Processing System for Insight Investments

Framework Solutions launches the DealPro Lease Transaction Processing System for Insight Investments Corp. DealPro is a cloud-based MVC.NET portal application which enables users to pre-process and submit transaction to the internal ERP.

Salesforce Dynamics GP Integration Portal for RED8

Salesforce to Dynamics GP integration Portal Solution released by Framework Solutions for RED8, a division of Insight Investments located in Irvine California. Introducing a Custom Middleware Portal with API connections to both Salesforce and GP has dramatically reduced order processing time..

Why You Should Integrate CRM With Your Website

CRM integration can help manage and keep track of all user interactions that occur on your website, whilst capturing customer data, and making it accessible (usable) to your business.

Framework Portal Integrates Microsoft Dynamics GP

Framework portal solution integrates Microsoft Dynamics CRM with Dynamics GP for computer reseller 2NDGEAR, a rapidly expanding division of Orange County company Insight Investments.

2NDGEAR Migrate to Microsoft’s Dynamics 365 Online.

Framework Solutions, have just announced their successful online migration of an old CRM On-Premise 2011 system with Microsoft Dynamics 365 for the subsidiary organization of Insight Investment, 2NDGear.

Framework Solutions Custom Software Developer Los Angeles