Identify Cancelling Transactions Based on Unique ID

[mysticmoron109]

I have no idea how to explain this in excel terms but the desired result it is to capture "New business" only.

In Column A we have Policy Numbers
In Column H we have Transaction Types (New Policy, New, Cancellation, Reinstatement, Renewal, Extension)
In Column EB we have the $ amount of the transaction.

What I am trying to accomplish is to identify New Business only. Unfortunately It's not as simple as doing a sum of "New" and subtracting the sum of "Cancel" because some cancels relate to policies which doesnt have a new/renewal in the same period. I.e, some cancellations are standalone cancellations.

For Example, Let's say you had the following policies and transactions:

1) Policy A New 100
Policy A Cancel 100

The New business here would be 0

2) Policy B New 200

The New Business here would be 0

3) Policy C Cancel 500
No other occurrence of "policy C" found in the data, therefore this should be ignored.

Hopefully that makes sense and I have attached a somewhat representative spreadsheet


Thanks in advance

[loginjmor]

Hi -

I'm not sure I understand what you are doing. Example 1 seems straight forward in that new business was issued and then cancelled so that would be 0. In your example 2, Policy B, New is 200, but New Business is 0? I don't get it.

Looking at your data, the Issued As column has the following transactions:

Cancellation
Cancelled
Endorsement
Extension
Issued As
New
New Policy
Reinstated
Reinstatement
Renewal

Which of these transaction pairs or combinations are you trying to sum? What is the difference between Cancellation and Cancelled? New and New Policy?

[Glenn Kennedy]

One of the problems associated with posting a "sample excel file" with more than 14,000 rows is that no-one can really check the results to verify that they're correct. It's much easier if you post a sample of 10-20 rows covering the possible scenarios..

So here's something, based on a Pivot Table, for you to look over. No guarantees about its accuracy.