For Exercise 4, you are going to undertake data analysis using the multiple linear regression analytical tool. You can use the tool in any Analysis ToolPak or R (RStudio). In this instance, the analysis you are going to undertake will be slightly different depending on whether you are using an Analysis ToolPak or R. But irrespective of which tool you use you will start by setting up your data and preparing it for Regression Analysis.
Please set up your data set for regression analysis this way:
Select the columns (variables) of data that you need for all your regression runs. Create new columns (variables) of data by calculating and copying the cells. Copy all the columns (variables) of data that you need for all your regression runs to a new worksheet. Please clean up your data set for regression analysis by eliminating any data rows with missing values (or imputing the missing values) before you run the Regression to avoid errors. (The Medicare and Medicaid Discharge ratio variables have a few Division by Zero values. Any data rows with these and any other missing values need to be deleted. Save the data as a CSV file in an appropriate folder.
I have sent everyone an email message with 2 attachments with additional guidance on how to set up the data and do the regression analysis to estimate Model 1. The other models may be done in a similar fashion.
Using an Analysis ToolPak:
Model 1:
Run a multiple linear regression model to explain/predict Net Hospital Benefits (Net Revenue). The dependent variable is Net Hospital Benefits and the independent or predictor variables are Total Hospital Beds and whether the hospital is a Teaching Hospital or not. Complete Table 1.
Table 1-Model 1-To explain/predict Net Hospital Benefits – 2011, 2012
(using an Analysis Tool Pak)
Coef.
ST. ERR
T Stat
P-values
Lower 95%
Upper 95%
Intercept
Total Hospital beds
Teaching Hospital Dummy
R Square=
Model 2:
Run a multiple linear regression model and explain/predict Net Hospital Benefits (Net Revenue). In the 2nd model, the dependent variable is Net Hospital Benefits and the independent or predictor variables are Total Hospital Beds and whether the hospital is a Non-Teaching Hospital or not. (Note: You may convert the Teaching Hospital column into a Non-Teaching Hospital column by subtracting 1 and changing the sign of the data.) Complete Table 2.
Table 2-Model 2-To explain/predict Net Hospital Benefits – 2011, 2012
(using Excel Analysis Tool Pak)
Coef.
ST. ERR
T Stat
P-values
Lower 95%
Upper 95%
Intercept
Total Hospital beds
Non-Teaching Hospital Dummy
R Square=
Use the results from model 1 and model 2 to compare the results between teaching and non-teaching hospitals.
Model 3:
Run a multiple linear regression model and explain/predict Net Hospital Benefits (Net Revenue). In the 3rd model, the dependent variable is Net Hospital Benefits and the independent or predictor variables are Total Hospital Beds, whether the hospital is a Teaching Hospital or not, Ratio of Medicare Discharges, and Ratio of Medicaid Discharges. Complete Table 3.
Table 3-Model 3-To explain/predict Net Hospital Benefits – 2011, 2012
(using an Analysis Tool Pak)
Coef.
ST. ERR
T Stat
P-values
Lower 95%
Upper 95%
Intercept
Total Hospital beds
Teaching Hospital Dummy
Ratio of Medicare discharges
Ratio of Medicaid discharges
R Square=
How do you evaluate the impact of having higher or more Medicare and Medicaid patients on hospital net-benefit in teaching hospitals?
Model 4:
Run a multiple linear regression model and explain/predict Net Hospital Benefits (Net Revenue). In the 4th model, the dependent variable is Net Hospital Benefits and the independent or predictor variables are Total Hospital Beds, whether the hospital is a Non-Teaching Hospital or not, Ratio of Medicare Discharges, and Ratio of Medicaid Discharges. Complete Table 4.
Table 4-Model 4-To explain/predict Net Hospital Benefits – 2011, 2012
(using an Analysis Tool Pak)
Coef.
ST. ERR
T Stat
P-values
Lower 95%
Upper 95%
Intercept
Total Hospital beds
Non-Teaching Hospital Dummy
Ratio of Medicare discharges
Ratio of Medicaid discharges
R-Squared =
How do you evaluate the impact of having higher or more Medicare and Medicaid patients on hospital net-benefit in non-teaching hospitals?
Based on your finding please recommend 3 policies to improve hospital performance. Please make sure to use the final model for your recommendation.
Make sure to include or attach any plotted graphs that help you make your points.
Using R through RStudio:
Model 1:
Run a multiple linear regression model to explain/predict Net Hospital Benefits (Net Revenue). The dependent variable is Net Hospital Benefits and the independent or predictor variables are Total Hospital Beds, Whether Hospital is for-profit or not (dummy variable), Whether Hospital is not-for-profit or not (dummy variable), Whether Hospital is some other ownership status or not (dummy variable). Note: This is Model 1A in the R script. Complete Table 1.
Table 1-Model 1-To explain/predict Net Hospital Benefits – 2011, 2012
(using R through RStudio)
CoefficientStandard Error
T-Value
Pr(>|t|)
Intercept
Hospital beds
For-Profit Dummy
Public Ownership Dummy
Other Owner Type Dummy
R-Squared=
Discuss your findings.
Do you think having more beds has a positive impact on the hospital’s net benefit?
What about the ownership?
Note: We are not running the regression Model 1B. The model that uses the newly created bed categorical variable.
Model 2:
Now, run a multiple linear regression model to explain/predict Net Hospital Benefits (Net Revenue) as you did in Model 1, but this time add whether the hospital is a member of a system as an additional independent variable. Complete Table 2
Table 2-Model 2-To explain/predict Net Hospital Benefits – 2011, 2012
(using R through RStudio)
Coefficient
Standard Error
T-Value
Pr(>|t|)
Intercept
Hospital beds
For-Profit Dummy
Non-for-profit Dummy
Other Owner Type Dummy
System Membership
R-Squared=
Discuss your findings (not more than 2 lines).
Is the result statistically significant? Explain your answer.
Model 3:
Now, run a multiple linear regression model to explain/predict Net Hospital Benefits (Net Revenue) as you did in Model 2 above, but this time add the Medicare-discharge-ratio and Medicaid-discharge-ratio as additional independent variables. Complete Table 3.
Table 3-Model 3-To explain/predict Net Hospital Benefits – 2011, 2012
(using R through RStudio)
CoefficientStandard Error
T-Value
Pr(>|t|)
Intercept
Hospital beds
For-Profit Dummy
Non-for-profit Dummy
Other Dummy
System Membership
Medicare discharge ratio
Medicaid discharge ratio
R-Squared=
Discuss your findings (not more than 2 lines). Is the result statistically significant? Explain your answer.
Based on your findings please recommend 3 policies to improve hospital performance. Please make sure to use the final model for your recommendation.
Make sure to include or attach any plotted graphs that help you make your points.
