Need help with excel (Instructions are given) also please share the links!!

1. Two-Sample t-Test

Two-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. 1 W N 14 15 16 w 17 1 2. On the tool bar, choose Data and then open the Data Analysis Toolpak. ave . on H Book1 - Excel Behrouz Bakhtiari BB Home Insert Draw Page Layout Formula Data view View Developer Help Share Queries & Connections 24 2 2 Y & Clear Data Analysis Refresh Properties Stocks Geography = Z1 Sort Filter Reapply Text to What-If Forecast Outline All * [ Edit Links Advanced Columns ED |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 Analysis Tools Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling 4. Input: specify the range for Variable 1 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 Input Variable 1 Range: '-- vammg 1-H Hypothesized Mean Difference: l] Labels AI pha: 0.05 Output options 0 Qutput Range: New Worksheet Ely: 0 New workbook 5. Hypothesized Mean Difference: specify the hypothesized value so here. For instance, if your null hypothesis is H0: ,u1 ,uz = 0 then enter 0 here, or if the null hypothesis is Ho: #1 #2 = 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 T Cancel Variable 2 Range: $B$1:$8$50 Hypothesized Mean Difference: 0 Help Labels Alpha: 0.05 Output options 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 Variable 2 Range: Cancel $B$1:$8$50 Hypothesized Mean Difference: 0 Help Labels Alpha: 0.05 Output options Output Range: + New Worksheet Ply: 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 ? X Input OK Variable 1 Range: $A$1:$A$60 T Cancel Variable 2 Range: $B$1:$8$50 Hypothesized Mean Difference: 0 Help Labels Alpha: 0.05 Output options O Output Range: + New Worksheet Ply: 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 ? X Input OK Variable 1 Range: $A$1:$A$60 Cancel Variable 2 Range: $B$1:$8$50 Hypothesized Mean Difference: Help 0 Labels Alpha: 0.05 Output options Output Range: $G$8 T New Worksheet Ply: O 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 $14,963 $15,023 $15,084 b) What is the reason for the answer you provided in part (a)? ANSWER: -> $14,985 $15,131 $14,919 $14,963 $15,057 $14,827 $14,862 $15,003 $14,906 CO $14,855 $14,979 $15,179 10 $14,949 $14,96 $14,946 11 $14,952 $14,980 $15,158 12 $15,067 $14,953 $14,967 The result of your test should be placed here! 13 $15,082 $15,035 $15,019 14 $15,149 $15,034 $14,905 15 $14,956 $14,951 $14,912 16 $14,941 $14,915 $15,133 17 $15,073 $15,002 $14,984 18 $14,999 $15,251 $14,940 19 $15,015 $15,089 $15,006 20 $15,018 $14,991 $15,132 21 $15,022 $15,096 $15,033 22 $15,127 $14,94 $15,002 23 $15,100 $14,874 $14,923 24 $14,865 $15,146 $14,846 25 $15,013 $15,042 $14,800 26 $15,067 $14,990 $15,141 27 $14,944 $15,046 $15,057 28 $14,860 $15,098 $15,120 29 $15,013 $15,018 $14,902 30 $15,106 $14,952 $14,878 31 $15,005 $15,189 $15,179 32 $14,870 $15,026 $15,057 33 $14,935 $15,030 $15,120 34 $15,088 $14,937 $15,015 35 $14,998 $15,000 $14,874 36 $15,024 $14,840 $15,124 37 $15,104 $15,061 $14.968 38One-Way ANOVA using the Data Analysis Toolpak in Excel This document includes instructions to conduct a one-way ANOVA test and review and interpret the results. Conducting the test 1. Make sure your sampled data for each population is in a separate column. IN D UT 2. On the tool bar, choose Data and then open the Data Analysis Toolpak. ave . Of H ? Book1 - Excel Behrouz Bakhtiari BE Home Insert Draw Page Layout Formulas Data view View Developer Help Share Queries & Connections Y & Clear Data Analysis Refresh Properties III 24 4 2 Sort Filter TReapply Text to What-If Forecast Outline ANDS All * . Edit Links Stocks Geography # 21 Advanced Columns Es Analysis * Sheet 3. From the Analysis Tools available, choose ANOVA: Single Factor. This is the equivalent of the One-way ANOVA test we have discussed in lecture.Data Analysis Analysis Tools 'I ..-;.-'|-:: '3 i an: If F._- :to |' . actor Ith Replication Anova: Two-Factor Without Replication Correlation Covariance Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram 4. Input Range: specify the range for all of the variables by selecting the data for all of them together. You can include the header of the data as well. Anova: Single Factor Input 1an a... '-n Grouped By: El Labels in first row Output options 0 Quiput Range: 6) New WorksheetBly. O New workbook 5. Grouped By: the variables in this data set are grouped by columns. This means each variable is in a separate column. We will accept the default selection as our data is grouped by columns. 6. Labels in first row: 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). Anova: Single Factor ? X Input OK Input Range: $A$1:$C$51 + Grouped By: Columns Cancel O Rows Help Labels in first row Alpha: 0.05 Output options Output Range: + O 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 (Fcrit). Anova: Single Factor ? X Input OK Input Range: $A$1:$C$51 Cancel Grouped By: Columns Rows Help Labels in first row Alpha: 0.05 Output options O Output Range: New Worksheet Ply:] 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.Anova: Single Factor ? X Input OK Input Range: $A$1:$C$51 + Cancel Grouped By: Columns O Rows Help Labels in first row Alpha: 0.05 Output options O Output Range: $G$8 New Worksheet Ply: O 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). Anova: Single Factor SUMMARY Groups Count Sum Average Variance Sample 1 50 197 3.94 4.220816 Sample 2 50 186 3.72 2.573061 Sample 3 50 193 3.86 3.347347 ANOVA Source of Variation SS df MS F P-value F crit Between Groups 1.24 2 0.62 0.18341 0.83261715 3.057621 Within Groups 496.92 147 3.380408 Total 498.16 149 Notice there are two tables provided. The first table contains information on group size (sample size), sum, average and variance for each one of the samples (groups). The second table is the ANOVA table we have discussed in class (with the inclusion of the additional information on F crit). Also notice that the SS column and df columns have beenswitched in this table compared to the table we had in lecture. You can conrm all the relationships between different components of the table based on the material we learned in class. For this particular test with our data set, the p-value of the test has been calculated as approximately 0.83. The p-value is larger than the signicance of the test a = 0.05, which means we fail to reject the null hypothesis. The result could also be conrmed by looking at the F value (Fratia = 0.18341] and the critical value (Fm; = 3.06), indicating that we fail to reject the null hypothesis. A B C D E F G H 1 Daily Sales of Store 1 ($) Daily Sales of Store 2 ($) O 6849 4697 Please provide your answers here in this textbox: CON 6849 4681 4 5849 4693 a) Is the test one-tailed or two-tailed? ANSWER -> 5 5849 1732 b) Do you reject or fail to reject the null hypothesis? ANSWER -> 6 4681 4714 (c) What is the reason for the answer you provided in part (b)? 4731 4705 p-value method ANSWER -> 8 4685 1703 CO 4663 4673 critical value method ANSWER-> 10 4661 1728 11 4710 4739 12 4621 4751 The result of your test should be placed here! U 13 4655 4704 14 4704 4640 15 4746 4719 16 4673 4643 17 4758 4667 18 4691 4701 19 4673 1722 20 4753 4742 21 4728 4763 22 4636 4744 23 4694 4659 24 4721 4738 25 4669 4681 26 4632 4690 27 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 1702 37 4738 4716 38