RESEARCH DATA ANALYSIS USING EXCEL

Hospital cost

Selflove3

HMGT400FinalExam-EXCEL-RStudio_Spring2021.docx

Home >Mathematics homework help >Statistics homework help >RESEARCH DATA ANALYSIS USING EXCEL

Update: 2/23/2020

University of Maryland University College

HMGT 400 Research and Data Analysis in Health- FINAL EXAM Spring 2021

Dataset: HMGTFINALEXAM.csv (Please download dataset from the class)

Required program: EXCEL

Author, Hossein Zare, PhD

Citation: Zare, H. (2019). HMGT 400 Research and Data Analysis in Health Care.FINAL EXAM. UMGC.EDU

Question #1 (15 credits):[RStudio Users and Excel Users]

The FINAL EXAM dataset provides some information about hospitals in 2011 and 2012.Download the FINAL EXAM data and then complete the descriptive table. Please answer the following questions.

A) In terms of hospital characteristics, what are the significant differences between 2011 and 2012?

B) In terms of socio-economic variables what are the significant differences between 2011 and 2012?

(To report the “Per Capita Hospital Beds to Population”, you need to divide “total_hospital_beds/tot_population)

C) Based on your findings, in which years did hospitals have better performance? How is hospital performance related to hospital characteristics and socio-economic characteristics? Please write at least three main differences between 2011 and 2012.

Table 1. Descriptive statistics between hospitals in 2011 & 2012

	2011	2012	p-value
	N	Mean	St. Dev	N	Mean	St. Dev
Hospital Characteristics
1. Hospital beds
2. Number of paid Employees
3. Number of non-paid Employees
4. Interns and Residents
5. System Membership
6. Total hospital cost
7. Total hospital revenues
8. Hospital net benefit
9. Available Medicare days
10. Available Medicaid days
11. Total Hospital Discharge
12. Medicare discharge
13. Medicaid discharge
Socio-Economic Variables
14. Per Capita Hospital Beds to Population
15. Percent of population under poverty
16. Percent of Female population under poverty
17. Percent of Male population under poverty
18. Median Household Income

(Hospital net benefit= Hospital revenues - Hospital costs). Round all figures to 2 decimal places max

Question #2 (15 credits):[RStudio Users and Excel Users]

Use the final exam dataset and then answer the following questions.

Compare the following information between for-profit and not-for-profit hospitals:

1) What are the main significant differences between for-profit and not-for-profit hospitals? Which test is the best fit test? Why?

2) Use a box-plot and compare Hospital net benefit between for-profit and not-for-profit hospitals.

3) Show another scatterplot, and compare hospital cost (x-axis) and revenue (y-axis),then discuss your findings?

4) Comparing hospital net benefit, which hospitals has better performance? (Hospital net benefit= Hospital revenues - Hospital costs)

5) Overall, what are the main significant differences between for-profit and not-for-profit hospitals?

Table 2. Descriptive statistics between for-profit and not-for-profit hospitals, 2011 & 2012

	For-Profit	Not-For-Profit	p-value
	N	Mean	St. Dev	N	Mean	St. Dev
Hospital Characteristics
1. Hospital beds
2. Number of paid Employees
3. Number of non-paid Employees
4. Interns and Residents
5. System Membership
6. Total hospital cost
7. Total hospital revenues
8. Hospital net benefit
9. Available Medicare days
10. Available Medicaid days
11. Total Hospital Discharge
12. Medicare discharge
13. Medicaid discharge
Socio-Economic Variables
14. Per Capita Hospital Beds to Population
15. Percent of population under poverty
16. Percent of Female population under poverty
17. Percent of Male population under poverty
18. Median Household Income

Round all figures to 2 decimal places max

Question #3 (15 credits):[RStudio Users and Excel Users]

The dataset provides Herfindahl–Hirschman Index for health insurance market, please use the herf_ins variable and answer the following questions:

For this exercise you do not need to compute the HHI, but if you have any questions, please do not hesitate to ask me, but try to learn more about this as you will need that to report your findings.

Please remember for the class exercise you used the herf_cat as a hospital Herfindahl index. For this question make sure to use herf_ins as Herfindahl index for insurance market.

Use the final exam dataset and then answer the following questions:

1) In a short paragraph explain the Herfindahl index. You can use the reference provided in the class exercise or any other citation.

2) Compare the following information between hospitals located in high, moderate and low competitive health insurance markets?

· What are the main significant differences between hospitals in different markets? (use ANOVA test)

· What is the impact of being in high-competitive health insurance market on hospital revenues and cost?

· Do you think being in a high-competitive market has a positive impact on hospital net benefits?

· What about the number of Medicare and Medicaid discharge? Do you think hospitals in high competitive markets are more likely to accept more Medicare and Medicaid patients?

· What is the impact of other variables?

(Note: to answer the last 2 questions, compute the Medicare-discharge-ratio and Medicaid-discharge-ratio first and then run 2 independent t-tests: Total Medicare or Medicaid Discharge/Total Hospital Discharge)high vs. moderate and high vs. low competitive market), please support your findings with box-plots).

Table 3. Comparing hospital characteristics and market, 2011 and 2012

	High Competitive Market	Moderate Competitive Market	Low Competitive Market	P Value [ANOVA/Chi-Sq (results)]
Hospital Characteristics	N	Mean	STD	N	Mean	STD	N	Mean	STD
1. Hospital beds
2. Number of paid Employees
3. Number of non-paid Employees
4. Interns and Residents
5. System Membership
6. Total hospital cost
7. Total hospital revenues
8. Hospital net benefit
9. Available Medicare days
10. Available Medicaid days
11. Total Hospital Discharge
12. Medicare-discharge-ratio
13. Medicaid-discharge-ratio
Socio-Economic Variables
14. Per Capita Hospital Beds to Population
15. Median Household Income

(Hospital net benefit= Hospital revenues - Hospital costs). Round all figures to 2 decimal places max

Question #4 (Credits 20):[RStudio Users]

Linear Regression Model

If you have chosen to work with RStudio, please run the following model and complete the following tables.

1st Model:

Run a linear model and predict the impact of difference in hospital beds and hospital ownership on hospital net benefit.

	Model 1
Hospital Characteristics	Coef.	St. Err
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
N
R-Squared

1. Discuss your finding; do you think having higher beds has a positive impact on the hospital net benefit? What about ownership?

2nd Model:

Now, estimate the impact of system membership (i.e., being a member of a system) on hospital net benefit.

	Model 2
Hospital Characteristics	Coef.	St. Err
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
Membership
System Membership
N
R-Squared

2. Discuss your findings (no more than 2 lines). Is it significant?

3nd Model:

Now, include the Medicare-discharge-ratio and Medicaid-discharge-ratio in your model.

	Model 3
Hospital Characteristics	Coef.	St. Err
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
Membership
System Membership
Socio-Economic Characteristics
Medicare-discharge-ratio
Medicaid-discharge-ratio
N
R-Squared

3. How do you evaluate the impact of having higher Medicare and Medicaid patients on hospital net benefit?

4. Based on your finding, recommend 3 policies to improve hospital performance. Please make sure to use the final model for your recommendations.

Question #4 (Credits 20): [Excel Users]

If you have chosen to work with Excel, please run the models and complete the following tables.

Model 1:

Run a linear model to predict the impact of difference between hospital beds on hospital net-benefit in teaching hospitals


Note: hospital-net-benefit=total_hosp_revenue - total_hosp_cost

Y(benefit), B0+B1(beds)

Hospital Characteristics	Coef.	ST. ERR	T Stat	P-values	Lower 95%	Upper 95%
Model-1
Hospital beds
N
R Square

Model 2:

Run a linear model to predict the impact of difference between hospital beds on hospital net-benefit in non-teaching hospitals

Hospital Characteristics	Coef.	ST. ERR	T Stat	P-values	Lower 95%	Upper 95%
Model-2
Hospital beds
N
R Square

1. Using the results from model 1 and model 2, compare the results between teaching and non-teaching hospitals.

Model 3:

Now, include the Medicare-discharge-ratio and Medicaid-discharge-ratio in first model

Hospital Characteristics	Coef.	ST. ERR	T Stat	P-values	Lower 95%	Upper 95%
Model-3
Hospital beds
Medicare-discharge-ratio
Medicaid-discharge-ratio
N
R Square

2. How do you evaluate the impact of having higher Medicare and Medicaid patients on hospital net benefit in teaching hospitals?

Model 4:

Now, include the Medicare-discharge-ratio and Medicaid-discharge-ratio in second model

Hospital Characteristics	Coef.	ST. ERR	T Stat	P-values	Lower 95%	Upper 95%
Model-4
Hospital beds
Medicare-discharge-ratio
Medicaid-discharge-ratio
N
R Square

3. How do you evaluate the impact of having higher Medicare and Medicaid patients on hospital net benefit in non-teaching hospitals?

4. Based on your finding(s) recommend 3 policies to improve hospital performance in teaching and non-teaching hospitals. Please make sure to use the final model for your recommendations.

Question #5 (Credits 20):[RStudio Users]

If you have chosen to work with RStudio, please run three models and complete the following tables.

Model 1

Run a logit model to find out the impact of being a member of a network on hospital ownership and hospital beds.

Hospital Characteristics	Coef.	St. Err	p-value
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
N
AIC

Model 2

Now, include hospital net benefit (income) and report the Coefficient.

Hospital Characteristics	Coef.	St. Err	p-value
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
Hospital net benefit
N
AIC

Model 3

Now, include the Medicare-discharge-ratio and Medicaid-discharge-ratio in your model. Keep all variables you used for models 1, 2 & 3 and discuss your findings.

Hospital Characteristics	Coef.	St. Err	p-value
Hospital beds
Ownership
For-Profit
Not-for-profit
Other
Hospital Income
Medicare-discharge-ratio
Medicaid-discharge-ratio
N
AIC

1. Discuss application of different models and the results you received. Do you recommend keeping membership (i.e., being a member of a network) for a hospital? Why or why not?

2. Based on your finding(s), recommend 3 policies to improve hospital performance in for-profit and not-for-profit hospitals. Please make sure to use the final model for your recommendations.

Question #5 (Credits 20): [Excel Users]

If you have chosen to work with Excel, please run three models and complete the following tables.

Model 1: Run a regression model to find out the impact of being a member of a network on hospital cost.

Model 2: Run a regression model to find out the impact of being a member of a network on hospital cost and hospital revenue.

Model 3: Run a regression model to find out the impact of being a member of a network on Medicare-discharge-ratio and Medicaid-discharge-ratio (include hospital cost and hospital revenue in model).

	Coef.	ST. ERR	T Stat	P-values	Lower 95%	Upper 95%
Model-3
Hospital cost
Hospital Revenue
Medicare-discharge-ratio
Medicaid-discharge-ratio
N
R Square

1. Based on your finding(s) please recommend 3 policies and discuss the relationship (impact)between hospital cost, hospital revenue, Medicare-discharge-ratio and Medicaid-discharge-ratio, and system membership (i.e., being a member of a network). Do you recommend keeping membership for a hospital? Why or why not?

Question 6: (15 credits) [RStudio Users and Excel Users]

Note: Please limit your answer for each question to maximum 2 paragraphs and make sure to support your responses with at least one credible citation – following APA 7.

1. Present a research question for a study using human subject research.

2. Explain the difference between the research process involving human subjects and the research process not involving human subjects.

3. Discuss ethical implications surrounding human subject research studies.

4. Explain the governance of human subject research studies over the data and the process.

5. Provide examples of the consequences for not meeting IRB (Institutional Review Board) protocol requirements.

Useful formula and guideline.

( The computation of the p-value is illustrated in following figures. )

HMGT 400 Research and Data Analysis in Health—FINAL EXAM,4