Leveraging Data in Decision-Making Using Regression Analysis

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.

Meat and Chicken Co.png
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.

Average by Location and BMI Table.png

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.
In the excel file:
Step 1: Descriptive Statistics to understand the distribution of each variable.
Step 2: Hypothesis testing to test if our assumptions are correct or not.
PreStep 2.0: Run correlation to identify “x” variables that are multi-correlated
PreStep 2:.1 Check if the patterns of the relationship of one x at time with the “Y” is not linear then transform: Ln or Quadratic
Step 3: Multiple regression to explain what variable counts the most. Run a regression with all the variables and then remove the non significant ones and explain why.
To have the best grades:
- Written report provides a clear recommendation that is practical and implementable for the company to be adopted. Those recommendations are supported with data and address the concerns of the company.
- No errors found inside the excel sheets
- Data visualization graphs are present and are insightful. The charts strengthen the storytelling and they are addressed within the narrative of the written text.
- Several outputs exist and are correctly calculated and interpreted.
- Dummy and interaction variables were correctly hypothesized, properly set up used in the regression. Most importantly dummy and interaction variables were interpreted correctly. Required transformations are impeccable.
- The report includes references to the predictive power of the model without using the technical statistic terms but rather giving a business interpretation of them
- All outliers and missing values were identified and treated effectively.