MIS Process Document

 

Brief of the process

Life Insurance Council collates MIS data from its member companies on monthly basis.  The MIS is collected in various formats and at various intervals (dates) during the month. The various MIS reports collected by Council are (a) Agents report, (b) Monthly data, (c) Products report and (d) detailed new business, renewal premium and other business parameters.

The below process document is for the part (d) detailed new business, renewal premium and other business parameters.

As per the schedule, member companies are expected to upload the report on or before 26th of every month of the business transacted by them in the previous month. For e.g. data (report) for the month of a July 2016 will be uploaded by members by 26th August 2016. As all companies are mandated to upload the report by 26th of the month, a consolidated statement is to be generated on or before 26th.

Kindly note the reports uploaded consist of current year and previous year data

About the Reports

The MIS report in discussion consists of two sheets and is uploaded in .xls & xlsx formatby individual companies. Sheet I of the report consists of various parameters such as policy related, premium related information. Sheet II of the report contains information relating to surrenders, death claim and new business premium - channel wise data. The format of reporting is illustrated in the below document which elaborates the various parameters.

                                                      

As the data parameters in Sheet I and Sheet II are defined, the possibility of companies entering or omitting a parameter is none. However we will have to ensure that a numeric value is entered in each of the cell to avoid any omissions.

The consolidated sheet above consist of data report uploaded by individual company from cell ‘I’ to cell ‘BC’ (i,e summation of Individual companies current year data) and ‘J’ to ‘BD5’(i,e summation companies previous year data )

 

The cells from ‘BM’ to ‘BP’ is summation of all the respective cells uploaded by the companies.

To give you an example – the data in cell ‘BM 5’ is summation of cells  from ‘I 5’ , ‘K 5’ ‘M 5’, ‘O 5’ till ‘BC 5’.

Similarly all cells in ‘BM’ to ‘BP’ are summation of respective cells (Please refer below illustration).

 

 

 

 

 

 

 

Exception to the Case

(I)     For 8 - New Business Avg Premium (Individual)

In Sheet I, Parameter 8 (a) (b) and 9 (a) (b) values need to be extracted from different sources.

8 (a) - For values in cell ‘BM 21’ to ‘BP 21’ and ‘BM 22’ to ‘BP 22’ the values need to be extracted from the detailed report of New Business data which is developed.

Formula applied:-

Regular - New Business Average Premium = Industry Non-Single Premium (upto the year fig) / Industry No. of Non-single policy (upto the year fig) (Cells Highlighted in pink  & blue)

Regular - New Business Average Premium=

 

 

For value in cell ‘BM 22’ (value for industry) the value is taken from Summary NB 2 data and is division of premium by policy MM/TT ( (D8 / I8) for year 2016 &( F8 / K8 ) for year 2015.

Single - New Business Average Premium = Industry Single Premium (upto the year fig) / Industry No. of single policy (upto the year fig). (Cells Highlighted in yellow & red)

Attaching NB 2 file for ready reference.

Similarly the value in ‘BN 22’(value for Private companies) is taken from detailed NB data and is division of premium by policy XY/ NNFormula applied :-

Regular - New Business Average Premium = Industry Non-Single Premium (upto the year fig) / Industry No. of Non-single policy (upto the year fig) (excluding LIC figure = private figures**)

Similar logic needs to be followed for values in other cells for Parameter no 8 so for previous year value in ‘BO 21’ will be division of values from KK/ CC.

** Please note Private Sector value is arrived by subtracting the LIC figures with Industry (Summary Total). Please refer the attached filed.

(II)   For Parameter 9 (A)       

Formula

Avg Sum Assured (Individual) (Rs)  =  Total Sum Assured by Insurers  / Total Individual New Business Policies (Single + Regular)

For arriving at the values of cell BM24, BN24, BO24 & BP24, the above formula is used.

Calculation of Total Sum Assured by Insurer & Total Individual New Business Policies (Single + Regular) is shown in the attached sheet.

 

 

(III) For Parameter 9(B)

For this parameter the value needs to be generated by putting a formulae for e.g value in cell BM 25 is division of the two values in cell (BM17/BM11*10000000)of the same report.

Similar for value in BN 25 following values from following cells need to be taken (BN17/BN11*10000000).

Same applies to BO 25 & BO 26.

 

 

(iv) Parameter 11 - Assets held break-up (Rs cr) Total = (33rd Row)

Formula = Equity (at Market Value) + Equity Net Buying and Selling during the current year (For Jun 2016 ) + Fixed Income (at Book Value)+ Others.  This formula applies to all cells from I33 to BP 33

(v) Parameter 12 - Assets held (Rs cr) Total = (37th row)

Formula = Linked + Others. This formula applies to all cells from I37 to BP 37

Validation & Warning

Along with basic validations of New Business Report the specific warnings and validations for said report is listed below:-

  1. Parameter 11 should be equal to parameter 12. If the values do not the match, file upload should be restricted at user level (Insurer).
  2. Parameter 3 Number of agents (Individual) should tally with the Net no. of agents for the month of “Individual Agents report”. If not Warning message should be displayed to admin in the interface.
  3. Parameter 14 & 15 – (Regular & Single Premium) Cell BM 44+BM45 + BM47 + BM48 Total should tally / match with parameter B of 2nd sheet – (channel wise) Total Amount in Rs crs. Cell BA68. Similarly Cell BO 44+BO45 + BO47 + BO48 should tally / match with parameter B of 2nd sheet – (channel wise) Total Amount in Rs crs. Cell BC68. If not Warning message should be displayed to admin in the interface.

             

  1. Parameter B of 2nd sheet – (channel wise) Total Amount policies & Total Amount should tally / match {Cell BA 63 & BA68 (For Current Year) } with NB report summary sheet (Cell No. D12& I12). Similarly for previous year (Cell No. BC 63 & BC 68) of MIS report should tally with NB report summary sheet (Cell No. F12& K12).

Same applies to private sector too.

MIS report sheet 2 highlighting the respective cells.

NB report screen shot. Highlighting the cells.

 

 

Basic validation points similar to NB report for ready reference:-

1.      Upload all 24 members’ data (xls or  xlsx)

2.      Input File - The period must be mentioned only in the following format: “New Business as on ‘Month-Year’” Month – First Three Alphabets of Month with First Alphabet being Capital Ex. January = Jan; September – Sep Year – YYYY format eg. 2016, 2017.

3.      Input File – Should not be password protected

4.      Input File – Negative Values under cell must be taken by the system.

5.      Input File - Blank Spaces must not be taken, Only Numeric Value must be taken

6.      Input File - No Junk data like Alphabets special characters should be accepted.

7.      Output Files – Past data will have to be uploaded separately as one batch upload from backend.

8.      Downloads of report should be made available in excel & PDF files (All Sheets)

a.      Provide download link for each company data

b.      Consolidated data (Total of 24 companies)

9.      Access Rights To IRDAI – Download and view access of all Member Companies, Consolidated report except admin rights (resetting of passwords)

Reminder Mail on every 1st of the month & Acknowledgment mail for every upload of data should be sent to Insurance Companies with CC to council should be sent.