Home > Dynamics AX > Manufacturer Sponsored Rebate (MSR) Module in Microsoft Dynamics AX 2012

Manufacturer Sponsored Rebate (MSR) Module in Microsoft Dynamics AX 2012

We recently completed a large supply chain implementation that had complex requirements to calculate manufacturers’ rebates and claims. These rebates and claims were important because they had an impact on customer pricing and salesperson commissions.   

To meet these requirements, Ignify developed a module that is tightly integrated with the other modules of Microsoft Dynamics AX 2012 for configuring rebates given by the manufacturers to their resellers. This module is designed to calculate and claim rebates from the manufacturers, with rebates calculated at the time of sales order creation, thus helping the sales representative to quote the competitive price to the customer.  

A service hosted by this module can be also used in a third party application used for creating sales orders. The rebates are stored as claims to the vendor or manufacturer in the MSR module. They are claimed to the manufacturer and are also reconciled back to approved or rejected claims.

Characteristics:

  • The rebates can be configured for a Manufacturer or a Group of Manufacturers. Manufacturer is the vendor entity in Microsoft Dynamics AX 2012.
  • The rebates can be configured for individual item, group of items, partial string matching an item, and by a category under which many items fall.
  • Rebates can be configured by the financial dimensions that match the dimensions on the sales line.
  • Rebates are date controlled by having effective start and end period. The sales orders shipped during the active life of the rebate program are eligible for rebates.
  • The rebates can have constraint based setup such as an allowed maximum quantity and allowed maximum amount. Once the maximum amount or quantity is claimed, the rebate is no longer active. It also has minimum constraints for quantity that is needed for it to qualify.

Rebate Types:

There are primarily four types of rebates as described below:

  1. Individual Item Type: Line or Bids.

This rebate has a specific bid price or calculation rule based on the price or bid of an item. The calculation rules can be based on percentage of price, or percentage of bid price provided by the manufacturer. Usually a bid file is integrated from the manufacturer that contains the latest bids and prices.

  1. Combination

Get a rebate on selling two or more items, forming a bundle.
E.g. A laptop with an external disk combo is eligible for $10. In this case, the amount would be split equally for both the products – i.e. $5 each.

  1. Mix and Match:

One item from every group must be sold to get the rebate.
E.g. If the setup of items is as below:

Mix and Match Group Items
A iPod
A Headphones
B Laptop

A laptop can be combined with either an iPod or headphones to be eligible for a rebate.  At least one item from group A and B must be present to qualify for a rebate.

  1. Buy X, Get Y Free:

Get item Y free with selling item X. Rebate is the cost price of Y.

Rebate Stacking:

Sometimes more than one promotion is given by manufacturers to promote their products during a holiday or through web promotions. This can be set up in the rebate module as a stack. Stacked rebates mean that more than one rebate can be applied to the product during a period of time. For instance, a web promotion that gives an additional 10% discount to the original bid price.

Optimistic Algorithm:

The rebates are always calculated based on the best combination to give the best price to the customers. A customer may place many items in a sales order and the rebates are calculated based on a single order. Some rebates are also configured to look at the past sales to that customer and then the price is decided accordingly. These are called tiers – where you can have promotions that have a minimum and maximum quantity to qualify. For example, a customer gets a 10% discount for the first 100 quantities, and after that 15% for the next 1,000 quantities. The calculation, when considered with the stacking and tiers, gives the best result to the customer.

The calculation is based on best fit algorithm. To understand this, let us use a scenario. The items on the sales order consist of Apple products, as shown below. There are two active rebates, R1 and R2, configured individually and in combination, respectively. R1 gives rebates to individual items, and R2 gives rebates for a combination of items if sold in combination.

Items master
Iphone4S
Iphone4G
IPad32
Sales Order
Items master Qty
Iphone4S 10
Iphone4G 20
IPad32 10
Rebate :R1 Line Type (individual based)
Items Amount
Iphone4S 10
Iphone4G 20
IPad32 10
Rebate :R2 Combo Type (bundles)
Items Amount
Iphone4S  
IPad32  

 

Bundled Rebate: 120
Sales Order
Item Qty Rebate Rebate Amt
Iphone4S 10 R2 600
IPad32 10 R2 600
Iphone4G 10 R1 200
IPad32 10 R1 300
Total rebate 1700

The best way to allocate the rebate to get the highest rebate would be allocating as many combinations as possible on the SO. Note that only 10 combination rebates can be reserved against the order as the IPAD32 is only 10 pieces on the SO. The remaining 10 pieces of IPAD32 can be given a rebate from R1 at $30 per piece.  Thus the above table shows how R1 and R2 are combined to fulfill the rebate of the sales order.

Now let’s make it more complex by making R1 and R2 stacked rebates. Say that R1 is a web promotion for a limited period. If the above sales order was to calculate based on stacking, the allocation would look like:

Sales Order
Item Qty Rebate Rebate Amt
Iphone4S 10 R2 600
IPad32 10 R2 600
Iphone4G 10 R1 200
IPad32 20 R1 600

Note that the IPad32 would fully use promotion R1 and overlap the promotion R2 as they are stacked. Thus the stacked rebate amount would be $2,000 and this is the best among both calculations, so this is allocated to the sales order.

EDI Support:

The rebates can be updated by EDI files. There are two types of files for each manufacturer: the bid file and the price file. The bid file contains the bid information for all the items to various customers, and the price file contains the net and list price of the items. These files are loaded periodically through the EDI feeds and are used as the basis of rebate programs. EDI staging tables and services are hosted by this module.

Estimate on a Sales Order:

The estimates of rebates are shown on the sales order through a service call. They can also be obtained in a third party application through a live web service hosted from Microsoft Dynamics AX. If the item is a type of bill-of-materials (BOM), the estimates of all its components are also shown, even if it has not been exploded.

Calculating and Processing the Claims:

The rebate claims can be calculated for shipments for a defined period. They can be recalculated if better known rebates are available. If the items are returned, this module will adjust the transaction and finally the vendor account.

The claims transactions can be reviewed and adjusted before submitting to the vendor. When the claims are ready for submitting to the manufacturer, a claim journal is created for all the claims that are ready. The amount is posted in a GL transaction to debit the vendor (AR linked customer) balance. The claims are extracted into a file at a claim location and shared with the manufacturer. Receivable and P&L postings are set up for review and posting when a claim is submitted. Offset to AR posing is user definable in the rebate module parameters. An AR customer is determined by an address book relationship to the manufacturer AP vendor form. There is full traceability from the financial journal to claim transactions and back.

Settlement:

The manufacturer gets the claim file and usually makes the payment that is captured in the payment journal. The claims can either be fully paid, partially paid, or rejected. They are reconciled based on input from the manufacturer. The manufacturer pays the claim either through AP credit or direct AR funding.

Payment per claim transaction information is imported from Excel and reconciled to claims submitted.  Short pays or denied claims can be written off by creating a write off journal.  Over pay can be written on by creating a write on journal to credit the vendor (AR customer). Short or denied claims can be marked to adjust the salesperson’s commission calculation accordingly.

If you have any questions on rebate functionality in Microsoft Dynamics AX, please email us at dynamics@ignify.com.

Parthav Patel is a Sr. Technical Analyst of Microsoft Dynamics AX and Michael Gabriel is a Sr. Solutions Architect of Microsoft Dynamics AX at Ignify. Ignify is a technology provider of ERPCRMeCommerce and Point of Sale software solutions to organizations. Ignify has won the worldwide Microsoft Partner of the Year Award in 2013, 2012 and 2011. Ignify has been included as the fastest growing business in North America for 7 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine from 2007 to 2013.

  1. No comments yet.
  1. No trackbacks yet.