Objective:
In this article, we will be taking a look at how you can calculate the total value of your deposit or loan conversions in Core iQ. We will explore the following topics:
- How to pull a list of your conversions and a list of your new accounts
- How to use Excel to merge these two lists into a master list of the total value for your new conversions
How to find your loan/deposit conversions in Core iQ
In order to find your conversions in Core iQ, you will need to use the Outbound Communication Status report. In a separate article, found HERE, we walk you through how to run this report and what data is available to you there. For the purposes of this article, we will assume that you have run the Outbound Communication Status report for all of your Loan/Deposit conversions. Export the results of this report to Excel, as this will be what we use for our main working document.
How to find the total balance for a customer's new loan/deposit account
To find the second set of data that we need in order to calculate the value of our conversions, we need to run an additional report for each related Service Group. In this case, we are looking at loan and deposit products, so we will want to run the Account Listing report for both our Loan products and our Core Deposit products. The Account Listing report is similar across all five of the major groups, so we will walk through only one version of the report here.
Let's assume that you are wanting to track the value of your conversions for March, relating to the Consumer Loan product offers that you sent from Core iQ. We would want to navigate to the Loan Account Listing report to pull a list of all the Consumer Loan accounts that were opened in March. To do so, we would use the following fields:
- Service Grouping - This field would need to be set to whatever product group we are viewing conversions for. This would be the same product group that is tied to all of our Product Offer templates. In this case, that is our 'Consumer Loan' service group.
- Current Status - We will want to make sure that this field is set to 'Open and Active' to include only the Active loans from Core iQ, not those that have been Closed or Charged Off.
- Date Range - For this field, we want to isolate only the accounts that were Opened during the conversion window that we set for our template. If our conversion window was set to only include the month of March, we would want to use the dates shown in the above image.
Once we have the criteria set for the report (and any others that you might want to add), we will run our Account Listing report and export the results to Excel. Copy your list of loan accounts to a new tab in the Excel document that you downloaded from the Outbound Communication Status report and name that tab 'Conversions'. You should now have a list of all your loan conversions and new loan accounts within a single Excel workbook. Using this data, we can create a VLookup to pull our data together.
How to pull this data together to calculate total value
Navigate back to your Outbound Communication Status report and go to the Communications tab. Here, we have a list of all the conversions that are attributed to our product offer template, and we will want to add our Loan Origination Amount to this table. To do so, we will create a new column in the Communications tab, right after the 'Conversion' header (column AB). We will give this column a header of 'Value'. In this column, we will input the following value:
=VLOOKUP($G2, Conversions!D$2:Z$12,21,FALSE)
This will look at the value found in cell G2 (our Customer No. column) and will search our Conversions tab to find the Origination Amount. The reason that certain values here are red is that they are potentially different depending on which report you have run. This VLOOKUP formula breaks down as follows:
- The first value is the cell that starts our Customer No. column. This is always G2.
- The second value is the range of the other sheet that holds the values we want to look up. You will want to start this range with the first value of the Customer No. column in the Conversion tab and end it with the last value of the column you want to return an amount for.
- For the Loan Account Listing report, this is column Y (Origination Amount).
- For the Core Deposit Account Listing report, this column is either Y (Available Balance) or Z (Current Balance).
- For the Timed Deposit Account Listing report, this is column Y (Original Amount).
- The next value in our formula is the column number that we want to return. Count the number of columns from the start of your above range to the end. It should either be 21 or 22, depending on the Account Listing report that you are using.
- The last value can just be set to FALSE
If you followed the above instructions, you should be able to drag your formula down the new 'Value' column and Excel will match Customer No. values between the two sheets. What you are left with is a list of all the balance information from your Account Listing report. This can be used to calculate a total value for your conversions, or an average balance value per conversion.