Simulation modeling. Can use 3 spreadsheets in Excel (can be in a single workbook) for Zhu Industries; Answer all questions on this sheet.

Simulation modeling.

Can use 3 spreadsheets in Excel (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

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 use any type of graph (i.e. histogram, line plot, area plot) showing the modeled distribution of the out come.