Total Excel work

profileafdybz
Regression_Assignment_XXXXXX.xlsx
Home>Business & Finance homework help>Total Excel work

Part A Q1

Day 24oz units 12oz units Size change Label change Total minutes
1 4400 7650 7 6 178 SUMMARY OUTPUT
2 9600 4430 3 9 136
3 7440 4060 5 7 88 Regression Statistics
4 9800 8035 2 5 124 Multiple R 0.9652034251
5 11500 6140 9 10 241 R Square 0.9316176518
6 6280 6900 8 12 202 Adjusted R Square 0.9206764761
7 8320 6770 4 2 111 Standard Error 16.7299882564
8 10500 5300 4 8 146 Observations 30
9 11450 4560 10 14 263
10 2830 3830 7 7 177 ANOVA
11 10850 6880 6 9 200 df SS MS F Significance F
12 2680 3920 11 14 269 Regression 4 95328.9873235525 23832.2468308881 85.1478558015 0
13 7400 7160 14 16 318 Residual 25 6997.3126764474 279.8925070579
14 3720 8700 6 11 199 Total 29 102326.3
15 4580 8500 8 9 191
16 3770 5050 9 13 247 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
17 2820 3220 2 4 60 Intercept -2.377596872 16.2975483293 -0.1458867815 0.8851807109 -35.9430259715 31.1878322275 -35.9430259715 31.1878322275
18 5070 6400 8 12 200 24oz units 0.0022415107 0.0010252698 2.18626413 0.0383707821 0.0001299279 0.0043530934 0.0001299279 0.0043530934
19 7300 8900 7 8 181 12oz units 0.0047027371 0.0020134783 2.335628347 0.0278322673 0.0005559008 0.0088495734 0.0005559008 0.0088495734
20 7060 6640 3 3 117 Size change 14.2080770462 1.9837469465 7.1622426799 0.000000166 10.122473731 18.2936803614 10.122473731 18.2936803614
21 3050 7740 7 9 199 Label change 5.2559462352 1.5885859955 3.3085689097 0.0028443161 1.9841921331 8.5277003374 1.9841921331 8.5277003374
22 10480 6400 10 12 271
23 3800 6000 4 4 114
24 6570 6800 9 9 212 The regression model is
25 6140 7000 7 12 189 Total minutes=-2.3776+0.00224* (24oz units) +0.0047*(12oz units) +14.2* (Size change) +5.26* (Label change)
26 10370 8600 6 7 195 This indicates that the model needs an update since does not fit the equation y = (1/200) *x1 + (1/255) *x2 + 10*x3 + 5*x4 ,
27 4060 7000 5 5 139 which was estimated for production run time
28 3470 7200 8 12 223
29 10130 6900 10 10 246
30 11300 8450 6 9 195 b.) A positive intercept indicates that an increase in the independent variables which include (label change, size change,12ozunits and 24oz units)
will lead to an increase in the dependent variable (total minutes)

Q2 data

Property # Profit Size Advertexp Mgrperf Neighbors Attract
1 166.855 0 12.72 3 8 1
2 299.980 0 42.50 3 5 2
3 840.615 1 32.35 4 28 3
4 201.433 0 10.09 5 4 2
5 872.874 1 20.27 4 31 3
6 186.493 1 23.70 2 9 3
7 300.901 1 19.11 3 9 4
8 755.746 1 32.87 4 19 3
9 808.764 1 40.37 4 17 3
10 332.976 0 25.37 4 13 2
11 590.922 1 46.40 3 6 2
12 329.644 0 39.47 2 12 1
13 829.321 1 38.99 5 7 3
14 713.162 1 42.72 4 13 4
15 341.979 0 29.17 3 24 2
16 322.471 0 43.33 3 6 2
17 608.782 1 40.95 3 3 2
18 134.128 0 26.01 3 9 1
19 194.545 0 22.53 5 11 1
20 494.896 1 21.52 4 12 3
21 206.774 1 27.91 3 4 3
22 565.189 1 31.51 4 8 3
23 279.658 0 33.81 3 7 3
24 89.235 0 43.45 1 6 2
25 372.522 1 17.41 4 2 2
26 89.877 0 29.45 4 7 3
27 91.116 0 23.86 2 11 1
28 201.996 0 32.04 5 6 2
29 673.770 1 44.16 5 11 2
30 326.183 0 45.10 4 7 4
31 382.848 0 28.69 4 14 1
32 730.514 1 43.87 4 13 2
33 457.112 1 24.84 3 12 1
34 823.807 1 49.36 3 20 3
35 425.945 1 32.73 3 9 4
36 530.593 1 14.23 3 20 3
37 449.527 1 27.69 3 9 3
38 297.237 0 35.02 3 6 3
39 198.809 0 45.02 2 10 2
40 420.942 1 13.65 4 9 1
41 581.784 1 48.10 3 9 5
42 229.914 0 15.01 4 17 2
43 683.982 1 49.55 4 10 3
44 801.843 1 41.93 5 11 3
45 203.803 1 11.46 2 10 3
46 101.432 0 18.82 3 13 3
47 254.989 1 40.94 3 -0 2
48 432.992 0 46.02 3 17 3
49 496.599 1 24.53 4 12 3
50 204.977 1 23.36 4 5 3
51 -5.541 1 16.78 1 4 2
52 645.317 1 27.97 5 11 3
53 707.284 1 36.58 2 20 4
54 372.360 0 14.59 3 26 2
55 190.481 1 17.92 3 8 2
56 421.723 0 49.20 4 9 3
57 172.960 0 11.54 4 9 3
58 413.843 0 36.03 4 13 1
59 761.802 0 47.79 4 26 2
60 1322.114 1 37.38 5 29 4

Profit = annual profit in thousands Size = small (0) or large (1) design Advertexp = annual spending on local advertising, in thousands Mgrperf = Performance rating of local manager, on scale where 5 = best and 1 = worst Attract = Rating of physical attractiveness of property (location, architecture, etc.) on a scale where 4 is best Neighbors = Quantity and quality of nearby attractions: restaurants, shopping, arts venues, etc. Scale from 0 to 30, where 30 is best

Part B Q(1a)

Question 1
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.3851958122
R Square 0.1483758138
Adjusted R Square 0.1336926381
Standard Error 244.2010125818
Observations 60
ANOVA
df SS MS F Significance F
Regression 1 602612.368589524 602612.368589524 10.1051582819 0.002372386
Residual 58 3458779.80366727 59634.1345459875
Total 59 4061392.1722568
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 158.6375159665 91.6634833378 1.730651184 0.0888315905 -24.8468812884 342.1219132214 -24.8468812884 342.1219132214
Attract 108.7188643834 34.2005702269 3.1788611612 0.002372386 40.2589849925 177.1787437743 40.2589849925 177.1787437743
Profit=158.64+108.72*(attractiveness)

Part B Q(1b)&Q2

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9440259813
R Square 0.8911850534
Adjusted R Square 0.8811095954
Standard Error 90.4658900748
Observations 60
ANOVA
df SS MS F Significance F
Regression 5 3619451.99983697 723890.399967393 88.4510710674 9.35899484975784E-25
Residual 54 441940.172419833 8184.0772670339
Total 59 4061392.1722568
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -548.6481457102 58.7941616378 -9.3316773371 0 -666.5233426442 -430.7729487762 -666.5233426442 -430.7729487762
Size 255.9771554027 26.0985271242 9.8081073382 0 203.6527589191 308.3015518863 203.6527589191 308.3015518863
Advertexp 9.5709608184 1.0331515504 9.2638498333 0 7.4996166733 11.6423049634 7.4996166733 11.6423049634
Mgrperf 91.3665182189 12.3795841212 7.380419029 0.000000001 66.5469464179 116.1860900198 66.5469464179 116.1860900198
Neighbors 19.6507863624 1.741968598 11.2807925383 8.09143067121739E-16 16.1583495996 23.1432231253 16.1583495996 23.1432231253
Attract -3.2510704768 14.5079526262 -0.2240888539 0.8235338616 -32.337764211 25.8356232574 -32.337764211 25.8356232574
Profit=-548.65+255.98*(size)+9.57* advertising expenditure+91.37*(managerial performance) +19.65*(neighboring amenities)-3.25* (attractiveness)
From the two analysis it is clear that in the first analysis gives a more credible results for attractiveness since the effect in profit only is
caused by one variable in this case attractiveness
Attractiveness differs in part (a) and (b) since in (a) profit has only one effect from attractiveness while in (b) indicates
effects from other independent variables thus affecting the results for attractiveness.
Question 2
local advertising expenditure does not indicate diminishing returns since from the regression analysis in part (b)
there exists a positive coefficient of 9.5709608, this implies that it has a positive effect to the profit thus increase in advertising expenditure
will lead to an increase in profit.

Part B Q3

Question 3
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.4583427876
R Square 0.2100781109
Adjusted R Square 0.196458768
Standard Error 235.1882069909
Observations 60
ANOVA
df SS MS F Significance F
Regression 1 853209.595216024 853209.595216024 15.4249813825 0.0002307437
Residual 58 3208182.57704077 55313.4927075996
Total 59 4061392.1722568
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.2086313973 114.1176448904 0.0018282133 0.9985475713 -228.2226536293 228.6399164239 -228.2226536293 228.6399164239
Mgrperf 124.6263538272 31.7320087129 3.9274650072 0.0002307437 61.1078371794 188.1448704749 61.1078371794 188.1448704749
Profit=0.209+124.626*(managerial talent)
The regression model in (question 1(b)) shows that managerial performance had a positive coefficient of 91.37
and for the regression model in (question 3) the coefficient is 124.626, in both cases it is clear that managerial talent has
a positive impact to the profitability of the company. From the two models, the model with only managerial performance
is more powerful since it only has the effect of the managers’ performance.

Part B Q4

Mgrperf Profit
3 166.855
3 299.980
4 840.615
5 201.433
4 872.874
2 186.493
3 300.901
4 755.746
4 808.764
4 332.976
3 590.922
2 329.644
5 829.321
4 713.162
3 341.979
3 322.471 A linear regression model takes the form
3 608.782 Y = a0 + b1X1
3 134.128 Where x is the explanatory variable while y is the dependent variable
5 194.545 Nonlinear regression model takes the form
4 494.896
3 206.774 Y = f(X,β) + ε
4 565.189 Where:
3 279.658
1 89.235 X = a vector of p predictors,
4 372.522 β = a vector of k parameters,
4 89.877 f (-) = a known regression function,
2 91.116 ε = an error term.
5 201.996
5 673.770
4 326.183 The regression model is y = 124.63x + 0.2086
4 382.848 This is a linear model thus managerial talent (measured by performance ratings) has no nonlinear effect on profit.
4 730.514
3 457.112
3 823.807
3 425.945
3 530.593
3 449.527
3 297.237
2 198.809
4 420.942
3 581.784
4 229.914
4 683.982
5 801.843
2 203.803
3 101.432
3 254.989
3 432.992
4 496.599
4 204.977
1 -5.541
5 645.317
2 707.284
3 372.360
3 190.481
4 421.723
4 172.960
4 413.843
4 761.802
5 1322.114

managers performance

Profit

3 3 4 5 4 2 3 4 4 4 3 2 5 4 3 3 3 3 5 4 3 4 3 1 4 4 2 5 5 4 4 4 3 3 3 3 3 3 2 4 3 4 4 5 2 3 3 3 4 4 1 5 2 3 3 4 4 4 4 5 166.855073939953 299.98017454936985 840.61497407861521 201.43299999999999 872.87431745521485 186.49299999999999 300.90139121265383 755.74586390573529 808.76418298287433 332.976 590.92200000000003 329.64361227448796 829.32100000000003 713.16152467067582 341.97899999999998 322.4712761423574 608.78188732354897 134.12848241566215 194.54465671913931 494.89592610056513 206.77432114584835 565.18927812222853 279.65849927720262 89.234999999999999 372.52155376455278 89.877048363159417 91.116125973844902 201.99600000000001 673.77024831317374 326.18305832993008 382.84798663196619 730.5140683953091 457.11166469779101 823.80674644033661 425.94509781387637 530.593318072680 86 449.52658580860589 297.2374343852606 198.809 420.94224519572219 581.7837005888158 229.9139500592905 683.98176896063842 801.84299999999996 203.8031977205747 101.432 254.989 432.99191034764982 496.59891321257339 204.97698913912288 -5.5408403872500633 645.31728844882923 707.28394800151818 372.36026555435501 190.48063801335869 421.72340562593428 172.95985787701326 413.84262500506475 761.8017352268007 1322.1135019920625

managers performance

profit

https://www.statisticshowto.datasciencecentral.com/error-term/