Need help with Excel (share the excel documents)

1. Two-Sample t-Test

Question 1 You are the regional director in the store management department of a retail chain store. You are interested in investigating if the average daily sales of two of your stores are different from one another. The test is done at 5% significance level. The test is outlined below: Ho: M1 - M2 = 0 HA: M1 - M2 # 0 Based on the data file in the first sheet of the Excel file and using the Data Analysis Toolpak, conduct a two-sample t-test and provide your answers directly on the Excel sheet. Make sure to enter the last 4 digits of your student ID in Cells A2, A3, A4 and A5 before conducting the test. Note: MS Excel uses the scientific exponential format to show very small and very large numbers. For example, 1.23E-12 means 1.23 x 10 12. Similarly, 1.23E+12 means 1.23 x 1012. The p-value may be represented using this format.Question 2 You have collected data on weekly sales volumes of three of your stores. You are interested in investigating if average weekly sales across these three stores are all the same or if at least one of them is different. The test is done at 5% signicance level. The test is outlined below: {Hail-11 = #2 = #3 HA: At least one 1.1 is different Based on the data le in the second sheet of the Excel le and using the Data Analysis Toolpak, conduct the AN OVA test and provide your answers directly on the Excel sheet. Note: MS Excel uses the scientific exponential format to show very small and very large numbers. For example, 12313-12 means 1.23 x 10-\". Similarly, 1.23E+12 means 1.23 x 1012. The p-value may be represented using this format. A B C D E F Daily Sales of Store 1 ($) Daily Sales of Store 2 ($) 6849 4697 Please provide your answers here in this textbox: 684 4681 6849 4693 a) Is the test one-tailed or two-tailed? ANSWER -> 6849 4732 b) Do you reject or fail to reject the null hypothesis? ANSWER -> 4681 4714 c) What is the reason for the answer you provided in part (b)? 4731 4705 p-value method ANSWER -> 4685 4703 4663 4673 critical value method ANSWER-> 10 466 4728 11 4710 4739 12 4621 4751 The result of your test should be placed here! 13 4655 4704 14 4704 4640 15 4746 4719 16 4673 4643 17 4758 4667 18 469 4701 19 4673 4722 20 475 4742 4728 4763 22 4636 47441 23 4694 4659 24 4721 4738 4669 4681 26 4632 4690 4769 4669 28 4752 4741 29 4670 4710 30 4684 4733 31 4767 4691 32 4661 4700 33 4672 4710 34 4693 4719 35 4716 4694 36 4676 4702 37 4738 4716 38 39X A B C D Weekly Sales of Store 1 ($) E Weekly Sales of Store 2 ($) Weekly Sales of Store 3 ($) N $14,915 $14,933 $14,970 $14,965 Please provide your answers here in this textbox: $14,897 $14,963 $15,008 $14,996 $14,974 $14,963 $15,023 a) Do you reject or fail to reject the null hypothesis? ANSWER $15,084 $14,985 $15,131 b) What is the reason for the answer you provided in part (a)? $14,919 $14,963 $15,057 $14,827 $14,862 6 00 $15,003 $14,906 $14,855 $14,979 10 $15,179 $14,949 $14,966 $14,946 $14,952 $14,980 $15,158 12 $15,067 $14,953 13 $14,967 $15,082 The result of your test should be placed here! $15,035 14 $15,019 $15,149 $15,034 $14,905 $14,956 $14,951 16 $14,912 $14,941 $14,915 17 $15,133 $15,073 $15,002 $14,984 18 $14,999 $15,251 19 $14,940 $15,015 $15,089 20 $15,006 $15,018 $14,991 21 $15,132 $15,022 $15,096 22 $15,033 $15,127 $14,946 23 $15,002 $15,100 $14,874 $14,923 24 $14,865 $15,146 $14,846 25 $15,013 $15,042 26 $14,800 $15,067 $14,990 27 $15,141 $14,944 $15,046 28 $15,057 $14,860 $15,098 29 $15,120 $15,013 $15,018 30 $14,902 $15,106 $14,952 31 $14,878 $15,005 $15,189 32 $15,179 $14,870 $15,026 33 $15,057 $14,935 $15,030 34 $15,120 $15,088 $14,937 35 $15,015 $14,998 $15,000 36 $14,874 $15,024 $14,840 $15,124 37 $15,104 $15,061 38 $14.968 39 40 41 42Two-Sample t-Test using the Data Analysis Toolpak in Excel This document includes instructions to conduct a two-sample t-test based on two samples and review and interpret the results. Conducting the test 1. Make sure your sampled data for each population is in a separate column. A W N J O O O V Q UI A W N 15 16 v w 17 2. On the tool bar, choose Data and then open the Data Analysis Toolpak. ave (@ Of) H Book1 - Excel Behrouz Bakhtiari BB Home Insert Draw Page Layout Formulas Data view View Developer Help Share Queries & Connections Clear Co Ca Filter Reapply GE Data Analysis IIII Refresh Properties Text to Outline 14 SOIve All 2 Edit Links Stocks Geography Sort What-If Forecast Advanced Columns CO 9 Analysis * Sheet 3. From the Analysis Tools available, choose t-Test: Two-Sample Assuming Unequal Variances. This is the equivalent of Two-Sample t-Test we have discussed in lecture. It is used because we cannot assume population variances are equal.Data Analysis ? X Analysis Tools Camel LII l Histogram A Moving Average Random Number Generation - Rank and Percentile Regression Sampling thest: Paired Two Sample for Means _. t-T- ._ _'. t-Test Two-Sample Assuming Unequal Variances 4. Input: specify the range for Variable l and Variable 2 by selecting the data for each sample. You can include the header of the data as well. t-Test: Two-Sample Assuming Unequal Variances ? X In put Variable 1 Range: Variable ; Range: Hypothesized Mean Difference: D Labels Alpha: 0-05 Output options 0 Qutput Range: New Worksheet Ely: 0 New workbook 5. Hypothesized Mean Difference: specify the hypothesized value no here. For instance, if your null hypothesis is H0: p1 p2 = 0 then enter 0 here, or if the null hypothesis is H0: #1 p2 = 10 then enter 10 in this box. For this particular example, let's assume we are trying to test if there is a difference between the two population means. t-Test: Two-Sample Assuming Unequal Variances ? X Input OK Variable 1 Range: $A$1:$A$60 + Cancel Variable 2 Range: $B$1:$8$50 Hypothesized Mean Difference: Help Labels Alpha: 0.05 Output options O Output Range: New Worksheet Ply: New Workbook 6. Labels: if in your input you included the header of the data check this box. In this particular example, the headers have been included in the ranges. Therefore, the box will be checked. This tells Excel that the first row should not be included in the analysis as it is the header (is a text). t-Test: Two-Sample Assuming Unequal Variances ? X Input OK Variable 1 Range: $A$1:$A$60 T Variable 2 Range: $B$1:$B$50 Cancel Hypothesized Mean Difference: 0 Help Labels Alpha: 0.05 Output options O Output Range: New Worksheet Ply: O New Workbook 7. Alpha: specify the significance level of your test here. For this particular example, we are testing at 5% significance. Excel will use this a value to determine the critical value (t*).t-Test Two-Sample Assuming Unequal Variances ? ""\"t III Variable 1 Range: $A$1 :$A$60 Variable a Range: -$B$1$B$50 : I Cancel Hypothesized Mean Difference: 0 Labels Output options 0 Qutput Range: - : I New Worksheet Ely. 0 New workbook 8. Output options: choose Output Range and in the box specify the Cell where you want the report of the test to be pasted. In the assignment make sure you pay attention to where you need to place the report. In this particular example, we will place the report in the Cell G8. Feel free to move the window to select the desired cell if it is hidden behind the window. t-Test: Two-Sample Assuming Unequal Variances Input Variable 1 Range: $A$1:$A$60 VariableZ Range: $B$1:$B$50 Hypothesized Mean Difference: 0 New Worksheet Ely: 0 New workbook 9. Click Ok to View the report. Reviewing the Report and Interpreting Results Here is the result we had for this particular test (I had to change the column width a little to fit the text in the report). t-Test: Two-Sample Assuming Unequal Variances Sample 1 Sample 2 Mean 3.152542373 4.93877551 Variance 3.545295149 5.517006803 Observations 59 49 Hypothesized Mean Difference 0 df 91 t Stat -4.298480433 P(T