Coastal Fuel Oil is a distributor of fuel-oil products in the Northeast. It cont
ID: 449401 • Letter: C
Question
Coastal Fuel Oil is a distributor of fuel-oil products in the Northeast. It contracts with shippers for deliveries of home heating oil and distributes product in its own trucks. Since its storage capacity is limited, and purchasing storage for fuel oil it cannot store itself is expensive, accurate demand forecasts are valuable. Monthly data covering the past 10 years are given in the table below.
a. Develop an appropriate forecast using a moving average approach.
b. Develop an appropriate forecast using a simple exponential smoothing approach.
c. Develop an appropriate forecast using static trend line approach
d. Develop an appropriate forecast using static trend line with seasonality approach.
e. Which of the four forecasts developed above would you recommend using?
f. Predict requirement for year 11 by using the recomeded model in part e.
Year Time Period Month 1 1 Jan 194.92 1 2 Feb 222.77 1 3 Mar 250.62 1 4 Apr 278.46 1 5 May 306.31 1 6 Jun 334.15 1 7 Jul 222.77 1 8 Aug 250.62 1 9 Sept 278.46 1 10 Oct 306.31 1 11 Nov 334.15 1 12 Dec 362 2 13 Jan 203.71 2 14 Feb 232.81 2 15 Mar 261.92 2 16 Apr 291.02 2 17 May 320.12 2 18 Jun 349.22 2 19 Jul 232.81 2 20 Aug 261.92 2 21 Sept 291.02 2 22 Oct 320.12 2 23 Nov 349.22 2 24 Dec 378.32 3 25 Jan 209.84 3 26 Feb 239.81 3 27 Mar 269.79 3 28 Apr 299.77 3 29 May 329.74 3 30 Jun 359.72 3 31 Jul 239.81 3 32 Aug 269.79 3 33 Sept 299.77 3 34 Oct 329.74 3 35 Nov 359.72 3 36 Dec 389.7 4 37 Jan 213.67 4 38 Feb 244.19 4 39 Mar 274.71 4 40 Apr 305.24 4 41 May 335.76 4 42 Jun 366.28 4 43 Jul 244.19 4 44 Aug 274.71 4 45 Sept 305.24 4 46 Oct 335.76 4 47 Nov 366.28 4 48 Dec 396.81 5 49 Jan 219.2 5 50 Feb 250.52 5 51 Mar 281.83 5 52 Apr 313.15 5 53 May 344.46 5 54 Jun 375.77 5 55 Jul 250.52 5 56 Aug 281.83 5 57 Sept 313.15 5 58 Oct 344.46 5 59 Nov 375.77 5 60 Dec 407.09 6 61 Jan 230.43 6 62 Feb 263.34 6 63 Mar 296.26 6 64 Apr 329.18 6 65 May 362.1 6 66 Jun 395.02 6 67 Jul 263.34 6 68 Aug 296.26 6 69 Sept 329.18 6 70 Oct 362.1 6 71 Nov 395.02 6 72 Dec 427.93 7 73 Jan 237.36 7 74 Feb 271.27 7 75 Mar 305.17 7 76 Apr 339.08 7 77 May 372.99 7 78 Jun 406.9 7 79 Jul 271.27 7 80 Aug 305.17 7 81 Sept 339.08 7 82 Oct 372.99 7 83 Nov 406.9 7 84 Dec 440.81 8 85 Jan 241.77 8 86 Feb 276.31 8 87 Mar 310.85 8 88 Apr 345.39 8 89 May 379.93 8 90 Jun 414.47 8 91 Jul 276.31 8 92 Aug 310.85 8 93 Sept 345.39 8 94 Oct 379.93 8 95 Nov 414.47 8 96 Dec 449.01 9 97 Jan 252.63 9 98 Feb 288.72 9 99 Mar 324.81 9 100 Apr 360.9 9 101 May 396.99 9 102 Jun 433.08 9 103 Jul 288.72 9 104 Aug 324.81 9 105 Sept 360.9 9 106 Oct 396.99 9 107 Nov 433.08 9 108 Dec 469.17 10 109 Jan 260.01 10 110 Feb 297.16 10 111 Mar 334.3 10 112 Apr 371.45 10 113 May 408.59 10 114 Jun 445.73 10 115 Jul 297.16 10 116 Aug 334.3 10 117 Sept 371.45 10 118 Oct 408.59 10 119 Nov 445.73 10 120 Dec 482.88Explanation / Answer
Answer: Year Time Period Month 6 month moving average exponential smoothing with smoothing constant = 0.3 1 1 Jan 194.92 194.92 1 2 Feb 222.77 203.275 1 3 Mar 250.62 217.4785 1 4 Apr 278.46 235.77295 1 5 May 306.31 256.934065 1 6 Jun 334.15 264.5383 280.0988455 1 7 Jul 222.77 269.18 262.9001919 1 8 Aug 250.62 273.8217 259.2161343 1 9 Sept 278.46 278.4617 264.989294 1 10 Oct 306.31 283.1033 277.3855058 1 11 Nov 334.15 287.7433 294.4148541 1 12 Dec 362 292.385 314.6903978 2 13 Jan 203.71 289.2083 281.3962785 2 14 Feb 232.81 286.24 266.8203949 2 15 Mar 261.92 283.4833 265.3502765 2 16 Apr 291.02 280.935 273.0511935 2 17 May 320.12 278.5967 287.1718355 2 18 Jun 349.22 276.4667 305.7862848 2 19 Jul 232.81 281.3167 283.8933994 2 20 Aug 261.92 286.1683 277.3013796 2 21 Sept 291.02 291.0183 281.4169657 2 22 Oct 320.12 295.8683 293.027876 2 23 Nov 349.22 300.7183 309.8855132 2 24 Dec 378.32 305.5683 330.4158592 3 25 Jan 209.84 301.74 294.2431015 3 26 Feb 239.81 298.055 277.913171 3 27 Mar 269.79 294.5167 275.4762197 3 28 Apr 299.77 291.125 282.7643538 3 29 May 329.74 287.8783 296.8570477 3 30 Jun 359.72 284.7783 315.7159334 3 31 Jul 239.81 289.7733 292.9441534 3 32 Aug 269.79 294.77 285.9979073 3 33 Sept 299.77 299.7667 290.1295351 3 34 Oct 329.74 304.7617 302.0126746 3 35 Nov 359.72 309.7583 319.3248722 3 36 Dec 389.7 314.755 340.4374106 4 37 Jan 213.67 310.3983 302.4071874 4 38 Feb 244.19 306.1317 284.9420312 4 39 Mar 274.71 301.955 281.8724218 4 40 Apr 305.24 297.8717 288.8826953 4 41 May 335.76 293.8783 302.9458867 4 42 Jun 366.28 289.975 321.9461207 4 43 Jul 244.19 295.0617 298.6192845 4 44 Aug 274.71 300.1483 291.4464991 4 45 Sept 305.24 305.2367 295.5845494 4 46 Oct 335.76 310.3233 307.6371846 4 47 Nov 366.28 315.41 325.2300292 4 48 Dec 396.81 320.4983 346.7040204 5 49 Jan 219.2 316.3333 308.4528143 5 50 Feb 250.52 312.3017 291.07297 5 51 Mar 281.83 308.4 288.300079 5 52 Apr 313.15 304.6317 295.7550553 5 53 May 344.46 300.995 310.3665387 5 54 Jun 375.77 297.4883 329.9875771 5 55 Jul 250.52 302.7083 306.147304 5 56 Aug 281.83 307.9267 298.8521128 5 57 Sept 313.15 313.1467 303.1414789 5 58 Oct 344.46 318.365 315.5370353 5 59 Nov 375.77 323.5833 333.6069247 5 60 Dec 407.09 328.8033 355.6518473 6 61 Jan 230.43 325.455 318.0852931 6 62 Feb 263.34 322.3733 301.6617052 6 63 Mar 296.26 319.5583 300.0411936 6 64 Apr 329.18 317.0117 308.7828355 6 65 May 362.1 314.7333 324.7779849 6 66 Jun 395.02 312.7217 345.8505894 6 67 Jul 263.34 318.2067 321.0974126 6 68 Aug 296.26 323.6933 313.6461888 6 69 Sept 329.18 329.18 318.3063322 6 70 Oct 362.1 334.6667 331.4444325 6 71 Nov 395.02 340.1533 350.5171028 6 72 Dec 427.93 345.6383 373.7409719 7 73 Jan 237.36 341.3083 332.8266804 7 74 Feb 271.27 337.1433 314.3596762 7 75 Mar 305.17 333.1417 311.6027734 7 76 Apr 339.08 329.305 319.8459414 7 77 May 372.99 325.6333 335.789159 7 78 Jun 406.9 322.1283 357.1224113 7 79 Jul 271.27 327.78 331.3666879 7 80 Aug 305.17 333.43 323.5076815 7 81 Sept 339.08 339.0817 328.1793771 7 82 Oct 372.99 344.7333 341.6225639 7 83 Nov 406.9 350.385 361.2057948 7 84 Dec 440.81 356.0367 385.0870563 8 85 Jan 241.77 351.12 342.0919394 8 86 Feb 276.31 346.31 322.3573576 8 87 Mar 310.85 341.605 318.9051503 8 88 Apr 345.39 337.005 326.8506052 8 89 May 379.93 332.51 342.7744237 8 90 Jun 414.47 328.12 364.2830966 8 91 Jul 276.31 333.8767 337.8911676 8 92 Aug 310.85 339.6333 329.7788173 8 93 Sept 345.39 345.39 334.4621721 8 94 Oct 379.93 351.1467 348.1025205 8 95 Nov 414.47 356.9033 368.0127643 8 96 Dec 449.01 362.66 392.311935 9 97 Jan 252.63 358.7133 350.4073545 9 98 Feb 288.72 355.025 331.9011482 9 99 Mar 324.81 351.595 329.7738037 9 100 Apr 360.9 348.4233 339.1116626 9 101 May 396.99 345.51 356.4751638 9 102 Jun 433.08 342.855 379.4566147 9 103 Jul 288.72 348.87 352.2356303 9 104 Aug 324.81 354.885 344.0079412 9 105 Sept 360.9 360.9 349.0755588 9 106 Oct 396.99 366.915 363.4498912 9 107 Nov 433.08 372.93 384.3389238 9 108 Dec 469.17 378.945 409.7882467 10 109 Jan 260.01 374.16 364.8547727 10 110 Feb 297.16 369.5517 344.5463409 10 111 Mar 334.3 365.1183 341.4724386 10 112 Apr 371.45 360.8617 350.465707 10 113 May 408.59 356.78 367.9029949 10 114 Jun 445.73 352.8733 391.2510964 10 115 Jul 297.16 359.065 363.0237675 10 116 Aug 334.3 365.255 354.4066373 10 117 Sept 371.45 371.4467 359.5196461 10 118 Oct 408.59 377.6367 374.2407523 10 119 Nov 445.73 383.8267 395.6875266 10 120 Dec 482.88 390.0183 421.8452686 The trend line is y = 0.954x + 265.5 I would recommand the trendline as this gives much more accurate result than any other method.