Question

Case Study Chapter 18 Forecasting

Use simple linear regression analysis with seasonality to forecast demand.

Rebar Sizes   

The standard Rebar sizes that the Company manufactures and sells are as follows: 10mm, 12mm, 16mm, 20mm, 25mm, 28mm, 32mm, and 36mm. For special orders, it can also make 40mm and 50mm bars.

Case 1 Introduction

Over the past years, the demand for 25mm rebars have fluctuated with a seasonal pattern being observed.   Since rebars are manufactured on a “made to order” basis, the VP for Production wanted an accurate forecast for the second half of this year 2019, to enable them to plan and schedule the ordering and the storage of the raw materials needed for the production. Demand for the 25 mm rebars for last year (2018) were as follows:

Demand for the year 2018

	x	y	Forecast	Seasonal Factor	Deseasonalized demand (yd)	X2	xy
1	January	3000
2	February	3500
3	March	4800
4	April	4300
5	May	2400
6	June	2700
7	July	3500
8	August	3500
9	September	1700
10	October	2100
11	November	3200
12	December	2700
Sum: 78		37,400

Based on the data above, using simple linear regression with seasonality, compute the forecasts for the months of July, August, September, October, November and December of 2019. (Show Your Work Step by Step and Do Not Solve On Excel.)

Answer 1

ANSWER:-

Looking at the time-series plot, we have taken 4 consecutive months as one seasonal cycle. The computation for the seasonality-adjusted regression forecast is as follows:

Result

Year	Month	Season	x	y	Seasonal averages	Seasonal indices	Deseasonalized y	Trendline	Seasonality-adjusted forecast
2018	Jan	1	1	3000	2366.7	0.759	3950.7	4042.0	3069.3
	Feb	2	2	3500	2766.7	0.888	3942.8	3873.8	3438.7
	Mar	3	3	4800	3833.3	1.230	3902.6	3705.5	4557.6
	Apr	4	4	4300	3500.0	1.123	3829.0	3537.3	3972.3
	May	1	5	2400		0.759	3160.6	3369.0	2558.3
	Jun	2	6	2700	3116.7	0.888	3041.6	3200.8	2841.3
	Jul	3	7	3500		1.230	2845.7	3032.5	3729.9
	Aug	4	8	3500		1.123	3116.7	2864.3	3216.6
	Sep	1	9	1700		0.759	2238.7	2696.1	2047.3
	Oct	2	10	2100		0.888	2365.7	2527.8	2243.9
	Nov	3	11	3200		1.230	2601.7	2359.6	2902.1
	Dec	4	12	2700		1.123	2404.3	2191.3	2460.8
2019	Jan	1	13			0.759		2023.1	1536.2
	Feb	2	14			0.888	Slope	1854.8	1646.5
	Mar	3	15			1.230	-168.2	1686.6	2074.4
	Apr	4	16			1.123	Intercept	1518.4	1705.1
	May	1	17			0.759	4210.3	1350.1	1025.2
	Jun	2	18			0.888		1181.9	1049.1
	Jul	3	19			1.230		1013.6	1246.7
	Aug	4	20			1.123		845.4	949.4
	Sep	1	21			0.759		677.1	514.2
	Oct	2	22			0.888		508.9	451.7
	Nov	3	23			1.230		340.6	419.0
	Dec	4	24			1.123