Scenario Overview

In this scenario we have a fictitious Insurance company that rents out vehicles. The rental agreement between the Insurance company & the customer is represented by a Contract. And the Contract is made up of many (1:N) Rental Payments which are paid on a monthly basis. 

The business need here is to calculate the settlement amount when a contract is terminated early via an Early Termination Quote. The screenshot below shows where we have added two new sub-grids to the out of the box contract entity which represent, 
 

• The Rental Payments associated to this contract
• Any Early Termination Quotes associated to this contract

Business Rules

On the contract record an Early Termination Quote record can be added via the sub-grid.  The user will enter in a 'Last Rental Payment Date' on this record & click save. During the save process the settlement amount must be calculated based on certain rules, 

IF 4 or more Rental Payments are due after the Last Rental Payment Date THEN
      Find Max of either, 
                (-) 40% of Sum of Rental Payment Amounts after the Last Rental Payment Date + 
                    Early Termination Admin Fee
                (-) 4 times the Rental Payment Amount + Early Termination Fee
ELSE
     Sum of Rental Payment Amounts after the Last Rental Payment Date + Early Termination Admin Fee


* We assume all rental payment for a contract are the same value per month

Data Model

North52 Decision Suite Solution

The North52 Decision Suite solution works like this,

• A formula of type 'Save To Current Record' is created that will execute whenever a new Early Termination Quote is created
• The SmartFlow result will be placed into the Settlement Amount field on the Early Termination Quote record
•  Next Calculate the Count of Remaining Months
•  Next Calculate the Sum of the Remaining Amount
•  Next Calculate four times the standard payment
•  Next Calculate the Admin Fee
• It then checks if the remaining months are greater than 4
• If yes then it calculates the greater value of
  • 40% of the RemainingAmount + Admin Fee OR Four Times the standard payment + Admin Fee
• Otherwise it rteurns the Remaining Amount + the Admin Fee

North52 Decision Suite Steps

The following set of steps outline how to create this Formula

• Create a new formula of type 'Save To Current Record'
• Set the Source Entity to 'Early Termination Quote'
• Set the Event field to 'Create'
• Copy & paste the formula below into the formula 
• Click save & test

Formula

SmartFlow(

   SetVar('CountRemainingMonths', FindCountFD('RemainingRentalMonths', 'new_rentalpaymentid', '0' )), 

   SetVar('SumRemainingAmount', FindSumFD('RemainingRentalMonths', 'new_amount', '0' )), 

   SetVar('FourTimesPayment', 4 * FindValue('new_rentalpayment', 'new_contractid',
            [new_earlyterminationquote.new_contractid],'new_amount','0') ), 

   SetVar('AdminFee',   FindValueFD('GetAdminFee', 'new_earlyterminationfee', '0' )), 

   SmartFlowReturn(  if ( GetVar('CountRemainingMonths') >= 4 ,

                                  MaxOf( GetVar('AdminFee') + (GetVar('SumRemainingAmount')*.4)     ,
                                  GetVar('AdminFee') + GetVar('FourTimesPayment') ), 

                                  GetVar('SumRemainingAmount') + GetVar('AdminFee')
                         )
                 )
    ) 

Formula Detail - Remaining Rental Months 

The following set of steps outline how to create this Formula Detail

• Create a new formula detail record for the formula
• Set the name to be 'RemainingRentalMonths'
• Copy & paste the fetch-xml from below in the query field
• Click save

RemainingRentalMonths - Fetch-XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="new_rentalpayment">
    <attribute name="new_rentalpaymentid" />
    <attribute name="new_amount" />
     <filter type="and">
      <condition attribute="new_contractid" operator="eq"  value="@new_contractid@" />
      <condition attribute="new_duedate" operator="on-or-after" value="@new_lastrentalpaymentdate@" />
    </filter>
  </entity>
</fetch>

Formula Detail - Get Admin Fee

The following set of steps outline how to create this Formula Detail

• Create a new formula detail record for the formula
• Set the name to be 'GetAdminFee'
• Copy & paste the fetch-xml from below in the query field
• Click save

GetAdminFee - Fetch-XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <attribute name="new_earlyterminationfee" />
    <link-entity name="contract" from="customerid" to="accountid" alias="aa">
      <filter type="and">
        <condition attribute="contractid" operator="eq"  value="@new_contractid@" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Wizard - FindCountFD

Please see below the wizard you can use to create the FindCountFD() function call used in this formula. 

Note that for Formula Detail (Query ) , you will need to have created the Fetch Xml beforehand.