Reducing Risks and Errors Associated With Excel Data Input and Aggregation
Amlin relied heavily on spreadsheets as a data gathering tool, with the goal of achieving rapid response to constantly updated insurance scenario modeling information. However, the use of spreadsheets to input data from multiple users resulted in a workflow which suffered from frequent errors, and propagation of those errors, as well as excessive elapsed time in aggregation of multiple spreadsheets from the end-users.
Using EASA, Amlin built a fit-for-purpose, multi-user web application. In addition to error reduction and time savings, Amlin benefited from many other EASA advantages, including:
No need to distribute spreadsheets (which included macros and VBA), thus reliability for end-users dramatically improved
Access control (who can enter data, who can see aggregated results) was enhanced
Critical intellectual property was made completely secure
Custom software programming was not necessary, reducing cost and time of implementation
“Applying EASA has brought us significant savings in time through massive reduction in manual intervention, and greater accuracy of captured data.” -David Ford, Catastrophe Risk Manager Risk Assessment & Monitoring Amlin Plc.
The high risk of errors and high cost of spreadsheet-based processes
As in any organization, many key processes at Amlin are under-pinned by spreadsheets, largely because spreadsheets are easy to create, familiar to users, and flexible. However, lack of control means that the risk of mistakes and the cost of executing the process are unacceptably high.
Staff at Amlin identified one process particularly in need of better control – RDS, or Realistic Disaster Scenario.
RDS requires every agent to submit a six monthly report to Lloyd’s, showing exposure against scenario and by insurance risk code. In AMLIN’s case, this means some 20 underwriters around the world must each submit reports.
Historically, this process has been spreadsheet-based; AMLIN underwriters complete and submit a spreadsheet cloned from a template.
Complicating this process is the fact that the data required by Lloyd’s changes on a regular basis. Hence a new template must be created, tested and distributed each time there is a change.
The issues which arise include:
- Users change the spreadsheet to suit local requirements before completing and submitting, which results in aggregation errors down-stream.
- The spreadsheet must serve every user, and is therefore more complex than any one user needs – and most users have access to much more of the spreadsheet than is necessary.
- Any change requires that the new spreadsheet is distributed to every user – with instructions to remove the out-dated version. There is no mechanism to ensure the correct version is being used.
- Aggregation is carried out using a series of linked spreadsheets. Links have to be constantly updated, and many layers of spreadsheet refreshed.
- The data exists in many spreadsheets and not in a database, so historical comparison is time-consuming and expensive.
Reducing the risk and cost
A solution requiring the complete elimination of Excel was considered. However, that would have required significant investment in building a database application, re-creating the business intelligence already contained in the spreadsheet.
Instead, EASA’s spreadsheet management solution offered a far more cost-effective alternative – allowing AMLIN to secure a master version of the spreadsheet on a server.
Users now access it only via a custom web application created with EASA’s codeless application builder, allowing a more natural work-flow.
- The custom web application is so intuitive that training is no longer required.
- Users only see what they need to see; they no longer access the spreadsheet directly, and are not able to “dabble” with it.
- If a change is required, it is made in one place and is immediately published to all users; version control is ensured.
- Aggregation is now automatic.
David Ford concludes, “The RDS application, created with EASA, is used in the UK, Bermuda and Singapore. The RDS return for Lloyd’s is now produced at the push of a button.