By Andrew Chan
It is quite common to see a powerful, robust actuarial system be surrounded by many Excel workbooks. Excel is powerful, flexible and cost effective; actuaries can easily develop Excel solutions to handle their daily challenges on a timely basis. However, Excel is a desktop product, which typically requires more user involvement. Should we use it to handle our critical enterprise actuarial processes, especially if we consider productivity and operational risk? Is there any tool that runs more effectively than Excel and can reduce the routine process significantly? In today’s SOX and compliance-intensive environment, are all Excel workbooks completely documented, well secured and locked-down in a production environment, fully automated and monitored effectively?
Some Excel workbooks are fully automated, i.e., click a button and everything will be done. However, many workbooks require extensive human interaction to set up, e.g., copy and paste, change folder/file name … etc. These manually-intensive workbooks can definitely be improved. But let’s focus on those fully automated workbooks and see if the actuarial process can be further optimized in terms of performance and risk.
Enterprise Scheduler/Business Process Management (BPM)
Even when the Excel workbooks are fully automated, users still have to open it, click the start button and wait for it to complete. It may be fine if it only takes a few minutes, but some workbooks can take hours to process. What if the workbooks fail in the middle of the process? Users may only learn of the crash a few hours later, or they may have to monitor the process.
An enterprise scheduler and Business Process Management can automate, log and monitor all these jobs. If any job fails or runs longer than expected, it will alert the users. When successfully completed, it will start the next job automatically. No time will be wasted!
Auditors and regulators definitely like the control of logging the entire process.
Change Management
Perhaps the business process is very stable and the workbooks are perfectly developed. In that case, we don’t have to worry about change management. But in reality, we often have to add new features and fix bugs, so the workbooks require constant changes for every release.
If we have a change management system, we can see all the changes that were made in each version and make sure that there is nothing more or less that we want to add to the system. All the changes are thoroughly tested. It can also automatically build systems and run regression tests daily to validate each system. We can deliver a more robust system effectively!
Unfortunately, most change management systems do not tightly integrate with Excel workbooks. If you had tried to implement any change management procedures for Excel applications, you would definitely understand that many tasks are required. The consequence is that most Excel applications have poor or no change management capability.
Performance Tuning
I recently managed to cut a batch job cycle from 16 hours to eight minutes in a few days. I couldn’t have achieved such magnificent results without the profiling wizard from Microsoft Visual Studio --it quickly discovers the bottlenecks. Once we identify the bottlenecks, the rest is relatively straightforward.
Performance tuning in Excel is much more time consuming because Excel has no performance profiling tool to effectively identify where the performance issues are. We have to rely on expert opinions, individual experience or trial and error.
Advanced Technology
After the bottlenecks are identified, we try our best to optimize the code. However, there are situations where the code cannot be further optimized and we must look for other advanced technology, e.g., multi-core/multithread.
Excel can only partially support multithread; VBA and some Excel functions are not considered multithread safe. So, if the Excel application uses VBA user-defined functions, then it may not fully benefit from the extra CPU core.
Enterprise Platform
The actuarial system may be rock solid in the process chain; it is fast, robust and secure. However, a chain is only as strong as its weakest link. This is why an actuarial data lifecycle should be built on an enterprise platform that is robust and scalable, fully automated/monitored, supports change management, and able to adopt advanced technology.
Instead of using Excel to clean, format and massage data, we use the Extract, Transform and Load (ETL) tool to manage data. We store data (including historical data) to an enterprise database management system (DBMS) where we automate routine data validation and analysis. We can perform more advanced analytics using historical data, e.g., trend analysis. When it comes to ad hoc analysis, there are many powerful business intelligence tools that are superior to Excel, in providing better and faster information!
Conclusion
Excel can automate a lot of manual processes and provide the data that we desire. However, Excel is a desktop product that is not optimized for an enterprise process. If your actuarial system is caught in an Excel web and you are hitting an invisible wall when you try to improve the turnaround time and minimize error, then consider moving beyond Excel and migrating the actuarial data life cycle to an enterprise platform.
Ideally, all routine processes, from gathering data to results analysis, should be fully automated to improve productivity and minimize risk. Actuaries should be using potent business intelligence tools to conduct advanced analysis, to maximize business insights to their organization!
However, we may not have the budget and resource to migrate all our Excel applications. So we must have a mature Excel development process and our Excel developers must be fully equipped. It is a great idea to conduct a survey in your organization to understand the maturity level of the development process and the skill levels of the Excel developers. The current issue of CompAct includes a companion article that provides a survey directed to actuaries, polling their self-assessed level of mastery of advanced skills in Excel.
Andrew Chan, ASA, is a financial model engineer with ALG Consulting. He can be contacted at chan_a@algconsultings.com.