Production Adjustments in ICM model - looking for ideas

Context:

  • We have an ICM (Incentive Compensation Management) model with a DAT03 module that is an upload (from our data hub > SQL query) of all the monthly transactions we need to pay IC on. It includes all the production amounts, sales reps, product type and many other dimensions.
  • Our module also applies production mounts to several (2-5) beneficiaries who receive production roll-ups of a given transaction.
  • There are various reasons why we need to occasionally adjust production amounts to something other than what the data feed is showing.
  • Currently, to accomplish these production adjustments, we manually check a Boolean to trigger an adjustment, manually enter the amount being adjusted, and enter a free form reason for the adjustment.
  • The manual production adjustments do not occur at the lowest level of dimensionality (DAT03 - i.e. all production data transactions), it occurs at a higher level called the product suite level, or the super suite level.
  • Our IC calculations have a pre-adjustment line item, and a post-adjustment line item, with the post-adjustment item factoring in the manual adjustment amount.

Problems with this process:

  • It requires manual math to determine all the correct transactions you want to adjust and add the amounts up. This can be prone to manual error.
  • With only a free form "adjustment reason" field, it makes looking back at prior adjustments, and reporting, nearly impossible to track.  Adding it policy numbers or product types would be more manual work that we wish to avoid.
  • We don't want to add many more fields to our DAT03 module (all production data transactions), because it's already large, and doing so would add decent amount of model space and complexity.
  • Adjustments have to be entered at all levels of our beneficiary roll-ups - which can be up to 5 separate times in some instances.

Anyone have ideas for a better process?

 

@Aaron.wasinger