Question

In: Statistics and Probability

Run two regressions using Excel from the data below. Find the following information: 1. estimated regression...

Run two regressions using Excel from the data below. Find the following information: 1. estimated regression equations for both regressions 2. both coefficients of determination. 3. significance of each independent variable 4.Report the significance of both models. 5.Predict y for a fictitious set of x values for both

Years Weekend Daily Tour Income Daily Gross Revenue Number of Tourists
1 Friday 3378 4838.95 432
1 Saturday 1198 3487.78 139
1 Sunday 3630 4371.3 467
2 Friday 4550 6486.48 546
2 2467 3437.39 198
2 Sunday 3593 4571.43 452
3 Friday 898 2515.15 119
3 Saturday 2812 5462.11 342
3 Sunday 2650 5498.89 321
4 Friday 3230 5071.14 402
4 Saturday 4798 8051.43 523
4 Sunday 3253 4291.95 353
5 Friday 2848 4545 347
5 Saturday 4632 8865.01 534
5 Sunday 3767 4710.64 412
6 Friday 4499 10752.74 529
6 Saturday 3868 6435.63 422
6 Sunday 2489 3389.37 288
7 Friday 3448 6129.58 367
7 Saturday 3612 7357.12 406
7 Sunday 1937 2121.76 216
8 Friday 2548 4738.86 294
8 Saturday 2833 4141.98 317
8 Sunday 2214 4878.35 284
9 Friday 1520 4102.49 169
9 Saturday 4322 8639.55 462
9 Sunday 1833 3946.71 203
10 Friday 2271.63 4236.31 235
10 Saturday 2407.88 5613.27 266
10 Sunday 1772.17 5580.17 182
11 Friday 1494 3833.52 177
11 Saturday 1998 3986.57 213
11 Sunday 1388 2721.56 165
12 Friday 1925 3952.19 190
12 Saturday 2695 6281.3 243
12 Sunday 1525 3356.14 172
13 Friday 1725 3822.59 187
13 Saturday 2450 4141.75 253
13 Sunday 1407.5 3312.41 173
14 Friday 2394 4571.5 242
14 Saturday 3012 6363.3 311
14 Sunday 2058 3502.22 239
15 Friday 2427 5881.13 267
15 Saturday 3189 10409.13 336
15 Sunday 2109 4955.05 178
16 Friday 2244 4347.41 184
16 Saturday 3195 4935.17 274
16 Sunday 1017 3486.27 114
17 Friday 3470 6290.99 325
17 Saturday 5323 13132.55 478
17 Sunday 2345 5014.45 242
18 Friday 1671 2740.23 177
18 Saturday 2321.94 4423.31 246
18 Sunday 1542 2650.48 182

Solutions

Expert Solution

Analysis
regression 1:

1)  
regression equation:   
Daily Gross Revenue = 1408.52 + 12.68*number of tourists  
  
2)  
R^2 =    48.91%
  
3)  
Ho: beta1 is not significant  
h1: beta1 is significant  
With t=7.05, p<5%, I reject ho and conclude that beta1 is significant.  
  
4)  
Ho: model is not significant  
h1: model is significant  
With F=49.79, p<5%, I reject ho and conclude that the model is significant.

5)
when x= 119,
predicted Y = 1408.52 + 12.68*119 = 2917.44

regression 2:

1)  
regression equation:   
Daily Gross Revenue = 748.65179 + 1.6362*Daily Tour Income  
  
2)  
R^2 = 62.707%
  
3)  
Ho: beta1 is not significant  
h1: beta1 is significant  
With t=9.35, p<5%, I reject ho and conclude that beta1 is significant.  
  
4)  
Ho: model is not significant  
h1: model is significant  
With F=87.43, p<5%, I reject ho and conclude that the model is significant.

5)
when x= 3012,
predicted Y = 748.65179 + 1.6362*3012 = 5676.88619

procedure
data -> data analysis -> regression

regression 1: dependent variable: Daily Gross Revenue
independent variable: Number of Tourists

regression 2: dependent variable: Daily Gross Revenue
independent variable: Daily Tour Income

output
regression 1:

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.699382402
R Square 0.489135744
Adjusted R Square 0.479311431
Standard Error 1543.231194
Observations 54
ANOVA
df SS MS F Significance F
Regression 1 118573929.8 1.19E+08 49.78829188 4.02752E-09
Residual 52 123841250.9 2381563
Total 53 242415180.7
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 1408.526138 566.125925 2.488009 0.016088648 272.5113593 2544.541 272.5114 2544.541
Number of Tourists 12.68245322 1.797378968 7.056082 4.02752E-09 9.075748453 16.28916 9.075748 16.28916

regression 2:

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.791878228
R Square 0.627071127
Adjusted R Square 0.619899418
Standard Error 1318.533714
Observations 54
ANOVA
df SS MS F Significance F
Regression 1 152011560.6 1.52E+08 87.43677682 1.00186E-12
Residual 52 90403620.06 1738531
Total 53 242415180.7
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 748.6517999 500.552028 1.495652 0.140789091 -255.7793278 1753.083 -255.779 1753.083
Daily Tour Income 1.636240094 0.174984653 9.350763 1.00186E-12 1.285107699 1.987372 1.285108 1.987372

orchestra answered 3 years ago
Next > < Previous

Related Solutions

Run two different multiple regressions using excel. One should include two of the three idepenedent variables...
Run two different multiple regressions using excel. One should include two of the three idepenedent variables and the other should include all three variables. What are the regession equations? Years Weekend Daily Tour Income Daily Gross Revenue Number of Tourists 1 Friday 3378 4838.95 432 1 Saturday 1198 3487.78 139 1 Sunday 3630 4371.3 467 2 Friday 4550 6486.48 546 2 Saturday 2467 3437.39 198 2 Sunday 3593 4571.43 452 3 Friday 898 2515.15 119 3 Saturday 2812 5462.11 342...
Run two different multiple regressions using excel. One should include two of the three idepenedent variables...
Run two different multiple regressions using excel. One should include two of the three idepenedent variables and the other should include all three variables. The first multiple regression equation is Gross Revenue = 766.981 + 2.977*Daily Tour Income - 12.31*Number of Tourists. 1. What is the second multiple regression equation?   Would adding dummy variables for the three days for my second regression work? l  need 2 dummy variables since I have 3 categories (days, in this case). What would the equation...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression line. X-Bar 2 4 5 6 Y-Bar 7 11 13 20
Find two lines of regressions from the following Data: Age of husband : 25 22 28...
Find two lines of regressions from the following Data: Age of husband : 25 22 28 26 35 20 22 40 20 18 & Age of wife: 18 15 20 17 22 14 16 21 15 14 Estimate the following. the coefficient of correlation
You are comparing the regression output across two publicly-traded companies. Both regressions were run using monthly...
You are comparing the regression output across two publicly-traded companies. Both regressions were run using monthly data for 5 years and against the S&P500 with the returns on each company’s stock as the independent variable. Nero Cannery Rand Foods Intercept 0.15% 0.45% R-squared 20% 35% Slope 1.20 1.10 1.If the implied equity risk premium with a constant dividend growth rate and based on a broad U.S. stock market index is equal to 8% and the risk-free rate is 2%, what...
The multiple regression model is estimated in Excel and part of the output is provided below....
The multiple regression model is estimated in Excel and part of the output is provided below. ANOVA df SS MS F Significance F Regression 3 3.39E+08 1.13E+08 1.327997 0.27152899 Residual 76 6.46E+09 85052151 Total 79 6.8E+09 Question 8 (1 point) Use the information from the ANOVA table to complete the following statement. To test the overall significance of this estimated regression model, the hypotheses would state there is    between attendance and the group of all explanatory variables, jointly. there is...
Question 1: A small data set is given below. You will run a t-test using Excel....
Question 1: A small data set is given below. You will run a t-test using Excel. In order to receive full credit, you must turn in Excel output and your final answers. Round off final answers to three decimal places, if appropriate. A medical researcher wants to determine whether a drug changes the body’s temperature. Seventest subjects are randomly selected, and the body temperature (in degrees Fahrenheit) of each is measured. The subjects are then given the drug and after...
Use the data below to solve the following problem using excel: 1 a) Import the data...
Use the data below to solve the following problem using excel: 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in each cell to determine the class for each vehicle. i. Class 1 if the vehicle horsepower is less than 80 HP. ii. Class 2 if the vehicle horsepower is between 81 and...
Consider the data set below. Excel File: data12-33.xls The estimated regression equation is ŷ = 30.33...
Consider the data set below. Excel File: data12-33.xls The estimated regression equation is ŷ = 30.33 - 1.88x. Estimate the standard deviation of ŷ p when x = 3 (to 3 decimals). Develop a 95% confidence interval for the expected value of y when x = 3 (to 2 decimals). (  ,  ) Estimate the standard deviation of an individual value of y when x = 3 (to 2 decimals). Develop a 95% prediction interval for y wh
Please show all steps using data set below and excel. By using a bivariate regression trend...
Please show all steps using data set below and excel. By using a bivariate regression trend line model, forecast total houses sold for the next five years. Prepare a time series plot of these data (years are on the x axis, THS is on the y axis) that shows both actual and forecast THS for the next five years. Total Houses Sold Year Northeast 1996         74.00 1997         78.00 1998         81.00 1999         76.00 2000         71.00 2001...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT