Question

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

QUARTER	QUANTITY (UNITS)		QUARTER	QUANTITY (UNITS)
LAST YEAR			THIS YEAR
I	15		I	15
II	21		II	27
III	29		III	31
IV	19		IV	15

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.)

Period	Trend Forecast	Seasonal Factors
Last Year	I
	II
	III
	IV
This Year	I
	II
	III
	IV

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

Next year

period:

1 Forecast:

2 Forecast:

3 Forecast:

4 Forecast:

Answer 1

a.

Last year	Quarter		Period(x)	Quantity(y)	xy	x2
	I		1	15	15	1
	II		2	21	42	4
	III		3	29	87	9
	IV		4	19	76	16
This year	I		5	15	75	25
	II		6	27	162	36
	III		7	31	217	49
	IV		8	15	120	64
		Total	36	172	794	204

x-bar = 36/8 = 4.5
y-bar = 172/8 = 21.5

b = (sum(xy)-n*x-bar*y-bar)/(sum(x2)-n*x-bar^2) = (794-8*4.5*21.5)/(204-8*4.5^2) = 0.476190476

a = y-bar - b*x-bar = 21.5-0.476190476*4.5 = 19.35714286

So, regression equation Y = a+bx = 19.357+0.476x

b.

Last year	Quarter		Period(x)	Quantity(y)	xy	x2	Trend forecast	Seasonal factor
	I		1	15	15	1	19.833	0.723
	II		2	21	42	4	20.31	1.125
	III		3	29	87	9	20.786	1.381
	IV		4	19	76	16	21.262	0.771
This year	I		5	15	75	25	21.738
	II		6	27	162	36	22.214
	III		7	31	217	49	22.69
	IV		8	15	120	64	23.167
		Total	36	172	794	204

c.

Next year	Quarter	Period	Forecast
	I	9	17.098
	II	10	27.127
	III	11	33.959
	IV	12	19.319

Calculation

Formula (part 1)

Part 2