• This is a timed and individual assessment.
• The dataset will be released on the last day of class.
• Students will have 48 hours to complete the analytics, and write a business report.
• Students are expected to write a 500 to 700 words executive summary of their findings on the data. Including two recommendations to the management of the company. The word count is a guideline, there is no strict limit for this assignment. If students' analysis merits writing more that is fine.  Just don't be verbose.

====================

A company in the meat and chicken industry is pondering an unexpected result in their insurance health plan. Originally, Meat&ChickenCo was outsourcing its poultry operations to a third-party supplier based out of Arkansas. In 2019, the company decided to acquire the poultry operations to become even more vertically integrated. This move improved their profits and helped them gain bargaining power. However, this same move turned out negatively for the HR team. The company saw an increase in insurance premiums a year after the Arkansas employees had joined.

Insurance companies set premium prices based on the expenditure patterns of each specific company’s employees. This is called Group Health Insurance. Group insurance has typically lower premiums as the risk is spread over a large group of people. The company pays 70 to 90% of the premium fees, while the employee gets deducted the remainder from their weekly/monthly salary pay. There are tax benefits for the company, as the premiums paid towards the employee are tax-deductible.

However, increased use of the insurance by employees leads inevitably to an increase in the premiums for the next year. And when this happens, the company must decide if it will absorb the increase in premiums. When companies do this, the personnel expenses go up and the employees may not realize that their benefits compensation has gone up. Another option is to pass the increase to the employees. The employees would see it as a salary reduction, as their net take-home check would go down.

This is the conundrum the HR team at Meat&ChickenCo is facing. The annual average claim of the employees was around $7k. After the poultry workers in Arkansas became part of the group insurance plan, the company’s average claim per employee increased by ~21%. This was puzzling for the HR team. Costs of living -medical care included- are lower in Arkansas than in any of the other four regions the company operates: California, the East Coast, and the sales office in Miami.

The HR team wants to understand the leading causes behind higher medical bills. Perhaps, there is something the company can do to help its workforce to stay healthier. After all, it is not only a concern that the compensation costs could go up. Meat&ChickenCo is also concerned about the well-being of its employees. Through a combination of self-reported metrics and insurance provider records, the HR team has collected data points about their employees which are attached to this file Download attached to this file.

The HR Team looks at the data to discover that medical expenses derived from the Arkansas employees, on average, are higher than those from the California location. Which doesn’t match the known costs of doctors. California and Massachusetts rank in the top five most expensive states for health care (Ziegler, 2021). The HR team knows they must look beyond averages, but they are unsure of how to account for other factors. For example, obesity may play a role, if the weights and heights they collected were converted into BMI scores.

Your task:

• Help the HR team to uncover the leading causes behind the medical expenses of their employees.
• Produce a business written report with two actionable steps you recommend for the company to take based on your findings.
• Read the rubric to understand what is expected from you.
• This is a non-comprehensive list of guiding questions for your regression analysis and recommendations:
  • Which is the number one factor that explains the cost of medical bills for the Chicken&Meat Co employees?
  • Based on your regression analyses, is it true that the medical costs are higher in Arkansas?  
  • How does the prevalence of smoking among our employees compare to the prevalence of smoking in the regions they operate?
  • Are age and weight a factor in explaining medical bills for our employees?
  • The company now faces the dilemma of increased premiums. If the company increases the premium for all the employees just because the Arkansas poultry was brought into the group, is that fair? Should the company absorb the premiums and reward those who may have different lifestyles?

Deliverables:

• Word document with your analysis and two recommendations derived from your findings. 
  • Don't structure your paper as Q&A. The questions above are meant to guide you in your analysis. If you find other patterns and insights from your analysis outside of those guiding questions, feel free to include those. 
• Upload your Excel with the regression analyses and any other work you conducted. 
• Read the rubric to understand what are the expectations from you for this assignment.

References

CDC. n.d. About Adult BMI. Centers for Disease Control and Prevention. Retrieved on Oct 27, 2021. Available at https://www.cdc.gov/healthyweight/assessing/bmi/adult_bmi/index.htmlLinks to an external site.

Ziegler, B. March 9, 2021. Health Care Rankings. Measuring how well states are meeting citizens’ health care needs. U.S. News. Retrieved on Oct 27, 2021. Available at https://www.usnews.com/news/best-states/rankings/health-care