le time period ls from ome high wchon over te es 1 mber of cans of soft drinks s
ID: 3045217 • Letter: L
Question
le time period ls from ome high wchon over te es 1 mber of cans of soft drinks sold in a machine each week is recorded below of s. The number of WEEKLY PERIOD Soft drinks sold WEEKLY PERIOD Soft drinks sold WEEKLY PERIOD 122 85 10 98 98 110 108 115 95 17 92 105 19 78 125 85 106 140 84 20 Soft drinks sold 85 105 152 140 108 a. Using Excel, develop forecasts for periods 1 to 24, using a trend line b. Calculate the MSE and MAD for your model. Calculate an R statistic and interpret it. 6. Attendance at the IceBats Hockey games is listed below 2863, 2481, 3239, 3519, 3349, 3637, 3501, 3892, 3732, 3526, 3652, 2584, 3849, 2458, 3598, 4001, 3999, 3145, 2895, 2958. Excel, develop forecasts for periods 1 to 20, using a trend line. a. b. Calculate the MSE and MAD for your model. c. Calculate an R statistic and interpret it. 417Explanation / Answer
5)ans) a) To fit a trend line select all the data points of time points and soft drink sold values.The go to scatterplot in Insert tab and plot the scatterplot. Now on the graph select the data points and right click on it and select "fit trendline" option.To forecast the values for period 1 to 24 first we have to fit a trend line. Here I have fit a linear trend. The trend equation is given by- y = 0.6109*t + 98.239 where y are the values of soft drink sold and t is the time points. Then put t= 1,2,...24 to get the forecasted values at the time points.
b) MSE is sum of squares of the difference of actual values and forecasted values divided by total time points. By using the formula in excel we get the value is 14.212.
MAD is sum of the absolute difference of actual values and the forecasted values divided by total time points. By the given formula we get the value is 333.15. You can calculate absolute values in excel by abs() function.
c) The R-statisitcs can be directly evaluated from the graph by clicking on the data points of the scatter plot and select trendlines. There you will get a option to show the trend line eqaution and the R-square value. Here the R-square is 0.0509. It shows how well the line fits the data values. Higher the value better the fit. Here R-square is very low. So the trend line is not very well fitted.
For this case you can go for other trend line like polynomial trend lines and see which curve fits the data best.
6)ans)It is same as question 5. To fit a trend line select all the data points of time points and soft drink sold values.The go to scatterplot in Insert tab and plot the scatterplot. Now on the graph select the data points and right click on it and select "fit trendline" option.To forecast the values for period 1 to 20 first we have to fit a trend line. Here I have fit a linear trend. The trend equation is given by- y = 7.2632*t + 3267.6 where y are attendence in hockey games and t is the time points. Then put t= 1,2,...20 to get the forecasted values at the time points.
b) MSE is sum of squares of the difference of actual values and forecasted values divided by total time points. By using the formula in excel we get the value is 554800.
MAD is sum of the absolute difference of actual values and the forecasted values divided by total time points. By the given formula we get the value is 506.89.
c) Here the R-square is 0.0076. It shows how well the line fits the data values. Higher the value better the fit. Here R-square is very low. So the trend line is not very well fitted.
For this case you can go for other trend line like polynomial trend lines and see which curve fits the data best.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.