In: Statistics and Probability
What is the forecast and MSE using regression? 2019 is the holdout sample and "car sales" is the independent variable.
Shipments | Car Sales | Fasteners |
Jan-17 | 17680000 | 335798 |
Feb-17 | 17650000 | 297853 |
Mar-17 | 17130000 | 318399 |
Apr-17 | 17230000 | 311730 |
May-17 | 17200000 | 363876 |
Jun-17 | 17200000 | 296832 |
Jul-17 | 17180000 | 297513 |
Aug-17 | 17020000 | 321144 |
Sep-17 | 18380000 | 317677 |
Oct-17 | 18200000 | 325487 |
Nov-17 | 17860000 | 272937 |
Dec-17 | 17700000 | 276282 |
Jan-18 | 17550000 | 335439 |
Feb-18 | 17560000 | 310514 |
Mar-18 | 17690000 | 407754 |
Apr-18 | 17770000 | 356169 |
May-18 | 17780000 | 345322 |
Jun-18 | 17700000 | 331997 |
Jul-18 | 17380000 | 343059 |
Aug-18 | 17360000 | 350277 |
Sep-18 | 17840000 | 265205 |
Oct-18 | 18000000 | 389332 |
Nov-18 | 17880000 | 310474 |
Dec-18 | 17890000 | 308429 |
Jan-19 | 17240000 | 385807 |
Feb-19 | 17030000 | 332529 |
Mar-19 | 17770000 | 407606 |
Apr-19 | 17050000 | 361946 |
May-19 | 17930000 | 453432 |
Jun-19 | 17710000 | 412892 |
Jul-19 | 17440000 | 447359 |
Aug-19 | 17510000 | 363769 |
Sep-19 | 17720000 | 361232 |
Oct-19 | 17050000 | 451421 |
Nov-19 | 17450000 | 363724 |
Dec-19 | 17160000 | 331619 |
Holdout data is a data which doesnot include in actual data to find regression
Fasteners = Dependent
Car Sales = Independent
Excel > Data > Data Analysis > Regression
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.010917236 | |||||||
R Square | 0.000119186 | |||||||
Adjusted R Square | -0.045329942 | |||||||
Standard Error | 35060.75612 | |||||||
Observations | 24 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 1 | 3223609.046 | 3223609.046 | 0.002622405 | 0.959620764 | |||
Residual | 22 | 27043645639 | 1229256620 | |||||
Total | 23 | 27046869248 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 305683.6791 | 368727.7498 | 0.829022712 | 0.415996031 | -459010.8707 | 1070378.229 | -459010.8707 | 1070378.229 |
Car Sales(X) | 0.001071567 | 0.020925192 | 0.051209426 | 0.959620764 | -0.042324625 | 0.044467759 | -0.042324625 | 0.044467759 |
Fasteners(Y) = 305683.6791 + 0.0011 * Car Sales(X)
MSE = 1229256620