With each state determining its tax filing requirements, all entities and producers must be aware of all surplus lines tax rules and regulations for any state they are licensed in, regardless of whether or not they write business. Unfortunately, this can create a painful process for those responsible for filing these reports, especially if they aren’t equipped with the skills and tools to automate the filing process.
So what can a filer do to make the filing process better?
Besides following some of the tips and tricks defined here, the #1 thing a filer can do to be more efficient in filing surplus lines taxes is to increase one’s spreadsheet skills. Many options are available for spreadsheet classes that go over a myriad of functions, tips, and tricks. Some of this information can be helpful, but mostly I find it to be overwhelming. So, if you are not an Excel wizard yet, where should you start?
The purpose of this article is to share what we deem to be the TOP 7 MOST IMPACTFUL Excel tools, which will help streamline your filing process! These tools are available to any user of MS Excel or Google Sheets. When mastered, these tools can dramatically reduce the time it takes to file a surplus lines report.
Excel Trick #1: CONCATENATE
When creating state reports, sometimes the Surplus Lines Association (SLA) or Department of Insurance (DOI) requires the data to be combined into one field. An example of this may be that addresses need to be merged into one field rather than broken into multiple columns. There are a few ways to concatenate in Excel, either by using the & sign or the CONCATENATE function. However, I find using the ampersand character (&) to combine multiple values into one cell faster and easier to use.
How to Use CONCATENATE
To start, select a field that you want the combined data to eventually exist. Then enter ‘=’ followed by the first cell of data you want to combine and then the & sign. Directly after the ‘&’ symbol, you have the option to add the next set of data or choose to add spaces or punctuation to separate the data. To add a space, you would add a quotation mark followed by a space and then a closing quotation mark. It will look like this: “”. If you want to add a comma and a space to separate text such as city and state, then after the ‘&’ sign, add this “, “where the comma and space are both inside of the quotation marks. Proceed to follow this pattern of referring to a cell, adding an ampersand sign (&) followed by a space and another ‘&’ sign until you have referenced all cells in the same row that you want to combine. After this is complete, hit enter to save the function. The beauty about referencing cells as a dynamic function is that now you can copy and paste the function down (or left click on the cell > left click on the blue square in the corner of the cell and while holding down the mouse, drag the function down to the bottom of the list to populate the remaining cells.
Once done, you will have a completed list of combined data. Here is an example of how this may play out in practice with the function in column F and the returned value shown in column G in the image below:
Excel Trick #2: VLOOKUPS
Excel has a slick way to cross-check data from one list to another using the VLOOKUP Function. For surplus lines, this is especially handy when trying to reconcile two lists. Certainly, this process can be done manually or by printing off the lists and going through line-by-line with a highlighter. But going the manual route is not only more time-consuming but is also prone to human error. Therefore, if you do a lot of cross-checking or reconciliation between spreadsheets and are not already familiar with the VLOOKUP function, I strongly recommend you learn this function. Of course, it will take some practice, but if you know how to use it effectively, it will change your life!
There are four pieces of information that you will need to build the VLOOKUP syntax:
The value you want to look up, also called the lookup value.
The range where the lookup value is located. The system will check for the first instance of when that lookup value was found. Remember that the lookup value should always be in the first column in the range (starting from the left side) for VLOOKUP to work correctly.
The column number in the range containing the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, etc. Changing the column number in the function will change the returned value. However, each of the returned values will be on the same row as the first instance of the lookup value. So, for example, if you have a table of information and in column A you are looking for the value of “George,” the system will find the first instance of when “George” appears in column A and note that row. Then the column number will tell the system which value in that row you want to return (Column 1, 2, 3, 4, etc.). Note: the column number must be contained within the range specified in part 2 above. Meaning, you can’t call out a column outside the scope of the range you defined.
Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. In almost all cases, you should set this value to be FALSE.
Now put all of the above together as follows:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, FALSE“
Reminder: The secret to VLOOKUP is to organize your data so that the value you look up is always to the left of the return value you want to find.
How to Use VLOOKUPS
Suppose I have a small list of Invoice numbers, and I want to know the SL tax due on a separate table of information instead of looking up each value one by one. In that case, I can create a VLOOKUP function that looks up the invoice number, in this case in column A, determine the row in which that value exists, and then return the value in column 9 on the same row which in this case is SL Tax. The function would look like this:
=VLOOKUP(A21,$A$2:$I$12,9,FALSE)
Pro Tip: If you have multiple fields that you are trying to check together, a simple VLOOKUP will not work as the function only checks the first instance of a lookup value. There may be multiple. A trick that you can use to get around this is to consider making a unique key and referencing that key instead. Unique keys can be generated by taking two or more values and CONCATENATING these fields together to create a unique key.
For example, say you have 100 transactions in a list, and let’s say that 20 transactions in this list have the same policy number. In this case, simply using the policy number to lookup a certain value to compare two tables will not work because you can’t be assured that you will be referencing the correct row of information. Instead, try creating a unique key on both tables you wish to compare to ensure that you look up the right data row. You could do this by selecting specific values such as policy number, effective date, and premium and then combining these into one field on both tables. The CONCATENATE function would look like this:
=” C2″ &” D2″ &” E2″, which would be added to your reference table like this:
The next step would be to complete a similar process on your source table of data and combine the same values in the same order to create a lookup unique key that matches a value on the reference table.
Lastly, putting this together requires a VLOOKUP function that looks up the unique key created in the reference table and returns a value on the same row. In the example below, I want to return the invoice number from the table above from a list of generated unique IDs:
Excel Trick #3: IF STATEMENTS
Using IF statements to create simple algorithms can undoubtedly help you make the complex more manageable. If you can learn simple If Statements, then moving on to more complex functions such as SUMIFS or COUNTIFS will be easier to grasp.
If statements come in three basic parts:
Make a statement > Is this statement true or false?
If the statement is true, THEN what?
Otherwise, what?
The function may be written as follows:
=IF(STATEMENT,CONDITION IF TRUE,CONDITION IF FALSE)
How to Use IF STATEMENTS
Let’s say I have a list of policy premiums, some being negative and some being positive. Instead of going through one by one and marking each one as such, I can create a simple IF STATEMENT that says if the premium is greater than or equal to 0, then return the value of “Positive”; otherwise, return the value of negative.
The function would be as follows:
=IF(H20>=0,”Positive”,”Negative”). Then I would copy this function down for each value/row I want to check.
Excel Trick #4: SUMIFS
Compound IF statements, such as SUMIFS and COUNTIFS, can be powerful tools to help you summarize data by groupings. SUMIFS sums all values in a column of data that meets a specific set of criteria. In contrast, COUNTIFS counts the number of instances in a column of data where a certain set of criteria is met.
SUMIFS functions are broken into three main parts:
Sum Range – The column of information that you want to Sum data together if criteria are met
Criteria Range -The criteria column that needs to be checked
The Criterion – The value you want to lookup in the criteria range
The syntax of this function is written as follows:
=SUMIFS(sum_range, criterian_range1, criterion1,…..)
Pro Tip: The key with this function is to ensure that the ranges selected are all a single column and are of the same size (meaning the same number of rows) as the other columns. To ensure this, consider just selecting the entire column for your ranges. You can choose the SUMIF function, but this only allows for a single criteria set. Using the SUMIFS function allows you to add as many criteria as you want as long as these values come in a set with a criterion range.
There are many instances when creating surplus lines reports where using the SUMIFS function can be helpful.
For example, if you need to sum filings by month, sum all of the positive premium vs. the negative premium by period, or sum premium by insurance company or line of business. Having this tool in your toolbelt will save you time and make your reporting more effective and accurate. Here is one example applied where the SUMIFS function helps identify the total premium for transactions with the same policy number:
Excel Trick #5: COUNTIFS
COUNTIFS functions are formatted almost identical to the SUMIFS function, except you won’t reference a column to SUM values. Instead, this function counts the number of records in the range met by the set criteria given. This formula can be useful if you want to count the instances that a certain field is returned, such as the number of lines of businesses written in a given month, or the count of endorsements for a certain policy number.
The syntax for the CONTIFS function is as follows:
=COUNTIFS(criterian_range1, criterion1,…….)
Note: you can have as many criteria as you want as long as it comes in a set with the criterion range.
Excel Trick #6: PIVOT TABLES
Pivot tables, if used correctly, may be the most effective tool that you can use in Excel for Surplus Lines summary reporting. Any report that requires a summary can easily be created using pivot tables, including some of the most time-consuming reports. Pivot tables allow you to cross and compare data to get a summary of information.
For example, a pivot table could be used in creating tables of summary information such as those asked for by Kentucky (in their LGPT 141 form). This quarterly report requires users to break out, by municipality, the tax rate, premiums, tax payable, collection fee, and amount collected from policyholders by line of business categories. If you have this information stored in your database or saved this information when the initial filing was completed, creating this summary table can be very quick using a pivot table.
Being good at pivot tables will require practice. Here is a very basic video put out by Microsoft on how to get started. The real key with pivot tables is to understand what you want your rows and columns to be to get the correct calculated values. Calculated values default in most cases to “SUM,” but this can easily be changed to “COUNT” or other formulas depending on what you are trying to calculate.
Excel Trick #7: MACROS
Perhaps nothing is more powerful in automating repeatable processes than Macros in Excel and Google Sheets. Macros are based on a coding language called Visual Basic Applications (or VBA) in Excel and Google Script for Google Sheets. However, you don’t necessarily need to know how to code in VBA or Script to create basic Macros. For example, both Excel and Sheets have a feature that allows you to record yourself performing specific steps. Each step gets recorded and saved. Then after you are done with the recording, you can run the macro, and it will repeat the exact steps you took during the recording.
Some simple things that may be recorded are formatting changes, such as bolding a cell or changing the font color. But it will also save things like formulas, copying and pasting information to other rows, creating pivot tables, adding columns or rows, etc. In addition, there are numerous things that you can do during the recording that will be saved within the macro, which can be rerun at any time with the click of a button.
Learning to create Macros effectively will probably require you to invest more time than any of the other tools listed above. Still, in my opinion, these have the most significant potential for time savings than any of the other tools available to you in Excel as it pertains to Surplus Lines tax reporting.
Here is an article from Microsoft that provides step-by-step instructions on recording and saving a Macro in Excel.
Examples in Practice
InsCipher does tax filings for clients in all 50 states, including the more complex states, such as Kentucky. As such, we are always looking for ways to streamline the filing process. We have created a number of macros that assist us in doing just that. One we created recently was for Kentucky’s LGPT report. If you file in Kentucky, you know that this report is required for every single municipality for which an insured may reside. To streamline this process, we created a macro to help us reconcile, summarize, and sort the data so that the report building became much easier and more accurate to build. Here is the basic process that we use when using this Macro template.
We run a report out of our system and paste this report into a pre-built template with a Macro already built and saved within the file.
We run a similar report out of KY’s DOI system to check for filings submitted during the same time period.
We created a macro that compares the two lists and highlights any discrepancies.
After the data has been reconciled, we run a second macro to format, summarize, and sort the data so that creating the final report becomes easy
We hope you have found these Excel tools helpful and that you take the time to master each one!
We’ve compiled a list of all the states that require some form of Excel reporting as a free download, the Excel Reporting by State Cheat Sheet. Enter your information below to unlock the free download, then, using the tools outlined above, streamline your reporting in different ways for each of these states. Good luck!
Download our ‘Excel Reporting by State’ Cheat Sheet Now!
InsCipher is an insurtech company providing software and services that are revolutionizing inefficient insurance processes. Save your agency time and money by automating surplus lines compliance, filing, and reporting. Want to learn more? Request a free demo today!