Data report (refer to the Excel file) the number of new constructions for residential homes,per month,...

Transcribed Image Text:

Data report (refer to the Excel file) the number of new constructions for residential homes,per month, in thousands, from 2013 to 2018 in the state of Oregon. a) Prepare a graph of the-data. (4 peint) b) Compute forecasts for the twelve months of 2019 using linear regression. (7 point) c) Prepare a graph of the forecast data. (4 point) d) Compute the MAD of your model. (5 point) e) Compute and chart the tracking signal. (5 point) Hint: You can use the Excel template of the similar example in the slides. Month numbers (t)constructions absolute (y) ty 1 forecast error error Jan-13 1 29 29 1 34.31 -5.31 5.31 Feb-13 2 32 64 4 34.63 -2.63 2.63 Mar-13 Apr-13 May-13 Jun-13 Jul-13 Aug-13 8 Sep-13 9 Oct-13 10 345670 41 123 9 34.95 6.05 6.05 44 176 16 35.27 8.73 8.73 49 245 25 35.59 13.41 13.41 47 282 36 35.91 11.09 11.09 46 322 49 36.23 9.77 9.77 47 376 64 36.55 10.45 10.45 43 387 81 36.87 6.13 6.13 45 450 100 37.19 7.81 7.81 Nov-13 11 34 374 121 37.51 -3.51 3.51 Dec-13 12 31 372 144 37.83 -6.83 6.83 Jan-14 13 35 455 169 38.15 -3.15 3.15 Feb-14 14 43 602 196 38.47 4.53 4.53 Mar-14 15 46 690 225 38.79 7.21 7.21 Apr-14 16 46 736 256 39.11 6.89 6.89 May-14 17 43 731 289 39.43 3.57 3.57 Jun-14 18 41 738 324 39.75 1.25 1.25 Jul-14 19 44 836 361 40.07 3.93 3.93 Aug-14 20 47 940 400 40.39 6.61 6.61 Sep-14 21 41 861 441 40.71 0.29 0.29 Oct-14 22 40 880 484 41.03 -1.03 1.03 Nov-14 23 32 736 529 41.35 -9.35 9.35 Dec-14 24 32 768 576 41.67 -9.67 9.67 Jan-15 25 34 850 625 41.99 -7.99 7.99 Feb-15 26 40 1040 676 42.31 -2.31 2.31 Mar-15 27 43 1161 729 42.63 0.37 0.37 Apr-15 28 42 1176 784 42.95 -0.95 0.95 May-15 29 43 1247 841 43.27 -0.27 0.27 Jun-15 30 44 1320 900 43.59 0.41 0.41 Jul-15 31 39 1209 961 43.91 -4.91 4.91 Aug-15 32 40 1280 1024 44.23 -4.23 4.23 Sep-15 33 33 1089 1089 44.55 -11.55 11.55 Oct-15 34 32 1088 1156 44.87 -12.87 12.87 Nov-15 35 31 1085 1225 45.19 -14.19 14.19 Dec-15 36 28 1008 1296 45.51 -17.51 17.51 Jan-16 37 34 1258 1369 45.83 -11.83 11.83 Feb-16 38 29 1102 1444 46.15 -17.15 17.15 Mar-16 39 36 1404 1521 46.47 -10.47 10.47 Apr-16 40 42 1680 1600 46.79 -4.79 4.79 May-16 41 43 1763 1681 47.11 -4.11 4.11 Jun-16 42 44 1848 1764 47.43 -3.43 3.43 Jul-16 43 44 1892 1849 47.75 -3.75 3.75 Aug-16 44 48 2112 1936 48.07 -0.07 0.07 Sep-16 45 45 2025 2025 48.39 -3.39 3.39 Oct-16 46 44 2024 2116 48.71 -4.71 4.71 Nov-16 47 40 1880 2209 49.03 -9.03 9.03 Dec-16 48 37 1776 2304 49.35 -12.35 12.35 Jan-17 49 45 2205 2401 49.67 -4.67 4.67 Feb-17 50 49 2450 2500 49.99 -0.99 0.99 Mar-17 51 62 3162 2601 50.31 11.69 11.69 Apr-17 52 62 3224 2704 50.63 11.37 11.37 May-17 53 58 3074 2809 50.95 7.05 7.05 Jun-17 54 59 3186 2916 51.27 7.73 7.73 Jul-17 55 64 3520 3025 51.59 12.41 12.41 Aug-17 56 62 3472 3136 51.91 10.09 10.09 Sep-17 57 50 2850 3249 52.23 -2.23 2.23 Oct-17 58 52 3016 3364 52.55 -0.55 0.55 Nov-17 59 50 2950 3481 52.87 -2.87 2.87 Dec-17 60 44 2640 3600 53.19 -9.19 9.19 Jan-18 61 51 3111 3721 53.51 -2.51 2.51 Feb-18 62 56 3472 3844 53.83 2.17 2.17 Mar-18 63 60 3780 3969 54.15 5.85 5.85 Apr-18 64 65 4160 4096 54.47 10.53 10.53 May-18 65 64 4160 4225 54.79 9.21 9.21 Jun-18 66 63 4158 4356 55.11 7.89 7.89 Jul-18 67 63 4221 4489 55.43 7.57 7.57 Aug-18 68 72 4896 4624 55.75 16.25 16.25 Sep-18 69 61 4209 4761 56.07 4.93 4.93 Oct-18 70 65 4550 4900 56.39 8.61 8.61 Nov-18 71 51 3621 5041 56.71 -5.71 5.71 Dec-18 72 47 3384 5184 57.03 -10.03 10.03 total 2628 3288 129961 127020 -0.24 483.94 Data report (refer to the Excel file) the number of new constructions for residential homes,per month, in thousands, from 2013 to 2018 in the state of Oregon. a) Prepare a graph of the-data. (4 peint) b) Compute forecasts for the twelve months of 2019 using linear regression. (7 point) c) Prepare a graph of the forecast data. (4 point) d) Compute the MAD of your model. (5 point) e) Compute and chart the tracking signal. (5 point) Hint: You can use the Excel template of the similar example in the slides. Month numbers (t)constructions absolute (y) ty 1 forecast error error Jan-13 1 29 29 1 34.31 -5.31 5.31 Feb-13 2 32 64 4 34.63 -2.63 2.63 Mar-13 Apr-13 May-13 Jun-13 Jul-13 Aug-13 8 Sep-13 9 Oct-13 10 345670 41 123 9 34.95 6.05 6.05 44 176 16 35.27 8.73 8.73 49 245 25 35.59 13.41 13.41 47 282 36 35.91 11.09 11.09 46 322 49 36.23 9.77 9.77 47 376 64 36.55 10.45 10.45 43 387 81 36.87 6.13 6.13 45 450 100 37.19 7.81 7.81 Nov-13 11 34 374 121 37.51 -3.51 3.51 Dec-13 12 31 372 144 37.83 -6.83 6.83 Jan-14 13 35 455 169 38.15 -3.15 3.15 Feb-14 14 43 602 196 38.47 4.53 4.53 Mar-14 15 46 690 225 38.79 7.21 7.21 Apr-14 16 46 736 256 39.11 6.89 6.89 May-14 17 43 731 289 39.43 3.57 3.57 Jun-14 18 41 738 324 39.75 1.25 1.25 Jul-14 19 44 836 361 40.07 3.93 3.93 Aug-14 20 47 940 400 40.39 6.61 6.61 Sep-14 21 41 861 441 40.71 0.29 0.29 Oct-14 22 40 880 484 41.03 -1.03 1.03 Nov-14 23 32 736 529 41.35 -9.35 9.35 Dec-14 24 32 768 576 41.67 -9.67 9.67 Jan-15 25 34 850 625 41.99 -7.99 7.99 Feb-15 26 40 1040 676 42.31 -2.31 2.31 Mar-15 27 43 1161 729 42.63 0.37 0.37 Apr-15 28 42 1176 784 42.95 -0.95 0.95 May-15 29 43 1247 841 43.27 -0.27 0.27 Jun-15 30 44 1320 900 43.59 0.41 0.41 Jul-15 31 39 1209 961 43.91 -4.91 4.91 Aug-15 32 40 1280 1024 44.23 -4.23 4.23 Sep-15 33 33 1089 1089 44.55 -11.55 11.55 Oct-15 34 32 1088 1156 44.87 -12.87 12.87 Nov-15 35 31 1085 1225 45.19 -14.19 14.19 Dec-15 36 28 1008 1296 45.51 -17.51 17.51 Jan-16 37 34 1258 1369 45.83 -11.83 11.83 Feb-16 38 29 1102 1444 46.15 -17.15 17.15 Mar-16 39 36 1404 1521 46.47 -10.47 10.47 Apr-16 40 42 1680 1600 46.79 -4.79 4.79 May-16 41 43 1763 1681 47.11 -4.11 4.11 Jun-16 42 44 1848 1764 47.43 -3.43 3.43 Jul-16 43 44 1892 1849 47.75 -3.75 3.75 Aug-16 44 48 2112 1936 48.07 -0.07 0.07 Sep-16 45 45 2025 2025 48.39 -3.39 3.39 Oct-16 46 44 2024 2116 48.71 -4.71 4.71 Nov-16 47 40 1880 2209 49.03 -9.03 9.03 Dec-16 48 37 1776 2304 49.35 -12.35 12.35 Jan-17 49 45 2205 2401 49.67 -4.67 4.67 Feb-17 50 49 2450 2500 49.99 -0.99 0.99 Mar-17 51 62 3162 2601 50.31 11.69 11.69 Apr-17 52 62 3224 2704 50.63 11.37 11.37 May-17 53 58 3074 2809 50.95 7.05 7.05 Jun-17 54 59 3186 2916 51.27 7.73 7.73 Jul-17 55 64 3520 3025 51.59 12.41 12.41 Aug-17 56 62 3472 3136 51.91 10.09 10.09 Sep-17 57 50 2850 3249 52.23 -2.23 2.23 Oct-17 58 52 3016 3364 52.55 -0.55 0.55 Nov-17 59 50 2950 3481 52.87 -2.87 2.87 Dec-17 60 44 2640 3600 53.19 -9.19 9.19 Jan-18 61 51 3111 3721 53.51 -2.51 2.51 Feb-18 62 56 3472 3844 53.83 2.17 2.17 Mar-18 63 60 3780 3969 54.15 5.85 5.85 Apr-18 64 65 4160 4096 54.47 10.53 10.53 May-18 65 64 4160 4225 54.79 9.21 9.21 Jun-18 66 63 4158 4356 55.11 7.89 7.89 Jul-18 67 63 4221 4489 55.43 7.57 7.57 Aug-18 68 72 4896 4624 55.75 16.25 16.25 Sep-18 69 61 4209 4761 56.07 4.93 4.93 Oct-18 70 65 4550 4900 56.39 8.61 8.61 Nov-18 71 51 3621 5041 56.71 -5.71 5.71 Dec-18 72 47 3384 5184 57.03 -10.03 10.03 total 2628 3288 129961 127020 -0.24 483.94