The data file airline.xlsx contains a dataset of monthly airline passengers from Jan 1949 to Dec...

  

 

  
 
 
 
 

 
 

Transcribed Image Text:

The data file "airline.xlsx" contains a dataset of monthly airline passengers from Jan 1949 to Dec 1960. Sample size is 144. Variables in the dataset include: • Passenger Numbers • Date (first day of the year month) A line chart of the monthly passengers is shown below: Passenger Numbers 700 600 500 400 300 200 100 0961/L6 0961/1 6561//11 65619 0561/17 Task A: Moving Average 1. Compute a three-month moving average forecast for April 1949 through December 1960. 2. Compute a five-month moving average forecast for June 1949 through December 1960, 3. Calculate MAD for the above two methods. Compare the two models using MAD, Which model is better to predict the number of passengers for January 1961? What is the predicted number of passengers for January 1961 by using the more accurate model? Clear explain your calculation procedure and results (8 points) and answer questions (2 points) below: Date Passengers D1 D2 D3 D4 D5 D6 D7 Jan-49 112 1 Feb-49 118 1 Mar-49 132 1 Apr-49 Мау-49 Jun-49 129 1 121 1 135 1 Jul-49 148 1 Aug-49 Sep-49 148 136 Oct-49 119 Nov-49 104 Dec-49 Jan-50 Feb-50 Mar-50 Apr-50 May-50 Jun-50 Jul-50 Aug-50 Sep-50 118 115 1 126 1 141 1 135 1 125 1 149 1 170 1 170 158 Oct-50 133 Nov-50 114 Dec-50 Jan-51 Feb-51 140 145 1 150 1 Mar-51 Apr-51 May-51 Jun-51 Jul-51 Aug-51 Sep-51 178 1 163 1 172 1 178 1 199 1 199 184 Oct-51 162 Nov-51 146 Dec-51 166 Jan-52 171 1 Feb-52 180 1 Mar-52 Apr-52 May-52 Jun-52 Jul-52 Aug-52 Sep-52 193 1 181 1 183 1 218 1 230 1 242 209 Oct-52 191 Nov-52 172 Dec-52 194 Jan-53 196 1 Feb-53 196 1 Mar-53 236 1 Apr-53 May-53 235 1 229 1 Jun-53 Jul-53 Aug-53 Sep-53 Oct-53 243 1 264 1 272 237 211 Nov-53 Dec-53 Jan-54 Feb-54 180 201 204 1 188 1 Mar-54 235 1 Apr-54 May-54 Jun-54 Jul-54 Aug-54 Sep-54 Oct-54 Nov-54 227 1 234 1 264 1 302 1 293 259 229 203 Dec-54 229 Jan-55 Feb-55 Mar-55 Apr-55 May-55 Jun-55 Jul-55 Aug-55 Sep-55 242 1 233 1 267 1 269 1 270 1 315 1 364 1 347 312 Oct-55 274 Nov-55 Dec-55 Jan-56 Feb-56 Mar-56 237 278 284 1 277 1 317 1 Apr-56 May-56 Jun-56 Jul-56 Aug-56 Sep-56 313 1 318 1 374 1 413 1 405 355 Oct-56 306 Nov-56 Dec-56 271 306 Jan-57 315 1 Feb-57 Mar-57 301 1 356 1 Apr-57 May-57 Jun-57 Jul-57 Aug-57 Sep-57 348 1 355 1 422 1 465 467 1 404 Oct-57 347 Nov-57 305 ㅇ 336 340 Dec-57 Jan-58 1 Feb-58 318 1 Mar-58 362 1 Apr-58 May-58 Jun-58 348 1 363 1 435 1 Jul-58 Aug-58 Sep-58 Oct-58 Nov-58 491 1 505 404 359 310 Dec-58 337 Jan-59 360 1 Feb-59 342 1 Mar-59 406 1 Apr-59 May-59 Jun-59 396 1 420 1 472 1 Jul-59 Aug-59 Sep-59 548 1 559 463 Oct-59 407 Nov-59 362 405 Dec-59 Jan-60 Feb-60 417 1 391 1 Mar-60 419 1 Apr-60 May-60 Jun-60 461 1 472 1 535 1 Jul-60 Aug-60 Sep-60 Oct-60 622 1 606 508 461 Nov-60 390 Dec-60 432 D8 D9 D10 D11 D12 TIME TIME2 1 1 4 3 4 16 25 36 7 49 1 8 64 1 81 1 10 100 1 11 121 1 12 144 13 169 14 196 15 225 16 256 17 289 18 324 19 361 400 1 20 1 21 441 1 22 484 1 23 529 1 24 576 25 625 26 676 27 729 28 784 29 841 30 900 31 961 1 32 1024 1 33 1089 1156 1 34 1 35 1225 1 36 1296 37 1369 38 1444 39 1521 40 1600 41 1681 42 1764 1849 43 1 44 1936 45 2025 2116 1 1 46 1 47 2209 1 48 2304 49 2401 50 2500 51 2601 52 2704 53 2809 54 2916 55 3025 1 56 3136 1 57 3249 1 58 3364 1 59 3481 1 60 3600 61 3721 62 3844 63 64 3969 4096 65 4225 66 67 4356 4489 1 68 4624 1 69 4761 1 70 4900 1 71 5041 1 72 5184 73 5329 74 5476 75 5625 76 5776 77 5929 78 6084 79 6241 1 80 6400 1 81 6561 1 82 6724 1 83 6889 1 84 7056 85 7225 86 7396 87 7569 88 89 7744 7921 90 8100 91 8281 1 92 8464 1 93 8649 1 94 8836 1 95 9025 96 97 1 9216 9409 98 9604 99 9801 100 10000 101 10201 102 10404 103 10609 10816 11025 1 104 1 105 1 106 11236 1 107 11449 1 108 11664 109 11881 110 12100 111 12321 ㅇ 112 12544 113 12769 114 12996 115 13225 ㅇ 116 13456 13689 1 1 117 1 118 13924 1 119 14161 1 120 14400 121 14641 122 14884 123 15129 124 15376 125 15625 126 15876 127 16129 1 128 16384 129 16641 16900 1 1 130 1 131 17161 1 132 17424 133 17689 134 17956 135 18225 136 18496 18769 137 138 19044 139 19321 1 140 19600 1 141 19881 1 142 20164 1 143 20449 1 144 20736 The data file "airline.xlsx" contains a dataset of monthly airline passengers from Jan 1949 to Dec 1960. Sample size is 144. Variables in the dataset include: • Passenger Numbers • Date (first day of the year month) A line chart of the monthly passengers is shown below: Passenger Numbers 700 600 500 400 300 200 100 0961/L6 0961/1 6561//11 65619 0561/17 Task A: Moving Average 1. Compute a three-month moving average forecast for April 1949 through December 1960. 2. Compute a five-month moving average forecast for June 1949 through December 1960, 3. Calculate MAD for the above two methods. Compare the two models using MAD, Which model is better to predict the number of passengers for January 1961? What is the predicted number of passengers for January 1961 by using the more accurate model? Clear explain your calculation procedure and results (8 points) and answer questions (2 points) below: Date Passengers D1 D2 D3 D4 D5 D6 D7 Jan-49 112 1 Feb-49 118 1 Mar-49 132 1 Apr-49 Мау-49 Jun-49 129 1 121 1 135 1 Jul-49 148 1 Aug-49 Sep-49 148 136 Oct-49 119 Nov-49 104 Dec-49 Jan-50 Feb-50 Mar-50 Apr-50 May-50 Jun-50 Jul-50 Aug-50 Sep-50 118 115 1 126 1 141 1 135 1 125 1 149 1 170 1 170 158 Oct-50 133 Nov-50 114 Dec-50 Jan-51 Feb-51 140 145 1 150 1 Mar-51 Apr-51 May-51 Jun-51 Jul-51 Aug-51 Sep-51 178 1 163 1 172 1 178 1 199 1 199 184 Oct-51 162 Nov-51 146 Dec-51 166 Jan-52 171 1 Feb-52 180 1 Mar-52 Apr-52 May-52 Jun-52 Jul-52 Aug-52 Sep-52 193 1 181 1 183 1 218 1 230 1 242 209 Oct-52 191 Nov-52 172 Dec-52 194 Jan-53 196 1 Feb-53 196 1 Mar-53 236 1 Apr-53 May-53 235 1 229 1 Jun-53 Jul-53 Aug-53 Sep-53 Oct-53 243 1 264 1 272 237 211 Nov-53 Dec-53 Jan-54 Feb-54 180 201 204 1 188 1 Mar-54 235 1 Apr-54 May-54 Jun-54 Jul-54 Aug-54 Sep-54 Oct-54 Nov-54 227 1 234 1 264 1 302 1 293 259 229 203 Dec-54 229 Jan-55 Feb-55 Mar-55 Apr-55 May-55 Jun-55 Jul-55 Aug-55 Sep-55 242 1 233 1 267 1 269 1 270 1 315 1 364 1 347 312 Oct-55 274 Nov-55 Dec-55 Jan-56 Feb-56 Mar-56 237 278 284 1 277 1 317 1 Apr-56 May-56 Jun-56 Jul-56 Aug-56 Sep-56 313 1 318 1 374 1 413 1 405 355 Oct-56 306 Nov-56 Dec-56 271 306 Jan-57 315 1 Feb-57 Mar-57 301 1 356 1 Apr-57 May-57 Jun-57 Jul-57 Aug-57 Sep-57 348 1 355 1 422 1 465 467 1 404 Oct-57 347 Nov-57 305 ㅇ 336 340 Dec-57 Jan-58 1 Feb-58 318 1 Mar-58 362 1 Apr-58 May-58 Jun-58 348 1 363 1 435 1 Jul-58 Aug-58 Sep-58 Oct-58 Nov-58 491 1 505 404 359 310 Dec-58 337 Jan-59 360 1 Feb-59 342 1 Mar-59 406 1 Apr-59 May-59 Jun-59 396 1 420 1 472 1 Jul-59 Aug-59 Sep-59 548 1 559 463 Oct-59 407 Nov-59 362 405 Dec-59 Jan-60 Feb-60 417 1 391 1 Mar-60 419 1 Apr-60 May-60 Jun-60 461 1 472 1 535 1 Jul-60 Aug-60 Sep-60 Oct-60 622 1 606 508 461 Nov-60 390 Dec-60 432 D8 D9 D10 D11 D12 TIME TIME2 1 1 4 3 4 16 25 36 7 49 1 8 64 1 81 1 10 100 1 11 121 1 12 144 13 169 14 196 15 225 16 256 17 289 18 324 19 361 400 1 20 1 21 441 1 22 484 1 23 529 1 24 576 25 625 26 676 27 729 28 784 29 841 30 900 31 961 1 32 1024 1 33 1089 1156 1 34 1 35 1225 1 36 1296 37 1369 38 1444 39 1521 40 1600 41 1681 42 1764 1849 43 1 44 1936 45 2025 2116 1 1 46 1 47 2209 1 48 2304 49 2401 50 2500 51 2601 52 2704 53 2809 54 2916 55 3025 1 56 3136 1 57 3249 1 58 3364 1 59 3481 1 60 3600 61 3721 62 3844 63 64 3969 4096 65 4225 66 67 4356 4489 1 68 4624 1 69 4761 1 70 4900 1 71 5041 1 72 5184 73 5329 74 5476 75 5625 76 5776 77 5929 78 6084 79 6241 1 80 6400 1 81 6561 1 82 6724 1 83 6889 1 84 7056 85 7225 86 7396 87 7569 88 89 7744 7921 90 8100 91 8281 1 92 8464 1 93 8649 1 94 8836 1 95 9025 96 97 1 9216 9409 98 9604 99 9801 100 10000 101 10201 102 10404 103 10609 10816 11025 1 104 1 105 1 106 11236 1 107 11449 1 108 11664 109 11881 110 12100 111 12321 ㅇ 112 12544 113 12769 114 12996 115 13225 ㅇ 116 13456 13689 1 1 117 1 118 13924 1 119 14161 1 120 14400 121 14641 122 14884 123 15129 124 15376 125 15625 126 15876 127 16129 1 128 16384 129 16641 16900 1 1 130 1 131 17161 1 132 17424 133 17689 134 17956 135 18225 136 18496 18769 137 138 19044 139 19321 1 140 19600 1 141 19881 1 142 20164 1 143 20449 1 144 20736