Question

Locate Hospital Tab on Excel Data. View Data:https://drive.google.com/file/d/1HMgLG7BVcvpoQ6iUxjkL_xCBzvpgHhHp/view?usp=sharing

Problem 1 What is a better predictor of the number of Personnel in a hospital? Using the "Excel Databases.xls" file, locate the tab for the Hospital Database. Use Excel to develop two simple regression models for the Hospital Database at a 5% significance level Model 1: Predict the number of Personnel by the number of Births Model 2: Predict number of Personnel by number of Beds Must complete all the parts to this problem: PART 1: Perform a simple linear regression analysis in Excel for both models and output the results. Include the Regression Statistics, ANOVA, and table of Coefficients for each model. PART 2: Perform a test for the overall model for model 1 and model 2. Clearly state the hypotheses, the p-value, and your decision and conclusion for each model. PART 3: For model 1 and model 2, write the model using the Excel output PART 4: Perform a residual analysis using Excel on each model. How many residuals are within 1 standard error for each model? (Do not output all residuals, use your Excel output to answer this question). PART 5: Which model (1 or 2) is stronger at predicting number of Personnel? Why?

Answer 1

Part 1

Model 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.697463374
R Square	0.486455158
Adjusted R Square	0.483861497
Standard Error	590.2581194
Observations	200
ANOVA
	df	SS	MS	F	Significance F
Regression	1	65345181.8	65345181.8	187.5554	1.79694E-30
Residual	198	68984120.2	348404.6475
Total	199	134329302
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	390.6214398	54.07601821	7.223561437	1.07E-11	283.9825868
Births	0.538734917	0.039337822	13.69508763	1.8E-30	0.461160045

Model 2

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.752728714
R Square	0.566600517
Adjusted R Square	0.564411631
Standard Error	542.2467758
Observations	200
ANOVA
	df	SS	MS	F	Significance F
Regression	1	76111051.95	76111051.95	258.8533368	8.44745E-38
Residual	198	58218250.05	294031.5659
Total	199	134329302
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	107.1732106	60.56689007	1.769501629	0.078349474	-12.26575515
Beds	3.593743637	0.223367616	16.08891969	8.44745E-38	3.153258794

part 2

p-value for model 1 =

1.79694E-30

   < 0.05 , hence the model 1 is significant

p-value for model 2 is

8.44745E-38

< 0.05 , hence the model 2 is significant

Part 3 )

model 1

y^= 390.6214 + 0.5387 Births

model 2

y^= 107.1732 + 3.5937 Beds

Part 4

Model 1 has 165 residuals are within 1 standard error

Model 2 has 159 residuals are within 1 standard error

Part 5

Model 1 is stronger ,because more number of observation has standardized residual less than1.