Question

690 The manager of the Petroco Service Station wants to forecast the demand for unleaded gasoline next month so that the proper number of gallons can be ordered from the distributor. The owner has accumulated the following data on demand for unleaded gasoline from sales during the past 12 months: Month Gasoline Demanded (gal.) October 800 November 725 December 630 January 500 February 645 March 730 May 810 June 1,200 980 August 1,000 September 850 e. Compute linear trend line demand forecast for the months of October to September. Calculate MAD, MAPD and MSE. Compare the five forecasting methods by using MAD, MAPD and MSE and indicate which method is the best fit for the data based on MAD, MAPD (or MAPE), and MSE, respectively. April July

Answer 1

data

Using Excel

data -> data analysis -> regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.6284
R Square	0.3949
Adjusted R Square	0.3344
Standard Error	155.6614
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	158112.0629	158112.0629	6.5253	0.0286
Residual	10	242304.6037	24230.4604
Total	11	400416.6667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	580.5303	95.8029	6.0596	0.0001	367.0680	793.9926
t	33.2517	13.0171	2.5545	0.0286	4.2479	62.2556

t	y	forecast	abs(error)	error^2	error/Ft
1	800	613.782	186.218	34677.14	0.232773
2	725	647.0337	77.9663	6078.744	0.10754
3	630	680.2854	50.2854	2528.621	0.079818
4	500	713.5371	213.5371	45598.09	0.427074
5	645	746.7888	101.7888	10360.96	0.157812
6	690	780.0405	90.0405	8107.292	0.130493
7	730	813.2922	83.2922	6937.591	0.114099
8	810	846.5439	36.5439	1335.457	0.045116
9	1200	879.7956	320.2044	102530.9	0.266837
10	980	913.0473	66.9527	4482.664	0.068319
11	1000	946.299	53.701	2883.797	0.053701
12	850	979.5507	129.5507	16783.38	0.152413
			MAD	MSE	MAPD
			117.5068	20192.05	0.153

Formulas

t	y	forecast	abs(error)	error^2	error/Ft
1	800	=580.5303+33.2517*A2	=ABS(B2-C2)	=D2*D2	=D2/B2
=1+A2	725	=580.5303+33.2517*A3	=ABS(B3-C3)	=D3*D3	=D3/B3
=1+A3	630	=580.5303+33.2517*A4	=ABS(B4-C4)	=D4*D4	=D4/B4
=1+A4	500	=580.5303+33.2517*A5	=ABS(B5-C5)	=D5*D5	=D5/B5
=1+A5	645	=580.5303+33.2517*A6	=ABS(B6-C6)	=D6*D6	=D6/B6
=1+A6	690	=580.5303+33.2517*A7	=ABS(B7-C7)	=D7*D7	=D7/B7
=1+A7	730	=580.5303+33.2517*A8	=ABS(B8-C8)	=D8*D8	=D8/B8
=1+A8	810	=580.5303+33.2517*A9	=ABS(B9-C9)	=D9*D9	=D9/B9
=1+A9	1200	=580.5303+33.2517*A10	=ABS(B10-C10)	=D10*D10	=D10/B10
=1+A10	980	=580.5303+33.2517*A11	=ABS(B11-C11)	=D11*D11	=D11/B11
=1+A11	1000	=580.5303+33.2517*A12	=ABS(B12-C12)	=D12*D12	=D12/B12
=1+A12	850	=580.5303+33.2517*A13	=ABS(B13-C13)	=D13*D13	=D13/B13
			MAD	MSE	MAPD
			=AVERAGE(D2:D13)	=AVERAGE(E2:E13)	=AVERAGE(F2:F13)

since I don't which are other 4 models, you have to compare yourself

Lower is the value of MSE,MAPE, MAD , better is the model