Create and submit 3 spreadsheets (can be in a single workbook) for Zhu Industrie
ID: 3318182 • Letter: C
Question
Create and submit 3 spreadsheets (can be in a single workbook) for Zhu Industries; Answer all questions on this sheet.
Zhu Industries provides a self-funded group medical insurance for its full –time employees. In December of 2016 they employee 18,533 full-time employees and they are currently expanding their full-time work force by 2% each month. Each employee contributes $125 a month to the insurance pool. The average claim per employee in December of 2016 is $250 (i.e. they paid out 18,533*250 = $4,633,250 from the insurance fund in December 2016). The amount of the average claim is anticipated to rise by 1% each month in 2017. The company cost is considered to be the total claims minus the employee contribution. Zhu estimates that if annual company costs are below $37 million they should continue with the self funded plan. If they exceed $37 million they are considering various insurance companies.
Part A
Create a spreadsheet that estimates the expected monthly and annual company cost in 2016. This is a simply deterministic model and should not take long.
a) Based on this estimate would Zhu continue with the self-funded plan?
Part B
Zhu has looked more closely at their growth estimates. They now estimate that the number of employees will grow between 1 and 5 percent each month with a uniform distribution. They still estimate they average claim per employee monthly growth rate to be $2.5 but now wish to model it as a normal distribution with a standard deviation of $3.
b) Based on this estimate would Zhu continue with the self-funded plan?
Part C
Repeat the model you created in part B 1000 times and create any type of graph (i.e. histogram, line plot, area plot) showing the modeled distribution of the out come.
Explanation / Answer
Answer
After calculations, the spreadsheet would look like
Period
Employee Count
Per Employee contribution
Total contribution
Average claim
Total claim
Company Cost
Dec-16
18533
125
23,16,625
250
46,33,250
23,16,625
Jan-17
18904
125
23,62,958
253
47,73,174
24,10,217
Feb-17
19282
125
24,10,217
255
49,17,324
25,07,107
Mar-17
19667
125
24,58,421
258
50,65,827
26,07,406
Apr-17
20061
125
25,07,589
260
52,18,815
27,11,226
May-17
20462
125
25,57,741
263
53,76,423
28,18,682
Jun-17
20871
125
26,08,896
265
55,38,791
29,29,895
Jul-17
21289
125
26,61,074
268
57,06,063
30,44,989
Aug-17
21714
125
27,14,295
271
58,78,386
31,64,091
Sep-17
22149
125
27,68,581
273
60,55,913
32,87,332
Oct-17
22592
125
28,23,953
276
62,38,802
34,14,849
Nov-17
23043
125
28,80,432
279
64,27,214
35,46,782
Total Cost
3,47,59,200
As per Information given in the question,
Employee count grows by 2% every month
Average claim is expected to raise by 1% every month
Assuming a time period from Dec-16 to Nov-17(1 year period),
Total cost for the company is 34,759,200 or 34.75 Million
As the Total cost for the company is less than 37 million, Zhu should continue with the self-funded plan
Period
Employee Count
Per Employee contribution
Total contribution
Average claim
Total claim
Company Cost
Dec-16
18533
125
23,16,625
250
46,33,250
23,16,625
Jan-17
18904
125
23,62,958
253
47,73,174
24,10,217
Feb-17
19282
125
24,10,217
255
49,17,324
25,07,107
Mar-17
19667
125
24,58,421
258
50,65,827
26,07,406
Apr-17
20061
125
25,07,589
260
52,18,815
27,11,226
May-17
20462
125
25,57,741
263
53,76,423
28,18,682
Jun-17
20871
125
26,08,896
265
55,38,791
29,29,895
Jul-17
21289
125
26,61,074
268
57,06,063
30,44,989
Aug-17
21714
125
27,14,295
271
58,78,386
31,64,091
Sep-17
22149
125
27,68,581
273
60,55,913
32,87,332
Oct-17
22592
125
28,23,953
276
62,38,802
34,14,849
Nov-17
23043
125
28,80,432
279
64,27,214
35,46,782
Total Cost
3,47,59,200
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.