Question

2024 Question 5 Create a forecast for the rest of 2020 and 2021. Period 2018 2019 2020 Jan 1300 1290 1200 Feb 1320 1324 1265 Mar 1340 1300 1300 Apr 1360 1300 1200 May 1380 1303 1100 June 1300 1200 1050 1320 1300 Aug 1340 1403 Sept 1300 1200 Oct 1320 1300 Nov 1340 1422 1363 1340 Pick a method that you think will work.

Answer 1

Since the historical data is available, we will use Trend forecasting as shown below:

We use the Excel as shown below:

We enter the data in excel as find the coefficient using Data analysis as:

SUMMARY OUTPUT Regression Statistics Multiple R 0.556697057 R Square 0.309911613 Adjusted R Square 0.2852656 Standard Error 68.85937652 Observations 30 ANOVA df Significance F 0.001398227 1 Regression Residual Total SS 59623.48209 132765.1846 192388.6667 MS F 59623.48209 12.57451269 4741.613735 28 29 Intercept X Variable 1 Coefficients 1371.501149 -5.150611791 Standard Error t Stat P-value Lower 95% Upper 95% 25.78596419 53.18789476 1.13253E-29 1318.680996 1424.321303 1.452490284 -3.546055935 0.001398227 -8.125903262 2.17532032 Lower 95.0% Upper 95.0% 1318.680996 1424.321303 -8.125903262 -2.17532032

As seen from above

The equation for the Trend = 1371.5 + 5.151 * x

We find the forecast as:

Period Forecast 1 Actual Value Y 1100 1371.5-5.151 * x х 29 30 1050 31 2 31 32 33 34 35 36 37 32 33 34 35 36 38 39 37 40 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20 Nov-20 Dec-20 Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 38 39 1211.82 1206.67 1201.52 1196.37 1191.22 1186.06 1180.91 1175.76 1170.61 1165.46 1160.31 1155.16 1150.01 1144.86 1139.71 1134.55 1129.40 1124.25 40 41 42 43 44 45 46 47 48 49 50 41 42 43 44 45 46 47 48 Dec-21

The above solution can be updated in the format required.

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!