assignment- data analytics

Walter would like to find cost drivers for his landscaping jobs. He asks for your help to run some Walter would like to find cost drivers for his landscaping jobs. He asks for your help to run some Year Month Equipment HoNumber of La DL Hours Landscaping Cost 1 1 120 10 500 18,000.00 1 2 85 9 450 13,500.00 1 3 105 8 300 8,250.00 1 4 125 12 550 15,000.00 1 5 90 5 475 11,250.00 1 6 110 10 250 9,300.00 1 7 135 8 400 11,250.00 1 8 105 7 400 20,250.00 1 9 145 10 225 14,250.00 1 10 130 10 550 24,000.00 1 11 150 13 625 21,000.00 1 12 155 6 350 14,250.00 2 1 85 8 460 19,800.00 2 2 95 7 575 14,850.00 2 3 115 9 310 9,075.00 2 4 135 10 450 16,500.00 2 5 100 6 485 12,375.00 2 6 120 11 260 10,230.00 2 7 145 9 410 12,375.00 2 8 115 8 410 22,275.00 2 9 105 11 235 15,675.00 2 10 140 11 560 26,400.00 2 11 100 12 635 23,100.00 2 12 80 7 360 15,675.00 3 1 242 9 600 24,010.00 3 2 171 7 585 18,070.00 3 3 103 10 320 13,800.00 3 4 224 8 460 20,050.00 3 5 124 7 285 15,100.00 3 6 270 12 660 24,500.00 3 7 114 5 240 18,500.00 3 8 262 9 420 26,980.00 3 9 257 12 245 19,060.00 3 10 240 8 570 28,400.00 3 11 180 7 645 27,970.00 3 12 144 8 650 19,060.00

cost analyses on three key aspects of his landscaping jobs: equipment hours, number of l cost analyses on three key aspects of his landscaping jobs: equipment hours, number of l 1. You woud like to first decide which method to use to find cost drivers. Yo High Low Method Highest equipment hours 270 Lowest Equipment hours 80 Lowest cost $24,500 Highest cost $15,675 Variable cost per unit $46.45 $11,959.21 Regression analysis is the better choice for identifying multiple or single cos Number of Larger Trees as a cost driver SUMMARY OUTPUT Regression Statistics Multiple R 0.16107475 Fixed cost Helps deal with complex relationships between variables : Can handle non Robust Against Outliers: Less influenced by outliers compared to the High-L Statistical Significance: Provides statistical measures to assess the relations Handles Multiple Variables: Can analyze multiple potential cost drivers sim Better Cost Estimation: Offers a precise equation for accurate cost predictio 1. Evaluate each of the three possible cost drivers for all 3 years combine

R Square 0.543017354977 Adjusted R Square 0.515321437096 Standard Error 3916.51076537 Observations 36 ANOVA df Regression 2 Residual 33 Total 35 Coefficients Intercept 2337.121200117 X Variable 1 47.0253488266 X Variable 2 19.43897047814 Recommended Model: The recommended regression model for explaining model that includes both Direct Labor Hours and Equipment Hours as cost d value (0.5430) compared to the individual regression models for each cost d 0.3525). The adjusted R-Square value also indicates a good fit of the model.

large trees in the project, and direct labor (DL) hours. Data is available for the pa large trees in the project, and direct labor (DL) hours. Data is available for the pa ou start with High-Low method and regression methods and use equipment hour Regression Method SUMMARY OUTPUT Regression Statistics Multiple R 0.575911525778202 R Square 0.331674085524176 Adjusted R Square 0.31201744098077 Standard Error 4666.17618896739 Observations 36 ANOVA df Regression 1 Residual 34 Total 35 Coefficients Intercept 9093.34923832664 X Variable 1 59.9042037532203 st drivers and the reasons are as follows: n-linear relationships more accurately. Low method. ship’s significance. multaneously, in this case we only considered equipment hours but there are two ons. ed by using the method you selected. If more than one cost driver is statistically

SS MS F 601487480.238789 300743740.119395 19.6064039951808 506188866.983433 15339056.5752555 1107676347.22222 Standard Error t Stat P-value 2539.44142351311 0.9203288480993 0.364080090413375 12.6765382424646 3.70963648964274 0.000760891147788 4.97590331309505 3.90662142228109 0.00043822896048 g Walter's landscaping costs is the multiple regression drivers. This model has a significantly higher R-Square driver (R-Square values ranging from 0.0259 to .

ast 3 years. ast 3 years. rs as the driver. You find the more approporite you would like to go and show the reasons to Walter SS MS F Significance F 367387539.52169 367387539.52 16.87338268 0.000237541 740288807.700532 21773200.226 1107676347.22222 Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% 2215.46009213388 4.1044969714 0.000239762 4590.99263 13595.70585 4590.99263 14.5833138302589 4.1077223223 0.000237541 30.26734429 89.54106321 30.26734429 o other potential cost drivers that we may want to evaluate. significant, consider running a multiple regression model, evaluating the validity of this model. which re

Significance F 2.44537330099048E-06 Lower 95% Upper 95% Lower 95.0% Upper 95.0% -2829.41122299648 7503.6536232 -2829.41122 7503.653623 21.2347378536025 72.8159598 21.23473785 72.8159598 9.31541906901582 29.562521887 9.315419069 29.56252189

Upper 95.0% 13595.70585 89.54106321 egression model should you recommend that Walter use to explain his landscaping costs? Specif

SUMMARY OUTPUT Regression Statistics Multiple R 0.57591153 R Square 0.33167409 Adjusted R S 0.31201744 Standard Erro 4666.17619 Observations 36 ANOVA df SS MS F Significance F Regression 1 367387540 367387540 16.8733827 0.00023754 Residual 34 740288808 21773200.2 Total 35 1107676347 CoefficientsStandard Error t Stat P-value Lower 95% Upper 95% Intercept 9093.34924 2215.46009 4.10449697 0.00023976 4590.99263 13595.7058 X Variable 1 59.9042038 14.5833138 4.10772232 0.00023754 30.2673443 89.5410632

Lower 95.0% Upper 95.0% 4590.99263 13595.7058 30.2673443 89.5410632

Lower 95.0% Upper 95.0% 5248.53597 22231.5712 -496.518869 1371.05223

SUMMARY OUTPUT Regression Statistics Multiple R 0.73689711 R Square 0.54301735 Adjusted R S 0.51532144 Standard Erro 3916.51077 Observations 36 ANOVA df SS MS F Significance F Regression 2 601487480 300743740 19.606404 2.44537E-06 Residual 33 506188867 15339056.6 Total 35 1107676347 CoefficientsStandard Error t Stat P-value Lower 95% Upper 95% Intercept 2337.1212 2539.44142 0.92032885 0.36408009 -2829.41122 7503.65362 X Variable 1 47.0253488 12.6765382 3.70963649 0.00076089 21.2347379 72.8159598 X Variable 2 19.4389705 4.97590331 3.90662142 0.00043823 9.31541907 29.5625219

SUMMARY OUTPUT Regression Statistics Multiple R 0.73691201 R Square 0.54303931 Adjusted R S 0.51447927 Standard Erro 3976.86583 Observations 35 ANOVA df SS MS F Significance F Regression 2 601428888 300714444 19.0139527 3.61461E-06 Residual 32 506094779 15815461.9 Total 34 1107523667 CoefficientsStandard Error t Stat P-value Lower 95% Upper 95% Intercept 2331.55309 2579.58562 0.90384792 0.3728304 -2922.89087 7585.99706 120 47.1178631 12.9276531 3.64473448 0.00093917 20.7850954 73.4506308 500 19.4020908 5.07515823 3.82295288 0.00057438 9.06433175 29.7398498

Lower 95.0% Upper 95.0% -2922.89087 7585.99706 20.7850954 73.4506308 9.06433175 29.7398498