|
|
Post by Admin on Dec 26, 2019 13:33:29 GMT -8
Feel free to ask any questions, we will try to answer all of them. However, please be aware that some questions might not be answered. Best of luck! Update 1/9/2020: Please delete column B from the Renewal Training Data sheet. Please use Column H as the net dental renewal increase.Updates 1/11/2020: - In the Renewal Training Data sheet, please use "Stage", not "Won/Loss". The column "Won/Loss" contains errors in some formulas.
- Given some group number, there may be significant changes between the group size between the renewal training data and the in-force metrics. However questionable the data, we recommend you do not focus on this discrepancy during your presentation. Assume all data given is accurate, even if you see large swings in group size from the training data to the in-force data.
|
|
|
|
Post by Ivan Escusa on Jan 9, 2020 19:27:54 GMT -8
In Sheet 2 of the Excel, there are two columns called "Net Dental" which has the same values for the first 5000 or so rows, but after that, the values are different. Is this what the sheet is supposed to be like?
|
|
|
|
Post by Admin on Jan 9, 2020 19:41:21 GMT -8
In Sheet 2 of the Excel, there are two columns called "Net Dental" which has the same values for the first 5000 or so rows, but after that, the values are different. Is this what the sheet is supposed to be like? Please use column H, not column B. We have sent out an email to all teams about this issue; thank you for bringing it up. |
|
|
|
Post by Jean Ngo on Jan 10, 2020 20:05:15 GMT -8
We observed that for the second and third sheet, there are different number of entries (starting at row 4867). Is that how the data is supposed to look like? This presents complications in comparing data between the client group.
|
|
|
|
Post by Admin on Jan 10, 2020 20:36:40 GMT -8
We observed that for the second and third sheet, there are different number of entries (starting at row 4867). Is that how the data is supposed to look like? This presents complications in comparing data between the client group. Yes, this is intentional. Notice that sheet 2 is titled "Renewal Training Data", and consists of all groups and their results ("Closed Won" or "Closed Lost"). On the other hand, sheet 3 ("In-Force Group Metrics") contains data only for the company's in-force book of business. There shouldn't be any comparison of data necessary. The first two tasks should be completed using the renewal training data, since this is the only sheet containing information about wins and losses. All other tasks should be completed using the probability model built in task 2, along with the in-force data. |
|
|
|
Post by team28 on Jan 11, 2020 11:08:29 GMT -8
In the "renewal training data" sheet, there is a column titled "Net Dental Renewal Increase", is it the same concept as "rate increase" (appearing in the second last line of the Introduction in the pdf). Also, there seems to be 2 meaning of "rate increase": the increase in the ratio/percentage of people renewed, or the increase in the charge of the renewal plan. Could you clarify which one is the exact meaning?
|
|
|
|
Post by Admin on Jan 11, 2020 11:24:36 GMT -8
In the "renewal training data" sheet, there is a column titled "Net Dental Renewal Increase", is it the same concept as "rate increase" (appearing in the second last line of the Introduction in the pdf). Also, there seems to be 2 meaning of "rate increase": the increase in the ratio/percentage of people renewed, or the increase in the charge of the renewal plan. Could you clarify which one is the exact meaning? Net dental renewal increase refers to the increase in rates upon renewal. In the tasks, rate increase is defined as it usually is within the insurance industry. |
|
|
|
Post by Chloe on Jan 11, 2020 14:04:38 GMT -8
Hi,
In the sheet "Renewal Training Data", are the Column D "Stage" and Column I "Won/Loss" meant to mean the same idea? Since all "Closed Won" should be corresponding to "1" according to the formula. but there are 15 data which are the exceptions (0 for a "Closed Won"). Was that a mistake or on purpose?
|
|
|
|
Post by Admin on Jan 11, 2020 14:39:13 GMT -8
Hi, In the sheet "Renewal Training Data", are the Column D "Stage" and Column I "Won/Loss" meant to mean the same idea? Since all "Closed Won" should be corresponding to "1" according to the formula. but there are 15 data which are the exceptions (0 for a "Closed Won"). Was that a mistake or on purpose? Please use "Stage", not "Won/Loss". The column "Won/Loss" contains errors in some formulas. |
|
|
|
Post by Team22 on Jan 11, 2020 14:44:46 GMT -8
Hi,
So we compared sheet "Renewal Training Data" and "In-Force Group Metrics" and found that the variable "Average Group Size" behaves oddly.
This variable has the same value if you compare them by the row number however 85+% of them do not correspond to the the same "Group Number", which should be the key indexing variable as we understand.
Here's an example:
In row 8 of both sheet, "Average Group Size" is 58.0501225058817,
However row 8 in "In-Force" sheet is group TN96985, which row 9 in "Renewal Training",
and has corresponding 6.27816830314224 as "Average Group Size".
It is odd that group CA53556 in row 9 of "In-Force" has the same "Average Group Size" of 6.27816830314224.
Could you confirm if it is intended to have the data this way or is it corrupt? Thanks.
|
|
|
|
Post by Admin on Jan 11, 2020 15:37:51 GMT -8
Hi, So we compared sheet "Renewal Training Data" and "In-Force Group Metrics" and found that the variable "Average Group Size" behaves oddly. This variable has the same value if you compare them by the row number however 85+% of them do not correspond to the the same "Group Number", which should be the key indexing variable as we understand. Here's an example: In row 8 of both sheet, "Average Group Size" is 58.0501225058817, However row 8 in "In-Force" sheet is group TN96985, which row 9 in "Renewal Training", and has corresponding 6.27816830314224 as "Average Group Size". It is odd that group CA53556 in row 9 of "In-Force" has the same "Average Group Size" of 6.27816830314224. Could you confirm if it is intended to have the data this way or is it corrupt? Thanks. Please reference our response below to understand how to continue with the analysis. There shouldn't be any comparison of data necessary. The first two tasks should be completed using the renewal training data, since this is the only sheet containing information about wins and losses. All other tasks should be completed using the probability model built in task 2, along with the in-force data. To answer your question: Yes, this could be considered a data quality issue. However, we would not recommend you spend much time on this. The intended approach is as follows: - Complete tasks #1 and #2 using the "Renewal Training Data" sheet. This should yield a probability model.
- Complete tasks #3 and #4 using the "In-Force Group Metrics" sheet, along your probability model from task #2. At this point, please disregard any data discrepancies between the two sheets.
- Discuss your results in task #5.
Throughout your analysis, assume that all data is accurate (however unrealistic it may be for TN96985's group size to grow from 6 to 58 in a year). During your presentation, we would recommend you ignore this discrepancy -- there should be plenty to present without spending time focusing on this issue. |
|
|
|
Post by Guest on Jan 11, 2020 20:41:55 GMT -8
In task 3, are we supposed to do a 1-year strategy or a 3-year strategy?
|
|
|
|
Post by Admin on Jan 11, 2020 22:43:18 GMT -8
In task 3, are we supposed to do a 1-year strategy or a 3-year strategy? 3 years. The idea is for your strategies in task 3 to inform your strategies for task 4, which explicitly requires a 3-year strategy. |
|
|
|
Post by guest on Jan 12, 2020 17:26:17 GMT -8
For task 1, when we are asked if the win rates are intuitive for each factor, are we restricted only to the 3 factors we are given bands for?
|
|
|
|
Post by Admin on Jan 12, 2020 20:18:52 GMT -8
For task 1, when we are asked if the win rates are intuitive for each factor, are we restricted only to the 3 factors we are given bands for? Please leave brief commentary for each factor (including those for which bands are not provided) within your report. During your presentation, you may choose which ones (if any) to focus on. |
|
|
|
Post by Anonymous on Jan 13, 2020 18:59:17 GMT -8
Hi,
In part 2b we are wondering if we are plot the individual curves for each factor or a surface.
Thanks
|
|
|
|
Post by Admin on Jan 13, 2020 19:01:38 GMT -8
Hi, In part 2b we are wondering if we are plot the individual curves for each factor or a surface. Thanks You may decide what the best way to visualize your curves is. However, please keep in mind that your visuals should be appropriate for your audience. Recall that your audience is upper management (think CFO, CEO, etc.), not a chief actuary. |
|
|
|
Post by Guest on Jan 14, 2020 19:12:11 GMT -8
Is the renewal template in the excel sheet referring to the strategies for question 3?
Thanks
|
|
|
|
Post by Admin on Jan 14, 2020 20:57:07 GMT -8
Is the renewal template in the excel sheet referring to the strategies for question 3? Thanks The renewal template is a recommended preliminary approach for both tasks 3 and 4. You may use this as your final approach, or you may implement refinements on top of this. You are not required to use the template, it is merely a suggestion. |
|
|
|
Post by participant on Jan 15, 2020 10:38:32 GMT -8
For task 2, is the renewal model we're supposed to create a mathematical model or just a probability distribution of the data we've been given?
|
|
