Protecting Your Excel-based Business Calculations & Logic with Coherent Spark

Insurers run a lot of their business on Excel. From rating workbooks to benchmark models and claims models, Excel is pervasive. The most useful Excel tools are distributed throughout the enterprise. For example, a large account pricing tool could be shared among the entire business insurance underwriting staff. A recommendation model on claims handling may be in the hands of hundreds or thousands of people. Having a broad user base is a great demonstration of how easy it is to deploy and how useful these Excel-based tools can be.

But broad distribution also carries significant risk; Excel-based tools can easily exit the enterprise, and with that, the proprietary business logic they contain.

Distribution of Excel files outside of the enterprise occurs in a wide variety of ways. Here are a few examples:

• Non-malicious:
  • An underwriter sends an underwriting workbook to a Managing General Agency (MGA) to fill in fields in an underwriting workbook to save your underwriting staff time.
  • A claims manager sends a claim model to a third-party administrator of claims.
• Malicious:
  • An employee emails a workbook to their home email address after they resign to take a new job at a competitor.
  • An employee sends a workbook to a colleague at another company.
  • An employee uploads a workbook to a file hosting service.
  • A vendor sends a workbook to a competitor.

Regardless of intent, sending these workbooks outside of the organization leads to the possibility of your proprietary work being used to give your competitors an unfair advantage.

Consequences

There are numerous consequences from both malicious and non-malicious distribution. Here are a few notable ones:

• Loss of Intellectual Property:
  1. Models take time and data to develop – malicious distribution would give competitors access to that without the same investment.
  2. Pricing Competitiveness – Competitors knowing precisely how you price would make it easier for them to undercut you in the market.
• Using the Correct Version – It’s difficult to ensure that individuals are using the correct version of the workbook after it has been non-maliciously distributed.
• Branding/Goodwill – Many spreadsheets have data, thoughts, and content used to prepare the workbook not related to the calculation or for external distribution that may damage the brand if released.

Naturally, this leads to IT trying to solve this issue in a variety of ways.

Current Solutions

Actuarial and IT staff at companies I’ve worked with have invested significant resources in a wide range of attempts to safeguard their propriety knowledge living in Excel.

Protect the Excel Workbook

Advantages

• Logic is better protected than an unprotected workbook
• Employees can still use Excel to complete their work

Disadvantages

• Time-consuming to implement
• Must be applied to each Excel workbook individually
• Appropriately locking/unlocking content based on the end user’s role takes VBA and time to develop
• The security mechanisms are easy to break

Monitor Outgoing Emails

Advantages

• See all types of files that are sent outside of the organization

Disadvantages

• Discover tools have left the company after the fact
• Separating the signal from the noise and identifying which files are to be protected is difficult.

Block Outgoing Emails with Files Attached

Advantages

• Prevents files from exiting the company via email

Disadvantages

• Doesn’t address other mechanisms of sending files, such as sharing platforms
• Prevents employees from sharing files with outside parties who could help complete the work and reduce the burden on staff

Lock Down File Sharing Websites

Advantages

• Prevents sharing all file types, not just Excel

Disadvantages

• Doesn’t address other mechanisms of sending files, such as email
• Prevents employees from sharing files with outside parties who could help complete the work and reduce the burden on staff

Using Spark to deploy the logic

What if there were a way to still distribute Excel for front-end data entry while still having logic built in Excel protected from accidental edits and malicious individuals? With Coherent Spark, this is now possible with the following steps:

1. Build a file with calculations/logic in it in Excel. For example, it could be a rating workbook for a specialty P&C program written by an MGA.
2. Add named ranges for Inputs necessary to perform the calculations/logic in the Excel workbook and the outputs you want returned.
3. Upload the file to Coherent Spark to deploy an API input.
4. Create an Excel file with just the inputs and outputs in it.
5. Connect the file to the API endpoint with a few lines of VBA.

This lightweight front-end workbook can then be distributed. Using Coherent Spark with an Excel front-end has several advantages:

• The logic and tables contained in the workbook are neither visible nor editable by the end users.
• Models with lots of calculations often run faster because the Coherent Spark engine performs 10 – 100 faster than Excel offsetting the API call round trip.
• Reduces email and LAN storage costs because the logic and tables aren’t replicated for every account, claim, etc. you want to perform the calculation on.
• All executions of the model are logged along with a unique transaction ID and the version they were run on.
• This is often faster to complete than applying Excel’s protection mechanisms.

Conclusion

Coherent Spark allows companies to use the tool the business knows, Microsoft Excel, to build and develop their logic, while adding additional security protecting the underlying logic and calculations from view and editing by end users.