PLEASE HELP ME WTIH PART 4 ( WITH DETAIL EXPLANATION HOW TO DO IT ON EXCEL,
PART 1) Given this table, Build a forecasting model that accounts for trend in the data and run the regression using Excel.
What percentage of change in the sales can be explained by trend? =>
answer: I used data analysis-regression tool and got 38.3%
PART 2)
Using the data from the previous question, build another forecasting model that accounts for both trend and seasonal effects. Run the regression analysis and provide the coefficient of determination for this new model.
=>answer: I used analysis-regression for the dummies variable and got 97.86%
PART 3)
Using the forecasting model you developed that controls for both trend and seasonality, provide the forecasted revenue (sales) for the first quarter of year 4.
I take Y intercept + X1 + Perriod * 13 = 2977.1
PART 4) Based on the 3-year data from the model used in the previous questions, evaluate your trend and seasonality regression model calculating MAE (mean absolute error).
MAE =? Please help me explain why MAE =109.23 is the correct answer?
A B C D E F Year Quarter Q1 Q2 Q3 Period Sales 2 1 1 1 0 1 1690 3 2 2 940 4 3 1 3 2625 4 2500 2 1 1 3 2145 2 1 6 1175 8 3 1 7 3350 6 4 : 2720 10 3 1 1 2388 11 2 1 10 1948 2 3 1 11 3575 3 4| o| o| 12 3455