Spreadsheet services: An efficient approach to implementing business logic

Introduction
Modeling, managing and pricing risk are among the most important priorities for every insurance company. Sophisticated proprietary models are developed by actuarial, underwriting, and finance units to perform these tasks. From a technical standpoint, a common, flexible, and easy-to-use analytical platform was necessary to build those models. As a result, spreadsheets have emerged as the preferred platform used by the vast majority of insurance professionals. The visual nature and step-by-step auditing capabilities have separated spreadsheets from more traditional programming environments such as Visual Basic, Java, C++ or mathematical programming tools like Matlab and Mathematica. Today, almost every insurance company uses spreadsheets to manage their risk one way or another. However, as an increasing number of  insurance companies streamline and automate their business processes (including those complex models) they must deal with a major downside of spreadsheet technology. Spreadsheets are designed for single user desktop environments and do not scale in an enterprise environment, which serves a large number of users concurrently. Facing this challenge, most insurance IT departments attempted to rewrite those spreadsheets in a more scalable programming environment. Taking into consideration the complexity of their models, this approach has often been very expensive and time consuming. In most cases, by the time IT departments complete the rewriting phase, business units have already modified their models to keep up with changes in the marketplace. This leads to never-ending projects that are vastly over budget, and significantly reduces the agility of insurance organizations who are less able to react to changes and opportunities in the marketplace.
This paper presents a technological alternative that enables insurance organizations to integrate their spreadsheet models with enterprise applications without having to rewrite and convert them to another platform. As a result, insurance organizations can experience substantial cost savings, react to changes in the marketplace more quickly, and take advantage of opportunities before their competitors do. It also encourages superior collaboration between business units and IT departments, enabling each to concentrate on their core functions.
Challenge
To stay competitive, insurance companies must constantly face the challenge of properly managing their risks. Managing risk requires collective effort from all parts of the organization. In particular, a collaborative effort involving the actuarial, underwriting and finance departments is crucial. Sophisticated models are built to better understand and properly price their exposure. “What if” scenarios are executed to understand the effect of model variables. Rules- based models are designed for underwriting. To illustrate this further, following is a partial list of complex models that are used in insurance organizations:

• data validation and scrubbing;
• actuarial pricing;
• rating engines;
• reserve calculations;
• product selection rule engines;
• predictive models; and
• underwriting engines

Highly capable analytical platforms are necessary to build, test, and execute risk models. Traditionally, spreadsheet software has been used by insurance carriers for this purpose. There are multiple reasons that support the notion that spreadsheets provide an ideal platform for analytics:

• Almost every insurance professional knows how to use spreadsheet software.

• Hundreds of built-in functions simplify developing sophisticated models.

• The familiar grid interface and built-in auditing tools enable users to visually follow complex algorithms.

• Simple import/export features allows data manipulation.

• Easy debugging is possible using built-in tools.

While spreadsheets are extremely powerful analytical tools, the fact that they are designed for single user desktop environments is a major disadvantage. This becomes more evident and critical as insurance companies move to web-based platforms that require integration of complex business logic with calculations that currently exist in spreadsheet format.
Traditional approach
In its most simplified form, there are three major components in any enterprise insurance software (Figure 1). They are the data layer (database), business layer (business rules and calculations), and presentation layer (user interface).

The business layer is where complex spreadsheet models need to be integrated. In general, insurance companies chose to rewrite spreadsheet models using traditional programming languages. This is a long and expensive process (seeFigure 2); the process typically starts with business units writing specification documents, describing in extreme detail how their algorithms work, a tedious process that insurance companies either handle internally or outsource to a consulting firm to develop a specification document. Once finalized, the specification document is delivered to the IT department. Software developers then have to understand the algorithm and code it. Considering most software developers are not equipped with skills and experience to understand complex insurance calculations, this process is often protracted and error-prone. After the code is completed, it is delivered to QA teams for testing. Considering the analytical nature of this code, business units, in conjunction with QA teams, are ideally involved in testing. When testing, original spreadsheet models are used as reference points, and results obtained from the application are compared with those obtained from the spreadsheet models. A large amount of test cases are typically used to ensure that every aspect of the insurance algorithm has been triggered. Inconsistencies between spreadsheet models and the application are reported to IT units. At the risk of generalizing, these inconsistencies are often difficult for software developers to resolve as their understanding of the algorithm tends to be somewhat limited. As a result, testing becomes a long, iterative process that consumes valuable resources from business units and the IT department. At the end of the process, after all inconsistencies are resolved, business units sign-off on the application and it is – finally – ready to be rolled out.

Unfortunately, this is only a part of the process. Business units continue to adjust their algorithms to stay competitive in the marketplace. With each algorithm adjustment comes a related need to be implemented in the insurance application. A process similar to the one described above is repeated to for all such changes.
The traditional process of implementing business logic and calculations is not only time consuming but very expensive; it negatively impacts an insurance organization’s ability to roll out new products faster.
An efficient new approach – spreadsheet services
Software products have recently become available that process spreadsheets in a server environment and integrate them with other enterprise applications. These products eliminate the need to rewrite spreadsheet models in traditional programming environments. Further, existing spreadsheets can be used “as-is” or with minimal modifications in order to integrate with other insurance applications.
Figure 3 below illustrates this new approach, which we dub “spreadsheet services.” The spreadsheet engine is the central component, essentially replacing the functionality of desktop spreadsheet software. The majority of insurance carriers use Microsoft Excel as their desktop spreadsheet software. However, using Excel in server environments is not recommended by Microsoft; unstable behavior and deadlocks are some of the problems that Excel can cause when run in server environments.[1] A spreadsheetengine can be used to process spreadsheet files in a server environment without depending on the spreadsheet software with which they were created.[2]

Due to the fact that web applications require concurrent access by a large number of users, a spreadsheet engine can be designed and optimized to handle a high volume of requests and perform in multi-threaded environments.
The interface between the spreadsheet engine and software applications is another important component worthy of discussion. There are different ways to handle this interface. With recent developments in Service-Oriented Architecture (SOA), insurance organizations are moving to implement applications that support Web Services. A web service interface between the spreadsheet engine and the insurance application makes it easier for carriers to implement this new approach.

How do you select the right technology?
There are already several products on the market that allow spreadsheet models to be run in a server environment and be integrated with enterprise applications. While each has many features designed for different applications, it is important to identify those criteria that define the right technology for your insurance application:

• Web services. Designs based on Web Services have proven to be a valuable architecture for building enterprise applications in insurance organizations. As such, it is important to select a technology that can integrate with existing Web Services platforms. Aside from technological advantages, identical spreadsheet models can be used by multiple applications, making it easier to build within an SOA environment. For example, one rating engine can be used by internal quoting and underwriting systems as well as broker applications developed by external vendors. Having a Web Services-based rating engine that can be accessed internally as well as externally makes it easier to maintain and eliminates rating inconsistencies between the two.

• Platform independence. Many insurance companies utilize Linux and Unix servers for their back office operations. Accordingly, platform-independent solutions provide the best alternative from a maintenance and operational point of view.

• Performance. Running complex spreadsheet models in a server environment is a performance-intensive process that consumes significant CPU resources and memory. Performance-optimized solutions will therefore meet  concurrency and response-time requirements of enterprise applications, without needing to scale up with additional hardware capacity.

• Maintain the integrity of spreadsheet files. There are products available that convert spreadsheet files into program code (e.g., Visual Basic, C++ or a propriety file format). This approach requires the involvement of software developers to integrate the code with the overall application every time business units update their spreadsheets. This could slow down the rollout process and increase testing requirements. Converting spreadsheet files into proprietary formats makes spreadsheet management more difficult as the number of files increases over time.

• Small footprint. Processing spreadsheet models in a server environment is a back office operation that consumes significant server resources. Therefore, general purpose products offering spreadsheet processing as an additional feature will consume valuable server resources and leave limited CPU capacity and memory for executing spreadsheets. As a result, additional server capacity is often needed to meet performance requirements.

• Grid computing. Insurance applications accessed by a large number of users typically require multiple servers to operate. Solutions that support grid computing will enable carriers to scale up their applications by simply adding new servers.

Benefits of the new approach

Short term
By adopting the spreadsheet-based approach, insurance organizations realize the benefits of accelerated application development and cost savings.
The spreadsheet services approach completely eliminates the time-consuming coding of insurance algorithms and their testing. Coding complex business logic and algorithms tends to be the most time consuming part of the development of any insurance application; eliminating the need for such tedium can have a profoundly positive impact on the project development cycle.
Traditionally, business units utilize business analysts to write specifications and test applications, while IT staff write the actual code and quality assurance teams perform extensive tests to validate the accuracy of the code. Using spreadsheet services virtually eliminates this process and substantially reduces project costs.
Another important benefit of the new approach is a better collaboration between business units and IT; each unit can focus on their core business functions, improving efficiency throughout the enterprise.
Medium term
Maintaining applications by periodically adjusting business logic using the traditional approach requires heavy involvement from all parties, as the specification writing, coding, and testing processes have to be repeated each time business units update their models. With spreadsheet services, business units need only provide IT with updated spreadsheet models. New algorithms can be implemented with minimal system testing.
Insurance organizations also benefit from faster time to market, as updates in business logic and calculations are rolled-out in days rather than weeks or months using the traditional approach.
Long term
In the long term, insurance organizations benefit from this superior architecture as spreadsheet services pervade the organization and an increasing number of business units start adopting the approach. As it is based on SOA, multiple enterprise applications access common Web Services for certain similar calculations and rules. Algorithms can be served from a single point, eliminating redundancy among unit applications used within the enterprise.
Typical insurance applications
Spreadsheet services can be utilized wherever spreadsheets are used, or can possibly be used, to model complex business logic and calculations. Actuarial pricing, underwriting and product rules engines, broker commission calculations, reserve calculations, and predictive modeling are only a few of the critical insurance processes where the new approach adds value.
Rating engines
Rating is typically a self-contained process in the policy lifecycle. Rating engines are simply software programs that return results based on programmed logic for a given set of inputs. In some cases, they require database connectivity; in others, they stand-alone.
An ideal insurance rating system may be characterized as follows:[3]

• It supports all lines of businesses;
• It easily handles algorithm changes;
• It has strong decision-support capabilities;
• It supports customization, including state- or company-specific deviations;
• It easily integrates with existing systems (i.e., policy administration); and
• It supports multi-line operations.

The spreadsheet services approach meets all of these characteristics. The modeling capabilities of spreadsheets used in conjunction with the many built-in formulas enables the development of rating algorithms for even the most complex lines of businesses, providing a single source for all rating regardless of complexity .
To respond to the dynamism of the insurance industry, carriers need the ability to quickly adjust their rates. Insurers often allocate sizeable maintenance budgets in IT departments to handle ongoing rate changes. A spreadsheet-based approach significantly reduces the burden on IT departments, frees up budgets and enables carriers to adjust their rates faster.
Conclusion
Solid risk management principles are crucial for every insurance organization. Sophisticated proprietary models are developed by actuaries, underwriters, and financial professionals to properly manage and price their risk. Most modeling is typically done in spreadsheet environments because of the familiarity, flexibility and features provided. Traditionally, the business logic already built into spreadsheet models is rewritten when integrating them with enterprise applications – typically a long and expensive process.
The spreadsheet services approach completely eliminates the need to rewrite business logic and calculations, while enabling business units to maintain control of their models by keeping them in a familiar format.
The spreadsheet services approach significantly reduces the costs of developing applications utilizing business logic promoting a more collaborative relationship between business units and IT by allowing each to concentrate on their core competence. Business users remain in full control of business logic, enabling faster time to market and greater profitability.
References