Insurance Analysis in the 21st Century

Presentation given by Kevin Pledge and Robert LaLonde to the South Eastern Actuaries Conference in 2005

Transcript

1. Insurance Analysis in the 21st Century Robert LaLonde Kevin Pledge SEAC 22nd June 2005
2. Agenda ƒ Finding the Truth ƒ IT Puzzle ƒ BI History ƒ BI Key Ideas ƒ Application Examples Finding the Truth IT Puzzle BI History BI Key Ideas Applications
3. Finding the Truth Robert LaLonde
4. Let’s Ask the Audience ƒ What are the top 10 analytical report a life and health Insurance company should produce? ƒ How does your company produce that kind of analysis? ƒ Frequency? ƒ Reconciliation efforts? ƒ Slicing and dicing? ƒ Suppose you want to use data in a report that resides in different systems? Finding the Truth IT Puzzle BI History BI Key Ideas Applications
5. Current ‘Spider Web’ of Reports Jumbled information flow and lack of organization Finding the Truth IT Puzzle BI History BI Key Ideas Applications
6. Case Study ƒ Life company IT manager buys Brand name DW ƒ Many P&C clients ƒ Includes Earned Premium computation ƒ Is closed system ƒ Users can’t write reports ƒ Very Limited use ƒ A limited success Finding the Truth IT Puzzle BI History BI Key Ideas Applications
7. Ideal DW Definition ƒ End-to end system from a business perspective, not a tool perspective ƒ Cleanses, stores and manages data ƒ Presents results for reporting and analysis ƒ Includes applications specific to insurance for enhanced analysis ƒ Should deal with status changes ƒ Has the end user in mind ƒ Most DW’s start as Marketing projects ƒ Most are unsuccessful Finding the Truth IT Puzzle BI History BI Key Ideas Applications
8. Challenges to Overcome ƒ Disparate systems ƒ Multiple definitions of data ƒ Different calculations of the same item by different departments (e.g. unearned premium) ƒ Multiple departments producing reports that don’t agree requiring reconciliation Finding the Truth IT Puzzle BI History BI Key Ideas Applications
9. Temporal Nature of Insurance ƒ Temporal = Of, relating to, or limited by time ƒ Most of our metrics are around dates, status and events à Underwriting and issue process à Inforce à Claim à Exit ƒ Need a multistate temporal query tool that simultaneously queries around dates, durations, and events, Finding the Truth IT Puzzle BI History BI Key Ideas Applications
10. Slicing and Dicing ƒ Standard approach à Rerun study according to a new bias à Rerun again, and again, and again à A diarrhea of reports ƒ Better approach à Pivot table query à Run it through specialized cubes à A repository of reports Finding the Truth IT Puzzle BI History BI Key Ideas Applications
11. Cut your Losses ƒ “There is nothing that we have done with our DW that we couldn’t have done with out it.” à It just would have been harder. à It just would have taken longer. à The opportunity cost can be fatal. ƒ How long do you want to wait to cut off a losing product? Finding the Truth IT Puzzle BI History BI Key Ideas Applications
12. Point vs Enterprise ƒ Point Solutions à Adhoc studies à Mortality/Experience Studies à Hodgepodge of reports ƒ Enterprise solution Finding the Truth IT Puzzle BI History BI Key Ideas Applications
13. Costs ƒ Tools ƒ Applications ƒ Data Model à Open à Closed ƒ Licensing fees ƒ Implementation ƒ Opportunity Costs ƒ Benefit Justification Finding the Truth IT Puzzle BI History BI Key Ideas Applications
14. Benefits ƒ Cost savings à Reporting from multiple systems à Legacy system data à Single data source ƒ Clearly defined information and processes (Sarbanes-Oxley) ƒ Multiple areas working with the same view of the business ƒ Functional information and analysis ƒ Getting at the Truth Finding the Truth IT Puzzle BI History BI Key Ideas Applications
15. Insurance Analysis in the 21st Century Kevin Pledge
16. Insurance Analysis in the 21st Century "He who has the most information wins." - John D. Rockefeller ƒ Information should be: à Accurate à Consistent à Relevant à Timely ƒ How can you plan for this? Finding the Truth IT Puzzle BI History BI Key Ideas Applications
17. IT Puzzle
18. BI System - The Missing Piece? Process business Report to regulators Value business Finding the Truth IT Puzzle BI History BI Key Ideas Applications
19. BI System - The Missing Piece? Manage sales customer contact Process business Report to regulators Value business Finding the Truth IT Puzzle BI History BI Key Ideas Applications
20. BI System - The Missing Piece? ƒ Traditionally companies focus on operations ƒ Analytics left to ad-hoc processes Finding the Truth IT Puzzle BI History BI Key Ideas Applications
21. Operational Processing vs. BI Operational ƒ When was the policy issued? ƒ Are premiums paid to date on a policy? ƒ Which agent sold the policy? ƒ What is the current policy value? Analytic ƒ What factors affect underwriting time? ƒ How did each product line (or product) contribute to profit last quarter? ƒ Which factors contribute to profit? ƒ Who are the most successful agents? Who asks what? Finding the Truth IT Puzzle BI History BI Key Ideas Applications
22. ƒ OLTP supports à Granular transactions à Real time production systems à Current, changing data ƒ DW/BI supports à Summarized queries à Consistent, heterogeneous data à Voluminous, historical, stable data Operating Operating Business Business Managing Managing Business Business Operational Processing vs. BI Finding the Truth IT Puzzle BI History BI Key Ideas Applications
23. BI System - The Missing Piece? Manage sales customer contact Process business Report to regulators Understand Analyze Manage Value business Finding the Truth IT Puzzle BI History BI Key Ideas Applications
24. Understand - Analyze - Manage ƒ These functions are explicitly addressed by a Business Intelligence (BI) system ƒ “a data infrastructure specifically designed for query, analysis and reporting” ƒ Includes: à Data cleansing à Data Storage à Application à Reporting Tools ƒ A relatively new category of system Finding the Truth IT Puzzle BI History BI Key Ideas Applications
25. BI History History Success Rates
26. BI Timeframe – Early Years SIAD MDS { Origins: APL? Comshare Oracle Express Cognos Finding the Truth IT Puzzle BI History BI Key Ideas Applications
27. Data Warehouse Timeframe – 1990’s Information Warehouse concept coined by IBM Mainstream DW books published First mainstream Pivot application: Excel 95 OLAP Defined by Dr Codd Large DB vendors release products MDX Finding the Truth IT Puzzle BI History BI Key Ideas Applications
28. BI Timeframe – 1990’s Products Cognos Holos Essbase MicroStrategy Business Objects Oracle IBM Microsoft Finding the Truth IT Puzzle BI History BI Key Ideas Applications
29. BI Timeframe – 2000’s McGarry’s Temporal data model Vendors look to vertical applications Data Mining SQL Server 2005 promises to converge RD and OLAP Finding the Truth IT Puzzle BI History BI Key Ideas Applications
30. BI Timeframe – 2000’s Products 9% 7% 5% 6% 7% MicroStrategy 7% 8% 7% 8% 7% Business Objs (incl Crystal) 14% 14% 15% 14% 14% Cognos (incl Adaytum) 27% 24% 23% 22% 21% Hyperion Solutions (incl Brio) 12% 21% 24% 26% 27% Microsoft 2000 2001 2002 2003 2004 ƒ Vendor consolidation ƒ Emergence of Microsoft in DW’ing Finding the Truth IT Puzzle BI History BI Key Ideas Applications
31. Why Implement a BI System? ƒ Access to enterprise wide data ƒ Consistent data ƒ Perform analysis quickly ƒ Analysis that was never possible previously ƒ Savings in time and resources Finding the Truth IT Puzzle BI History BI Key Ideas Applications
32. Why not? ƒ Cost ƒ Reputation à Complex à Poor success rate ƒ Perception that current systems do the job Finding the Truth IT Puzzle BI History BI Key Ideas Applications
33. Cost ƒ Cost Factors à Expertise à Scope ƒ Cost Benefit Analysis à Savings à Improvements Finding the Truth IT Puzzle BI History BI Key Ideas Applications
34. BI Project Success Rates ƒ Success Rate à Overall Success à Data Acquisition à Complete Metadata ƒ Contributing Factors à Business Sponsor Commitment à Senior Business Sponsorship à Alignment to Business Finding the Truth IT Puzzle BI History BI Key Ideas Applications
35. Overall Success of Data Warehouse Very successful 17% Fairly successful: 31% Somewhat successful: 25% Not very successful 7% Not sure yet 15% Does not apply 5% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
36. Data Acquisition Success Rate Failure 9% Minimal Success 23% Success 45% Great Success 22% Extraordinary 1% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
37. Metadata Implementation Not addressed, no plan 14% Recognised the importance, but no plan 46% Have plan, but have not yet implemented 21% Have plan, and started to implement 16% Have implemented metadata 3% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
38. How committed is the Business Sponsor? Fairly committed 36% Somewhat committed 9% Not very committed 3% Very committed 52% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
39. Level of Business Sponsor Top IT executive (e.g., CIO or director of IT) 13% Top corporate executive (CEO, CFO, COO) 35% Business unit leader (e.g., head of unit or division) 32% None 1% Executive council or committee 4% Other 1% IT program or project manager 5% Functional unit leader (e.g., head of sales) 9% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
40. Alignment of Project Team to Business Fairly aligned 48% Somewhat aligned 19% Not very aligned 5% Very aligned 28% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
41. Success Rate Summary ƒ Success Rate à Approximately 70% of projects class themselves as successful à Only 3% have successfully implemented metadata ƒ Contributing Factors à Business Sponsor Commitment 52% à Senior Business Sponsorship 35% à Alignment to Business 28% Finding the Truth IT Puzzle BI History BI Key Ideas Applications
42. Business Intelligence – Key Ideas Distinct components Dimensional Modeling Multidimensional Analysis Data Management Scale
43. Key Idea: Distinct Components Finding the Truth IT Puzzle BI History BI Key Ideas Distinct Components Applications
44. Key Idea: Dimensional Modeling ƒ Data is presented in a standard, intuitive framework that allows for high-performance access ƒ The measurement data is organized in a single table with a multipart key, called the fact table, and a set of smaller tables called dimension tables ƒ RI constraints in-place between the fact table and the dimension tables ƒ The other non-FK fields in the fact table are numeric, additive measures, e.g., sales $, counts, etc. ƒ Dimensions are generally descriptive, text fields ƒ So commonly used that many RDBMS have a specialized join technique called a “star” join which optimizes access Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
45. Star and Snowflake Schemas ƒ Star = denormalized hierarchies into a single dimension table ƒ Snowflake = normalized (FK constraint) tables ƒ Structures are always one-to-many up the hierarchy – they can be regular, ragged or parent- child, but always one-to-many à Facilitates rollups; drill down, etc. Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
46. Star Schema Report Date (PK) Report Year Band Report Year Report Quarter Report Month Report Date Company Id (PK) Company Statutory Company Company Attributes Issue Location (PK) State or Province Sales Area Country Issue Location Policy Id (PK) Product Cell Id (FK) Agent Id (FK) Report Date (FK) Company Id (FK) Issue Location (FK) Status (FK) Gender Issue Age Premium Reserve Benefit Amount Policy Fact Table Agent Id (PK) Name Address Gender Manager Region Agent Attributes Product Cell Id (PK) Product Product Description Product Type Benefit Type Premium Type Insurance Event Product Group Product Line Product Cell Attributes Status (PK) Status Group Status Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
47. Snow-flaked Star Schema ƒ Extend process of defining attributes ƒ What are the advantages and disadvantages of this over the original star schema? Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
48. Star Schema Features ƒ Designed for analysis ƒ Assumes data is static or unchanging ƒ Benefits à Efficient Storage of data à Intuitive query building Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
49. Actual Schema ƒ Comments / Observations? Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
50. Total premium for 'Active' policies in Dec 2004 Report Date (PK) Report Year Band Report Year Report Quarter Report Month Report Date Company Id (PK) Company Statutory Company Company Attributes Issue Location (PK) State or Province Sales Area Country Issue Location Policy Id (PK) Product Cell Id (FK) Agent Id (FK) Report Date (FK) Company Id (FK) Issue Location (FK) Status (FK) Gender Issue Age Premium Reserve Benefit Amount Policy Fact Table Agent Id (PK) Name Address Gender Manager Region Agent Attributes Product Cell Id (PK) Product Product Description Product Type Benefit Type Premium Type Insurance Event Product Group Product Line Product Cell Attributes Status (PK) Status Group Status 1) Select Premium 2) Where report date = 2004-12 and Status = ‘Active’ Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
51. Total premium for 'Active' policies at 31 Dec 2004 ƒ Sample Schema select sum(f.Premium) as Premium from PolicyFactTable f join Status t on t.Status=f.Status Join ReportDate d on d.ReportDate = f.ReportDate where t.Status = 'Active' and d.ReportDate = '2004-12‘ ƒ Actual Schema select sum(f.AnnlzdPremium) as Premium from iwfAllPolDynamics f join PolDynamicsType t on t.PolDynamicsTypeKey=f.PolDynamicsTypeKey Join ReportDateMth d on d.ReportDateMthKey = f.ReportDateMthKey where t.PolDynamicsType = 'Active' and d.ReportDateMth = '2004-12' Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
52. Active policy count by product at 31 Dec 2004 ƒ Syntax select p.Product, count(*) from PolicyFactTable f join Status t on t.Status=f.Status Join ReportDate d on d.ReportDate = f.ReportDate join ProductCellAttributes p on p.ProductCellID = f.ProductCellID where t.Status = 'Active' and d.ReportDate = '2004-12' Group by p.Product Order by p.Product Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
53. Why Use A Dimensional Model? ƒ Clear, predictable model which allows reporting and query tools to make assumptions around à How data is presented, grouped, and navigated (i.e., slice ‘n pivot) à Browse dimensions (and hierarchies) independent of numeric data à Users are not locked into one data access route – drilldown and pivot ƒ Well-understood semantics for high-performance, i.e. aggregation Finding the Truth IT Puzzle BI History BI Key Ideas Dimensional Modeling Applications
54. Key Idea: Multidimensional Analysis ƒ Cube concept ƒ Edges are dimensions West East South North Geography Q2 Q1 Q3 Q4 Time Life Health Annuity Seg Funds Product Finding the Truth IT Puzzle BI History BI Key Ideas Multidim’l Analysis Applications
55. Customer Customer All Country State Zip Cust ID Multidimensional Analysis Key Feature: Aggregations Policy Policy All LOB Type Product Policy ID Facts Facts … $1,003 135672 563601 $6,732 451236 563601 $4,567 135123 345623 Reserves Policy ID custID Highest Level Aggregation Highest Level Aggregation $145,212,301 All All Reserves Product Customer Intermediate Aggregation Intermediate Aggregation … $57,931,945 WOL678 US $23,914,730 IT452 Can Reserves Product Type CountryCode Subtotals at a certain level from every dimension Finding the Truth IT Puzzle BI History BI Key Ideas Multidim’l Analysis Applications
56. Multidimensional Analysis Contrasting OLAP to relational Query Analyzer + aggregate navigator (or very sophisticated query tools) Run-time query engine selects nearest aggregations Optimizing queries SQL MDX Query language Analyze which aggs best support query pattern Wizards (for simple design process)? Designing aggregations Materialized view with GROUP BY clauses Intrinsic to DB Aggregation Relational Multidimensional Finding the Truth IT Puzzle BI History BI Key Ideas Multidim’l Analysis Applications
57. Demo ƒ “Slice ‘N Dice” Analysis, Drilldown And Pivot Finding the Truth IT Puzzle BI History BI Key Ideas Multidim’l Analysis Applications
58. Key Idea: Data Management Intro to ETL ETL = Extract, Transform, Load ƒ Moving data from production systems to DW ƒ Checking data integrity ƒ Assigning surrogate key values ƒ Collecting data from disparate systems ƒ Reorganizing data Finding the Truth IT Puzzle BI History BI Key Ideas Data Management Applications
59. Data Management ETL tools give graphical representation of data flows Finding the Truth IT Puzzle BI History BI Key Ideas Data Management Applications
60. Data Management Key Challenges ƒ Changing data à “Changing dimensions” issue à Rewriting history versus maintaining history à Temporal dimensions solve this à Restatements à Do not to alter facts à What if you have to re-do a month of data? à Data lineage à Where does this number come from? ƒ Data cleansing ƒ Data standardization à Across various source systems à Surrogate keys help here too Finding the Truth IT Puzzle BI History BI Key Ideas Data Management Applications
61. Key Idea: Scale ƒ There will be large data volumes in the DW ƒ Must manage multiple technologies at scale! ƒ Insurance is one of the most complex applications ⇒ Watch out retail applications are different from insurance ƒ Complex calculations (“wide queries”) à Experience studies are simple example ƒ Very large dimensions ƒ CrossJoins on large dims à Age and duration, for example Finding the Truth IT Puzzle BI History BI Key Ideas Scale Applications
62. Scale ƒ DB are designed for volume ƒ Technology has price ‘sweet spot’ ƒ Not just storage à Load time à Query processing ƒ Key is in the design à Storage vs processing Finding the Truth IT Puzzle BI History BI Key Ideas Scale Applications
63. Applications Key Idea Experience Studies Source of Earnings
64. Applications: Key Idea ƒ Rethink application for DW à Experience Studies à Source of Earnings Finding the Truth IT Puzzle BI History BI Key Ideas Applications
65. Experience Studies ƒ Withdrawal à Surrender à Lapse à Conversion ƒ Mortality ƒ Premium Persistency ƒ Transition à Incidence/Termination Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
66. Purpose of Experience Studies ƒ Experience for pricing, re-pricing ƒ Dividend setting ƒ Assumption setting for management projections, Embedded Value, other reserves ƒ Improve performance Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
67. Key Concepts ƒ Principle of Correspondence ƒ Rate Interval à Life Year à Calendar Year à Policy Year ƒ Study Period ƒ Exposure Type à Initial à Central ƒ Dependent vs. Independent Rates Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
68. What can a DW do for Experience Studies? ƒ Consistent studies à Lapse vs Sales Persistency ƒ Be automatically notified of adverse experience the moment it occurs? ƒ Have the ability to produce new studies in 5 seconds? ƒ Slice and dice experience studies ƒ Tie results to financial impacts and demographic changes? Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
69. The Evolution of Experience Studies Mainframe Applications Mainframe Extracts Data Warehouse Spreadsheets / Access Computational systems Integrated or Extract provider Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
70. Study Methods ƒ Direct Approach à Computation based on exact exit dates and exposure à Based on current view of data ƒ Census Approach à Traditional approach for handling aggregate data ƒ Multi-state Approach à Based on true history of data Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
71. Direct (or Seriatim) Approach ƒ Calculate exposure directly from birth date, study dates and exit dates ƒ Group by age, duration and risk factors Pros à Intuitive Cons à Inflexible over time à Computationally intensive à Uses current attributes Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
72. Census Approach ) T ( Px 2 1 ) t ( Px ) 0 ( Px 2 1 dt ) t ( Px E 1 T 1 t T 0 x + + ≈ = ∑ ∫ − = Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
73. Census Approach Pros à Flexible study period à Based on historic values Cons à Approximations à Complex formulae Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
74. Multi-State Approach ƒ Policy level study based on business model ƒ Data intensive (data warehouse) ƒ Ultimate flexibility – true data, slice and dice ƒ Simplest calculation ƒ Population validation Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
75. Multi-State Approach Pros à Flexible study period à Easy to add new risk factors à Simple computations à More accessible Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
76. Comparison of Approaches Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
77. Comparison of Approaches Direct Approach: All exposure as Georgia Census Approach: Approx exposure at that time Multi-state: Exact exposure either way + ability to study Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
78. Comparison of Approaches Direct Approach: Calculate exposure depending on study Census Approach: Calculate exposure depending on study Multi-state: Single exposure filtered based on event Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
79. Comparison of Approaches 9 9 True historic attributes 9 Supporting demographics 9 9 Flexible time period 9 9 Precise exposure calculation Multi-state Census Direct Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
80. Other considerations ƒ Supporting demographics ƒ Credibility Testing / Supplemental data ƒ Regression line fitting ƒ Data Mining à Cluster algorithms to define grouping à Predictive algorithms to manage experience ƒ Source of Earnings à Contingency sources should be consistent with experience Finding the Truth IT Puzzle BI History BI Key Ideas Applications Experience Studies
81. Source of Earnings ƒ Used to explain earnings ƒ Reconcile assumptions ƒ Can be based on statutory, GAAP or management reserves ƒ EBS is a management tool ƒ Related to experience studies and pricing profit signature Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
82. BI vs. Traditional Approach ƒ Traditional approach explains modeled earnings à Uses projected actual Actual Business Actual Business Transaction File SOE Calcs Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
83. BI Approach Actual Events Actual Events Actual Events Actual Events Actual Events Data Warehouse Actual Experience Reserve Calcs SOE Analysis and Reporting Constantly Update Data Analysis of actual experience (not reserve model) ƒ BI approach explains actual earnings Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
84. Other limitations of traditional approach ƒ Often analyzed at fund level, with a substantial degree of approximations. ƒ Approximations may not be understood ƒ Static – prevents analysis by product, sales office or demographic ƒ Different approaches by line of business ƒ Generally not divisible over time Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
85. Compounding Assumptions ƒ Experience in earlier periods impacts perceived results in later periods ƒ Illustrated with simple example à Mortality as expected à Withdrawals 50% of expected for 1st three quarters ƒ Expected values in Q4 based on artificial population Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
86. Projection at Outset vs. Changing 88,000 90,000 92,000 94,000 96,000 98,000 100,000 102,000 Start Q1 Q2 Q3 Q4 expected actual Understatement of Inforce Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
87. Actual vs. Expected Mortality 340 350 360 370 380 390 Q1 Q2 Q3 Q4 Actual Expected Mortality appears to be worse than expected due to calculation being based expected inforce Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
88. Monthly Recognition of Sources ƒ Impact of sales recognized in month of issue ƒ Monthly sources accumulated using Fund return ƒ Flexible over time à yearly results are easily accumulated from quarterly results. ƒ Simple formula ƒ Reduces compounding and interaction ƒ Contingency sources are based on the immediate impact of the event ƒ Gives immediate impact of sales ƒ After the month of issue, policies are included in the “in-force” analysis of variations. Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
89. Projection at Outset vs. Changing ƒ Projection at outset: à Less accurate à May show business plan numbers ƒ Changing à More meaningful for decisions à Explains earnings! Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
90. SOE and BI ƒ A BI system with coverage level reserves and earnings allows: à Knowledge discovery with regard to profitability and performance by product, sales office or client segment à Can lead to successful management action ƒ Makes SOE a management tool Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
91. Report Structure ƒ Linear report ƒ Grid report X X X Total Earnings X X X Sales Impact X X X Basis Change X X X Contingency … X X X Transaction … X X X Investment Total Expected Experience Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
92. General Method ƒ Applicable to any reserving method or basis, including à Gross or net premium valuation à Statutory, GAAP, Management, Embedded Value ƒ Variations by line of business due to contingencies ƒ May vary components depending on application, but always consistent ƒ No approximations Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
93. General Method ƒ Define calculation order ƒ Begin with actual earnings exp’n ƒ Modify 1st var. from “act’l” to “exp’d” ƒ Subtract modified from initial exp’n ƒ Modify 2nd var. from “act’l” to “exp’d” ƒ Subtract modified from prior exp’n ƒ Continue through sources ƒ Final earnings = zero à mgmt earnings on mgmt reserves ƒ BI approach uses this to derive formulae Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
94. Example Formulae ƒ Interest = Act’l interest on cash-flows + res. – Exp’d interest on cash-flows + res. ƒ Life Insurance Mortality = -(Actual Strain – Expected Strain) ƒ Disablement = Actual Gain – Expected Gain à Gain = Reserve Released – Reserve Set Up ( )( ) ∑ − − + = Initial Beginners m a a a m i i E P V 0 ( ) ( )         − − − − = ∑ ∑ Initial Beginners m m Initial Deaths m V S q V S 0 , 1 0 , 1 ( ) ( ) ∑ ∑ − − − = Healthy Beginners m D m H m Healthy ts Disablemen m D m H V V d V V 0 , 1 0 , 1 0 , 1 0 , 1 Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
95. Reconciling to Actual Earnings ƒ Sum of Sources = Actual Earnings ƒ No approximations necessary ƒ Known approximations due to data availability à Magnitude can be estimated à Modeling variation ƒ Also acts as an error-check on the calculation à Or an order-check on approximations Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
96. Benefits Data Warehouse Approach ƒ Accessible across organization ƒ Explain components ƒ Integrate with other reports ƒ Flexible over time ƒ Analysis by product, sales office, demographic etc ƒ Continuous approach ƒ EBS can be an effective management tool Finding the Truth IT Puzzle BI History BI Key Ideas Applications Source of Earnings
97. Thank you for attending
98. Further Information ƒ www.insightdecision.com à Links to other sites à Several papers ƒ The Analysis of Insurance Earnings by John McGarry and Kevin Pledge
99. Presenters Kevin Pledge kpledge@insightdecision.com (905) 475 3282 x 2 Robert LaLonde rlalonde@insightdecision.com