[Skip to content]

Sign up for our daily newsletter
The Actuary The magazine of the Institute & Faculty of Actuaries

Financial modelling: Tools for success

Complex financial modelling needs in actuarial work are complicated by relentless competition, specific requirements such as Solvency II and the general shift towards stringent risk management controls, post-credit crunch. In these changing conditions, are actuaries’ financial modelling needs being held back, or even let down, by the software choices available?

It has always been difficult to bring different modelling options into corporate governance processes. Changing market conditions may make this issue impossible to deal with unless companies make massive investments in technology-based processes. Are new approaches needed to balance the different needs of actuaries, IT departments and auditors?

Some observers would say that actuaries are currently depending on a number of competing approaches. There are enterprise-wide software platforms, including Enterprise Resource Planning (ERP) systems, that many actuaries are mandated to use. Adapted over time to the insurer’s requirements, these proven systems have the overwhelming advantage of delivering standardised approaches across company operations.

However, they can be complex and time-consuming to operate. In particular, they cannot be customised for the individual, so it is extremely difficult for the user to undertake new and innovative types of analysis. Whisper it in the presence of a hard-pressed IT department, but this is why practitioners may still turn to financial modelling using off-the-shelf software and spreadsheets. The spreadsheets are often hacked together, untidy, difficult to maintain and challenging for the compliance department to audit, but they are bespoke items — vital for those seeking new modelling approaches and a competitive edge — despite their disadvantages.

Another well-trodden path is the dedicated actuarial software package. Specialist software firms have long provided these valuable software tools. However, these are clearly niche products which may come as part of a wider service agreement. This approach may traditionally have allowed actuaries greater freedom in their work but raises issues of maintenance and compliance. Since many of these tools were developed without the IT department’s involvement, there can be difficulties where software issues need to be fixed or systems maintained.

Increasingly, IT departments are being mandated to maintain processes and ensure the integrity of transactional data in preparation for new auditing regimes. The often unsupervised development of complex scenario analysis using dedicated actuarial software might, in the past, have caused problem-solving and compliance issues for firms’ IT functions, as well as the actuaries themselves. Continued use of such systems may tie actuaries to expensive support agreements and complicate their departmental colleagues’ efforts to manage new compliance controls in the future.

Actuaries in smaller firms or departments may, at certain times, use spreadsheets. This approach provides a certain level of ease of use and customisability through their simplicity and their easy-to-use row and column programming system. In the longer term, these advantages are outweighed by those same traits: row- and column-based formulae leave users unable to read formulae they wrote only a few weeks before (‘now, what was cell AC329 again?’) and scenario models that have been re-used by copying and pasting from sheet to sheet may change over time and lead to inconsistent results which are time-consuming to check and audit. Beyond simple office use, the spreadsheet approach creates needless complications and constraints on users’ capabilities, when rigour and creativity are the order of the day.

However, a new type of tool is emerging: programmable fi nancial modelling spreadsheets. These are used in the financial and academic sectors, where the development interfaces are open so that the actuary can perform (or vary) challenging analyses, or connect the spreadsheet to new data sources. Non-technical financial professionals claim to be able to use programmable spreadsheets to devise the analytical models they require and convert their spreadsheets into web applications that are robust and repeatable. These programmable tools are designed to give users a strong, customisable modelling option without the limitations of the simple spreadsheet or the painful choice of the heavy ERP system.

With these next generation analytical tools, actuaries can enhance the scope and accuracy of the recommendations they make. They can also bring them into an organisation’s changing governance needs in a trouble-free manner. Balancing daily workloads while following a company’s changing governance procedures demands that all staff understand and accept new procedures. The new types of functionality in these programmable spreadsheet products could genuinely contribute to actuaries’ complex and often ‘hidden’ work being more painlessly brought into beefed-up business and compliance processes in the future.

Actuaries require flexible tools that will improve their financial modelling capabilities. Meanwhile, their employers face the unenviable task of balancing flexibility with greater staff and technology resources required as new regulatory regimes bite. Next generation modelling tools could represent a third way — helping to achieve the difficult balance between user demands for customisability and accuracy, and the reliability and auditability built over time into enterprise-level ERP systems and actuarial software packages.

Giles Thomas is the managing director of Resolver Systems