3. How confident atr yiu in your calculations of annual sales?

Jacob's Convenience Store is a chain of Gas Station/Convenience Stores with multiple locations that is growing rapidly. Zachary, the manager of operations is constantly on the lookout for new locations. To assist in choosing good locations, Zachary has gathered data from the current stores. That data can be found in the file Jacobs.xlsx. The data gathered include: Variable Description Annual Sales: Annual Sales/$1000's SqFt: Building size in square feet/1,000 Inventory: Inventory levels/$1000 Advertising: Advertising Dollars/$1,000 Size of District: Number of families in the area/1,000 No Competing Stores: The number of competing stores in the area Zachary has the possibility of opening a new store with the following characteristics: SqFt: 3,000 Inventory: $450,000 Advertising $12,000 Size of District: 11,000 No Competing Stores: 10 Zachary has requested that you provide him with the following information, 1. What is your prediction for annual sales in the new store? Please show your work. 2. Of the three variables that Zachary can control: SqFt, Inventory, and Advertising, which variable contributes the most to annual sales? What is the second most important contributor to sales? If Zachary adds an additional 1,000 units on each of these three items how much will they Individually contribute to annual sales? 3. How confident are you in your calculation of annual sales? 4. Keeping all other parameters above constant, how large in SqFt, would a store need to be in order to generate $1,000,000 in sales? Is a store of that size practical given the size of the current stores? Explain. Assignment: Analyze the data, answer the questions. For the sake of simplicity you may place your answers in the spreadsheet in a textbox. When completed, upload your workbook to the appropriate D2L dropbox B41 fx Sq Ft Obse + 102 c B G D E H 1 Annual Sales Inventory Advertising Size of District No Competing Stores 3 231 2 294 8.199999809 11 SUN 8.199999809 156 2 200000048 3 232 6.900000095 12 4.099999905 4 0.5 10 149 15 3 4.300000191 5.5 5 600 519 1 12 Multi 16.10000038 567 6 5 10.60000038 437 4.400000095 RS 14.10000038 487 4.800000191 7 571 11.80000019 4 12.69999981 Adju 8 512 299 3.099999905 8.1 Stan 10.10000038 10 9 195 2.5 347 7.699999809 12 10 20 1.200000048 212 3.299999952 2.099999905 15 68 0.600000024 4.900000095 11 8 4.699999809 ANO 12 570 5.400000095 788 17.39999962 12.30000019 1 13 428 4.199999809 577 10.5 14 7 Regn 464 4.699999809 535 14 11.30000019 3 15 Resic 15 15 0.600000024 163 2.5 2.5 14 Total 16 65 1.200000048 168 4.699999809 3.299999952 11 17 98 1.600000024 151 4.599999905 2.700000048 10 18 398 4.300000191 342 5.5 16 4 Interc 19 161 2.599999905 196 7.199999809 6.300000191 13 Sq Ft 20 397 3.799999952 453 10.39999962 13.89999962 7 Inven 21 497 5.300000191 518 11.5 16.29999924 1 Adves 22 528 5.599999905 615 12.30000019 16 0 23 99 0.800000012 278 2.799999952 6.5 14 24 0.5 1.100000024 142 3.099999905 1.600000024 12 25 347 3.599999905 461 9.6 11.30000019 6 26 341 3.5 382 9.800000191 11.5 5 27 507 5.099999905 590 12 15.69999981 0 28 400 8.6 517 7 12 B 29 30 31 Q1) Annual Sales (V) Formula Constant + B1*(X1) + B2*(X2) + B3*(x3) 32 = Intercept + (3*Sq Ft Coefficients) + (450*Inventory Coefficients) + (12*Advertising Coefficients) 33 34 35 Prediction for annual sales: $365,93 36 37 Prediction for annual sales = $365.93 38 39 40 41 02) 42 Mir05 + SUMMARY OUTPUT Regression Statistics Multiple R 0.9737604 R Square 0.948209317 Adjusted R 0.941454011 Standard 46.47178962 Observatio 27 ANOVA df F 140.3651076 Significance F 6.30754E-15 Regression Residual Total MS 303136.3085 2159.62723 3 23 26 SS 909408.9256 49671.4263 9590B0.3519 Intercept Sq Ft Inventory Advertising Coefficients Standard Error -98.55395312 21.58426475 33.80275790 8.460971972 0.329976678 0.13958751 17 88181559 5.725257093 Stat P-value Lower 95% 4.56600928 0.0001373 -143 2044067 3.995138865 0.000569373 16.2990030 2.36394128 0.026904405 0.041217912 3.12332098 0.0047759586.038218937 Upper 95% Lower 95.0% Upper 95.0% -53.90349950 -143 2044067 -33.90349959 51.30561202 18.299903951 30561202 0.618735444 0.041217912 0.618735444 29.72541225 6.038218937 29.72541225 advertising Coefficients) Q1) Annual Sales (Y) Formula = Constant +B1*(X1) + B2*(X2) + B3* (x3) Intercept + (3*Sq Ft Coefficients) + (450*Inventory Coefficients) Prediction for annual sales: $365.93 Prediction for annual sales = $365.93 Q2) Inventory contributes the most to sales and the second most important contributor to sales is advertising. Adding 1,000 units to squre feet it will contribute 89,800 more. Adding 1,000 more units to inventory will contribute 10462000 more. And lastly adding 1,000 more to advertising will contribute 218700 more. Sq Feet 89800 Inventory 10462000 Advertising 218700