1. The managers of the Ada Hotel forecast the breakfast covers based on number o
ID: 335086 • Letter: 1
Question
1. The managers of the Ada Hotel forecast the breakfast covers based on number of guest rooms. Following table presents the number of rooms sold and breakfast covers for the past year, 2017. Room Guests Breakfast Covers January 1,200 1,400 February 1,020 1,220 March 1,250 1,420 April 1,100 1,320 May 1,380 1,520 June 1,400 1,580 July 1,520 1,650 August 1,550 1,690 September 1,450 1,400 October 1,210 1,380 November 1,080 1,240 December 1,050 1,220 Required: 1. Determine the coefficient of correlation and coefficient of determination for the Ada Hotel based on the data presented above. Comment on the strength of the relationship between room guests and breakfast covers. 2. Determine a regression equation for forecasting breakfast covers based on the number of room guests. 3. If 1,620 room guests are forecasted for next January, how many breakfast customers are forecasted to be served? Note: You need to use MS Excel’s regression function to answer these questions. Please attach your regression output (Summary Output) to your submission. 1. The managers of the Ada Hotel forecast the breakfast covers based on number of guest rooms. Following table presents the number of rooms sold and breakfast covers for the past year, 2017. Room Guests Breakfast Covers January 1,200 1,400 February 1,020 1,220 March 1,250 1,420 April 1,100 1,320 May 1,380 1,520 June 1,400 1,580 July 1,520 1,650 August 1,550 1,690 September 1,450 1,400 October 1,210 1,380 November 1,080 1,240 December 1,050 1,220 Required: 1. Determine the coefficient of correlation and coefficient of determination for the Ada Hotel based on the data presented above. Comment on the strength of the relationship between room guests and breakfast covers. 2. Determine a regression equation for forecasting breakfast covers based on the number of room guests. 3. If 1,620 room guests are forecasted for next January, how many breakfast customers are forecasted to be served? Note: You need to use MS Excel’s regression function to answer these questions. Please attach your regression output (Summary Output) to your submission. 1. The managers of the Ada Hotel forecast the breakfast covers based on number of guest rooms. Following table presents the number of rooms sold and breakfast covers for the past year, 2017. Room Guests Breakfast Covers January 1,200 1,400 February 1,020 1,220 March 1,250 1,420 April 1,100 1,320 May 1,380 1,520 June 1,400 1,580 July 1,520 1,650 August 1,550 1,690 September 1,450 1,400 October 1,210 1,380 November 1,080 1,240 December 1,050 1,220 Required: 1. Determine the coefficient of correlation and coefficient of determination for the Ada Hotel based on the data presented above. Comment on the strength of the relationship between room guests and breakfast covers. 2. Determine a regression equation for forecasting breakfast covers based on the number of room guests. 3. If 1,620 room guests are forecasted for next January, how many breakfast customers are forecasted to be served? Note: You need to use MS Excel’s regression function to answer these questions. Please attach your regression output (Summary Output) to your submission.Explanation / Answer
Here Y (Breakfast Cover) is the dependent Varialbe & X (Room Guests) is independent variable
Put the data in X & Y Column in Excel > go to data Tab > click data Analysis > select X&Y input range > select the plot> you will get following output.
1. Step 1 : Run a regression equation, then following output will be observed
As per data set, and total number of XY pair is 12
Output equation is as below
Y= Slope + Co-efficient of co-relation (r) * X
Y= 403.8 + 0.8018*X
Co-efficient of correlation ( r )= 0.8018
Co- efficient of determination = (r)^2 = 0.6423
Therefore 64.23% is the likelihood of forecast with this model to be accurate
Strenght of relationship is good as the r value is more than 0.8.
2. Regression equation is as below
Y= Slope + Co-efficient of co-relation (r) * X
Y= 403.8 + 0.8018*X
3. X=1620 then we will have to calculate the Y as per equation above
Y= 403.8 + 0.8018*1620 = 1702.7 = 1703
SUMMARY OUTPUT Regression Statistics Multiple R 0.932347 R Square 0.86927 Adjusted R Square 0.856197 Standard Error 61.16764 Observations 12 ANOVA df SS MS F Significance F Regression 1 248785.2 248785.2 66.49378 9.96E-06 Residual 10 37414.81 3741.481 Total 11 286200 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 403.773 125.8683 3.207901 0.009365 123.321 684.225 123.321 684.225 X Variable 1 0.801757 0.098322 8.154372 9.96E-06 0.582681 1.020833 0.582681 1.020833Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.