Example 1

Actuarial valuations

Monthly valuations in the actuarial department of a life insurance company

Motivation

• Reduce the time taken to produce monthly valuation reports from 6 person days to less than one
• Free up skilled staff to review results rather than perform mundane, mechanical processes
• Reduce the error rate that is a consequence of involvement in repeated, manual work
• Scale up the system to perform similar work for a newly acquired subsidiary and satisfy ever increasing reporting requirements from senior management
• Obviate the need to hire an extra person to cover the additional workload

Data sources

• Excel spreadsheets that use data from the previous month as a starting point
• Comma delimited text files with downloaded policy information from the administration system
• Loans administration system from which information is loaded
• Actuarial valuation system projection assumptions in Excel spreadsheets
• Cash flows in FoxPro databases that are calculated from the actuarial projection system

Some of the Processes

• Convert the comma delimited text data to Excel
• Combine the policy data with the loan data for specific policy types and individual policies
• Combine multiple records for each coverage type into a single record per policy
• Perform checks to see if all data is present
• Set certain multi-use data fields to zero
• Copy the data to FoxPro DBF format
• Compare this month’s actuarial projection assumptions to those of last month
• Save the projection assumptions as multiple text files suitable for use with the actuarial projection system
• Run the actuarial projection system several times for different product lines and assumption sets
• Summarise in Excel format the output cash flows from the source FoxPro databases
• Insert a summary of the resultant Excel spreadsheet into a PowerPoint presentation
• Send an e-mail with the presentation attached to the Chief Actuary
• Send a text message (SMS) to the Valuation Actuary to report that the valuation has been completed

Example 2 

Progress reports

Monthly progress reports in a high tech hardware company

Motivation

• Enable engineers in multiple locations to easily input updates of their progress into readily available front-end screens
• Remit the engineers’ updated information to head office for dynamic measurement of the overall status of development projects against plan
• Deliver the aggregated development status to senior management in a summarised format
• Eliminate the manual effort currently required to produce these weekly reports

Data Sources

• Microsoft Project documents
• Excel spreadsheets

Some of the Processes

• Convert the screen data into Excel format
• Apply pre-defined formulae to measure the overall status of development projects
• Import Microsoft Project data representing the engineers’ development plan
• Compare the measured status with the plan
• Prepare an aggregated report in Excel format
• Export the Excel data into a PowerPoint report
• Send an e-mail with the PowerPoint report attached to members of senior management
• Send a text message (SMS) to the Chief Engineer to report a deviation from plan of >5%