Question

This is a tableau question.

Year	Sales
2005	49387
2006	53412
2007	56783
2008	58436
2009	59994
2010	61515
2011	63182
2012	67989
2013	70448
2014	72601
2015	75482
2016	78341
2017	81111
2018	82517
2019	83275
2020	84005

I. (a) Determine the trend line using both linear and two nonlinear equations Hint: You can choose any two of the nonlinear options in edit trend lines within Tableau. (b) Write down the equations (coefficients). Hint: Double click on trend line and click on describe the model.

II. Which trend line would you suggest? Why?

III. Estimate the sales for 2022. Does this seem like a reasonable estimate based on historical data? (Hint: Show Me — first icon on the left hand side)

IV. Check the quality of the forecast prepared by Tableau. Also, Provide Mean Absolute Error (MAE), and the Mean Absolute Percentage Error (MAPE). Hint: one click on forecast area with the right button of your mouse, then describe forecast and check first Summary and later Models.

V. Prepare a dashboard with 4 sheets: Sheet 1 for the trend line using linear function, Sheet 2 for the trend line using one of the nonlinear function of your choice, Sheet 3 for the trend line using another nonlinear function of your preference and Sheet 4 for Forecasting.

Answer 1

1)

Linear trend

Trend Lines Model

A linear trend model is computed for sum of Sales given Year. The model may be significant at p <= 0.05.

Model formula:	( Year + intercept )
Number of modeled observations:	16
Number of filtered observations:	0
Model degrees of freedom:	2
Residual degrees of freedom (DF):	14
SSE (sum squared error):	2.29183e+07
MSE (mean squared error):	1.63702e+06
R-Squared:	0.988314
Standard error:	1279.46
p-value (significance):	< 0.0001

Individual trend lines:

Panes	Line	Coefficients
Row	Column	p-value	DF	Term	Value	StdErr	t-value	p-value
Sales	Year	< 0.0001	14	Year	2387.67	69.3886	34.4102	< 0.0001
	intercept	-4.73654e+06	139645	-33.9184	< 0.0001

Non linear trend (Logarithmic)

Trend Lines Model

A linear trend model is computed for sum of Sales given natural log of Year. The model may be significant at p <= 0.05.

Model formula:	( ln(Year) + intercept )
Number of modeled observations:	16
Number of filtered observations:	0
Model degrees of freedom:	2
Residual degrees of freedom (DF):	14
SSE (sum squared error):	2.2762e+07
MSE (mean squared error):	1.62585e+06
R-Squared:	0.988394
Standard error:	1275.09
p-value (significance):	< 0.0001

Individual trend lines:

Panes	Line	Coefficients
Row	Column	p-value	DF	Term	Value	StdErr	t-value	p-value
Sales	Year	< 0.0001	14	ln(Year)	4.80537e+06	139167	34.5296	< 0.0001
	intercept	-3.64864e+07	1.05866e+06	-34.4647	< 0.0001

2)

3)4)

5)