1. Use the data on the Costs worksheet of Chapter 10-1.xls, and replicate the calculations in Figure 10.3. (See attached photo)

a. Use a pivot table and the formula in Equation 10.1 to calculate the between group sum of squares (SSB) for the costs in the four hospitals.

b. Use the results of the pivot table from (a) and the formula in Equation 10.2 to calculate the within groups sum of squares (SSw) for the costs in the four hospitals.

c. Use the formula in Equation 10.3 to calculate the total sum of squares (SST) for the costs in the four hospitals.

d. Determine the appropriate degrees of freedom, and use the formula in Equation 10.4 to calculate the F value for a test of the Null hypothesis that the mean costs in the four hospitals are all the same.

e. use the =FDIST( ) function to obtain the probability of the F value, and draw the appropriate conclusion in regard to the null hypothesis.

2. on a Fresh spreadsheet, rearrange the Data on the costs worksheet of Chapter 10-1.xls into four columns, as shown in figure 10.4

a. use the ANOVA: Single factor data analysis ad-in to carry out the analysis of variance.

b. confirm that the results are the same as those obtained in exercise 1.

ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Hosp Cost Albemarle $5,342.61 Albemarle $7,237.59 Albemarle $11,801.95 Albemarle $1,082.36 Albemarle $2,379.59 Albemarle $8,469.39 Albemarle $4,453.55 Albemarle $4,745.26 Albemarle $11,963.80 Albemarle $5,294.96 Albemarle $2,965.55 Albemarle $3,896.16 Albemarle $6,074.21 Albemarle $7,217.18 Albemarle $3,730.40 Albemarle $1,248.14 Albemarle $14,258.95 Albemarle $19,475.78 Albemarle $3,384.80 Albemarle $6,680.14 Albemarle $1,871.78 Albemarle $4,655.22 Albemarle $7,899.41 Albemarle $12,005.57 Albemarle $3,117.39 Albemarle $10,750.39 Albemarle $5,097.72 Albemarle $2,280.30 Albemarle $16,611.95 Albemarle $3,237.47 Beaufort $6,844.89 Beaufort $6,283.80 Beaufort $5,206.09 Beaufort $22,030.51 Beaufort $5,372.86 Beaufort $2,197.14 Beaufort $13,472.69 Beaufort $8,486.18 Beaufort $2,447.01 Beaufort $19,182.24 Beaufort $10,021.34 Beaufort $1,594.57 Beaufort $4,412.80 Beaufort $7,000.21 Beaufort $1,221.58 Beaufort $6,956.35 Beaufort $2,568.13 Beaufort $2,257.24 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Beaufort Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Catawba Dare Dare Dare Dare Dare Dare Dare $3,946.68 $9,546.52 $9,321.12 $4,278.73 $3,720.09 $2,314.85 $2,741.32 $3,489.61 $9,916.19 $2,793.26 $4,394.15 $3,598.72 $5,455.73 $4,448.48 $8,996.80 $4,734.98 $13,055.10 $10,194.42 $16,229.83 $7,099.69 $5,948.36 $4,155.38 $14,470.90 $4,957.09 $5,040.75 $4,113.04 $18,527.78 $7,272.11 $10,481.06 $3,156.14 $14,447.60 $6,412.51 $13,480.78 $18,847.52 $15,593.65 $10,049.19 $9,632.61 $18,571.33 $4,513.67 $6,049.84 $6,716.69 $8,768.23 $1,035.75 $6,130.58 $2,584.28 $2,635.85 $5,371.03 $14,433.99 $2,283.88 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare Dare $3,630.39 $4,775.36 $6,084.66 $1,414.80 $6,630.33 $5,139.11 $16,346.71 $5,068.54 $1,810.36 $5,881.90 $4,149.18 $3,597.68 $10,679.39 $5,249.46 $10,377.23 $3,063.96 $3,580.55 $10,516.46 $6,082.93 $960.28 $3,118.49 $12,919.48 $5,933.54