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.