Question

Ch 04 HW 3 Forecasting problem Method: Linear Trend optimized with Solver - pick your own initial values for the y-axis intercept (a) and slope parameter (b) before optimizing the model The data for this problem is shown in the table below. Note: changes from the original problem data are highlighted in yellow. DO NOT USE the "brute force" method shown in Example 8 on pages 120-121. DO NOT USE the linear regression function from Excel's ToolPak or the LINEST Excel function. Set up a forecasting table and use Excel Solver to optimize it. Time (t) Actual A(t) 1 950 2 930 3 940 4 955 5 972 6 926 7 834 8 840 9 828 10 813 11 don't have yet What is the value for MAD using this method?

Answer 1

MAD = 26.12727

Period	Demand (y)	Period(x)		Forecast	Error	Absolute	Squared	Abs Pct Err
Period 1	950	1		976.6909	-26.6909	26.69091	712.4046	02.81%
Period 2	930	2		959.3818	-29.3818	29.38182	863.2912	03.16%
Period 3	940	3		942.0727	-2.07273	2.072727	4.296198	00.22%
Period 4	955	4		924.7636	30.23636	30.23636	914.2377	03.17%
Period 5	972	5		907.4545	64.54545	64.54545	4166.116	06.64%
Period 6	926	6		890.1455	35.85455	35.85455	1285.548	03.87%
Period 7	834	7		872.8364	-38.8364	38.83636	1508.263	04.66%
Period 8	840	8		855.5273	-15.5273	15.52727	241.0962	01.85%
Period 9	828	9		838.2182	-10.2182	10.21818	104.4112	01.23%
Period 10	813	10		820.9091	-7.90909	7.909091	62.55372	00.97%
				Total	-1.1E-13	261.2727	9862.218	28.58%
Intercept	994			Average	-1.1E-14	26.12727	986.2218	02.86%
Slope	-17.309091				Bias	MAD	MSE	MAPE
						SE	35.11093
						Correlation	-0.84546
						Coefficient of determination	0.714797