Please use an excel spreadsheet and show me the formulas for how you got the ans
ID: 3143282 • Letter: P
Question
Please use an excel spreadsheet and show me the formulas for how you got the answers
The manager of Gilley’s Ice Cream Parlor needs an accurate forecast of the demand for ice cream. The store orders ice cream from a distributor a week ahead; if the store orders too little, it loses business, and if it orders too much, the extra must be thrown away. The manager belives that a major determinant of ice cream sales is temperature (i.e.,the hotter the weather, the more ice cream people buy). Using an almanac, the manager has determined the average day time temperature for 14 weeks, selected at random, and from store records he has determined the ice cream consumption for the same 14 weeks. These data are summarized as follows:
Average Temperature(degrees)
a. Develop a linear regression model for these data and forecast the ice cream consumption if the average weekly daytime temperature is expected to be 85 degrees.
b. Determine the strength of the linear relationship between temperature and ice cream consumption by using correlation.
c. What is the coefficient of determination? Explain its meaning.
Please use an excel spreadsheet and show me the formulas for how you got the answers
WeekAverage Temperature(degrees)
Ice cream sold (gal) 1 68 80 2 70 115 3 73 91 4 79 87 5 77 110 6 82 128 7 85 164 8 90 178 9 85 144 10 92 179 11 90 144 12 95 197 13 80 144 14 75 123Explanation / Answer
Result from regression : -
hence
Sales ^ = -176.8924 + 3.821642197 * Avg_Temp
type " =(-176.8924 + 3.821642197*85) " to get sales = 147.947187
b) r = 0.8837
r> 0.8
hence strong relationship
c)
R^2 = 0.781068 = 78.11 %
it means 78.11 % of variation in sales is explained by the model
SUMMARY OUTPUT Regression Statistics Multiple R 0.88378097 R Square 0.781068803 Adjusted R Square 0.762824537 Standard Error 17.71105561 Observations 14 ANOVA df SS MS F Significance F Regression 1 13429.25068 13429.25068 42.8117408 2.76E-05 Residual 12 3764.177892 313.681491 Total 13 17193.42857 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -176.8924105 47.83687429 -3.697825434 0.00304786 -281.12 -72.6648 -281.12 -72.6648 Average Temperature(degrees) 3.821642197 0.584074949 6.543068147 2.7572E-05 2.549052 5.094232 2.549052 5.094232