Question

Problem 3-26 The following table shows the past two years of quarterly sales information Assume that there are both trend and seasonal factors and that the seasonal cycle is one year QUARTER 1 2 3 4 5 SALES 162 199 158 140 216 239 205 198 8 Use time series decom on to forecast quarterly sales for the next year (Do not round Intermediate calculations. Round your answers to the nearest whole number Quarter 9 10 11 12

Answer 1

Using linear regression in excel,we calculate the trend forecast for quarters 9-12 as,

Using excel for performing regression with sales as the dependent variable and quarter as independent variables.

In excel ,go to data tab-->data analysis and select regression

In the regression dialog select the sales values as Y range and quarter values as X range and click OK.

We get the regression output with coefficients of quarter and intercept .

The trend equation is Sales =Intercept+Coefficient of quarter*quarter

Trend forecast =157.428+7.154*quarter

Trend forecast for quarter 9=157.428+7.154*quarter=157.428+7.154*9=221.821

Similarly for other quarters as well.

B11 f =$E$6+$E$7*A11 А B с D E F G H J K L 1 2 Quarter Sales ? X 3 1 162 Regression Input Input Y Range: 4 2 199 OK $B$2:$B$10 SC ENT IFY 5 3 158 Cancel 6 4 140 Coefficients Intercept 157.4285714 Quarter 7.154761905 Input X Range: $A$2:$A$10 7 5 216 Help 6 239 8 9 Labels Confidence Level: Constant is Zero 95 % 7 205 10 8 198 11 9| 221.8214 12 10 228.9762 13 11 236.131 Output options Output Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals 14 121 243.2857 15 Residual Plots Line Fit Plots 16 17 Normal Probability Normal Probability Plots 18 19 20 21 22 23

Next,we calculate the seasonal factors as shown below :

Average of Quarter 1=AVERAGE(B2:C2)=189

Similarly we calculate the average of year 1 and year 2 for other quarters.

Overall average=AVERAGE(D2:D5)=189.6

Seasonal factor of quarter 1=Average of quarter 1/Overall average=D2/$D$6=0.997

Year 3 forecast(quarter 9)=E2*F2=221

Year 3 forecast(quarter 10)=E3*F3=264

Year 3 forecast(quarter 11)=E4*F4=226

Year 3 forecast(quarter 12)=E5*F5=217

Calculations are as shown below:

E2 foc =D2/$D$6 A B с D E F G 1 Quarter Year 1 Year 2 Average Seasonal index Trend forecast Year 3 forecast 2 1 162 216 189.0 0.997 221.821 221 3 2 199 239 219.0 1.155 228.976 264 4 3 158 205 181.5 0.957 236.131) 226 5 4 140 198 169.0 0.891 243.286 217 6 Overall average 189.6