BLOG SPOT

01/27/2020

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 www.2ndgear.com.

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

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.

GP Dynamics eConnect Integration At Sessions Payroll

A successful conclusion for Framework Solutions helping Sessions Payroll (Hollywood’s number one movie extra payroll bureau) to integrate their custom .Net Payroll Portal with GP Dynamics for fully automated accounting.

Biorepository Database Software At Disney Cancer Center

Framework Solutions deploys it's custom-built bio-repository cancer research database software application into the Disney Cancer Center in Burbank, enabling researchers to better track and process patients and specimens.

Framework Solutions Custom Software Developer Los Angeles