IFRS 17 GMM Insurance Financial Projection Model
User-friendly IFRS 17 GMM insurance 5-year quarterly insurance business financial projection excel model for 10 insurance portfolios / groupings.
DOWNLOAD DETAILS:
Excel Models
Populated & Unpopulated
€169.00
User-friendly Excel model for the preparation a of 5-year 3 statement (Income Statement, Balance Sheet and Cash flow Statement) financial projection with quarterly timeline for a general insurance company on an IFRS 17 General Measurement Model (GMM) approach generating insurance revenue, insurance service expenses, reinsurance expenses, finance expenses, investment return and other non-technical expenses.
The model allows the user to model up to 10 different insurance portfolios / grouping including separate premium amounts; reinsurance arrangements; commission and other directly attributable expenses; payment and recognition patterns; loss ratios and risk adjustment percentages. The model calculates balances for contractual service margin, risk adjustments and present values for future cash flows for each insurance portfolio / grouping.
Apart from insurance revenues , the model allows the user to model other operating expenses, investment return, fixed assets, corporate tax, borrowings, equity additions and dividend distributions.
KEY OUTPUTS
-
Projected full financial statements (Income Statement, Balance Sheet and Cash flow Statement) presented on a quarterly basis across 5 years and summarised on an annual basis.
-
Dashboard with:
-
Summarised projected Income Statement and Balance Sheet;
-
Compounded Annual Growth rate (CAGR) for each summarised income statement and Balance Sheet line item;
-
List of key ratios including, amongst other, gross service resultmargin; cession ratio, investment return, profit margins, average return on equity and average debt to equity ratio;
-
Bar charts summarising income statement and Balance Sheet projections;
-
Chart and table presenting insurance service result by portfolio / grouping.
-
KEY INPUTS
Setup Inputs:
-
Name of business;
-
Currency;
-
First projection year;
-
Naming for insurance portfolios, payment patterns, recognition patters, operating expenses, and fixed asset categories;
Pattern Inputs:
-
Premium payment patterns;
-
Claims payment patterns;
-
Recognition patterns.
Projection Inputs:
-
Insurance Service Result:
-
Gross premium written;
-
Gross loss ratios;
-
Gross commission ratios;
-
Risk adjustment percentage;
-
Payment and recognition patterns;
-
Proportional and non-proportional reinsurance
-
Discount rate.
-
Other operating expense inputs;
-
Financial investment inputs including return percentage and target cash balance;
-
Dividend inputs including amount or percentage of retained earnings and frequency;
-
Fixed Assets including addition amounts and useful life;
-
Borrowings including addition/redemption amounts and interest rate;
-
Share Capital additions.
MODEL STRUCTURE
The model comprises of 8 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The tabs to be populated by the user are the input tabs ('i_Setup', ‘i_Patterns’ and 'i_Assumptions'). The calculation tab uses the user-defined inputs to calculate and produce the projection outputs which are presented in 'o_Fin Stats' and ‘o_Dashboard’.
​
System tabs include:
-
A 'Front Sheet' containing a disclaimer, instructions and contents;
-
A Checks dashboard containing a summary of checks by tab.
KEY FEATURES
-
The model follows best practice financial modelling guidelines and includes instructions, checks and input validations;
-
The model is not password protected and can be modified as required following download;
-
The model contains a dynamic timeline that allows a different starting projection period;
-
Timeline is split on a quarterly basis and summarised on an annual basis;
-
Insurance Contract assets and liabilities are split into CSM, risk adjustment and present value for future cash flows;
-
The model allows for the following number of underlying categories for each line item (these can be easily expanded if required):
-
Insurance Portfolios / groupings – 10 portfolios / groupings;
-
Operating expenses – 10 categories;
-
Payment Patterns – 10 patterns;
-
Recognition patterns – 10 patterns;
-
Fixed Assets – 3 categories;
-
Apart from projecting revenue and costs the model includes the possibility to model, investment return, fixed assets, borrowings, dividends and corporate tax;
-
Business name, currency, starting projection period are fully customisable;
-
Revenue, cost, fixed asset and borrowing category descriptions are fully customisable;
-
The model includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors.