Zurich: Deploying Rating Spreadsheets with Database Integration
CPQ (Configure, Price, Quote) Automation with Excel and Database Integration
Zurich uses EASA to deploy critical rating and pricing spreadsheets as web applications. The spreadsheets themselves are safely located on secure servers on-premises rather than end-users’ machines, thus protecting critical intellectual property, and ensuring version control. This secures confidential data and removes risk, while making validated CPQ models accessible to authorized users. Additionally, errors and manual effort have been reduced. Zurich also benefits from many other capabilities offered by EASA, including:
Usage information is automatically logged to a secure and auditable database, greatly facilitating analytics
Excel failures due to inconsistencies in versions, builds, and security settings are eliminated
Intellectual property in the pricing tools is completely secured, as well as the input and output data
“The problem we were trying to solve was challenging, but EASA was flexible enough to meet our needs. In the future, we will rely on EASA even more …” – Nemanja Kostic, IT Solution Architect | Zurich Insurance Company Ltd.
Problem: How do you securely distribute spreadsheets used for product pricing?
It’s a common enough scenario in any industry: spreadsheets used for product pricing…
In the insurance sector, Excel® is widely used to create complex pricing tools for products and services because it is a fast and flexible platform. In fact, spreadsheets are often preferred to “hard-code”, because actuaries need to make frequent changes to calculations and business logic to reflect market fluctuations and actuarial adjustments.
Insurance rating is a specific example. It’s a critical one because the calculations and business logic embedded in rating models can contribute directly to the company’s bottom line.
It’s therefore vital that the authorized owner of a spreadsheet has access to maintain it – but equally vital that end-users only have access to the model’s functionality. The algorithms and methods embedded are key intellectual property – they must be applied correctly and protected from unauthorized distribution.
Solution: Use EASA to web-enable, secure, and deploy spreadsheets to your team.
EASA enables ultra-rapid creation of web-based applications which leverage existing assets such as spreadsheets, databases, web-services, and legacy tools. Zurich has seen benefits including:
- The custom web applications which replace “raw” spreadsheets are so intuitive that end-user training is no longer required
- End-users can only access the correct “certified” version of a spreadsheet, ensuring consistent pricing for customers
- If a change is required, it is made by the authorized “owner”, and is immediately published to end-users
- Confidential models and data embedded in key spreadsheets are protected
- Integration with other systems such as databases and web-services is enabled
- An audit trail of past usage is available
Other options considered and rejected include:
- The wholesale replacement of spreadsheets with custom applications – however, this approach is expensive and time-consuming
- Simply “locking-down” spreadsheets – but this is an obstacle to authorized updates, and does not help with the usage issues
- Spreadsheet collaboration products such as Excel Services – however, most cannot support spreadsheets with complex functions and macros, while EASA does.
EASA enables Zurich to secure a master version of a given spreadsheet on a server. Authorized access is only via a custom web application created with EASA’s codeless application builder, allowing a more natural work-flow.
Even spreadsheets with add-ins or macros can be deployed, because EASA runs Excel natively, in contrast to Excel Services.
In the future the system can be extended to provide access for mobile users.