QUESTION 7 Use the Financial database from "Excel Databases.xls" on Blackboard. Use Total Revenues, Total Assets, Return on Equity, Earnings Per Share, Average Yield, and Dividends Per Share to predict the average P/E ratio for a company. Use Excel to perform a forward selection regression analysis. Assume a 5% level of significance. Identify observation 2 in the original rom observation 2 to find the predicted value y-hat based on your final model selected. Write your answer as a number and round your final answer to a whole number. 12-Year Gasoline Agricultural Energy Month and Year U.S. Gasoline Prices in cents) OPEC Spot Price: per barrel 21.52 18.57 18.63 1283 127.6 125.1 124.4 124,5 124.2 16.64 Jan-97 Feb-97 Mar-97 Apr-97 May-97 Jun-97 Jul.97 Aug-97 Sep-97 Oct-97 Nov-97 Dec-97 122 126.8 127.6 7881 1242 121.6 117.7 1132 109.6 1064 18 31 16.69 17.16 16.79 17.34 18.18 17.57 15.65 13.48 11.66 11.98 11.78 11.92 10.73 11.55 11.54 12.56 12.45 11.09 9.56 10.46 107.7 110.5 1103 1094 8403 106.5 104.9 105.9 Consumer Food U.S. Natural Gas Gasoline Wellhead Price Production (1000 (3/1000 Cu.Ft) Barrels per Day 7315 7330 2.49 7079 1.79 7737 1.81 7998 8008 2.08 7959 8207 2.08 8134 233 268 7627 2.92 8331 2.28 7545 1.96 7508 1.96 7975 2.06 8083 2.16 8216 2.04 8368 1.91 8272 2.09 1.82 8026 7947 1.86 8181 1.94 8306 1.95 7792 1.85 7866 7676 8327 8401 2.17 8165 8156 8384 8493 8154 2.52 8254 2.68 8388 7836 7830 2.73 N256 2.66 8214 2.86 3.04 8325 3.77 8556 8295 3.73 8424 4.26 8249 8183 8505 7789 103.6 98.7 98 10.07 Fsbog Mars 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 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 13.24 15.73 14.89 15.68 18.74 19.95 2.51 962 102.2 117.1 117.1 115.4 119.7 126 1295 125 129.2 131.3 132.9 141.5 155.6 150.6 152.6 166.6 1591 224 22.33 21.6 24.51 24.32 25.54 25.94 24.73 23.63 27.67 29.11 25 22 28.4 28.96 30.66 30.28 20.89 8434 3.84 1506 458 utsa.b 4.37 4.52 4.36 3.79 3.35 3.33 2.93 2.78 3.41 145 159.1 173.8 165.8 146.6 146.1 155.7 135.7 121.2 1127 114.8 115.5 128.9 143.9 143.4 142.4 141.8 143.8 144.1 148.6 146.1 23.38 25.02 26.87 24.34 23.42 24.89 21.47 19.21 17.29 18.57 17.56 18.74 23.38 24.18 22.99 23.68 24.2 25.83 27.55 25.69 23.32 285 29.18 31.43 7955 8476 8567 8997 8266 8050 8455 8422 8535 8292 8149 7950 8371 8497 8440 8528 8550 8382 8461 8515 8647 3.42 2.5 2.19 24 2.94 2.96 2.92 2.76 297 3.24 3.59 3.96 4.43 1429 8796 SLOS 24 21 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 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-at 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 JOS Aug-os Sep.05 165.5 173.4 1633 153.9 6.96 4.47 4.77 153.3 SOS 4.46 4.59 155.4 166.1 172.1 1606 155.5 1522 4.32 23.55 24 2 25.04 26.64 28.26 24.88 27.14 27.6 28 32 28.84 29.21 31.28 31.91 35.28 33.OS 4.26 4.76 5.21 7521 7978 8004 8430 8656 8657 8524 8846 8749 8526 8702 8667 8148 8456 8530 8793 8817 8798 SR32 8879 8187 8779 SR57 9013 8465 8483 8198 8752 5.02 169 177.8 1839 2023 2013 195.4 192 191.2 204.2 5.12 5.03 5.4 36.6 5.62 $52 39.67 39.85 44.72 5.06 5.43 38.26 33.29 6.01 188.7 1875 1953 44,04 5.95 2285 20.5 485 44.25 53.08 53.59 59.95 58.83 9136 6.09 6.71 252.9 295.1 8799 utsa.blackk 155.5 152.2 161.4 8702 8667 8148 8456 Noy-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 1778 8530 5.12 8793 181.9 202.3 2013 28.32 28.84 29.21 31.28 31.91 35.28 33.05 36,6 39.67 39.85 44.72 38.26 33.29 8798 5.82 195.4 8832 192 8819 5.52 191.2 2042 202.3 188.7 187.5 195.3 8779 8857 9013 5.06 5.43 6.21 6.01 58 44.04 5.73 49.6 8485 8198 5.95 6.57 9128 9136 8661 6.71 8.95 2285 2205 2198 233.3 2529 295.1 276.5 2303 2229 236 232.6 246 278.7 7507 48734 8350 8595 2953 9208 Aup-04 Sep-04 Oct-04 Nov.04 Dec-04 Jan-05 Feb-os Marcos Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov.05 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 No.07 Dec-07 Jan-08 Feb.OR Mar-08 Apr-08 May-08 48 58 44 25 53.08 53.59 59.95 58.83 $3.45 50.29 52.22 60.9 55.76 59.91 67.59 64.12 65.7 68.71 67.48 $6.07 55.45 54.73 57.33 S0.17 54,4 61.7 64.35 67.39 68.02 73.74 68.83 76.52 8162 90.74 90.99 3025 2999 2606 2293 9141 8763 2275 8856 9326 2359 2289 9087 8773 9402 9429 6.19 260.9 289.1 318.7 310.2 301.1 283.4 2849 285.3 312.8 307 3095 9157 8698 8917 6.25 9092 9070 $$4 329.3 9042 8603 8964 350.7 381.5 110.96 16.43 QUESTION 7 Use the Financial database from "Excel Databases.xls" on Blackboard. Use Total Revenues, Total Assets, Return on Equity, Earnings Per Share, Average Yield, and Dividends Per Share to predict the average P/E ratio for a company. Use Excel to perform a forward selection regression analysis. Assume a 5% level of significance. Identify observation 2 in the original rom observation 2 to find the predicted value y-hat based on your final model selected. Write your answer as a number and round your final answer to a whole number. 12-Year Gasoline Agricultural Energy Month and Year U.S. Gasoline Prices in cents) OPEC Spot Price: per barrel 21.52 18.57 18.63 1283 127.6 125.1 124.4 124,5 124.2 16.64 Jan-97 Feb-97 Mar-97 Apr-97 May-97 Jun-97 Jul.97 Aug-97 Sep-97 Oct-97 Nov-97 Dec-97 122 126.8 127.6 7881 1242 121.6 117.7 1132 109.6 1064 18 31 16.69 17.16 16.79 17.34 18.18 17.57 15.65 13.48 11.66 11.98 11.78 11.92 10.73 11.55 11.54 12.56 12.45 11.09 9.56 10.46 107.7 110.5 1103 1094 8403 106.5 104.9 105.9 Consumer Food U.S. Natural Gas Gasoline Wellhead Price Production (1000 (3/1000 Cu.Ft) Barrels per Day 7315 7330 2.49 7079 1.79 7737 1.81 7998 8008 2.08 7959 8207 2.08 8134 233 268 7627 2.92 8331 2.28 7545 1.96 7508 1.96 7975 2.06 8083 2.16 8216 2.04 8368 1.91 8272 2.09 1.82 8026 7947 1.86 8181 1.94 8306 1.95 7792 1.85 7866 7676 8327 8401 2.17 8165 8156 8384 8493 8154 2.52 8254 2.68 8388 7836 7830 2.73 N256 2.66 8214 2.86 3.04 8325 3.77 8556 8295 3.73 8424 4.26 8249 8183 8505 7789 103.6 98.7 98 10.07 Fsbog Mars 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 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 13.24 15.73 14.89 15.68 18.74 19.95 2.51 962 102.2 117.1 117.1 115.4 119.7 126 1295 125 129.2 131.3 132.9 141.5 155.6 150.6 152.6 166.6 1591 224 22.33 21.6 24.51 24.32 25.54 25.94 24.73 23.63 27.67 29.11 25 22 28.4 28.96 30.66 30.28 20.89 8434 3.84 1506 458 utsa.b 4.37 4.52 4.36 3.79 3.35 3.33 2.93 2.78 3.41 145 159.1 173.8 165.8 146.6 146.1 155.7 135.7 121.2 1127 114.8 115.5 128.9 143.9 143.4 142.4 141.8 143.8 144.1 148.6 146.1 23.38 25.02 26.87 24.34 23.42 24.89 21.47 19.21 17.29 18.57 17.56 18.74 23.38 24.18 22.99 23.68 24.2 25.83 27.55 25.69 23.32 285 29.18 31.43 7955 8476 8567 8997 8266 8050 8455 8422 8535 8292 8149 7950 8371 8497 8440 8528 8550 8382 8461 8515 8647 3.42 2.5 2.19 24 2.94 2.96 2.92 2.76 297 3.24 3.59 3.96 4.43 1429 8796 SLOS 24 21 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 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-at 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 JOS Aug-os Sep.05 165.5 173.4 1633 153.9 6.96 4.47 4.77 153.3 SOS 4.46 4.59 155.4 166.1 172.1 1606 155.5 1522 4.32 23.55 24 2 25.04 26.64 28.26 24.88 27.14 27.6 28 32 28.84 29.21 31.28 31.91 35.28 33.OS 4.26 4.76 5.21 7521 7978 8004 8430 8656 8657 8524 8846 8749 8526 8702 8667 8148 8456 8530 8793 8817 8798 SR32 8879 8187 8779 SR57 9013 8465 8483 8198 8752 5.02 169 177.8 1839 2023 2013 195.4 192 191.2 204.2 5.12 5.03 5.4 36.6 5.62 $52 39.67 39.85 44.72 5.06 5.43 38.26 33.29 6.01 188.7 1875 1953 44,04 5.95 2285 20.5 485 44.25 53.08 53.59 59.95 58.83 9136 6.09 6.71 252.9 295.1 8799 utsa.blackk 155.5 152.2 161.4 8702 8667 8148 8456 Noy-03 Dec-03 Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 1778 8530 5.12 8793 181.9 202.3 2013 28.32 28.84 29.21 31.28 31.91 35.28 33.05 36,6 39.67 39.85 44.72 38.26 33.29 8798 5.82 195.4 8832 192 8819 5.52 191.2 2042 202.3 188.7 187.5 195.3 8779 8857 9013 5.06 5.43 6.21 6.01 58 44.04 5.73 49.6 8485 8198 5.95 6.57 9128 9136 8661 6.71 8.95 2285 2205 2198 233.3 2529 295.1 276.5 2303 2229 236 232.6 246 278.7 7507 48734 8350 8595 2953 9208 Aup-04 Sep-04 Oct-04 Nov.04 Dec-04 Jan-05 Feb-os Marcos Apr-05 May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov.05 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 No.07 Dec-07 Jan-08 Feb.OR Mar-08 Apr-08 May-08 48 58 44 25 53.08 53.59 59.95 58.83 $3.45 50.29 52.22 60.9 55.76 59.91 67.59 64.12 65.7 68.71 67.48 $6.07 55.45 54.73 57.33 S0.17 54,4 61.7 64.35 67.39 68.02 73.74 68.83 76.52 8162 90.74 90.99 3025 2999 2606 2293 9141 8763 2275 8856 9326 2359 2289 9087 8773 9402 9429 6.19 260.9 289.1 318.7 310.2 301.1 283.4 2849 285.3 312.8 307 3095 9157 8698 8917 6.25 9092 9070 $$4 329.3 9042 8603 8964 350.7 381.5 110.96 16.43