Question

Please show the way with excel data analysis tools as much as possible.

Please show all a, b, c, d options with your interpretation

The Conch Café, located in Gulf Shores, Alabama, features casual lunches with a great view of the Gulf of Mexico. To accommodate the increase in business during the summer vacation season, Fuzzy Conch, the owner, hires a large number of servers as seasonal help. When he interviews a prospective server, he would like to provide data on the amount a server can earn in tips. He believes that the amount of the bill and the number of diners are both related to the amount of the tip. He gathered the sample information in FILE1.

a) Develop a multiple regression equation with the amount of tips as the dependent variable and the amount of the bill and the number of diners as independent variables. Write out the regression equation. How much does another diner add to the amount of the tips?

b) Conduct a global test of hypothesis to determine if at least one of the independent variables is significant. What is your conclusion?

c) Conduct an individual test on each of the variables. Should one or the other be deleted?

d) Use the equation developed in part (c) to determine the coefficient of determination. Interpret the value.

Customer	Amount of Tip ($)	Amount of Bill ($)	Diners
1	7	48,97	5
2	4,5	28,23	4
3	1	10,65	1
4	2,4	19,82	3
5	5	28,62	3
6	4,25	24,83	2
7	0,5	6,24	1
8	6	49,2	4
9	5	43,26	3
10	4,75	31,36	4
11	5,25	32,87	4
12	6	34,99	3
13	4	33,91	4
14	3,35	23,06	2
15	0,75	4,65	1
16	3,3	23,59	2
17	3,5	22,3	2
18	3,25	32	2
19	5,4	50,02	4
20	2,25	17,6	3
21	5,5	44,47	4
22	3	20,27	2
23	1,25	19,53	2
24	3,25	27,03	3
25	3	21,28	2
26	6,25	43,38	4
27	5,6	28,12	4
28	2,5	26,25	2
29	9,25	56,81	5
30	8,25	50,65	5

here is the excel link if necessary: https://drive.google.com/file/d/1WxCMXGpBRdc5IWv0bDCBixq4xPLXVOQz/view?usp=sharing

Answer 1

Use the Excel Analysis ToolPak to perform a regression on the data using the tip amount as the dependent value. The result is shown below SUMMARY OUIPU Rerressim Safstics 1932474B19 Mulliple R RSAaRe 0.8695014284 AtfustedR Sae 185384331 StancardEIro 0.791101451 30 ANOVA MS 2 112 595446 5 24r9301 84.955E3ur 27 1684IT206 0.62541505 Total 24 12449E67 SantfEnor Coefficerts Pvate hteavept 0.14475408 Amot of 0.048520271 00 20316473112105 124095E-05 Dness 054800E4850.232158395 25754578 101579527 The coefficients are shown in the second column of the last table. Thus the regression equation IS Y =-0.586 + 0.09&K + 0.598X, where Y is the estimated tip amount, X, is the bill amount, and X, is the number of diners Therefore, each additional diner adds $0.60 to the expected tip amount.

To conduct the global hypothesis test, state the hypotheses. The null hypothesis is that all regression coefficients are zero. The alternate hypothesis is that not all coefficients are zero Hi : Not all the β's are 0. Use the level of significance α-0.05

The test statistic for the global test is the F-statistic given by the following formula SSR/k SSEAn-(k+)] where SSR is the regression sum of squares, SSE is the residual sum of squares, k -2 is the number of independent variables, and n 30 is the sample size.

Next, determine the critical value. Since the statistic follows an F distribution, the test will be one-sided. The number of degrees of freedom in the numerator is k = 2 and in the n-(k + 1)-27 degrees of freedom. On the 5% significance level F denominator there are distribution table in Appendix B-4, find the closest value corresponding to this condition. This freedom in the numerator is 3.39. Thus the critical value is 3.39

The decision rule is: If F>3.39, reject the null hypothesis. Otherwise do not. The computed value of F is 112.60/2 16.90/27 -89.96. there is sufficient evidence to reject the null hypothesis and conclude that not all the regression coefficients are zero. Therefore at least one variable is significant.

To test regression coefficient B, state the hypotheses Haß, #0. Use the level of significance α 0.05

The test statistic for individual regression coefficients is a t statistic given by the formula where b, is the computed regression coefficient and sh is its standard error. This t statistic has -(k +1) 27 degrees of freedom.

Next determine the critical values. This test will be two-tailed since the alternate hypothesis is a not-equal condition on the Students t Distribution table in Appendix B 2, examine the row a 0.05 for two-tailed tests. This value is 2.052. Therefore, the critical values are 2.052

Now compute the t statistic for each coefficient using the results shown on the regression output table. Note that these values are already computed and given in the fourth column of the table. -4.73 2-2.57 Thus, both coefficients are in the rejection region and therefore neither variable should be deleted

(cl) The coefficient of determination is given by SSR RC SS Total The SSR and SS Total can be found on the ANOVA table. Thus the coefficient of determination IS 112.60 129.49 0.87 Note that this value is found on the regression statistics table. This value indicates that 87% of the variation in tip amount is explained by the bill amount and number of diners.