For this week’s exercise, we need to try a few logit regression (or logistic regression) models (see this link for more information on these regression models: https://stats.idre.ucla.edu/r/dae/logit-regression/ ). You should also review the email attachment I sent to you called “Interpreting Odds Ratios”.
Data: We shall use the same data we have used for all our previous exercises – HMGT400Hosp.CSV
To analyze the data using the logit regression model (Logistic Regression analysis) in Excel, we cannot use regular Analysis ToolPak. But we can use either the XLMiner Analysis ToolPak add-in or the RegressItLogistic add-in. Of course, XLMiner Analysis ToolPak is an add-in for Google Sheets or Excel Online.
The “RegressItLogistic” add-in was developed by Professor Robert Nau, at the Duke University Business School in North Carolina. Instructions for downloading and using the add-in are at https://regressit.com/regressitlogistic.html . Note that we need the add-in that runs regressions including logistic regressions called “RegressItLogistic”. You can use it to run both linear regression and logistic regression models. There is a version for running just linear regression models called “RegressItPC”.
“RegressItLogistic” – Excel Add-In Software
To install “RegressItLogistic”, create a new “c:\RegressIt” file folder in which to store your RegressIt files. Then use one of these two links to download the program file:
1. If your computer will not allow the direct download of an executable file, then use this link to get the program file RegressItLogistic.xlam (version 2020.03.04) Right-click this link and choose the “save link as” option to save it to your new RegressIt file folder.
2. Otherwise use this link to get the program file in zip form: RegressItLogistic.zip Right-click the link and choose the “save link as” option to save it to your RegressIt file folder. Then right-click on the saved file and choose “unzip to here”. The program file will be extracted from the zip file.
Then follow these instructions to run the add-in for the first time:
1. To “Unblock” the program file go to the File Explorer, right-click on the file and choose Properties.
· At the bottom of the dialog box check the Unblock box.
· Then click Apply further below the unblock box.
· Then click OK
· This only needs to be done once. You should close the file explorer when running analyses because it may cause errors when producing non-editable graphs.
2. To run the program, start Excel, open the RegressItLogistic.xlam file, and click either “Trust all from the publisher” or “Enable macros” at the security prompt. You should see a RegressIt tab appear at the top of the Excel window. When you click on it you should see the RegressIt ribbon interface. You may click the “Instructions” button at the far right for details on how to load data and begin your analyses.
After you have tested the add-in as specified in the Instructions, please run the three models (using the HMGT400Hospital.CSV dataset we have used for all exercises) and complete the template tables below.
Data Setup for all Analyses (“RegressItLogistic”, “XLMiner Analysis ToolPak” or R :
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 and the Medicaid Discharge ratio. Then as you would for all regressions, clean the data by deleting all rows (hospitals) that have missing values or #DIV/0! values. Be sure to state and describe in your report how you cleaned the data, indicating the number of hospitals you deleted and which variables had missing values or #DIV/0! Values. Re-save the data file as a file with a ___.CSV extension.
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 0.95 Confidence level. In options select the Logit and Exponentiated Coefficient Table (not just Logit) and request for P-values.The exponentiated coefficients (exp(coeff) ETC.) 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.
Table 1 – Logit Model 1