Question

Gross Sales Distance to Neighborhood MedianNeighborhood NeighborhoodNumber Businesses Median Age Parking Dummy Variable Free Parking Nearby Location Day) University (mi) Residential Population within 5 mi 3 5 0 1 2 2 NeighborhoodNumber Businesses Parking Dummy Variable Free Parking Nearby Location(1 Day) University (mi) Residential Population within 5 mi 10400 9 1 Excel Assignment #2: Multiple Regression The Food Truck Case Study Overview and Objectives This assignment examines the case of a new food truck and the role that location plays in profitability. The food truck would like to not only identify profitable locations, but also to identify factors that relate to profitability. That way, predictions can be made about the profitability of new locations Statistics Concepts ° Multiple regression Dummy variables F-tests for model significance T-tests for coefficient significance Excel Skills Using the data analysis add-in to conduct multiple regressions Using IF statements to code categorical data into numeric representations Copy & paste spreadsheets Add a column of data . Delete a column of data Turn in: Completed Excel file. A Word document with answers to all the questions. The data that you will need is in the Excel file "2-FoodTruck.xlsx" on Canvas. This file contains data from 20 test locations that includes gross daily sales as well as characteristics of the location. The characteristics include the distance to a university, median income of the neighborhood, residential population within a 5-mile radius, the median age of the neighborhood, and the number of businesses within 5 miles. Tasks - Brief List (Turn in your Excel file with all tasks completed.) 1. Create a column in which to code the parking data. This column must be next to the other columns of location data. Then use "if" statements to code the parking data using “I” to represent free parking ("Yes"), and a "O” to represent no free parking IF(logical test, value if true, value if false) -IF(cell "Yes", 1, 0) 2. 3. 4. Calculate a multiple regression to create a model that can be used to predict the gross daily sales of a location Examine the results. Pay particular attention to the statistical significance of each model coefficient. Identify any coefficients that are not significant at the 5% level. Conduct a second multiple regression that omits any variables that did not reach statistical significance in the first regression. (We will talk more about model building in Chapter 16.) To do step 3, you will need to create a new table of data that omits the columns that correspond to the coefficients that were not statistically significant in the first regression. Questions (Turn in a Word document with your answers to all questions.) 1. 2. Explain the meaning of the coefficient for the dummy variable in the context of this 3. Report the F-statistic and its p-value for each regression. Describe what information 4. Compare the results of the two F-tests. Do the F-statistic and p-value change? If so 5. Summarize the results of the t-tests for the coefficients of the first regression. Report What is a dummy variable? problem is given by the F-tests. how? the p-values for each variable and explain which variables are significant and which are not. Specify the level of significance you are using 6. What happens to the p-values of the coefficients in the second model when a variable has been removed? If you were going to make predictions of the profitability of a location, which model would you prefer to use and why? 7.

Answer 1

Regression Statistics First Regression Multiple R R Square Adjusted R Square 0.939229741 0.882152506 0.840064116 204.7453487 20 Standard Error Observations ANOVA Significance F 5 4393184.54 878636.908120.9595210.0000048 df sS MS Regression Residual 14586889.2095 41920.65782 19 Total 4980073.75 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% ntercept Variable 1 Variable 2 Variable 3 Variable 4 Variable 5 3165.05818 678.1471988 4.667214118 0.000363039 1710.577096 4619.539264 1710.577096 4619.539264 -85.11115582 28.82267069 -2.9529239930.010485031 -146.9296362 -23.29267541 -146.9296362 -23.29267541 0.000175146 0.007853972 0.02200279 0.982523153 -0.016669949 0.017020241 -0.016669949 0.017020241 0.0084008050.292985412 -0.051737393 -0.292985412 -0.051737393 73 4.997387735 1.839741233 0.087103689 -1.524430416 19.91223096 -1.524430416 19.91223096 4.767876612 2.4812433540.026405687 1.604183871 22.05634045 1.604183871 22.05634045 -0.172361402 0.056240562 3.064716908 9.1939002 Shest? |Sheet Sheet4 Sheet3 Sheetó Sheet5 Shect2

1 SUMMARY OUTPUT 2 Second Regression Regression Statistics 4 Multiple R 5 IR Square 6 Adjusted R Square 0.805975457 7 Standard Error 8 Observations 0.914664371 0.836610911 225.5118779 20 10 ANOVA df MS FSignificance F 3 4166384.037 1388794.679 27.3085852x 16 813689.7133 50855.60708 12 Regression 13 Residual 14 Total 15 16 17 Intercept 18 X Variable 1 19 X Variable 2 20 X Variable 3 21 19 4980073.75 coefficients standard Error tstat P-value Lower 95% Upper 95% over 9509pper 95.0% 3203.577758 562.1943594 5.698345606 3.29568E-05 2011.778956 4395.37656 2011.779 4395.377 -91.52202135 29.73448504 3.077975665 0.007204573 -154.5563137 -28.48772895 -154.556 -28.4877 0.145348244 0.050080266 -2.902305734 0.010391156 -0.251513666 -0.039182822 -0.25151 0.03918 14.31611927 4.72403041 3.030488381 0.007956653 4.30162217 24.33061637 4.301622 24.33062

in first time while dloing enultiple pegvressien for 5 independent uro ble, we found ィhat, only th NAをtables ave sterf stieaug signi cant to Gross Soles en aze,distenee to Ui ver sitg (mi) re e 2) Numbet Bysiness withio s mt 3)Neighborhood Res, populati,わ. If p-va]ue.くo'as then that two uoziables arze Statis ticaigifeen 5o they Canit So ue ceeafe。notheも をeyession model with So Afte loing the iple vegression analysis mon 个eque s510わ. Selo nd timet toe, got愀murtiple regressoり model as + 14 916 (x3) oheze1 Estimafed Gross Sates YE Distance fo University CM1) 2A

questien first Regvessieh 20S3S Serond Regressieh, 27.3085 P- Nalye 01 000048 0. 000o f- tost is a test af ovevall rgo cance of Regressio A nalgsis The NedA tlypothesis states that the mode { uǐthno ens en The Aernaive typothesis soys that your model fts the datu better than he intercept on/9 mdalel Compare value for the test to yosigniican ou. 51 の,ヤ your sarmple data providle suffcient e vialence to onelugc that your vejve sich madel tis the late beter than the modej with oo inlapenaet arribes guestionヅも) After torparing the Ava fue toette F-test tour significance leve s. Both Plues gre less than ~005. we voneluole that in botth tases, gample dlatu proviele s sutfcient evidlence to cloneluele t our regression ael fts drta vaxy well

Page s a, s] t-t at is ysed慨test of statistical significance oC regression toefficients.t-fthe P-value is less than our signiftance level . Than w conclude toat 'this Navigble is sta tisカcany Bigolnedent that arie e went be emitfed from regressie r model After obset ving the p-values in first regressior it seems, threevevisbihs ext of fe ose 1) Distance, to University mi G, H3 P.velues f-values of 5ewod Regressior frst regression o o lo o O 2 beern osezued that ,ofter vemo ving twe uawables me regression yne less 1t has of secon thon #hat of fist time