D3: Adverse Selection in Health Insurance

A community has 10,000 people who are at risk of a serious illness. Five percent of the population is high risk (type H), and each H person has a 2% chance of becoming sick. The rest of the population is low risk (type L) and each L person has a 0.1% chance of becoming sick. For either type, the cost of being sick is $1 million.

Please download the Excel template in the Teams Daily channel and do the following:

1. Fill it in with appropriate data and formulas to determine: (a) the community's overall expected average cost; (b) whether each type of person would buy insurance from a company that charged everyone the community's average cost, and (b) what fraction of medical costs would fall on people without insurance.
2. Set the formats of the cells so that: (a) numbers of people are integers with commas; (b) dollar values are shown in currency format rounded to the nearest dollar and using commas; and (c) values that are percentages are shown as percentages rounded to either the nearest percent or tenth of a percent, as appropriate.
3. Save and then turn in your finished spreadsheet.