Question

The number of internal disk drives? (in millions) made at a plant in Taiwan during the past 5 years? follows: ??????????????????????????????????????????????????????????????????? Year Disk Drives

1      138

2       160

3         190

4       202

5 216

?a) Using simple linear regression?, the forecast for the number of disk drives to be made next year? = 240.6 disk drives

?b) The mean squared error? (MSE) when using simple linear regression? ____________drives²

c) The mean absolute percent error (MAPE) when using simple linear refression=__________%

I was able to wrk through A), but how to I calculate b & c?

Answer 1

Let the linear regression equation isY = a + b.X

Y ( Dependent variable ) = Forecasted number of disk drives

X = Year number

A, b = Constants

We place all the values of Year and disk drives as provided in 2 separate columns in excel and apply the formula LINEST ( ) to obtain values of a and b. Accordingly values of a and b are as per following :

A = 121.8

B = 19.8

Therefore Linear regression equation is : Y = 121.8 + 19.8.X

Y = 121.8 + 19.8.X

To determine forecast for number of disk drives to be made next year, we have to put value of X = 6

Therefore, forecast for number of disk drives to be made next year = 121.8 + 19.8 x 6 = 121.8 + 118.8 = 240.6

Following also may be noted :

Square error =( Forecast – actual )^2

Therefore , Mean Square Error ( MSE ) = Sum of values of Square error / 5 i.e. number of observations

Absolute Percentage error (APE ) = (Absolute difference between values of Forecast vs actual )/Actualx 100

Therefore , Mean absolute Percent Error ( MAPE )

= Sum of values of Absolute Percentage error ( APE ) / 5 I,e, number of observations

Accordingly , please refer below table which calculate values of MSE and MAPE :

Year	Disk drive - actual	Disk drive - Forecast	Square error (SE)	Absolute Percentage error ( APE)
1	138	141.60	12.96	2.61
2	160	161.40	1.96	0.88
3	190	181.20	77.44	4.63
4	202	201.00	1.00	0.50
5	216	220.80	23.04	2.22
		SUM =	116.40	10.83

Mean Square error = Sum of Square error / 5 = 116.40/5 =23.28

Mean absolute Percent error = Sum of absolute Percent error / 5 = 10.83/ 5 = 2.17% ( rounded to 2 decimal places )