Actuarial Science

nxnm

Main

Home >Homework Answsers >Business & Finance homework help

Got Excel data a few questions to be answered from the Excel data

2 years ago
29.10.2024
200

Report issue

files (3)

SubmitCompletedExcel.pdf
ExperienceStudyQuestionList.pdf
Excel.xlsx

SubmitCompletedExcel.pdf

ACTSCI 599

Fall 2024

Submit Completed Excel File

Students need to turn in their completed Excel file. Bring file on a flash drive to class on due

date (10/31/2024).

• Workbook should include (but not be limited to):

o 3 original data tabs provided.

o Multiple supporting tabs that answer all Experience Study Questions.

o Multiple supporting tabs looking into other items of note.

• Workbook should be organized

o I suggest separate tabs for different questions or items you are looking into.

o I suggest adding text or description cells.

o A summary tab can be helpful.

▪ Some individual tabs can get busy. You may want to put the main

takeaways on a summary tab.

ExperienceStudyQuestionList.pdf

ACTSCI 599

Fall 2024

Experience Study Question List

Use your Excel work to determine the answers to these questions. Questions should be

answered in a separate word document (or similar format). Most answers should have

supporting exhibits/charts/tables to illustrate the written responses. These supporting

illustrations can be screenshots or copy/pasted from your Excel file.

Trends

1. Did the experience improve or worsen over time?

2. Were there any sharp spikes or troughs in certain years?

3. Were there certain blocks of business that exhibited different than expected trends?

Why?

4. Were experience changes driven by a shift in the mix of products, demographics, etc.?

5. Did experience generally move in one direction over the years, or did it fluctuate in a

seemingly random manner?

Relationships

1. What were the key drivers impacting the loss ratios? Ex: certain demographic

information, certain policy information, etc. 2. Is there a seasonality to the claims throughout the year? 3. What are the actual policy lapse rates by duration? Do you notice anything that stands

out? 4. Today we do not rate by smoking status, do you think we should? 5. What do you think the impact on paid claims would be if we were to remove the waiting

periods? 6. What advantages and disadvantages do PPO plans have compared to this indemnity

plan from: a. a customer perspective? b. an insurer perspective?

7. Are there any agencies that we should consider talking to about their experience or consider terminating?

8. The only underwriting question that is asked today is “Is the Proposed Insured a dentist, dental hygienist, or employed in a dental office or clinic or as an insurance agent?” Do you think this question is needed? What other knock-out questions could we ask that could potentially improve the risk of future customers?

9. We do not currently market the Orthodontic Benefits today. What do you think the pros and cons of this may be? If we start to market, should we include it on every plan or make it optional? Consider anti selection (adverse selection).

Outliers

1. Were there any business cells that showed significantly different results than the rest of

the business?

2. Did external factors come into play in the experience? Could these factors impact the

future? Are adjustments needed to correct for these factors?

Credibility

1. What happens to Z if the threshold (60,000) increases?

2. What happens to Z if the threshold (60,000) decreases?

3. Is this dataset credible for the following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

4. What percentage member month change would we need to see for Z = 1 in the

following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

SubmitCompletedExcel.pdf

ACTSCI 599

Fall 2024

Submit Completed Excel File

Students need to turn in their completed Excel file. Bring file on a flash drive to class on due

date (10/31/2024).

• Workbook should include (but not be limited to):

o 3 original data tabs provided.

o Multiple supporting tabs that answer all Experience Study Questions.

o Multiple supporting tabs looking into other items of note.

• Workbook should be organized

o I suggest separate tabs for different questions or items you are looking into.

o I suggest adding text or description cells.

o A summary tab can be helpful.

▪ Some individual tabs can get busy. You may want to put the main

takeaways on a summary tab.

ExperienceStudyQuestionList.pdf

ACTSCI 599

Fall 2024

Experience Study Question List

Use your Excel work to determine the answers to these questions. Questions should be

answered in a separate word document (or similar format). Most answers should have

supporting exhibits/charts/tables to illustrate the written responses. These supporting

illustrations can be screenshots or copy/pasted from your Excel file.

Trends

1. Did the experience improve or worsen over time?

2. Were there any sharp spikes or troughs in certain years?

3. Were there certain blocks of business that exhibited different than expected trends?

Why?

4. Were experience changes driven by a shift in the mix of products, demographics, etc.?

5. Did experience generally move in one direction over the years, or did it fluctuate in a

seemingly random manner?

Relationships

1. What were the key drivers impacting the loss ratios? Ex: certain demographic

information, certain policy information, etc. 2. Is there a seasonality to the claims throughout the year? 3. What are the actual policy lapse rates by duration? Do you notice anything that stands

out? 4. Today we do not rate by smoking status, do you think we should? 5. What do you think the impact on paid claims would be if we were to remove the waiting

periods? 6. What advantages and disadvantages do PPO plans have compared to this indemnity

plan from: a. a customer perspective? b. an insurer perspective?

7. Are there any agencies that we should consider talking to about their experience or consider terminating?

Outliers

1. Were there any business cells that showed significantly different results than the rest of

the business?

2. Did external factors come into play in the experience? Could these factors impact the

future? Are adjustments needed to correct for these factors?

Credibility

1. What happens to Z if the threshold (60,000) increases?

2. What happens to Z if the threshold (60,000) decreases?

3. Is this dataset credible for the following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

4. What percentage member month change would we need to see for Z = 1 in the

following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

SubmitCompletedExcel.pdf

ACTSCI 599

Fall 2024

Submit Completed Excel File

Students need to turn in their completed Excel file. Bring file on a flash drive to class on due

date (10/31/2024).

• Workbook should include (but not be limited to):

o 3 original data tabs provided.

o Multiple supporting tabs that answer all Experience Study Questions.

o Multiple supporting tabs looking into other items of note.

• Workbook should be organized

o I suggest separate tabs for different questions or items you are looking into.

o I suggest adding text or description cells.

o A summary tab can be helpful.

▪ Some individual tabs can get busy. You may want to put the main

takeaways on a summary tab.

ExperienceStudyQuestionList.pdf

ACTSCI 599

Fall 2024

Experience Study Question List

Use your Excel work to determine the answers to these questions. Questions should be

answered in a separate word document (or similar format). Most answers should have

supporting exhibits/charts/tables to illustrate the written responses. These supporting

illustrations can be screenshots or copy/pasted from your Excel file.

Trends

1. Did the experience improve or worsen over time?

2. Were there any sharp spikes or troughs in certain years?

3. Were there certain blocks of business that exhibited different than expected trends?

Why?

4. Were experience changes driven by a shift in the mix of products, demographics, etc.?

5. Did experience generally move in one direction over the years, or did it fluctuate in a

seemingly random manner?

Relationships

1. What were the key drivers impacting the loss ratios? Ex: certain demographic

information, certain policy information, etc. 2. Is there a seasonality to the claims throughout the year? 3. What are the actual policy lapse rates by duration? Do you notice anything that stands

out? 4. Today we do not rate by smoking status, do you think we should? 5. What do you think the impact on paid claims would be if we were to remove the waiting

periods? 6. What advantages and disadvantages do PPO plans have compared to this indemnity

plan from: a. a customer perspective? b. an insurer perspective?

7. Are there any agencies that we should consider talking to about their experience or consider terminating?

Outliers

1. Were there any business cells that showed significantly different results than the rest of

the business?

2. Did external factors come into play in the experience? Could these factors impact the

future? Are adjustments needed to correct for these factors?

Credibility

1. What happens to Z if the threshold (60,000) increases?

2. What happens to Z if the threshold (60,000) decreases?

3. Is this dataset credible for the following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

4. What percentage member month change would we need to see for Z = 1 in the

following cells/cuts:

a. In total (all member months)

b. Calendar year

c. Dental level

other Questions(10)