Regression Analysis

For Practice Exercise #4, you are going to undertake data analysis using the multiple linear regression analytical tool. You will start by setting up your data and preparing it for Regression Analysis.

Data Setup for Regression Analysis

1. Select the columns (variables) of data that you need for all your regression runs.
2. Create new columns (variables) of data by calculating and copying the cells.
3. Copy all the columns (variables) of data that you need for all your regression runs to a new worksheet.
4. 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” 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.

Regression Analysis using EXCEL Analysis ToolPak (Regression) or EXCEL Add-In called RegressItLogistic:

You can use the Regression module in the familiar Analysis ToolPak, OR download and use RegressItLogistic from the following general home page link:

https://regressit.com/index.html

Download the Add-In and read the instructions on how to use it for your analysis.

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”.

To Use the Add-In Software

To install the add-in, 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 2022.12.14). 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 format: 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.

1. 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 four models (using the HMGT400Hospital.CSV dataset we have used for all exercises) and complete the template tables below.

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.

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 E.4.1 below:

Table 1 attached 

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 attached 

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. In each case, you would have to create the Ratio (new data variable) by dividing the columns of data.

How do you evaluate the impact of having higher or more Medicare and Medicaid patients on hospital net benefits 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 ATTACHED 

How do you evaluate the impact of having higher or more Medicare and Medicaid patients on hospital net benefits in non-teaching hospitals?

Based on your finding please recommend 3 policies to improve hospital performance. Please make sure to use the final model (Model 4) for your recommendation.

Make sure to include or attach any plotted graphs (properly tiled and labeled) that help you make your points