3.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 2007, so delete the earlier observations, and use the data through May 2020. 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. 2007) to 161 (May 2020); make a t² 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 t² as the independents, 2) seasonally unadjusted monthly as the dependent, and t, t², and D as the independents, 3) seasonally adjusted monthly as the dependent, and t and t² as the independents, and 4) seasonally adjusted monthly as the dependent, and t, t², 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.

date US NSA moUS SA year Jan-07 66 891 Feb-07 68 828 Mar-07 80 833 Apr-07 83 887 May-07 79 842 Jun-07 73 793 Jul-07 68 778 Aug-07 60 699 Sep-07 53 686 Oct-07 57 727 Nov-07 45 641 Dec-07 44 619 Jan-08 44 627 Feb-08 48 593 Mar-08 49 535 Apr-08 49 536 May-08 49 504 Jun-08 45 487 Jul-08 43 477 Aug-08 38 435 Sep-08 35 433 Oct-08 32 393 Nov-08 27 389 Dec-08 26 377 Jan-09 24 336 Feb-09 29 372 Mar-09 31 339 Apr-09 32 337 May-09 34 376 Jun-09 37 393 Jul-09 38 411 Aug-09 36 418 Sep-09 30 386 Oct-09 33 396 Nov-09 26 375 Dec-09 24 352 Jan-10 24 345 Feb-10 27 336 Mar-10 36 381 Apr-10 41 422 May-10 26 280 Jun-10 28 305 Jul-10 26 283 Aug-10 23 282 Sep-10 25 317 Oct-10 23 291 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Apr-13 May-13 Jun-13 Jul-13 Aug-13 Sep-13 Oct-13 Nov-13 Dec-13 Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 20 23 21 22 28 30 28 28 27 25 24 25 23 24 23 30 34 34 35 34 33 31 30 29 28 28 32 36 41 43 40 43 33 31 31 36 32 31 33 35 39 39 43 38 35 36 37 287 326 307 270 300 310 305 301 296 299 304 316 328 341 335 366 354 354 370 360 369 375 385 358 392 399 446 447 444 441 428 470 375 381 403 444 446 433 443 420 405 403 451 418 402 456 470 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 38 31 35 39 45 46 48 47 44 43 41 35 39 36 38 39 45 50 55 53 50 54 46 44 46 40 39 45 51 61 56 57 56 48 45 50 49 50 45 48 54 66 61 62 56 52 47 476 442 497 515 540 480 502 502 480 506 518 456 482 504 546 509 515 526 571 560 558 639 584 567 577 571 561 585 597 631 589 613 620 565 560 638 627 712 657 622 637 662 637 657 613 617 598 Sep-18 Oct-18 Nov-18 Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 46 43 44 38 49 57 68 64 56 66 55 57 56 55 50 49 59 63 59 54 64 596 552 614 564 637 665 700 664 600 726 661 706 726 706 696 731 774 716 612 580 676