PART 1:  Using the Pareto Defect data file, please produce the following in Excel and cut and paste your output below under each of the following 2 questions:    Using Excel and the data set called Pareto Defects on Moodle, please produce a pivot table that can address the validity of the statement: “Less than half of the sampled repair issues stem from engraving problems

PART 1:  Using the Pareto Defect data file, please produce the following in Excel and cut and paste your output below under each of the following 2 questions:

1.  Using Excel and the data set called Pareto Defects on Moodle, please produce a pivot table that can address the validity of the statement:

“Less than half of the sampled repair issues stem from engraving problems.”

(Please correct, if false.)

2. Using Excel, please produce a graphic of the distribution of repair costs.

PART 2: Please use the Excel results at the bottom of this file to answer the questions in Part 2.

1.         The United States Navy wishes to estimate how many hours of labor per month are needed to meet its medical needs based on a sample of twelve naval hospitals. [Please note: This data is based on an actual case study, but I have simplified the units of measure for ease of interpretation.]

The variables are:       

• Hours (number of monthly labor hours used)
• X-Rays (number of monthly X-rays)
• Bed-Days (number of monthly occupied bed days)
• Length (average length of a patient’s stay in days)
• Type of Hospital (Teaching/Training = 1, or not = 0)

Please answer the following questions based on the Excel results (below) and the information on the variables given above.

1. The dependent variable is : __

b) Which independent variable is most closely linearly related to this dependent variable?  ___

3. Interpret the statistic you used to determine your answer in (b) in words management can understand.  Is this what you would expect?  Why or why not?

4. The scatterplot between the y variable and the most highly correlated x-variable is shown in the printout.   Use the Excel output to write the equation of the line.

______________________________________

5. Estimate the y-variable for a hospital with X = 2,463 using the equation of the line.

Interpret your answer in a sentence for management.

6. Interpret the slope precisely (see the number on the Excel output) in one sentence for the management:

7. Identify the y–intercept. Is the y-intercept a potentially valuable piece of information for the hospital?  Explain your reasoning in 1-2 sentences. Include a statement interpreting the number itself.

8. Interpret the R-square (r²) number in a sentence management can understand but one that also uses correct statistical wording.

1. TRUE OR FALSE:  The correlation between x-rays and length is 0.194.

10. TRUE OR FALSE:  The correlation between hours and training is negative, and the negative sign indicates a weak correlation between the two variables.

11. TRUE OR FALSE:   The residual for the first hospital of -481.9 means the actual point on the scatter plot is below the estimated regression line.

50. Interpret the correlation (see Excel output) between bed-days and length of stay for management.  Please state the number and use 1-2 sentences.

1000. If I were to run a multiple regression and include the dummy variable for Training Hospital (training/teaching hospitals = 1, and regular naval hospitals = 0), what is the interpretation of a slope coefficient of -925.00?   How might you explain that result to the management?  (You can speculate on the reason; I don’t expect you to know about hospitals, but I want you to think critically.)

14. Is there room for improvement in this regression?  What would you recommend the next step be in the analysis and why?

Correlations	Hours	X-Rays	Bed-Days	Length	Training?
Hours	1
X-Rays	0.827	1
Bed-Days	0.582	0.544	1
Length	0.228	0.194	0.551	1
Training?	-0.496	-0.084	0.014	0.019	1
	Hours	X-rays	Bed-Days	Length
Mean	2021.33	8049.83	1103.166	5.3
Median	1733.5	6237	1352	5.35
Mode	#N/A	#N/A	620	#N/A
Standard Deviation	1088.918173	5170.55119	483.14647	0.892392
Range	3175	18058	1214	3
Minimum	566	2048	473	3.9
Maximum	3741	20106	1687	6.9
Count	12	12	12	12
Regression Analysis
	r²	0.684
	r	0.827
	Std. Error	641.553
Regression output
variables	coefficients	std. error
Intercept	618.8011
X-variable	0.1742	0.0374
Observation	Y actual	Y Predicted	Residual
1	566.0	1,047.9	-481.9
2	696.0	975.6	-279.6
3	1,033.0	1,305.3	-272.3
4	1,611.0	1,615.9	-4.9
5	1,613.0	2,625.9	-1,012.9
6	3,503.0	4,121.9	-618.9
7	1,603.0	1,752.2	-149.2
8	1,854.0	1,625.7	228.3
9	2,160.0	1,658.8	501.2
10	2,305.0	2,093.0	212.0
11	3,571.0	2,938.3	632.7
12	3,741.0	2,495.4	1,245.6