In: Operations Management
The sales volume for the last 7 years is given below by quarter in the following table:
Year |
Sales by Quarter |
|||
Q1 |
Q2 |
Q3 |
Q4 |
|
2013 |
289 |
410 |
301 |
213 |
2014 |
212 |
371 |
374 |
333 |
2015 |
293 |
441 |
411 |
363 |
2016 |
324 |
462 |
379 |
301 |
2017 |
347 |
520 |
540 |
521 |
2018 |
381 |
594 |
573 |
504 |
2019 |
444 |
592 |
571 |
507 |
Determine the values for the typical seasonal indexes and use them to obtain an estimate of demand for each quarter in 2020? Show all of your work.
Total average quarterly demand = 327.143 + 484.286 + 449.857 + 391.714 = 1653 |
--------------------------------------------------------------------------
After computing the seasonal index, we have to estimate the trend equation using linear regression analysis.
The linear trend equation is given as
y = a + b x
where y = demand to be forecasted
a = intercept
b = slope
x = independent variable
The trend equation is
y = 247.29 + 41.49 x
Using the above equation, the average demand for all seasons in year 2020 is calculated
y = 247.29 + 41.49 8 = 579.21
The above forecast is multiplied by the corresponsing quarterly seasonal indexes to obtain the forecasted estimates for each quarter of 2020.
Forecast for quarter 1 of 2020 = 579.21 0.7916 = 458.5
Forecast for quarter 2 of 2020 = 579.21 1.1719 = 678.8
Forecast for quarter 3 of 2020 = 579.21 1.0886 = 630.5
Forecast for quarter 4 of 2020 = 579.21 0.9479 = 549
The equation can be obtained using regression analysis in excel with average demand of all seasons as dependent variable. In excel go to data, data analysis and choose regression and select the variables as shown.