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	11		I	14
II	18		II	23
III	25		III	27
IV	15		IV	21

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

  Y =  +  t

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

	Period	Forecast             (Units)
Next year	I
	II
	III
	IV

Answer 1

a.

Y=a+bX where

b =

a =

Where n = 8 is the sample size, x is the quarter and y is the quantity

On solving,

a = 13.464

b = 1.286

The regression equation is Y = 13.464 + (1,286)X

b.

The trend forecast is calculated by substituting the values of X in the above equation. The answer is as follows

Seasonal factors is calculated as

Total of quarter/annual average

For example, 1st quarter average of previous and current year =(11+14) = 25, Similarly, Q2= 41, Q3 = 52 and Q4 = 36. Their average = (Q1+Q2+Q3+Q4)/4=38.5

Seasonal index for Q1 = 25/38.5 = 0.649, similarly for Q2 = 1.065, Q3 = 1.351 and Q4 = 0.935  

The seasonal factors for next quarters are same as previous

c. The forecast is calulated by multiplying seasonal index by trend forecast. The answer is given below