To analyze the data using the logistic regression model (logit) in Excel, we will need to use the free Excel add-in I introduced earlier called “RegressItLogistic”. We used it to do Regression Analysis in Practice Exercise #4.For Practice Exercise #5, please run the three models (using the HMGT400Hospital.CSV dataset we have used for all exercises) and complete the template tables below.
For Practice Exercise #5, please run the three models (using the HMGT400Hospital.CSV dataset we have used for all exercises) and complete the template tables below.
DATA ATTACHED
Data Setup for Regression Analysis
Create an extract of the HMGT400Hospital.CSV dataset by selecting the columns having the dependent variable “system_member”, and the independent variables Total Hospital Costs, Total Hospital Revenue, Medicare Discharges, Medicaid Discharges, and Total Hospital Discharges. Create the Medicare Discharge ratio (= Medicare Discharges/Total Hospital Discharges) and the Medicaid Discharge ratio (= Medicaid Discharges/Total Hospital Discharges). So they should NOT add up to 1. There are hospital discharges other than Medicare and Medicaid discharges.Select the columns (variables) of data that you need (above) 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 for your regression analysis by eliminating any data rows with missing values (or impute the missing values) before you run the Regression to avoid errors. (For example: the Medicare and Medicaid Discharge ratio variables have a few “division by zero” (#DIV/0!) 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.
Be sure to state and describe in your research report how you cleaned the data, indicating the number of hospitals you deleted and which variables had missing values or #DIV/0! Values.
Logit Model 1
Run a logit model to explain the “being a member of a network” variable (system_member). The independent variable is Total Hospital Costs. And choose a 0.95 Confidence level. In options select the Logit and Exponentiated Coefficient Table (not just Logit) and request for P-values. The exponentiated coefficients are the odds ratios. You may also request for the logistic curve or other plots or graphs you want, and request for the high-resolution graph format.
LOGIT MODEL 1 ATTACHED
Research Question: What is the impact of hospital costs on “being a member of a network”?
Explaining the Logistic Regression Output
Coefficient is the regression coefficient (like in all regressions)St.err and p-value are the standard error and p-values estimated for the regression coefficient.exp(coeff) is sometimes referred to as the odds ratio (this is a unique parameter for logistic regressions). It is the exponentiated coefficient.The exp (z SE) and exp (Std coeff) values are the standard error and p-values estimated for the odds ratio. They represent statistical estimates describing how statistically significant our odds ratio is from zero. It is important to establish how significant our odds ratio is from zero.
I have here a link to a PDF (“Idiot’s Guide to Odds Ratios — JournalFeed.pdf” that provides a very basic explanation of odds ratios and how to interpret their value in logistic regression.
Logit Model 2
Run a logit model to explain the “being a member of a network” variable (system_member). The independent variables area Total Hospital Costs and Total Hospital Revenues. And choose 0.95 Confidence level. In options select the Logit and Exponentiated Coefficient Table (not just Logit) and request for P-values. You may also request for the logistic curve and high-resolution graph format.
LOGIT MODEL 2 ATATCHED
Research Question: What is the impact of hospital costs and hospital revenue on “being a member of a network”?
Logit Model 3
For model 3, add the Medicare-discharge-ratio and the Medicaid-discharge-ratio variables to your Model 2, as independent variables.
LOGIT MODEL 3 ATTACHED
What is the impact of hospital costs and hospital revenue, and each of the two ratios you added in Model 3 on “being a member of a network”?
Based on your findings from the three models, would you recommend that hospitals keep their system memberships? Why or why not? Discuss 3 policies you would advocate for based on your findings.
Please attach any plotted or graphed information you may want to use to make your points.
