At our agency, NBT Insurance Agency, our sales managers like to see a complete picture of new business, including production, written and future sales numbers. The additional data is very useful for budgeting and managing sales staff. Once our accounting teams provide us new business production numbers for the month, we use additional reports from EPIC that pull the data from estimated fields in EPIC along with the pipeline reports from the “sales automation (opportunities module)” to add in current written new business and potential opportunities revenue.

Example of base New Business Production Report received from our accounting team.

Using Estimated Fields in EPIC

We base what our sales teams refer to as “written” new business on the estimated premium and commission fields at the policy level in EPIC. We define “written” new business as the policy being “sold,” and it should be set up in EPIC with one of our “new” policy statuses. The data in these fields is manually entered by our service staff, so we do audits to maintain accuracy. We have the fields marked as “desired,” and we run a policy list report to audit this monthly. There is a screenshot below of where these fields are in EPIC:

Adding Written Data to the New Business Production Report

To get the data we need to add to accounting’s production report, we start with a default policy list report in EPIC and make a few adjustments. We only select the line statuses that are applicable to new business for our agency. Under Service Summary stage, we include only policies in the stage of “in process,” “submitted” or “issued.” Here is a screenshot of the criteria for the monthly report:

One additional criterion that’s important to note is the record listing. If you have sales staff who split accounts in EPIC, you will want this criteria to show “each time found” so that you get the production split for both employees. In the layout, you want the PR/BR name, and the report will divide the commission out proportionally according to the production credit split on the policy. We run this report monthly and year to date. Once complete, we use a vlookup Excel formula to add the data figures into the original report we received. Once updated, it looks like the below example.

Defining the Sales Automation (Opportunities Module)

The sales automation feature of Applied EPIC, also known as the opportunities module, allows you to track and manage new business and renewal pipelines. There are dashboards within EPIC as well as reports and many features to the module. In order to use the reports for this section, you need to be actively entering opportunities in EPIC, using the stages and the premium and revenue fields. These are manual fields entered by the sales staff, so we audit these fields monthly for anything out of the ordinary and also have sales managers review with their staff to make sure the data is accurate. The reports we’ll use from this module are called “pipeline reports.”

Adding Data From Pipeline Reports to the New Business Production Report

There are two different pipeline reports we use to round out our total new business picture. In the first pipeline report, the criteria is adjusted to only pull opportunities that have been marked as “won” in EPIC for the current year. “Won” is defined by us as verbal or written commitment we will be quoting/selling that business. This helps the sales manager see what opportunities have been won and should be turning into written business in the future. Here is an example of a “won” opportunity within EPIC:

For this report, we adjust the criteria for “status” to closed (this means the user would have to have chosen a Won/Lost option) and the “actual closed date” to current year. Here is an example of the criteria:

For the second report, we run another pipeline report to capture only open opportunities for the rest of the year, which shows the future potential revenue for the sales employees. Here is the criteria page for the open opportunities report:

Once these reports have been run, the data is added to the new business production report using a vlookup formula in Excel.

Enhancing the New Business Report

Once the data from the additional reports has been added to the original new business production report, our final report looks like this:

Having the ability to add additional data translates to additional uses for the sales managers. The written/estimated fields help sales managers manage their sales employees by seeing what current business they have written that hasn’t been translated into production yet (e.g., the agency hasn’t been paid). When you add in the data from won opportunities, it allows them to see if their written new business numbers are tracking to match the opportunities they have sold. Additionally, having the data from their open opportunities allows the sales managers to see what potential sales they have for the rest of the year. Using the details from these reports, the managers can also drill down further to make their meetings with sales employees even more effective.

While there is added effort in auditing the data to maintain accuracy in the reports, the reward is a valuable enhanced new business report. Agency leaders can see the revenue we’ve already received in production, the revenue coming soon in written new business through the use of estimated fields and the potential of future revenue through opportunities. This helps in many areas of the agency, including budgeting, meeting goals and the overall management of sales staff. It gives our executives and sales managers a more complete picture of revenue to help them lead us into the future.