Please Work It Out On An Excel Doc To Show The Columns

Please Work It Out On An Excel Doc To Show The Columns And Headings An

Please work it out on an Excel document to show the columns and headings and formulas. The assignment contains three questions related to hypothesis testing, confidence intervals, and data analysis.

Paper For Above instruction

Question 1: A sample of 81 account balances of a credit company showed an average balance of $1,200 with a standard deviation of $126. Formulate the hypotheses to determine whether the mean of all account balances is significantly different from $1,150. Compute the test statistic. Using the p-value approach, what is your conclusion? Let α = 0.05.

Solution:

Step 1: Formulating Hypotheses

  • Null hypothesis (H₀): The population mean account balance equals $1,150, i.e., μ = 1150
  • Alternative hypothesis (H₁): The population mean account balance is not $1,150, i.e., μ ≠ 1150

Step 2: Computing the test statistic

Given:

- Sample mean (x̄) = $1,200

- Population mean under null (μ₀) = $1,150

- Sample size (n) = 81

- Sample standard deviation (s) = $126

The test statistic for a mean with a known or estimated standard deviation is the t-statistic:

t = (x̄ - μ₀) / (s / √n)

t = (1200 - 1150) / (126 / √81) = (50) / (126 / 9) = 50 / 14 = 3.57

Step 3: Determine the p-value and conclusion

Degrees of freedom (df) = n - 1 = 80

Using Excel, the p-value for a two-tailed test with t = 3.57 and df = 80 can be calculated as:

=2 * (1 - T.DIST.2T(3.57, 80))

which approximately equals 0.0006.

Since p-value

---

Question 2: During the recent primary elections, the Democratic presidential candidate showed the following pre-election voter support in Alabama and Mississippi. We want to determine whether the proportions of voters favoring the Democratic candidate were the same in both states. Provide the hypotheses. Compute the test statistic; determine the p-value; and at 95% confidence, test the hypotheses.

Assumed Data (for illustration):

  • Number of supporters in Alabama: x₁ = 400 out of n₁= 1000 voters
  • Number of supporters in Mississippi: x₂ = 500 out of n₂= 1200 voters

Step 1: Hypotheses

  • H₀: p₁ = p₂ (The proportions favoring the candidate are equal in both states)
  • H₁: p₁ ≠ p₂ (The proportions are different)

Step 2: Computing the test statistic

First, calculate the sample proportions:

p̂₁ = 400 / 1000 = 0.4


p̂₂ = 500 / 1200 ≈ 0.4167

Combined proportion:

p̂ = (x₁ + x₂) / (n₁ + n₂) = (400 + 500) / (1000 + 1200) = 900 / 2200 ≈ 0.4091

Standard error:

SE = √[p̂  (1 - p̂)  (1/n₁ + 1/n₂)] = √[0.4091  0.5909  (1/1000 + 1/1200)] ≈ √[0.2419  (0.001 + 0.000833)] ≈ √[0.2419  0.001833] ≈ √0.000444 = 0.02107

Test statistic:

z = (p̂₁ - p̂₂) / SE = (0.4 - 0.4167) / 0.02107 ≈ -0.0167 / 0.02107 ≈ -0.79

Step 3: p-value and conclusion

Using Excel:

=2 * (1 - NORM.S.DIST(ABS(-0.79), TRUE))

which approximates to 0.429.

Since the p-value > 0.05, we fail to reject the null hypothesis and conclude that there is no statistically significant difference in voter support proportions between Alabama and Mississippi at the 95% confidence level.

---

Question 3: To estimate the difference between the yearly incomes of marketing managers in the East and West of the U.S., given the data, develop a confidence interval and test if their average incomes are significantly different using the p-value approach at 95% confidence.

Assumed Data (for illustration):

  • East: sample mean = $85,000; standard deviation = $8,000; n₁= 50
  • West: sample mean = $82,000; standard deviation = $7,500; n₂= 45

Step 1: Confidence interval for the difference in means

Standard error:

SE = √[(s₁² / n₁) + (s₂² / n₂)] = √[(8000²/50) + (7500²/45)] ≈ √[(64,000,000 / 50) + (56,250,000 / 45)] ≈ √[1,280,000 + 1,250,000] ≈ √2,530,000 ≈ $1,590

Difference in sample means:

D = 85,000 - 82,000 = $3,000

Critical t-value for 95% confidence (df approximated via Welch’s method or using Excel’s T.INV.2T):

=T.INV.2T(0.05, degrees_of_freedom)

which is approximately 2.01 with df ≈ 90.

Confidence interval:

D ± t  SE = 3000 ± 2.01  1590 ≈ 3000 ± 3190

Thus, interval: (-$1,190, $6,190). Since zero is within this interval, there is no significant difference in mean incomes at 95% confidence.

Step 2: p-value for testing the difference

Null hypothesis: μ₁ = μ₂ → D = 0

=2 * (1 - T.DIST.2T(|t|, df))

where t = D / SE = 3000 / 1590 ≈ 1.89.

Using Excel:

=2 * (1 - T.DIST.2T(1.89, df))

which approximates to p ≈ 0.06, just above 0.05, indicating no significant difference at the 5% level.

Conclusion: Based on the confidence interval and p-value, there is no statistically significant difference in the annual incomes of marketing managers in the East and West regions of the U.S., though the data suggests a trend toward higher incomes in the East.

---

References

  • Agresti, A., & Finlay, B. (2009). Statistical Methods for the Social Sciences. Pearson Education.
  • Bluman, A. G. (2017). Elementary Statistics: A Step By Step Approach. McGraw-Hill Education.
  • Devore, J. L. (2011). Probability and Statistics for Engineering and the Sciences. Cengage Learning.
  • Moore, D. S., & McCabe, G. P. (2014). Introduction to the Practice of Statistics. W. H. Freeman.
  • Newbold, P., Carlson, W. L., & Thorne, B. (2013). Statistics for Business and Economics. Pearson Education.
  • rumsey, D. (2011). Statistics for Dummies. John Wiley & Sons.
  • Siegel, S., & Castellan, N. J. (1988). Nonparametric Statistics for the Behavioral Sciences. McGraw-Hill.
  • Walpole, R. E., Myers, R. H., Myers, S. L., & Ye, K. (2012). Probability & Statistics for Engineers & Scientists. Pearson.
  • Zar, J. H. (2010). Biostatistical Analysis. Pearson Education.
  • Field, A. (2013). Discovering Statistics Using R. Sage Publications.

Related Assignments