Answers - Configuration Challenge #R3

Answers - Configuration Challenge #R3

Challenge

Here is a link to the original challenge.

Answers

• The business purpose is to calculate a recoverable draw repayment amount.
• A recoverable draw consists of advances which need to be repaid.
  • A recoverable reward with a positive value is an advance.
  • A recoverable reward with a negative value is a repayment. 
  • Each rep's owed balance is the sum of granted recoverable rewards (which may be granted at different times). Normally, this balance would either be zero or be positive.
  • There can be many strategies in terms of repayment (ex: repay as much as possible, capped repayment, etc.).
• In this example, we want all commissions received in the current period to be used for draw repayment. For example, if a rep had an owed balance of $100, and earned commissions were $80, we would consume the full $80 earned in commissions to partially repay the draw. The new balance would be $20.  
• First, we calculate the balance owed by the current payee. Note that table "Rewards" is automatically scoped to the current payee.
  • We sum the value of rewards marked as Recoverable = true, whose calculation end date is NOT later than the current calculation's end date.
  • The [end_date] condition ensures we examine all recoverable advances from current and past periods, but not later periods.
  • If the balance is zero or less, we can exit immediately since no repayment is due.
• Second, we calculate how much has been earned in commissions (recoverable or not) for the current period.
  • We sum the value of rewards received within the current period.
  • The [in_period] condition ensures we only take into account commissions earned within the current period. Indeed, we can only use in-period earned commissions to repay the recoverable owed balance. 
• Third, we check if the amount earned in commissions is more than the balance. If so, the balance can be repaid in full. Otherwise, we consume the entire earned amount as a repayment.
  • This IIF() condition ensures we do not pay MORE than the balance (ex: a rep had an owed balance of $100, and earned commissions were $120, we want to only consume $100 to fully repay the balance).
  • If the amount earned is more than the balance, we only want to repay the balance's amount (in full).
  • If the amount earned is less than the balance, we want to consume the entire earned amount to repay the balance.
  • Many other repayment schemes are possible but this is the most popular setup.