Let's practice time-series forecasting of new home sales. Click here (https://www.census.gov/constructionrs/historical_data/index.html) to see the newest data in the first table: Houses Sold (Excel file is sold_cust.xls). Look at the monthly data on the "Reg Sold" tab. If you have trouble with the link, I have recreated the data in moodle in the Excel file "A3Q3 Census Housing Data." Only keep the dates beginning in January 2006, so delete the earlier observations, and use the data through May 2021. Keep only the US data, both the seasonally unadjusted monthly (column B) and the seasonally adjusted annual (column G). Make a new column of seasonally adjusted monthly by dividing the annual data by 12. Make a column called "t" where t will go from 1 (Jan. 2006) to 185 (May 2021); make a t2 column too (since, if you look at the data, you can see sales are U-shaped; hence the quadratic). Also make a column "D" that is a dummy variable equal to one during the spring and summer months of March through August. Determine the correlation between the unadjusted and the adjusted monthly data (=CORREL(unadjust., adjust.) in Excel), and produce scatterplots (with straight lines) of both. Do you think making a seasonal adjustment will be useful, given what you observe at this point? Run four regressions: 1) seasonally unadjusted monthly as the dependent, and t and t2 as the independents, 2) seasonally unadjusted monthly as the dependent, and t, t2, and D as the independents, 3) seasonally adjusted monthly as the dependent, and t and t2 as the independents, and 4) seasonally adjusted monthly as the dependent, and t, t2, and D as the independents. Discuss your findings, and determine which of the four models is the best for forecasting new home sales. When interpreting your p-values, remember that, say, 1.0E-08 is 1.0 * 10^-8, which is 0.00000001. State the equation that would be used to forecast sales.

https://www.census.gov/constructionrs/historical_data/index.html

I have attached the excel file to use for our dataset.

36 Apr-13 date Aug-09 43 441 Dec-16 39 561 Aug-20 81 977 US NSA mo US SA year 418 Jan-17 45 578 Sep-20 77 971 30 386 May-13 40 428 Jan-06 89 1174 Sep-09 Feb-17 51 601 Oct-20 78 969 88 1061 Oct-09 33 396 Jun-13 43 470 Feb-06 Jul-13 33 375 Mar-17 61 643 Nov-20 61 865 Mar-06 108 1116 Nov-09 26 375 Dec-20 63 943 Aug-13 31 381 Apr-17 56 604 Apr-06 100 1123 Dec-09 24 352 May-17 57 627 Jan-21 77 993 102 1086 345 Sep-13 31 May-06 Jan-10 24 403 Jun-17 56 612 Feb-21 70 823 Oct-13 36 444 Jun-06 98 1074 Feb-10 27 336 Nov-13 32 446 Jul-17 48 553 Mar-21 83 886 965 381 Jul-06 83 Mar-10 36 433 Aug-17 45 550 Apr-21 75 817 1035 Apr-10 41 422 Dec-13 31 Aug-06 88 Sep-17 50 622 May-21 69 769 Jan-14 33 443 Sep-06 80 1016 May-10 26 280 Feb-14 35 420 Oct-17 49 625 Oct-06 74 941 Jun-10 28 305 Nov-17 50 718 Nov-06 71 1003 Jul-10 26 283 Mar-14 39 405 Apr-14 39 403 Dec-17 45 658 Dec-06 71 998 Aug-10 23 282 May-14 43 451 Jan-18 48 610 Jan-07 99 891 Sep-10 25 317 Jun-14 38 418 Feb-18 54 644 Feb-07 68 828 Oct-10 23 291 Nov-10 20 287 Jul-14 35 402 Mar-18 66 680 Mar-07 80 833 Dec-10 23 326 Aug-14 36 456 Apr-18 61 658 Apr-07 83 887 Sep-14 37 470 May-18 62 680 May-07 79 842 Jan-11 21 307 Oct-14 38 476 Jun-18 56 598 Jun-07 73 793 Feb-11 22 270 Nov-14 31 442 Jul-18 52 600 Jul-07 68 778 Mar-11 28 300 Dec-14 35 497 Aug-18 47 582 Aug-07 60 699 Apr-11 30 310 May-11 28 305 Jan-15 39 515 Sep-18 46 584 Sep-07 53 686 Jun-11 28 301 Feb-15 45 540 Oct-18 43 546 Oct-07 57 727 27 296 Mar-15 46 480 Nov-18 44 618 Nov-07 45 641 Jul-11 Apr-15 48 502 Dec-18 38 566 Dec-07 44 619 Aug-11 25 299 Jan-19 49 628 304 May-15 47 502 Jan-08 44 627 Sep-11 24 Feb-19 57 675 Feb-08 48 593 Oct-11 25 316 Jun-15 44 480 Mar-19 68 721 Mar-08 49 535 Nov-11 23 328 Jul-15 43 506 Apr-19 64 689 Apr-08 49 536 Dec-11 24 341 Aug-15 41 518 May-19 56 619 May-08 49 504 Jan-12 23 335 Sep-15 35 456 482 99 45 Feb-12 30 Oct-15 39 Jun-19 711 Jun-08 487 366 Mar-12 34 354 Nov-15 36 504 Jul-19 55 636 Jul-08 43 477 Dec-15 38 546 Aug-19 57 677 Apr-12 354 Aug-08 38 435 34 Sep-19 56 706 Sep-08 35 433 May-12 35 370 Jan-16 39 505 Feb-16 45 517 Oct-19 55 703 Oct-08 32 393 Jun-12 34 360 Mar-16 50 532 Nov-19 50 700 Nov-08 27 389 Jul-12 33 369 Aug-12 31 375 Apr-16 55 576 Dec-19 49 733 Dec-08 26 377 May-16 53 571 Jan-20 59 756 Jan-09 24 336 Sep-12 30 385 Jun-16 50 557 Feb-20 63 730 Feb-09 29 372 Oct-12 29 358 28 392 Jul-16 54 628 Mar-20 59 623 Mar-09 31 339 Nov-12 Aug-16 46 575 Apr-20 52 582 Apr-09 32 337 Dec-12 28 399 Sep-16 44 558 May-20 64 704 May-09 34 376 Jan-13 32 446 Oct-16 46 575 Jun-20 79 839 Jun-09 37 393 Feb-13 36 447 85 972 Jul-09 411 Mar-13 444 -16 571 Jul-20 38