The data NCS_Exercise.XLS has the following three variables: the number of new cars sold (NCS; in millions of dollars); index of consumer sentiment (ICS); and prime loan rate (Prime). Software: Use EXCEL for analysis. Recreate some of the results with JMP. 1. Formulate and estimate a model of the effect of ICS and Prime on the number of new cars sold (NCS). Use the data from January 2002 to December 2011 for the purpose of estimation 2. Plot the number of new cars over time. What do you infer? 3. Based on what you learnt from the plot, can you formulate and estimate a \"best\" model. Note that to in order to identify the \"best\" model, you may have to formulate and estimate a couple of models. Report the results of all the models. As before, use the data from January 2002 to December 2011 for the purpose of estimation. 4. Use the data from January 2012 to December 2012 for the purpose of figuring out which model is able to predict the number of new cars sold better. Which model fits the data better? Explain how. Hint: Use Root Mean Squared Error (RMSE) to determine model fit. Model with lower RMSE is better. n RMSE = (y i =1 i y i ) n 2 where: y = Actual value y = Estimated value n = number of observations i i Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Year 1997 1997 1997 1997 1997 1997 1997 1997 1997 1997 1997 1997 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 Date Jan-97 Feb-97 Mar-97 Apr-97 May-97 Jun-97 Jul-97 Aug-97 Sep-97 Oct-97 Nov-97 Dec-97 Jan-98 Feb-98 Mar-98 Apr-98 May-98 Jun-98 Jul-98 Aug-98 Sep-98 Oct-98 Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 May-99 Jun-99 Jul-99 Aug-99 Sep-99 Oct-99 Nov-99 NCS 38922 40002 45926 44068 45638 45170 46076 46084 43646 44164 38715 40561 39755 39981 47285 47102 48975 51208 46200 44939 44531 46710 41922 43951 42026 45217 53829 50675 53276 54568 54028 55562 51577 49387 47703 ICS 97.4 99.7 100.0 101.4 103.2 104.5 107.1 104.4 106.0 105.6 107.2 102.1 106.6 110.4 106.5 108.7 106.5 105.6 105.2 104.4 100.9 97.4 102.7 100.5 103.9 108.1 105.7 104.6 106.8 107.3 106.0 104.5 107.2 103.2 107.2 Prime 8.25 8.25 8.3 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.5 8.49 8.12 7.89 7.75 7.75 7.75 7.75 7.75 7.75 7.75 8 8.06 8.25 8.25 8.37 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 1999 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 Dec-99 Jan-00 Feb-00 Mar-00 Apr-00 May-00 Jun-00 Jul-00 Aug-00 Sep-00 Oct-00 Nov-00 Dec-00 Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 Sep-01 Oct-01 Nov-01 Dec-01 Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02 Aug-02 Sep-02 Oct-02 Nov-02 48319 47038 53507 59385 51686 57483 57237 52953 56929 52109 50740 46452 44604 47298 49242 56665 52329 58137 57020 54087 58126 48656 65956 52701 49196 48169 49618 54935 55013 55706 55398 60611 63691 51564 52236 47693 105.4 112.0 111.3 107.1 109.2 110.7 106.4 108.3 107.3 106.8 105.8 107.6 98.4 94.7 90.6 91.5 88.4 92.0 92.6 92.4 91.5 81.8 82.7 83.9 88.8 93.0 90.7 95.7 93.0 96.9 92.4 88.1 87.6 86.1 80.6 84.2 8.5 8.5 8.73 8.83 9 9.24 9.5 9.5 9.5 9.5 9.5 9.5 9.5 9.05 8.5 8.32 7.8 7.24 6.98 6.75 6.67 6.28 5.53 5.1 4.84 4.75 4.75 4.75 4.75 4.75 4.75 4.75 4.75 4.75 4.75 4.35 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 2002 2003 2003 2003 2003 2003 2003 2003 2003 2003 2003 2003 2003 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2005 2005 2005 2005 2005 2005 2005 2005 2005 2005 2005 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03 Sep-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04 Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05 51125 49072 47557 55849 55593 59308 57881 61200 61111 55067 52839 48326 53082 47627 50313 60297 55239 58950 56874 60424 58803 57519 52694 50286 56868 47268 50452 60854 57975 58902 64957 68573 61927 52666 47549 49323 86.7 82.4 79.9 77.6 86.0 92.1 89.7 90.9 89.3 87.7 89.6 93.7 92.6 103.8 94.4 95.8 94.2 90.2 95.6 96.7 95.9 94.2 91.7 92.8 97.1 95.5 94.1 92.6 87.7 86.9 96.0 96.5 89.1 76.9 74.2 81.6 4.25 4.25 4.25 4.25 4.25 4.25 4.22 4 4 4 4 4 4 4 4 4 4 4 4.01 4.25 4.43 4.58 4.75 4.93 5.15 5.25 5.49 5.58 5.75 5.98 6.01 6.25 6.44 6.59 6.75 7 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2005 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 2007 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07 55280 49726 50426 61857 55942 60066 59569 61067 62997 55016 52740 51072 54991 51064 53428 63804 57967 63714 59607 59933 64575 56349 56855 51856 54106 91.5 91.2 86.7 88.9 87.4 79.1 84.9 84.7 82.0 85.4 93.6 92.1 91.7 96.9 91.3 88.4 87.1 88.3 85.3 90.4 83.4 83.4 80.9 76.1 75.5 7.15 7.26 7.5 7.53 7.75 7.93 8.02 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.25 8.03 7.74 7.5 7.33