Question

Consider the monthly sales data for the following questions.

Month	Sales
1	34400
2	29700
3	29000
4	16600
5	20500
6	20300
7	22500
8	17400
9	19600
10	16400
11	17700
12	18100
13	15300
14	17600
15	14200
16	15800
17	14500
18	13300

Step 1 of 6: Fit a linear trend model to the data. What is the R² value for the linear model? Enter 4 decimal places

Step 2 of 6: How well does the linear trend fit the observed time series data?

Step 3 of 6: What is the linear trend equation? y = [ intercept ] + [ slope ] t (Round to the nearest WHOLE number)

Step 4 of 6: Use the forecasting formula to forecast month t = 19.
Use the intercept and slope rounded to the nearest whole number to calculate your answer.
Round your answer to the nearest whole number. DO NOT ENTER COMMAS OR DECIMALS.

Step 5 of 6: Use the forecasting formula to calculate the fits (forecasts) for periods 1 through 18. Then calculate the MAD.Enter the value of the MAD rounded to the nearest whole number. DO NOT ENTER COMMAS OR DECIMALS.

Step 6 of 6: Use the forecasting formula to calculate the fits (forecasts) for periods 1 through 18. Then calculate the MAPE.Enter the value of the MAPE rounded to two decimals, for example, 0.2031 would be 0.20 and 0.0482 would be 0.05.

CAN YOU PLEASE EXPLAIN HOW TO DO THIS THROUGH EXCEL WITH STEPS? Any help would be greatly appreciated!

Cheers!

Answer 1

1)

	ΣX	ΣY	Σ(x-x̅)²	Σ(y-ȳ)²	Σ(x-x̅)(y-ȳ)
total sum	171	352900	484.5	580849444.4	-435050.00
mean	9.50	19605.56	SSxx	SSyy	SSxy

SSE=   (SSxx * SSyy - SS²xy)/SSxx =    190202380.461
      
std error ,Se =    √(SSE/(n-2)) =    3447.847
      
correlation coefficient ,    r = Sxy/√(Sx.Sy) =   -0.8201
      
R² =    (Sxy)²/(Sx.Sy) =    0.6725

2)

correlation coefficient ,    r = Sxy/√(Sx.Sy) =   -0.8201

Hence we can say that model is moderate and negative.

3)

sample size ,   n =   18          
here, x̅ = Σx / n=   9.50   ,     ȳ = Σy/n =   19605.56  
                  
SSxx =    Σ(x-x̅)² =    484.5000          
SSxy=   Σ(x-x̅)(y-ȳ) =   -435050.0          
                  
estimated slope , ß1 = SSxy/SSxx =   -435050.0   /   484.500   =   -897.9360
                  
intercept,   ß0 = y̅-ß1* x̄ =   28135.9477          
                  
so, regression line is   Ŷ =   28136 +   -898   *x

4)

Predicted Y at X=   19   is                  
Ŷ =   28136.00000   +   -898.000000   *   19   =   11074

5)

period	demand	forcast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
t	Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
1	34400	27238	7162.00	7162.00	51294244.00	20.82%
2	29700	26340.000	3360.00	3360.00	11289600.00	11.31%
3	29000	25442.000	3558.00	3558.00	12659364.00	12.27%
4	16600	24544.000	-7944.00	7944.00	63107136.00	47.86%
5	20500	23646.000	-3146.00	3146.00	9897316.00	15.35%
6	20300	22748.000	-2448.00	2448.00	5992704.00	12.06%
7	22500	21850.000	650.00	650.00	422500.00	2.89%
8	17400	20952.000	-3552.00	3552.00	12616704.00	20.41%
9	19600	20054.000	-454.00	454.00	206116.00	2.32%
10	16400	19156.000	-2756.00	2756.00	7595536.00	16.80%
11	17700	18258.000	-558.00	558.00	311364.00	3.15%
12	18100	17360.000	740.00	740.00	547600.00	4.09%
13	15300	16462.000	-1162.00	1162.00	1350244.00	7.59%
14	17600	15564.000	2036.00	2036.00	4145296.00	11.57%
15	14200	14666.000	-466.00	466.00	217156.00	3.28%
16	15800	13768.000	2032.00	2032.00	4129024.00	12.86%
17	14500	12870.000	1630.00	1630.00	2656900.00	11.24%
18	13300	11972.000	1328.00	1328.00	1763584.00	9.98%

MAD/MAE=   Σ |et|/n =    2499

6)

MAPE=   Σ | et/Dt |/n =    12.55%

= 0.13

Thanks in advance!

revert back for doubt

Please upvote