Answers - Configuration Challenge #R3
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.
Related Articles
Answers - Configuration Challenge #R2
Challenge Here is a link to the original challenge. Answers The GetDate() function gets the day component of the calculation's start date (ex: the day component of 2021-10-15 is 15). The business purpose is to use a lower quota of 8000 when the ...Answers - Configuration Challenge #R1
Challenge Here is a link to the original challenge. Answers First code block The business purpose is to exclude transactions whose policy ID is strictly more than 8797. This logic could also be moved to a transaction saved query as an alternative ...Configuration Challenge #R3
Goal The goal of this configuration challenge is to get your familiar with various formulas. For this challenge, you will analyze formulas and try to determine why they were added. Comments were removed to make the task more difficult. Difficulty ...Configuration Challenge #R2
Goal The goal of this configuration challenge is to get your familiar with various formulas. For this challenge, you will analyze formulas and try to determine why they were added. Comments were removed to make the task more difficult. Difficulty ...Configuration Challenge #R1
Goal The goal of this configuration challenge is to get your familiar with various formulas. For this challenge, you will analyze formulas and try to determine why they were added. Comments were removed to make the task more difficult. Difficulty ...