Case Study: Forecasting the Sales of Home Furnishing Products Go to http//www ce

Question

Case Study: Forecasting the Sales of Home Furnishing Products Go to http//www census aov/retail/index html Under monthly retail report, download the retail and food services sales (1992-present). It is an Excel file We will focus on Home Furnishing stores (NAICS Code=4422, Row 21). Suppose that now is the end of the 2014 You want to forecast the monthly sales in 2015 based on the data from 2012.2013. and 2014 Plot the time series data (2012-2014) using Excel what do you observe? Use trend and seasonal factors to forecast the monthly demand for 2015. Compute seasonal factors Compute Depersonalized Demand Use regression to computer the trend equation Generate the monthly forecast for 2015 Plot the forecasted demand and the actual demand (from 2012 to 2015) on the same graph Do your forecasting results successfully capture trend and seasonality?

Explanation / Answer

Time Code Year Month Sales 12 Month Moving Average Centerd Moving Average Irregular Component Seasonal Index Deseasonalize Trend Component Forecast Time Code Year Month Sales MV12 CM Yt/CM12 Avg(Mi) , Mi is the irregular component of ith Month Yt/St Tt=Intercept Coefficient +Slope coefficient*t Forecast t Y M Yt MV12 CM6 Yt/CM12 St Deseasonalize Tt St*Tt 1 2012 1 2,970 0.98 3044.136 3224.146462 3145.627 2 2012 2 2,845 0.99 2886.825 3250.408247 3203.315 3 2012 3 3,344 0.99 3375.585 3276.670032 3246.011 4 2012 4 3,075 0.99 3100.099 3302.931818 3276.191 5 2012 5 3,491 1.00 3497.916 3329.193603 3322.611 6 2012 6 3,302 1.00 3313.762 3355.455389 3343.545 7 2012 7 3,405 3,483 3497.416667 0.973575734 1.00 3411.721 3381.717174 3375.055 8 2012 8 3,693 3,512 3523 1.048254329 1.00 3679.510 3407.97896 3420.474 9 2012 9 3,311 3,534 3535.875 0.936401881 1.00 3320.634 3434.240745 3424.277 10 2012 10 3,467 3,538 3548.625 0.976998133 1.00 3456.949 3460.50253 3470.564 11 2012 11 4,022 3,559 3564.416667 1.128375377 1.02 3961.338 3486.764316 3540.158 12 2012 12 4,865 3,570 3576.541667 1.360252572 1.03 4720.962 3513.026101 3620.209 13 2013 1 3,328 3,583 3596.708333 0.925290486 0.98 3411.072 3539.287887 3453.093 14 2013 2 3,101 3,610 3617.708333 0.857172473 0.99 3146.589 3565.549672 3513.891 15 2013 3 3,397 3,625 3636.416667 0.934161377 0.99 3429.085 3591.811457 3558.204 16 2013 4 3,328 3,647 3660.291667 0.909217162 0.99 3355.164 3618.073243 3588.781 17 2013 5 3,617 3,673 3683.541667 0.981935411 1.00 3624.166 3644.335028 3637.13 18 2013 6 3,467 3,694 3699.458333 0.937164224 1.00 3479.350 3670.596814 3657.568 19 2013 7 3,724 3,705 3703.291667 1.005591872 1.00 3731.350 3696.858599 3689.576 20 2013 8 3,878 3,702 3704.458333 1.046846705 1.00 3863.834 3723.120385 3736.77 21 2013 9 3,575 3,707 3712.833333 0.962876509 1.00 3585.402 3749.38217 3738.504 22 2013 10 3,776 3,718 3731.541667 1.0119142 1.00 3765.053 3775.643955 3786.621 23 2013 11 4,271 3,745 3752.166667 1.138275663 1.02 4206.583 3801.905741 3860.126 24 2013 12 4,998 3,760 3769.416667 1.325934605 1.03 4850.025 3828.167526 3944.966 25 2014 1 3,287 3,779 3789 0.867511217 0.98 3369.049 3854.429312 3760.56 26 2014 2 3,170 3,799 3805.291667 0.833050467 0.99 3216.603 3880.691097 3824.466 27 2014 3 3,529 3,812 3819.75 0.923882453 0.99 3562.332 3906.952883 3870.396 28 2014 4 3,645 3,828 3834.625 0.950549271 0.99 3674.751 3933.214668 3901.371 29 2014 5 3,795 3,842 3838.125 0.988764045 1.00 3802.518 3959.476453 3951.648 30 2014 6 3,703 3,835 3851.291667 0.961495602 1.00 3716.191 3985.738239 3971.591 31 2014 7 3,958 3,868 1.00 3965.812 4012.000024 4004.097 32 2014 8 4,035 1.00 4020.260 4038.26181 4053.067 33 2014 9 3,765 1.00 3775.955 4064.523595 4052.732 34 2014 10 3,943 1.00 3931.569 4090.785381 4102.679 35 2014 11 4,188 1.02 4124.835 4117.047166 4180.093 36 2014 12 5,397 1.03 5237.211 4143.308951 4269.722 37 2015 1 0.98 4169.570737 4068.026 38 2015 2 0.99 4195.832522 4135.042 39 2015 3 0.99 4222.094308 4182.589 40 2015 4 0.99 4248.356093 4213.961 41 2015 5 1.00 4274.617878 4266.166 42 2015 6 1.00 4300.879664 4285.614 43 2015 7 1.00 4327.141449 4318.617 44 2015 8 1.00 4353.403235 4369.364 45 2015 9 1.00 4379.66502 4366.959 46 2015 10 1.00 4405.926806 4418.737 47 2015 11 1.02 4432.188591 4500.06 48 2015 12 1.03 4458.450376 4594.479 Seasonal Index Forecasted Values Month Seasonal Index Year Month Sales Forecast ($) M1 0.98 2015 1 4068.026341 M2 0.99 2015 2 4135.041973 M3 0.99 2015 3 4182.589102 M4 0.99 2015 4 4213.96067 M5 1.00 2015 5 4266.166184 M6 1.00 2015 6 4285.613802 M7 1.00 2015 7 4318.617324 M8 1.00 2015 8 4369.364151 M9 1.00 2015 9 4366.958662 M10 1.00 2015 10 4418.736699 M11 1.02 2015 11 4500.06041 M12 1.03 2015 12 4594.478815 Regression Output: The intercept and the Slope coefficient is taken from the below output to calculate the trend component Tt in the above table: This is done in Excel. SUMMARY OUTPUT Regression Statistics Multiple R 0.559977056 R Square 0.313574303 Adjusted R Square 0.293385312 Standard Error 415.3429424 Observations 36 ANOVA df SS MS F Significance F Regression 1 2679412 2679412 15.53195 0.000383297 Residual 34 5865332 172509.8 Total 35 8544744 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 3197.884676 141.3833 22.61855 4.51E-22 2910.559323 3485.210029 2910.559323 3485.210029 t 26.26178542 6.663637 3.941059 0.000383 12.71964594 39.8039249 12.71964594 39.8039249 Steps to get this output in excel: 1. Click on Data menu 2. Go to data analysis tab 3. Select for option "Regression" 4. Select the column stating "Deseasonalized" in the first table as your Y-values 5. Select the column stating "t" in the first table as your x-values 6.Check the box "Lable" 7,Click Ok Note: a) Seasonal Factors Refer to Row 54 b)Deseasonalized Demand Refer to Column named under Deseasonalized from calculation table above c) Trend Eqution Refer to the result of regression output from Row 69 Equation : 3197.884676+t*26.2617854 d)Monthly forecast for 2015 Refer to Row 54

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.