Task

An insurance company has three divisions as a result of separate acquisitions. Each division has a different administration system for recording policy information. Reports that detail sales activity in several different formats are required across the whole group. These must draw on information from each of the administration systems and from individual budgets. Some of the reports are used to provide information to the industry regulator on a monthly basis and must include a full audit trail.

Objectives

• Combine the data from each source in a consistent manner
• Produce the reports quickly and accurately without requiring manual intervention
• Be able to demonstrate to the auditors and regulators how the figures were derived
• Send the latest information to various Regional Managers
• Eliminate key person dependency currently endemic in production of the reports
• Ensure that additional reports can be produced or the format amended in line with changing needs of the business.

Data Sources

• Text files with policy data produced from each of the separate administration systems. Two are in comma-delimited format and one uses fixed fields
• An Excel workbook containing budgets for the month and year
• An Excel workbook that displays the various reports.

Steps that make up the automated process

1. Set parameters for the run, e.g. reporting period, company division, region, reporting currency
2. Re-load the modified report workbook if changes have been introduced
3. Load the source data files
4. Clear all data from the files used in last month’s reports
5. Copy all data from the three text files into separate sheets
6. Copy the formulae pertaining to the dimensions of each element of data, e.g. sales channel, month, product type, etc.
7. Automatically check there are no errors in the data and that the totals reconcile
8. Selectively copy the required data elements to the source of each report
9. Copy the relevant report to the workbook that is used to produce data for the financial regulator
10. Email the reports for each region to the respective Regional Manager or to the person responsible for reporting to the financial regulator
11. Send a text message to the Sales Manager on completion of the process
12. Lock the run together with its full audit trail.