Question

Tucson Machinery, Inc., manufactures numerically controlled machines, which sell for an average price of $0.5 million each. Sales for these NCMs for the past two years were as follows: Use Exhibit 18.10.

LAST YEAR			THIS YEAR
QUARTER	QUANTITY (UNITS)		QUARTER	QUANTITY (UNITS)
I	13		I	17
II	15		II	21
III	23		III	25
IV	13		IV	19

a. Find the equation of a simple linear regression line using Excel. (Round your answers to 3 decimal places.)

b. Compute trend and seasonal factor from a linear regression line obtained with Excel. (Do not round intermediate calculations. Round your answers to 3 decimal places.)

c. Forecast sales for next year using the above seasonal factors. (Do not round intermediate calculations. Round your answers to 2 decimal places.)

Answer 1

a.

	Quarter	Period(x)	Quantity (y)	xy	x2
Last year	I	1	13	13	1
	II	2	15	30	4
	III	3	23	69	9
	IV	4	13	52	16
This year	I	5	17	85	25
	II	6	21	126	36
	III	7	25	175	49
	IV	8	19	152	64
Total		36	146	702	204

x-bar = Sum(x)/n = 36/8 = 4.5
y-bar = Sum(y)/n = 146/8 = 18.25

b = (Sum(xy) – n*x-bar*y-bar)/(Sum(x2) – n*x-bar*x-bar) = (702-8*4.5*18.25)/(204–8*4.5*4.5)
   = 45/42 = 1.071428571 = 1.071 (Rounded to 3 decimal places)

a = y-bar –b*x-bar
= 18.25-1.071428571*4.5= 13.42857143 = 13.423 (Rounded to 2 decimal place)

Regression equation is y = a + bx,
we get following regression equation by substituting values of a and b
Regression equation is y =13.423 + 1.071x

b)

	Quarter	Quantity (y)	Trend forecast	Seasonal factor
Last year	I	13	14.5	0.901
	II	15	15.571	1.01
	III	23	16.643	1.288
	IV	13	17.714	0.799
This year	I	17	18.786
	II	21	19.857
	III	25	20.929
	IV	19	22

c.

Quarter	Period	Trend forecast	Seaosnality adjusted forecast
I	9	23.07142857	20.79
II	10	24.14285714	24.38
III	11	25.21428571	32.48
IV	12	26.28571428	21

---------------------------------------------------------------------------------------------------------------------------------------------------------

Calculation

Trend forecast (Rounded) is the answer for trend forecast and corresponding unrounded values have been used for intermediate calculations

Formula