Health insurance product simulation (1000 runs)

File can be downloaded from
https://drive.google.com/file/d/0B6PHuk6I5Sxbb0dwSDVxRk9abVE/view?usp=sharing

Investment Simulation file -- 28th July 2016

The file can be downloaded from https://drive.google.com/file/d/0B6PHuk6I5SxbZkYtR2J6TkM3Vnc/view?usp=sharing

 

Simulation in CTP 3.3

File can be downloaded from here
https://drive.google.com/file/d/0B6PHuk6I5SxbSXI3bzYtM0hId28/view?usp=sharing

Simulation (Normal Distribution, Binomial Distribution, Health Insurance Product), Probability etc.

Files can be downloaded from here
https://drive.google.com/folderview?id=0B6PHuk6I5SxbbFItN2NZOGVONjA&usp=sharing

Simulation using Calc

2000 Simulation runs.
Risky investment.
Interest follows Normal Distribution.
 And few things more.

File can be downloaded from https://drive.google.com/file/d/0B6PHuk6I5SxbVzhiZWZfcnlFR0E/view?usp=sharing.

Simulation -- Multiple Investment -- Normal Distribution -- Binomial Distribution

Work done in this file looks similar to the post of 12th January 2016 except that the simulation run is higher. You can get the file of today's work at https://drive.google.com/file/d/0B6PHuk6I5SxbeVFjRlU4NjZqSk0/view?usp=sharing.  You should download the file to get the formula.

Simulation file - Normal and Binomial distributions

In this file, the cumulative amounts at the end of different years in an investment scheme has been simulated where the interest rate follows Normal distribution with mean 8% and std deviation 3%. In this, Rs. 50000 is being invested every year.

It also simulates number of death in different years in a population of 10000 with probability of death as .002. In this mean and std deviation keep changing every year. You can try multiple simulation runs using the technique shown while simulating the the investment part.

You can further try simulating an Insurance Cum Investment product using life table where the probability of death too will change with year.

Download the file to get the formula too. The file is available here
https://drive.google.com/file/d/0B6PHuk6I5SxbVzBxMGpQNTQwck0/view?usp=sharing

Exercise on simulation & classification

Sir ,

Pl. find the exercise.

Group-3

P.S.Rukmani

P.K.Mohanty

S.N.Gachhe

sss - Invitation to collaborate

Senni Subbu has invited you to contribute to the following shared folder:

sss

sir,
pl find attached the file
sss

Open

This email grants access to this item without logging in. Only forward it to people you trust.

Google Drive: Have all your files within reach from any device.

NORTH SOUTH - Invitation to collaborate

SIMON TK has invited you to contribute to the following shared folder:

NORTH SOUTH

SIR
SENDING THE PRESENTATION
SIMON T K AND GROUP

Open

This email grants access to this item without logging in. Only forward it to people you trust.

Google Drive: Have all your files within reach from any device.

Presentation on classification & simulation 08.01.2016

Dear Sir,  

Please find our presentation on the classification & simulation exercises.

https://drive.google.com/file/d/0BzvqxxKegOADemY2ZTNWT0g2Vzg/view?usp=sharing

Regards,

ARUN

THRIVIKRAMAN

JS CHAUHAN

KP MALLICK

Simulation file -- One time investment in risky product

We invest Rs. 50000 in a scheme where the interest is likely to vary following a Normal Distribution with mean 8% p.a and standard deviation 3% p.a.. What will be the cumulated amount after 10 years?

In this file, 1000 simulation runs have been done. It also finds the amount to invest so that the probability of cumulated amount becoming at least Rs. 120000 is 0.9. The file can be downloaded from
https://drive.google.com/file/d/0B6PHuk6I5SxbUTVmSGhOVEZzQjQ/view?usp=sharing

Simulation file

You can download the file from https://drive.google.com/file/d/0B6PHuk6I5SxbcGlacmJCVE44Qnc/view?usp=sharing.  Data related to premium, number of policies, claim amount distribution and administrative expenses are given in the IO sheet.

The solution file of the classification question is available here https://drive.google.com/file/d/0B6PHuk6I5SxbcHhkUzI1Z05XVVU/view?usp=sharing.

You know pivot table, filter, vlookup, if and many more functions in calc. Try to find following from the data given in the above file.

1. How many High valued customers are of age less than 30 years?
2. How many Regular Customers have Annual Income more than Rs. 25 lakhs?
3. How many Regular Customers have Age less than 30 years and Annual Income more than equal to Rs. 8 lakhs?

Share your solution and/or approach as comment to this post.

Another file on Classification with some summary (29th July)

You can download the file from here and work further on that.

https://drive.google.com/file/d/0B6PHuk6I5SxbcHhkUzI1Z05XVVU/view?usp=sharing

Two persons having same month in DOB

To calculate the probability of total number of persons varying from 13 to 1 and finding probablity for two persons having same month in their Date of Birth. 

Measurement of profit

Sir 

        If we the probability of highest clm payment from 1 lac to 1.5 lac to .1 and increased the probability of medium size of 25000 to 100000 to 0.4 adm cost 0.1 cost and probability of claim 0.2 and 0.1 otherwise by simulation we get profit +

  

lcd

A decision situation related to product design

Probability that a health policy become a claim policy is equal to 0.3 if age of the policy holder is more than 50 years otherwise it is 0.2. This policy is designed for a particular lifestyle people where 20% of the persons are above 50 years of age.

Claim amount distribution is linear varying between Rs. 1000 to Rs. 25000 with 0.5 probability, between Rs. 25000 to Rs. 100000 with 0.3 probability and between Rs. 100000 to Rs. 150000 with 0.2 probability.

10% of the premium collected is accounted for administrative cost.

Simulate this for 1000 policies taking premium as Rs. 6000 per policy. Experiment by changing the claim distribution pattern and other variables in this model.

At present, this product is heavily loss making. But it is possible to reduce the premium and still have profit with same claim amount distribution. It can be a win-win product. Share your views and findings in the comment below.

A calc file is available at  https://drive.google.com/file/d/0B6PHuk6I5SxbcmN3WnpQYW5rTzg/view?usp=sharing without documentation for help.

Who will meet the target first?

Few groups of sales professionals are given some sales target. They divide the target in 5 equal parts and plan to complete these parts at different rates. Their plan depend on their experience in similar product, area and sales. There are four plans given below by four different sales teams. Which one will achieve the target fastest. Numbers given below are sales rate for first part, 2nd part, 3rd part, 4th part and 5th part of the target respectively in numbers/day..

A) 25, 40, 60, 75, 50
B) 60, 90, 50, 30, 20
C) 40, 40, 60, 60, 50
d) 50, 50, 50, 50, 50.

What kind of statistical analysis can help us in taking right decision?

Another set of rules for classifying the customers

An Insurance organization wants to classify it’s customers based on following parameters:

Annual Income -- < Rs. 3 lakhs; Rs. 3 lakhs to < Rs. 8 lakhs; Rs. 8 lakhs to <Rs 20 lakhs; Rs. 20 lakhs and above.

Premium paid in current policies -- < Rs. 5 K; Rs. 5K to < 8 K; 8 K to < 15 K; 15 K and above.

Past Claim – Yes; No.

A customer is classified as an Excellent customer if his annual income is Rs. 3 lakhs or more, premium paid is Rs. 8 K or more and has no past claim.

A customer is classified as a Good customer if (i) he has past claim but his annual income is greater than equal to Rs. 8 lakhs and has paid premium of Rs. 15 K or above; (ii) he has no past claim and premium paid by him is Rs 8 K or above.

In all other cases, a customer is classified as a valuable customer.

Check the consistency of this system and suggest an algorithm to do such classification.

Also, generate 1000 records based on your assumptions on % of customers in different ranges of annual income, premium and claim history. Implement your algorithm on this data using calc and test your results.

Share your ideas, concern or approach in comment.

Use Nested if, AND/ OR operator with in IF statement

http://www.teach-ict.com/software_skills/Excel03/Ifthen/nestedif_workbook/nestedif_workbook.pdf

@ Pravin K

Classification of customers based on given business rules

Calc has been used to create 500 records of customers (simulated) and business rules have been applied to classify them. The file is available at  https://drive.google.com/file/d/0B6PHuk6I5SxbOWExbnVkaW9ndjA/view?usp=sharing. 

Studying Development officer-agentwise early claim data for last 5 years

Respected Sir,

For getting the desired result per year under the above segregation what can be done.

Regards,

Samit

data mining

sir 

          In data mining how the data mining and from where

L C DAS

Interesting decision situation under uncertainty

You win the deal only when you win at least two consecutive negotiations. The probability of your winning against F is 0.8 and against C is 0.1. Which one is better for winning the deal CFC or FCF?

The spreadsheet is available at
https://drive.google.com/file/d/0B6PHuk6I5SxbSGpyaHZ3MEl5Wm8/view?usp=sharing. 

When can he purchase the house?

(A) Mr. John’s present salary is $50000 per year and he gets an annual increment of 4%. He wants to purchase a house that costs $70000 in the beginning. However its cost increases by 1% every year. In which year Mr. John will be able to purchase the house if he saves 20 % of his earning every year?

(B) If he has to purchase the house in the 4th year itself, minimum what % of salary he should save for this purpose?


Create few variations of above questions and discuss them in the comment.