regression

profilefinishlast
attachment_112.xlsx
Home>Mathematics homework help>Statistics homework help>regression

Data

Dependent Variable (y)
Quarter Supercharger Connectors WW
Q423 54892
Q323 51105
Q223 48082
Q123 45169
Q422 42419
Q322 38883
Q222 36165
Q122 33657
Q421 31498
Q321 29281
Q221 26900
Q121 24515
Q420 23277
Q320 19437
Q220 18100
Q120 17007
Q419 16104
Q319 14658
Q219 13881
Q119 12767

Double Moving Average-Forcast

QTR Actuals Moving Avg Dbl Moving Average Level Trend Forecast MSE MAD MAPE
Q319 14,658
Q419 16,104 15,381
Q120 17,007 16,556 15,968 17,143 1,175
Q220 18,100 17,554 17,055 18,053 998 18,317 47,198 1045.50 0.06
Q320 19,437 18,769 18,161 19,376 1,215 19,051 149,382 1276.00 0.07
Q420 23,277 21,357 20,063 22,651 2,589 20,591 7,214,596 3214.25 0.17
Q121 24,515 23,896 22,627 25,166 2,539 25,240 525,263 1888.50 0.08
Q221 26,900 25,708 24,802 26,613 1,812 27,705 647,220 2098.25 0.09
Q321 29,281 28,091 26,899 29,282 2,383 28,425 733,164 2382.00 0.09
Q421 31,498 30,390 29,240 31,539 2,299 31,665 27,889 2258.00 0.08
Q122 33,657 32,578 31,484 33,672 2,188 33,838 32,761 2173.50 0.07
Q222 36,165 34,911 33,744 36,078 2,334 35,860 93,330 2420.75 0.07
Q322 38,883 37,524 36,218 38,831 2,613 38,411 222,548 2665.50 0.07
Q422 42,419 40,651 39,088 42,215 3,127 41,444 951,600 3331.50 0.09
Q123 45,169 43,794 42,223 45,366 3,143 45,342 29,756 2946.50 0.07
Q223 48,082 46,626 45,210 48,041 2,832 48,509 181,902 2872.25 0.06
Q323 51,105 49,594 48,110 51,078 2,968 50,873 53,940 2995.50 0.06
Q423 54,892 52,999 51,296 54,701 3,405 54,046 716,562 3596.00 0.07
Q124 58,106 775,141 2,478 0.08 On average our forecast varies from actuals by .08 so almost 1%
Q224 61,511

Worldwide Supercharger Connectors

Actuals Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 Forecast Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 18317.25 19050.5 20591 25239.75 27704.5 28424.75 31665 33838 35859.5 38411.25 41443.5 45341.5 48508.5 50872.75 54045.5 58106 61511 Moving Avg Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 15381 16555.5 17553.5 18768.5 21357 23896 25707.5 28090.5 30389.5 32577.5 34911 37524 40651 43794 46625.5 49593.5 52998.5 Dbl Moving Average Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 15968.25 17054.5 18161 20062.75 22626.5 24801.75 26899 29240 31483.5 33744.25 36217.5 39087.5 42222.5 45209.75 48109.5 51296 Level Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 17142.75 18052.5 19376 22651.25 25165.5 26613.25 29282 31539 33671.5 36077.75 38830.5 42214.5 45365.5 48041.25 51077.5 54701 Trend Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 1174.5 998 1215 2588.5 2539 1811.5 2383 2299 2188 2333.5 2613 3127 3143 2831.5 2968 3405

Quarter

Total Supercharger Connectors

Regression-Forcast

QTR Actuals Time Period Time^2 Forecast MSE MAD MAPE
Q319 14,658.00 1 1 14298.99 128,887 359 2%
Q419 16,104.00 2 4 15688.55 172,596 415 3%
Q120 17,007.00 3 9 17202.09 38,061 195 1%
Q220 18,100.00 4 16 18839.61 547,016 740 4%
Q320 19,437.00 5 25 20601.09 1,355,116 1164 6%
Q420 23,277.00 6 36 22486.56 624,796 790 3%
Q121 24,515.00 7 49 24496.00 361 19 0%
Q221 26,900.00 8 64 26629.42 73,216 271 1%
Q321 29,281.00 9 81 28886.81 155,387 394 1%
Q421 31,498.00 10 100 31268.18 52,819 230 1%
Q122 33,657.00 11 121 33773.52 13,577 117 0%
Q222 36,165.00 12 144 36402.84 56,566 238 1%
Q322 38,883.00 13 169 39156.13 74,601 273 2%
Q422 42,419.00 14 196 42033.40 148,686 386 2%
Q123 45,169.00 15 225 45034.65 18,051 134 2%
Q223 48,082.00 16 256 48159.87 6,063 78 2%
Q323 51,105.00 17 289 51409.06 92,455 304 2%
Q423 54,892.00 18 324 54782.24 12,048 110 1%
Q124 19 361 58279.38 198,350 345 2%
Q224 20 400 61900.51
On average our forecast varies from actuals by 2%.

Worldwide Supercharger Connectors

Actuals Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 Forecast Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14298.991228070177 15688.553663570692 17202.091718266252 18839.605392156864 20601.094685242519 22486.559597523221 24496.000128998967 26629.416279669764 28886.808049535604 31268.175438596489 33773.518446852424 36402.837074303403 39156.131320949433 42033.401186790506 45034.646671826624 48159.867776057792 51409.064499484004 54782.236842105267 58279.384803921574 61900.508384932924

Quarter

Total Supercharger Connectors

Double Moving A-4P Forcast

QTR Actuals Moving Avg Dbl Moving Average Level Trend Forecast MSE MAD MAPE
Q319 14,658.00
Q419 16,104.00
Q120 17,007.00
Q220 18,100.00 16,467.25
Q320 19,437.00 17,662.00
Q420 23,277.00 19,455.25
Q121 24,515.00 21,332.25 18,729.19 23,935.31 1,735.38
Q221 26,900.00 23,532.25 20,495.44 26,569.06 2,024.54 25,671 1,511,209 6405 26%
Q321 29,281.00 25,993.25 22,578.25 29,408.25 2,276.67 28,594 472,513 6703 25%
Q421 31,498.00 28,048.50 24,726.56 31,370.44 2,214.63 31,685 34,938 6771 23%
Q122 33,657.00 30,334.00 26,977.00 33,691.00 2,238.00 33,585 5,175 6680 21%
Q222 36,165.00 32,650.25 29,256.50 36,044.00 2,262.50 35,929 55,696 6909 21%
Q322 38,883.00 35,050.75 31,520.88 38,580.63 2,353.25 38,307 332,352 7362 20%
Q422 42,419.00 37,781.00 33,954.00 41,608.00 2,551.33 40,934 2,205,596 8465 22%
Q123 45,169.00 40,659.00 36,535.25 44,782.75 2,749.17 44,159 1,019,427 8634 20%
Q223 48,082.00 43,638.25 39,282.25 47,994.25 2,904.00 47,532 302,592 8800 19%
Q323 51,105.00 46,693.75 42,193.00 51,194.50 3,000.50 50,898 42,746 8912 19%
Q423 54,892.00 49,812.00 45,200.75 54,423.25 3,074.17 54,195 485,809 9691 19%
Q124 57497.42 52,894.10 48,259.53 57,528.68 3,089.72 57,497 588,005 7757 21%
Q224 60,618

Worldwide Supercharger Connectors

Actuals Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 57497.416666666664 Forecast Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 25670.6875 28593.60416666 6668 31684.916666666668 33585.0625 35929 38306.5 40933.875 44159.333333333336 47531.916666666664 50898.25 54195 57497.416666666664 60618.401041666664 Moving Avg Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 16467.25 17662 19455.25 21332.25 23532.25 25993.25 28048.5 30334 32650.25 35050.75 37781 40659 43638.25 46693.75 49812 52894.104166666664 Dbl Moving Average Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 18729.1875 20495.4375 22578.25 24726.5625 26977 29256.5 31520.875 33954 36535.25 39282.25 42193 45200.75 48259.526041666664 Level Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 23935.3125 26569.0625 29408.25 31370.4375 33691 36044 38580.625 41608 44782.75 47994.25 51194.5 54423.25 57528.682291666664 Trend Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 1735.375 2024.5416666666667 2276.6666666666665 2214.625 2238 2262.5 2353.25 2551.3333333333335 2749.1666666666665 2904 3000.5 3074.1666666666665 3089.71875

Quarter

Total Supercharger Connectors

Tesla Q Sales-Regression

Dependent Variable (y) (X1) (previous quarter) (X2) (previous quarter)
Quarter Supercharger Connectors WW Tesla Vehicles Deliveries WW Production
Q119 12,767 90,700 86,555 Q418
Q219 13,881 64,382 77,138 Q119
Q319 14,658 101,498 87,048 Q219
Q419 16,104 97,186 96,155 Q319
Q120 17,007 112,095 104,891 Q419
Q220 18,100 88,496 102,672 Q120
Q320 19,437 90,891 82,272 Q220
Q420 23,277 139,593 145,036 Q320
Q121 24,515 180,667 179,757 Q420
Q221 26,900 184,877 180,338 Q121
Q321 29,281 201,304 206,421 Q221
Q421 31,498 241,391 237,823 Q321
Q122 33,657 308,650 305,840 Q421
Q222 36,165 310,048 305,407 Q122
Q322 38,883 254,695 258,580 Q222
Q422 42,419 343,830 363,923 Q322
Q123 45,169 405,278 439,701 Q422
Q223 48,082 422,875 400,808 Q123
Q323 51,105 435,059 479,700 Q223
Q423 54,892 435,059 430,488 Q323
Q124 55,786 484,000 495,000 Q423

Tesla Connectors & Production

Supercharger Connectors WW Q119 Q219 Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 12767 13881 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 Production

Q119 Q219 Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 77138 87048 96155 104891 102672 82272 145036 179757 180338 206421 237823 305840 305407 258580 363923 439701 400808 479700 430488 Tesla Vehicles Deliveries WW Q119 Q219 Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 64382 101498 97186 112095 88496 90891 139593 180667 184877 2013 04 241391 308650 310048 254695 343830 405278 422875 435059 435059 484000

Quarter

Total in thousands

Tesla Connectors & Deliveries

Supercharger Connectors WW Q119 Q219 Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 12767 13881 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 Tesla Vehicles Deliveries WW

Q119 Q219 Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 64382 101498 97186 112095 88496 90891 139593 180667 184877 201304 241391 308650 310048 254695 343830 405278 422875 435059 435059

Quarter

Total in thousands

Collinearity test

Production

90700 64382 101498 97186 112095 88496 90891 139593 180667 184877 201304 241391 308650 310048 254695 343830 405278 422875 435059 435059 86555 77138 87048 96155 104891 102672 82272 145036 179757 180338 206421 237823 305840 305407 258580 363923 439701 400808 479700 430488

Tesla Vehicle Deliveries

Production

Anova

ANOVA 1: Tesla Vehicle Deliveries/Tesla Charger Connectors
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9799037442
R Square 0.960211348
Adjusted R Square 0.9580008673
Standard Error 2741.9904157664
Observations 20
ANOVA
df SS MS F Significance F
Regression 1 3265968398.62722 3265968398.62722 434.3902944916 0
Residual 18 135333205.922782 7518511.44015458
Total 19 3401301604.55
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 7313.2992276048 1244.7071195935 5.8755181138 0.0000145571 4698.2666064059 9928.3318488038 4698.2666064059 9928.3318488038
Tesla Vehicles Deliveries WW (previous quarter) 0.1001494077 0.0048051653 20.8420319185 0 0.0900541301 0.1102446852 0.0900541301 0.1102446852
96% of the variance in the total number of chargers can be explained by the number of Tesla vehicle deliveries
Y=7313.29+0.100149408×(Tesla Vehicles Deliveries WW)
When there are no vehicle deliveries, the model predicts there would be 7,313 charger connectors.
For each additional Tesla Vehicle Delivery, the model predicts an increase of approximately .10014 Supercharger connectors the following quarter.
Tesla delivers 1,000 additional vehicles in a quarter, the model would predict that there will be approximately 100.14 more Supercharger connectors available in the following quarter
This means we can be 95% confident that the true value of the coefficient (the expected increase in the number of chargers for each additional 10 deliveries) lies between approximately .09005 and 0.1102
ANOVA 2: Tesla Vehicle Production/Tesla Charger Connectors
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9762250211
R Square 0.9530152919
Adjusted R Square 0.9504050303
Standard Error 2979.6454222094
Observations 20
ANOVA
df SS MS F Significance F
Regression 1 3241492441.39231 3241492441.39231 365.1033694951 0
Residual 18 159809163.157687 8878286.84209372
Total 19 3401301604.55
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 8041.8750581476 1323.3701262721 6.0768147161 0.0000096331 5261.577592466 10822.1725238293 5261.577592466 10822.1725238293
Production (previous quarter) 0.0956032014 0.0050033918 19.1076782864 0 0.0850914652 0.1061149375 0.0850914652 0.1061149375

Decision Tree-Table

Alternatives Projected U.S. capital expenditure needed to meet charger demand 2030 (billion) Predicted Projected Demand 2030 Average capital expenditure per Charging Station Current Demand Current Charger Utilization Current Utilization Annual Revenue 30% Utilization Increase 30%+ Utilization Annual Revenue Daily Capacity Avg Cost to consumer Daily Total Yearly Total
2030 2030 2023 2023 5455 0.30 1636.36 $ 597,273
Retail and destination $ 9,000,000,000 740000 $ 12,162.16 80000 0.15 $ 89,591 0.45 $ 268,772.73
Workplace $ 4,000,000,000 553000 $ 7,233.27 95000 0.10 $ 59,727 0.40 $ 238,909.09
Fleet depots $ 12,000,000,000 541000 $ 22,181.15 13000 0.30 $ 179,182 0.60 $ 358,363.64
On-the-go $ 29,000,000,000 459000 $ 63,180.83 79000 0.15 $ 89,591 0.45 $ 268,772.73 Decision Table
Prob. of utilization increasing by 30% Prob. of utilization remaining the same Decision
0% 100% Fleet Depots
40% 10% 90% Fleet Depots
Utilization Remains Constant 20% 80% Fleet Depots
$ 77,428.84 30% 70% Fleet Depots
Retail and Destination 89591 $ 77,428.84 40% 60% Fleet Depots
50% 50% Fleet Depots
$ (12,162.16) $ 184,907.88 60% 60% 40% Fleet Depots
Utilization increases by 30% 70% 30% Fleet Depots
$ 256,560.57 80% 20% Fleet Depots
268722.73 $ 256,560.57 90% 10% Fleet Depots
100% 0% Fleet Depots
40%
Utilization Remains Constant
$ 52,493.73
Workplace 59727 $ 52,493.73
$ (7,233.27) $ 160,002.98 60%
Utilization increases by 30%
$ 231,675.82
238909.09 $ 231,675.82
3
$ 264,509.84 40%
Utilization Remains Constant
$ 157,000.85
Fleet Depots 179182 $ 157,000.85
$ (22,181.15) $ 264,509.84 60%
Utilization increases by 30%
$ 336,182.49
358363.64 $ 336,182.49
40%
Utilization Remains Constant
$ 26,410.17
On-the-go 89591 $ 26,410.17
$ (63,180.83) $ 133,919.21 60%
Utilization increases by 30%
$ 205,591.90
268772.73 $ 205,591.90
Based on the highest returns listed, investing in Fleet Depots would be the best choice since it has the highest expected return at the 60% probability level with a 30% increase in utilization, resulting in $264,509 first year revenue.
Retail and Destination would be the second best place to invest in new chargers with a 10% expected utilization increase yielding $184,907 revenue within the first year.

Holt's

MSE 2331539.7 a 0.5753556691
Year Actuals Base Level Trend Forecast b. 1
Q319 14,658.00 14,658.00 0
Q419 16,104.00 14,658.00 0 14658.00
Q120 17,007.00 15,489.96 832 16321.93
Q220 18,100.00 16,716.09 1226 17942.21
Q320 19,437.00 18,033.00 1317 19349.90
Q420 23,277.00 19,400.02 1367 20767.03
Q121 24,515.00 22,211.16 2811 25022.30
Q221 26,900.00 24,730.42 2519 27249.69
Q321 29,281.00 27,048.49 2318 29366.56
Q421 31,498.00 29,317.33 2269 31586.18
Q122 33,657.00 31,535.44 2218 33753.55
Q222 36,165.00 33,698.00 2163 35860.56
Q322 38,883.00 36,035.72 2338 38373.44
Q422 42,419.00 38,666.62 2631 41297.52
Q123 45,169.00 41,942.77 3276 45218.92
Q223 48,082.00 45,190.20 3247 48437.63
Q323 51,105.00 48,233.01 3043 51275.83
Q423 54,892.00 51,177.54 2945 54122.07
Q124 54,565.05 3388 57952.57
Q224 57,952.57 3388 61340.08

Worldwide Supercharger Connectors

Actuals Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14658 16104 17007 18100 19437 23277 24515 26900 29281 31498 33657 36165 38883 42419 45169 48082 51105 54892 Forecast Q319 Q419 Q120 Q220 Q320 Q420 Q121 Q221 Q321 Q421 Q122 Q222 Q322 Q422 Q123 Q223 Q323 Q423 Q124 Q224 14658 16321.928594893619 17942.212325604924 19349.904405622936 20767.034340619775 25022.299274134697 27249.686209846448 29366.562772388952 31586.175225848048 33753.552473337855 35860.557811097598 38373.440240319302 41297.516323162359 45218.91829198513 48437.626725026021 51275.83222573081 54122.07099968371 57952.56509336381 61340.076171945664

Quarter

Total Supercharger Connectors