Guest Author: Nancy Skaggs

Background

We are a specialty commercial insurance company (Excess and Surplus Lines) that was acquired/merged with a larger corporate parent a couple of years ago. We continue to be challenged by the need to make our processes and practices more transparent to our parent company, especially since they have no experience with the type of business we write. Over time we have developed thousands of policy forms specific to our niche market. The process surrounding form creation, use and maintenance is a primary company function.

The Regulatory department is responsible for storage of policy coverage forms (mostly Word and Excel files) in an aggressively permission-protected file share we call the Form Library. They are available in read-only format to most associates. To facilitate navigation of the library, the forms were catalogued in a massive Excel spreadsheet by one dedicated Regulatory associate (RA). Key fields about each form were captured (such as form number, line of business, description, effective date, cross reference, and states in which that form had been filed).

This RA spent hours each day updating the spreadsheet, digging into folders/subfolders to find forms, emailing status updates and generating other communications to various managers/executives/colleagues pertaining to form activity. She was the sole point of contact for this data, causing bottlenecks and slowdowns if she simply took a vacation day. With the increase in activity following our company’s acquisition, it was only getting worse. The associate knew enough about SharePoint to recognize its potential and wanted a better way to leverage the untapped resource that the spreadsheet represented. She came to me with a request to transition from the Excel spreadsheet to SharePoint.

Her request led to creation of the Forms Portal, a SharePoint site designed to replace the Excel spreadsheet and facilitate access to its valuable data to the whole company. The decision was made to not place the documents themselves into SharePoint, but to link to them from the list we now called the Form Catalog. Now, anyone could find out with just a click when a form was created, get cross-reference information, open the form itself, or any of a dozen inquiries that previously required them to go through Regulatory.

Each list item in the Form Catalog contained the columns present in the original spreadsheet, along with new fields and calculated columns which passed even more data on to the users. Versioning added yet another layer of information. Soon we had built workflows for email communication, a search function, supporting document attachments and much more than was ever possible using just Excel. (The launch of the Forms Portal as a vehicle to present the Form Catalog to the company paved the way for everything to come, and we owe a lot to that one associate for her foresight and desire to find a better way.)

With this foundation laid, the next project to emerge from the Forms Portal dealt with the Forms Process itself. In very simple terms, the Forms Process involves the crafting of language for a new policy form (or changes to an existing form), often in response to a specific event or industry trend. Once the language is written and approved for use, it is then made available to the brokers and agents through whom we market our business.

Within our company, it was well known that the Forms Process was ripe for improvement. Once we were acquired, this reached critical mass. SharePoint was first suggested as a possible solution in late January 2010, when automation of a specific feedback sub-process was requested by the underwriting department following launch of the Form Catalog. As research into the requirements proceeded, it became clear that dealing with a single sub-process was not going to achieve anything. We had to attack the entire beast.

Project Preparation

Up to now, my SharePoint projects were handled without benefit of a dedicated PM or BA- I do everything! My first step here was to request that a project manager get between me and the business users. I cannot stress how valuable this was, and what a relief it was not to have to wear both hats and field every question. I knew I could never tame this tiger alone.

It is very difficult for me to take off the “solution developer” hat and put on the project manager hat. My first instinct is always to jump right in and try to fix whatever they bring to me. Often, though, that’s not the right response. My PM was great at keeping the focus where it needed to be and weeding out “nice-to-haves” from the “must-haves.” (An added benefit was the fact that she’d been with our company over 15 years and already knew every player and every relationship quirk!)

The first thing my PM did was an analysis and documentation of the entire Forms Process, start to finish, as it currently stood. This took several weeks, during which she interviewed participants at each point in the process and recorded “who did what” at every step. The result was a 20+ page document detailing the involvement of individuals at every level in nearly every department in the company. This was a massive undertaking that was in itself a remarkable achievement. Next came my evaluation of their process document. I had two primary goals- first, to make sure I had a thorough understanding of the process; second, to determine SharePoint’s potential value within that process. For me, this was the true beginning of the project. I looked for potential breakdowns in communication, reliance on email chains, approval steps, feedback loops and other areas where I thought SharePoint could help. From that analysis, we sat down to draw up our project objectives.

Project Plan

My analysis of their documentation identified three distinct phases in the overall process:

• Phase 1- Form Initiation: In response to awareness of an industry trend or specific need, a new policy form (or a change to an existing form) is required. The goal of this phase is approval of the form language by the Forms Committee.
• Phase 2- Form Implementation: Business users across multiple departments further refine, adapt and communicate about the form. The goal of this phase is to prepare the requirements needed by IT for the Production phase.
• Phase 3- Form Production: The Production phase contains the steps that enable the form to be made available for use. The goal of this phase is successful IT deployment of the form to all software application(s) from which it will be accessed by associates and agents.

Our initial action plan for each phase was:

• Meet with key participants
• Confirm our understanding of their part of the process (based on the original process documentation)
• Identify “pain points”
• Brainstorm potential SharePoint solutions

Phase 1

We began with Phase 1, Form Initiation, which is “owned” by the Regulatory department and is actually a precursor to the Form Catalog process described above.

Process:

• One of two regulatory associates (RAs) receives the new form request (usually from one or more Underwriting Managers, or UWMs). This request constitutes the initiation of a form project.
• The RA handling that project coordinates the drafting of and collaboration on the form language and confirms state filing requirements leading up final approval for use.
• At that time, the form is placed in the form library, added to the Form Catalog and released for company/agent use.

All that activity was- you guessed it- maintained in Excel (NOT the same Form Catalog spreadsheet mentioned above; in fact, each RA had their own individual spreadsheet tracking only their own work). More silos!

Unsurprisingly, our interviews revealed the most commonly listed complaint was“I need to know the status of my form project.” The UWM requesting the form was very often doing so in response to a specific industry mandate, meaning the company risked fines or other consequences if the form was not ready by a specific, inflexible target date.

Currently, the only way for the UWM to get form project status was to call, email or stop by the desk of the RA handling that form’s development. The RA would then have to open his personal tracking spreadsheet, pull out the relevant status information and follow up by composing an email/returning a call/having a conversation with the relevant UWM. Often all three follow-up methods had to be employed. As you can imagine, dealing with these requests for information was as much a burden on the RAs as the lack of information was on the UWMs.

Specific features requested for the Phase 1 solution included:

• At-a-glance indicator showing a colored icon reflecting proximity of Target Date to current date
• Ability to generate a “detail view” for each project, including a link to send email and a link to view status notes
• UW-division-specific filtered views to allow users to directly access data most relevant to his/her line of business
• Workflow to send alert email project owner when his/her project was completed
• Permission specifications restricting access to the data and to the pages displaying the data

My recommendation was a dashboard-style solution to gather, track and display form development status data in SharePoint. This would allow UWMs access to self-serve status updates as well as free up the RAs from tedious status follow-ups, replacing their “spreadsheet silos” with a one-stop data management interface both RAs could access. In addition, SharePoint Designer workflows would be configured to send custom emails triggered by key status changes.

1. Excel data SharePoint list

First we needed to create a dataset to use in our dashboard solution. I received a copy of the RA tracking spreadsheet and transformed it into a SharePoint list following a series of meetings with key individuals.

• This was more complex than you may think, as it required process participants to understand and accept that “their” spreadsheet was now serving several purposes.
• The transition from “ this is my data” (existing only to meet my needs and not subject to anyone else’s input) was now the basis for a dashboard open to a wide pool of users, some of whom were in a position to set additional requirements.
• One individual in particular struggled a great deal with a perceived loss of control in terms of this data. He found it difficult to acknowledge the greater good that would be achieved by this transition. Fortunately we were able to persuade him that what would be gained was worth the inconvenience, and he eventually became one of my biggest supporters.

In addition to the existing spreadsheet, several fields were added in response to requests from various consumers of the data beyond the original Regulatory users.

• The list had versioning enabled and included a Notes field to allow time-stamped status notes from RAs.
• Access to data is limited to process participants, therefore a “Product Development Dashboard” SharePoint Group was created to facilitate access to the data
• List permissions:
• RAs responsible for the data: Contribute
• “Product Development Dashboard” SharePoint Group Users: Read-only
• All others: Access denied

2. “Product Development Dashboard” web part page

The audience for this data consisted of users throughout the company in various Underwriting divisions. Discussions across the user spectrum gave us specifications for a default view for the dashboard; once established, that was used as a basis for additional configurations based on UW division.

1. Web Part page for default view
• Created Web Part page titled PROJECTS.aspx
• Set page permissions based on user role
• Page permissions
• Access to data is limited to process participants, therefore a “Product Development Dashboard” SharePoint Group was created to facilitate access to the Web Part page
• Only users who were members of this Group would see the link to the Dashboard from the Quick Launch menu of the Forms Portal Home page.
2. Web parts and other content present on PROJECTS.aspx
1. Header with instructions and links to other filtered views
2. List view web part (LVWP) displaying default data in the following configuration:


• Filtered by Status (show only Active items)
• Items grouped by Status to show subtotals and sorted by Target Date ascending
• Display following data fields: (all column headers enabled for sort/filter):
• Radio button (allowing Connection to Detail View DVWP)
• ID
• Indicator: HTML calculated column for KPI-style icon (red, yellow, green) based on proximity to Target Date

This is enabled by adapting some code courtesy of Dessie that captures the current date (TODAY) and compares it to the Target Date, then displays one of the following:

• If Target date = TODAY, red exclamation point icon; mouseover shows message: “Target date is TODAY”
• If Target date < TODAY, red icon; mouseover message: “OVERDUE”
• If Target date is 1-3 days away, yellow icon; mouseover message: “Target date is…”
• “… Tomorrow”
• “… in 2 days”
• “… in 3 days”
• If Target date is more than 3 days away, green icon; mouseover message: “Target date is 3 or more days away”
  • # of Forms: How many forms does this project represent? (Number)
  • Form number: Text field
  • UW Division: Choice field
  • Owner: People & Groups field
• Date fields:
• Initial date: Date project was initiated
• Target date: Date set for completion of the form project
• Committee date: Date the form was sent for review by the Form Committee
• Draft date: Date the draft form language was sent for approval
• Production date: Date the Production language was sent for approval
• Data view web part (DVWP) “PROJECTDETAIL” connected to the above LVWP to allow display of a single item’s detail; conditional formatting further enhanced delivery of data
• When radio button is selected in the LVWP table, the DVWP shows a Details view for that specific project alongside the LVWP:


• Status field value is conditionally formatted by color (Active = green text, etc.)
• Link to a custom item display form displaying all project notes for user to review


• Email link- opens a new email to the assigned RA, with the Project Name pre-populated in the Subject line
• Date field specifications and conditional formatting:

If any of the 4 date fields listed above do not have a value present, they are hidden via conditional formatting.

Date field values that are present allow display of the calculated number of working days elapsed between the 2 dates (also courtesy of Dessie)..

• There are set lengths of time pre-established as optimal for each stage.
• If the calculated number of elapsed working days for that stage exceeds recommended length of time, that value appears in red (conditional formatting)
3. Additional Web Part pages were created based on the specifics shown above for additional hyperlinked views:
• My Projects: All projects where Owner = Current User
• UW Division: 4 pages; each filtered on the value present in UW Division field
• Non-Active: Projects in a non-Active status (Completed, Cancelled, etc.)

Phase 1 Summary

The configuration described above allowed any user who was a member of the Project Dashboard Group access to real-time data for all Form Projects in development at any given time. If, after viewing all the available data, Notes etc., the user still needed more information, he/she could contact the RA and dig deeper.

In the months following implementation of this content, the Regulatory RAs reported a significant decrease in time spent responding to non-critical requests for information. The end users reported a high level of satisfaction resulting from immediate availability of data.

Following completion of an item in Phase 1, an RA would create an item in the Form Catalog for the newly-created Form and send a notification to the company that a new form was available for use. Recipients of that email (primarily Underwriting managers) would then evaluate the information about the new form and determine whether it would be applicable for their line of business. If so, the UWM would proceed to Phase 2, Forms Integration.

Guest Author: Nancy Skaggs

Nancy came relatively late to the party but now no one can get her to leave. Since talking her way into IT a little over two years ago, Nancy still considers herself a newbie. Learning from the great contributors on EUSP helped her get this far and Nancy is glad to have a chance to give back.