retention reporting- calculate new business lost business retention using excel 2007 macro

7/31/2019 Retention Reporting- Calculate New Business Lost Business Retention Using Excel 2007 Macro

1/16

SEMINAR HANDOUT

Retention Reporting:Calculate New Business,

Lost Business & Retention

using Excel 2007 PivotTables & Macro


2/16

We are committed to meeting the needs o our

agent partners with a wide array o innovativetechnology solutions that can help empower

them to turn challenges into opportunities, write

more business and grow proftably.

The Hartord is a proud sponsor o the 2011ASCnet TENCon. Please visit us at Booth 118

or contact our Agency Interace Services sup-

port team toll-ree at (877) 322-4833 or email

[email protected].

To learn more about becoming a Hartord P&C

agency, contact our New

Appointment team toll-ree

at (877) 853-2582, x 5601.

Our Commitment.

Our Technology Solutions.Your Success.

acebook.com/TheHartord

linkedin.com/company/2467


3/16


4/16

Retention Reporting: Calculate New Business, Lost Business & Retention Using Excel 2007

September, 2011 3

Class Description: One of the most important pieces of information affecting long termagency profitability and success is the regular tracking of retention information. Yet, inthe vast majority of agencies, running this report is the exception rather than the rule.The reason for this is simple. Up until now such reporting has been difficult and timeconsuming. This class and tool will help eliminate much of this difficulty. Bring your

laptop loaded with Excel 2007 and use the sample Excel workbook provided to practicethe skills learned during the session.

Learning Outcomes:

Define Retention, New Business, Lost Business as they relate to the Macro tool. Apply Excel Pivot Tables and special Macro to TAM, Vision, Doris or Epic data Evaluate current data integrity and variety of scenarios affecting the ability to

obtain accurate retention data from Billing Screen or Production data.

Assumptions: This seminar is based on the followingTAM Version 11.X

Microsoft Excel Version 2007Sample Workbook: General_Microsoft Products_Excel_Retention CalculationsUsing Excel Sample Workbook.xls

Table of Contents

Retention Reporting ........................................................................................................ 4Sample Summary Report ................................................................................................ 4Definitions ....................................................................................................................... 5

Retention ..................................................................................................................... 5

New Business .............................................................................................................. 5Lost Business .............................................................................................................. 5Difference on Retained Accounts ................................................................................ 5

Assumption ..................................................................................................................... 5Data Sources .................................................................................................................. 6

Transactions (Production Report): ............................................................................... 6Billing (or Policy Detail) Screens: ................................................................................. 6

APPENDIX ...................................................................................................................... 8RETENTION WORKBOOK Sample ................................................................................ 8

Step 1 Collect Data Sources ..................................................................................... 8Step 2 Add Data Column for Comparison ................................................................. 8

Step 3 Combine the Two Worksheets into One ........................................................ 9Step 4 Insert a Pivot Table ..................................................................................... 10Step 5 Copy and Paste Pivot Table Data to new worksheet ................................... 11Step 6 Run Macro ................................................................................................... 11Step 7 - Summary Sheet ........................................................................................... 12

APPENDIX II ................................................................................................................. 13Sample Search-Customers, Policies Current Criteria/Fields ......................................... 13


5/16


September, 2011 4

Retention Reporting

One of the most important pieces of information affecting long term agency profitabilityand success is the regular tracking of retention information. Yet, in the vast majority ofagencies, running this report is the exception rather than the rule. The reason for this is

simple: up until now such reporting has been difficult and time consuming.

The purpose of this Excel tool, created by Sue Good of Alexant Systems Corporation,an ASCnet Consultant Associate Member, for ASCnet members, will help eliminatemuch of this difficulty. We can use Production or Billing Screen data to calculate notonly Retention, but also New Business, Lost Business, and the Difference on RetainedAccounts.

Sample Summary Report

Heres what a summary report looks like:

We can easily run a report in TAM that will give us the same commission difference asthe example, but of the $16,831 that our commissions went down, wouldnt you want toknow that you had $8,122 in New commissions, but had $18,325 due to Lost Business,and of the accounts you kept, the commissions went down by $6,628? In this example,we need to write approximately $25,000 in new commissions to make up for thecommissions we lost.

Follow the steps in the appendix to collect your data, use Pivot Tables to total dataquickly by customer or policy, and run the reports using the Retention Macro. A macrois recorded keystrokes and programming in Excel to automate tasks, like the formulasand formatting needed to do the calculations.

Use the power of Pivot Tables to quickly collect this same information by Agency,Branch, Producer, Billing Company, Policy Type, Department, etc. Just Copy and


6/16


September, 2011 5

Paste the data from the Pivot Table into a new sheet in your Retention Workbook, andrun the Macro again.

Definitions

RetentionLets start with a basic definition of Retention, and then look at how we can measure itusing data from our management system. In THIS tool, the definition of Retention iswhat percentage of Customers or Policies did we keep between two points in time? Or,stated another way, the macro will take the number of Customers or Policies we had atPeriod 1 less the number we lost between Period 1 and Period 2, divided by the numberwe had at Period 1.

For example, at 12/31/2009, we had 3,000 Personal Lines customers, and at06/30/2010, we had 3,300. So, the customer count went up by 10%. Looks great,right? Maybe we actually LOST 300 customers, but wrote 600 NEW customers during

those six months. The net difference is still 300 more customers, but our RetentionRatio would be 90% (3,000 minus 300, divided by 3,000). New customers areextremely important to grow our business, but retaining existing customers provides thefoundation.

So, to calculate Retention, we need to know two things how many did we start with,and how many did we lose? How can we tell exactly how many customers we lost inthe first six months of 2010?

New Business

When comparing one period of data to another, the macro makes the assumption that if

there is NO data in Period 1, but there IS data in Period 2, its New Business.

Lost Business

When comparing one period of data to another, the macro makes the assumption that ifthere IS data in Period 1, but there is NO data in Period 2, its Lost Business.

Difference on Retained Accounts

When comparing one period of data to another, the macro calculates the difference ifthere IS data in Period 1, and there IS data in Period 2.

Assumption

When the macro encounters a zero or negative number, it will ignore the value. Forexample, if the amount in the pivot table from the billing screen or transaction is zero orless, this policy or customer should not be included in our count of policies orcustomers when calculating Retention.


7/16


September, 2011 6

Data Sources

This is a powerful tool, but you need to understand the data you are using to compareone period to another. There are two sources of data you can pull the information fromto use this tool: Transactions (Production Report) and Billing Screens (Book ofBusiness or Search-Policies Current).

Transactions (Production Report):

When comparing two periods of data based on transactions, understand the datesavailable in a transaction record to be sure you are getting the information you want.

Transaction Effective Date: this defaults to the Policy Effective Date unless it ischanged, as when invoicing an Endorsement, Audit or Cancellation.

Transaction Date: the date the transaction is created, or printed in the case ofan installment transaction.

Accounting Month use this date to reconcile the totals to Income numbers onthe income statement.

There are challenges with using Production Data to compare one period to another.One challenge is timing differences. Were ALL the policies invoiced in the sameAccounting Month each year? Theres a good chance they werent, especially if you arerecording Direct Bill Commissions from the commission statements. You arent gettingpaid until the client pays the company, and even then, you may be paid on installments.

Another challenge when basing your analysis on transaction date or accounting month,is Audit transactions, since we all know that audits are transacted after the policy periodand could disguise the fact that an account has been lost.

The Transaction Effective Date can be the more reliable date to use when comparing

one period to another, but be aware the monthly totals will not match the commissionincome on your Income Statement for those months (Accounting Month should matchIncome Statement).

Billing (or Policy Detail) Screens:

A Book of Business Report or Search-Customers-Policies Current is a snapshot of thebilling screens at the moment the report is run. If this report is run every month at thesame time (for example, at month end), and using the same criteria, then it can becompared to the same snapshot in the future.

Now, do you trust your Billing Screens to be an accurate reflection of the policy status,policy premium, agency commission and producer commission? If you answered yes,then you are able to get powerful information from your data. If you answered no, itstime to audit your Billing Screens, and clean them up.

Tip: Even if youre not ready to try the Retention Macro right away, start running amonthly Book of Business or Search-Policies Current with the Output Destination ofExcel-Raw Data each month, and save it. When you are ready, you will have the data


8/16


September, 2011 7

from earlier periods to compare to the current period. SeeAppendix IIfor setup of asample Search-Customers, Policies-Current report.

One of the biggest gotchas when calculating Retention by comparing Billing Screendata from one period to another is if your agency adds a new Billing Screen when a

policy is rewritten from one carrier to another. When the macro compares the twosnapshots at the policy level, it will look like the original policy was lost, and the rewriteis new. This is not a valid statement, since it is a renewal to the agency.

A workaround is to combine (using an Excel tool called Concatenate) the CustomerCode and Policy Type into a new column in your spreadsheet, and then use the PivotTable to get the totals per period. In this case, the gotcha is that it will not catch thesituations where, for example, a client had four Fire policies in one period, but only threeFire policies in the second period.

Another gotcha is with Customer renames. If a customer has been renamed to

another Customer Code between the two periods, it may appear as the originalcustomer was lost, and the renamed customer is new.

As you can see, there is no PERFECT way to do this. Retention reporting iscomplicated because of the tremendous variety of billing and timing scenarios thataffect each of our agencies. Factor in download and varying workflows and you have alot of challenges to calculating a true retention for your agency, carriers and producers.But, using the power of Pivot Tables and this macro enabled Excel workbook, you nowhave one really good way to start the process of calculating retention. From there, youcan refine your workflows and data input to get you on the right road to having betterdata and, ultimately, good retention numbers for your agency.


9/16


September, 2011 8

APPENDIX

RETENTION WORKBOOK Sample

In our example, well be comparing a billing screen report (Book of Business or Search-

Customers, Policies Current) from 12/31/09 to 07/31/10. We want to see at theCustomer Level and the Policy Level how our Book of Business has changed. Here arethe steps we need to complete:

1. Collect Data Sources2. Add Data Column to each Data Source for Comparison Purposes3. Combine Worksheets4. Create Pivot Table5. Copy & Paste Pivot Table data into new Worksheet6. Run Retention Macro to calculate New Business, Lost Business, Difference in

Retained and Retention

Step 1 Collect Data SourcesCollect two sources of data to compare, and copy and paste each report into aworksheet in the RetentionWorkbook. You can name each sheet by [Right Clicking]on the Sheet Name (Sheet1) and choosing Rename.

This has already been done in the sample Workbook. You have two worksheets titledBOB12/31/09 and BOB07/31/10 Look at the bottom of the Workbook to see thesheet names. These were reports exported to Excel from a Search, Customers Policies Current for Active Policy Statuses. One report was run on 12/31/09 and otheron 07/31/10.

A Search, Customers-Policies Current, like a Book of Business report, is a snapshot intime since it pulls its data from policy billing screens. By exporting the report to Excelevery month, you can now compare one snapshot to another snapshot whenever youwant. SeeAppendix IIfor setup of a sample Search-Customers, Policies-Currentreport.

Step 2 Add Data Column for ComparisonWe need to do this since these are two snapshots we want to compare. If we justcombined two snapshots of billing screen data, how would we be able to tell which

billing screen was from which snapshot? If you are using Production Data, you maynot need this step if you can do the comparison you want using an existing field in yourspreadsheet (for example, Transaction Effective Date). In that case, you arentcomparing snapshots.

Add Data Column to Worksheet BOB12/31/09

Highlight [Left click] Column A [Right click], Inserto This will insert a blank column to the left of Column A.


10/16


September, 2011 9

Type Period in Cell A1 Type 1 in Cell A2. Place your cursor in the bottom right hand corner of Cell A2. When the cursor

changes to a plus sign, you can [Double Click] and all the cells in Column A willfill with 1 (Autofill)

Add Data Column to Worksheet BOB07/31/10 Highlight [Left click] Column A [Right click], Insert

o This will insert a blank column to the left of Column A. Type Period in Cell A1 Type 2 in Cell A2. Place your cursor in the bottom right hand corner of Cell A2. When the cursor

changes to a plus sign, you can [Double Click] and all the cells in Column A willfill with 2 (Autofill)

Step 3 Combine the Two Worksheets into One Insert a new, blank worksheet in the RetentionWorkbook by clicking on the

icon next to the last sheet in our workbook. Rename the new worksheet Combined Data

Copy the Data from BOB12/31/09 into new worksheet Combined Datao Place the cursor in cell A1o Hold down your [Ctrl-Shift-End] keys. This will highlight just the data on

the worksheet.o [Right Click] [Copy] OR [Home Ribbon] [Clipboard Group] [Copy] OR

[Ctrl-C]o Click in cell A1 of worksheet Combined Data

o [Right Click] [Paste] OR [Home Ribbon] [Clipboard Group] [Paste] OR[Ctrl-V]

o Hold down your [Shift] and [Down Arrow] and click in Column A of thefirst row below the 12/31/09 data. This is where we will Paste the datafrom the BOB07/31/10 worksheet.

Copy the Data from BOB07/31/10 into new worksheet Combined Datao Place the cursor in cell A1o Hold down your [Ctrl-Shift-End] keys. This will highlight just the data on

the worksheet.o [Right Click] [Copy] OR [Home Ribbon] [Clipboard Group] [Copy] OR

[Ctrl-C]

o Click in the cell below the 12/31/09 data on the worksheet namedCombined Data

o [Right Click] [Paste] OR [Home Ribbon] [Clipboard Group] [Paste] OR[Ctrl-V]

o Look at the Header Row that was copied with the 07/31/10 data to be surethe data is in the same columns as the 12/31/09 data. If its OK, deletethe extra Header Row. Select the Header Row, [Right Click]Delete.


11/16


12/16


September, 2011 11

Step 5 Copy and Paste Pivot Table Data to new worksheet Click in the first cell of data (Customer Code or Policy Index, usually cell 5A) Hold down the [Shift] key and click the [Right Arrow] key twice. Then hold

down the [Shift-End-Down Arrow] keys. This will highlight just the data in thePivot Table

[Right Click] [Copy] OR [Home Ribbon] [Clipboard Group] [Copy] OR [Ctrl-C]

Insert a new, blank worksheet in the RetentionWorkbook by clicking on the

icon next to the last sheet in our workbook. Click in cell A1 of the new worksheet [Right Click] [Paste] OR [Home Ribbon]

[Clipboard Group] [Paste] OR [Ctrl-V]

Step 6 Run Macro

From the DeveloperToolbar, click Macro, select Retention_Worksheetmacroand click Run.

If you dont see the Developer Tab, Click the Microsoft Office Button, and then click ExcelOptions. Click Popular, and then select the Show Developer tab in the Ribbon check box.You can set your Macro Security settings from here, too. Macro Security setting needs to beon LOW in order to use the Retention Macro. When you make the change, you must exit andreopen Excel for the change to take effect. Save the current workbook before exiting so youdont lose any work youve done so far.

The Macro will put in Headers (that you can change). The Macro will also convertany negative values into blank cells. (My assumption is that if the total of the invoices

or the billing screens are a negative number, the business was lost in the previousyear.)

The formulas for columns D, E and F are:

The formula for New Business is: If there is NO data in Period 1, but there is data inPeriod 2, it is New Business. The formula for this cell looks like this:=IF(+D2>0,IF(+C2>0,IF(+B2=0,+D2,""),""),"")

The formula for Lost Business is: If there is data in Period 1, but there is NO data inPeriod 2, its Lost Business. The formula for this cell looks like this:

=IF(D2


13/16


September, 2011 12

Step 7 - Summary SheetIncluded in the sample workbook is a Summary Sheet. If desired, copy and paste justthe summary data to the summary worksheet. In order to keep the formatting when the

data is pasted, use the Paste Special, Values, instead of just Paste.


14/16


September, 2011 13

APPENDIX II

Sample Search-Customers, Policies Current Criteria/Fields

1. From TAM Homebase, Click ReportsSearch

2. Search Customers

3. Policies Current

4. Output Destination Excel-Raw Data

5. Criteria Tab

a. Policy Status Only ACTIVEpolicy statuses (NEW, REN, REI, REW whichever codes YOUR agency uses)


15/16


September, 2011 14

b. Expiration Date You might want to limit the Expiration Date range toinclude only billing screens that are expiring after the month you areclosing. For example, if you are running the report at November 30, 2009,choose an Expiration Date range of 12/01/09 thru 11/30/2015. This willkeep all the old billing screens with inaccurate policy statuses from

skewing the report, but include all expiration dates in the next six years.c. Record Listings Choose List Each Time Found if you are entering theCommission and Premium on EITHER the main Package line OR thesubsections, but NOT BOTH. If in your agency, the premium andcommission are entered on BOTH the main package line AND thesubsections, choose List Each Time Found, and then manipulate inExcel to get only the commission and premium on the main package.

6. FIELDS/SORT (Include as many as desired, but be consistent each month)Field Name Excel Column Title

Customer/Pol_idx Number Pol_polidx

Agency Pol_agcy

Branch Pol_brchDepartment Pol_dept

Policy Number Pol

Policy Effective Date Effective

Policy Expiration Date Expires

Date First Written Fwritten

Policy Type Pol_type

Policy Type Group Typegroup

Issuing Company Ico

Billing Company Bco

Type of Agency Commission Co_type

Total Commission Amount Co_amtTotal Commission % Com_p

Billing Mode Bill

Producer One Pr1

Producer One Comm % Pr_p

Producer One Comm $ Pr_amt1

Producer Two Pr2

Producer Two Comm % Pr_p2

Producer Two Comm $ Pr_amt2

Producer Three Pr3

Producer Three Comm % Pr_p3

Producer Three Comm $ Pr_amt3

Policy Premium Prem

Policy Status Pol_status

Policy Customer Service Rep Pol_csr

Customer Number Cust_rec

Customer Name Cust_name


16/16


September, 2011 15

7. When finished selecting fields, click Run.

8. Always choose the 3rd radio button to Send output to

a temporary file. Click OK

9. Click Yes to create a new temporary file.

10. Click Yes to Load Excel.

Excel will open with your Billing Screen data. SAVE EXCEL REPORT.

a. Suggestion: Save the report each month to a folder called Active BillingScreens. Name each file using the format: ABSYearMonthDay

November, 2009 would be ABS20091130

December, 2009 would be ABS20091231This way all the billing screen snapshots will be in chronological order inthe Active Billing Screen folder.

retention reporting- calculate new business lost business retention using excel 2007 macro

Documents