Question

Considering the following time series data: (Tableau)

1. Determine the least squares trend equation. Use a linear equation and any other non- linear equation. Provide R-squared for both cases.

2. Estimate the price of gold (ounce) for 2020. Does this seem like a reasonable estimate based on historical data?

3. What is the quality of the forecast? Also, Provide Mean Absolute Error (MAE), and the Mean Absolute Percentage Error (MAPE).

Year	Price of Gold (ounce)
2005	$513.00
2006	$635.70
2007	$836.50
2008	$869.75
2009	$1,087.50
2010	$1,420.25
2011	$1,531.00
2012	$1,664.00
2013	$1,204.50
2014	$1,199.25
2015	$1,060.00

Please provide step by step tabulea solution and output.

Answer 1

x	y	(x-x̅)²	(y-ȳ)²	(x-x̅)(y-ȳ)
2005	513	25.00	336236.57	2899.30
2006	635.7	16.00	208994.43	1828.64
2007	836.5	9.00	65719.98	769.08
2008	869.75	4.00	49777.67	446.22
2009	1087.5	1.00	28.72	5.36
2010	1420.25	0.00	107184.81	0.00
2011	1531	1.00	191967.46	438.14
2012	1664	4.00	326201.94	1142.28
2013	1204.5	9.00	12463.69	334.92
2014	1199.25	16.00	11319.03	425.56
2015	1060	25.00	1079.72	-164.30

	ΣX	ΣY	Σ(x-x̅)²	Σ(y-ȳ)²	Σ(x-x̅)(y-ȳ)
total sum	22110	12021.45	110	1310974.0	8125.20
mean	2010.00	1092.86	SSxx	SSyy	SSxy

sample size ,   n =   11          
here, x̅ = Σx / n=   2010.00   ,     ȳ = Σy/n =   1092.86  
                  
SSxx =    Σ(x-x̅)² =    110.0000          
SSxy=   Σ(x-x̅)(y-ȳ) =   8125.2          
                  
estimated slope , ß1 = SSxy/SSxx =   8125.2   /   110.000   =   73.8655
                  
intercept,   ß0 = y̅-ß1* x̄ =   -147376.7045          
                  
so, regression line is   Ŷ =   -147376.7045   +   73.8655   *x
................

R² =    (Sxy)²/(Sx.Sy) =    0.4578
..................

Predicted Y at X=   2020   is                  
Ŷ =   -147376.7045   +   73.865455   *   2020   =   1831.514
.................

demand	forcast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
513	723.5318	-210.53	210.53	44323.65	41.04%
635.7	797.397	-161.70	161.70	26146.01	25.44%
836.5	871.263	-34.76	34.76	1208.45	4.16%
869.75	945.128	-75.38	75.38	5681.87	8.67%
1087.5	1018.994	68.51	68.51	4693.12	6.30%
1420.25	1092.859	327.39	327.39	107184.81	23.05%
1531	1166.725	364.28	364.28	132696.61	23.79%
1664	1240.590	423.41	423.41	179276.03	25.45%
1204.5	1314.455	-109.96	109.96	12090.20	9.13%
1199.25	1388.321	-189.07	189.07	35747.81	15.77%
1060	1462.186	-402.19	402.19	161753.87	37.94%

	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
	et=Dt-Ft	| et |	(et)²	| et/Dt |
total sum=	0.00	2367.17	710802.418	220.72%
n=	11	11	11	11
average=	0.00	215.20	64618.40	20.07%

MAD/MAE=   Σ |et|/n =    215.20
      
  
MAPE=   Σ | et/Dt |/n =    20.07%

....................

THANKS

revert back for doubt

please upvote