Multiple Regression Analysis of Energy Consumption in Luxury Hotels - Hainan Province, China
Conducting a multiple regression analysis on the energy consumption of luxury hotels in Hainan Province, China using matrix form in Excel. The dataset includes 19 luxury hotels with the dependent variable being energy consumption (1M kWh) and predictors such as area, age, and effective number of guest rooms. The regression model is explored with scalar form, matrix form, ordinary least squares estimator, projection matrix, fitted values, residuals, and ANOVA analysis.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Multiple Regression in Matrix Form Using EXCEL Energy Consumption of Luxury Hotels in Hainan Province, China Source: Y. Xin, S. Lu, N. Zhu, W. Wu (2012). "Energy Consumption Quota of Four and Five Star Luxury Hotels Buildings in Hainan Province, China," Energy and Buildings, Vol. 45, pp. 250-256.
Data n = 19 Luxury Hotels Dependent Variable: Y = Energy Consumption (1M*kwh) Predictors: X1= Area (1000s of square meters) X2= Age (Years) X3= Effective # of Guest Rooms (#Rooms*Occupancy Rate) hotel enrgcons area 1.9539 1.0456 4.2453 2.1262 2.7860 13.8340 5.5581 4.0012 4.6698 8.9240 age effrooms hotel 136.92 135.45 177.59 156.51 330.38 385.39 159.25 104.02 179.50 290.03 enrgcons area 6.8655 6.0146 8.1857 11.7361 14.8374 5.3665 13.5162 3.8844 10.5734 age effrooms 391.37 287.17 170.03 292.11 324.54 167.62 338.53 130.02 195.57 1 2 3 4 5 6 7 8 9 43.000 19.979 46.529 20.962 24.212 112.200 45.000 28.548 32.865 59.406 6 11 12 13 14 15 16 17 18 19 45.000 37.435 50.828 68.000 78.868 28.454 70.000 20.000 50.000 10 13 4 13 8 13 4 5 12 16 7 6 5 4 3 6 8 5 10
Regression Model (n = #obs, p=#predictors) ( ) = + + + + = = + = 2 2 2 Y X Scalar Form: ... 1,..., ~ 0, Matrix Form: where: Y X X i n NID I 0 1 1 i i p ip i n 1 1 X X X X Y Y 11 1 0 p 1 1 21 2 1 p 2 2 = = = = Y X + 1 ( 1) nx nx p 1 nx 1) 1 + ( p x 1 X X Y 1 n np p n n ^ Ordinary Least Squares Estimator of : (Note: there are two common notations for OLS estimator: and ) b e: there are two common notations: and ) ^ ^ ^ ( ) ( ) 1 1 = = = = 2 2 E b X'X X'Y X'X 1) 1 + ( Projection (Hat) Matrix: (Not ( nxn p x P H ) 1 = = P H X X'X X' Fitted Values, Residuals, and Mean Vector: Y 1 0 0 1 0 0 1 1 1 1 1 1 1 n Y ^ ^ ^ ( ) = = = = = = = = Y X PY e Y Y I P Y nx Y JY nxn I J 1 1 nx 1 nx nxn 0 0 1 1 1 1 Y Analysis of Variance: ( ) 1 n n 2 = = = Y' I J Y Total (Corrected Sum of Squares): 1 SSTO Y Y df n i TO = 1 i 2 n ^ ( ) = = = + Y' I P Y Residual (Error) Sum of Squares: ( 1) SSE Y Y df n p i i E = 1 i 2 1 n n ^ = = = Y' P J Y Regression Sum of Squares: SSR Y Y df p i R = 1 i
X Matrix and Y Vector X Y 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 43.000 19.979 46.529 20.962 24.212 112.200 45.000 28.548 32.865 59.406 45.000 37.435 50.828 68.000 78.868 28.454 70.000 20.000 50.000 6 136.92 135.45 177.59 156.51 330.38 385.39 159.25 104.02 179.50 290.03 391.37 287.17 170.03 292.11 324.54 167.62 338.53 130.02 195.57 1.9539 1.0456 4.2453 2.1262 2.7860 13.8340 5.5581 4.0012 4.6698 8.9240 6.8655 6.0146 8.1857 11.7361 14.8374 5.3665 13.5162 3.8844 10.5734 Note: In my current EXCEL Worksheet: 16 7 6 5 4 3 6 8 5 10 13 4 13 8 13 4 5 12 X is in cells B31:E49 Y is in cells G31:G49 The matrix operations are applied only to cells with numeric values, not cells with the matrix names/labels.
Matrix Operations/Rules When computing a new matrix: Highlight a range of cells that will contain the matrix (you must know its dimension). The box above the leftmost viewable column shows the size of your highlighted area. Type the relevant matrix commands. Press the keys: Ctrl - Shift - Enter Useful Matrix Commands (mmult can only do 2-at-a-time): =mmult(RangeA,RangeB) computes AB =mmult(transpose(RangeA),RangeA) computes A A =minverse(RangeA) computes A-1 for square (full rank) A =mdeterm(RangeA) computes det(A) for square A =mmult(RangeA,mmult(RangeB,RangeC)) computes ABC
Computing XX, XY, (XX)-1,b X is in Cells B31:E49, Y is in Cells G31:G49 X X and (X X)-1are (p+1)x(p+1) 4x4 in this example X Y and bare (p+1)x1 4x1 in this example Computing X X: Highlight a 4x4 range (say J31:M34) and Type: =mmult(transpose(B31:E49),B31:E49) Ctrl-Shift-Enter Computing X Y: Highlight a 4x1 range (say O31:O34) and Type: =mmult(transpose(B31:E49),G31:G49) Ctrl-Shift-Enter Computing (X X)-1: Highlight a 4x4 range (say J38:M41) and Type: =minverse(J31:M34) Ctrl-Shift-Enter Computing b: Highlight a 4x1 range (say O38:O41) and Type: =mmult(J38:M41,O31:O34) Ctrl-Shift-Enter
Hotel Example X'X X'Y 19 881.286 50940.4 6470.05 227951 33603.29 1156375.75 148 4352 130.124 7638.204 983.8417 34573.19 881.286 6470.05 227950.988 1424 148 4352 33603.29 INV(X'X) 0.701445 -0.00363 -0.00363 0.000187 0.000239 -0.035 0.000239 0.004001 -3.164E-05 -0.00091 -3E-05 b -0.035 -0.0009071 -2.25277 0.148709 0.113045 0.005777 -3.01E-05 -3.2E-05 1.1132E-05 For the truly hard-core types, b can be computed in one step (after highlight 4x1 range): =MMULT(MINVERSE(MMULT(TRANSPOSE(B31:E49),B31:E49)),MMULT(TRANSPOSE(B31:E49),G31:G49)) However, you need to have (X X)-1to obtain the Variance-Covariance Matrix and Standard Errors for b.
Computing the Projection (Hat) Matrix P = X(X X)-1X and is nxn. X is in B31:E49, (X X)-1is in J38:M41 Highlight (say) Cells S31:AK49 and Type: =MMULT(B31:E49,MMULT(J38:M41,TRANSPOSE(B31:E49))) P Sum 0.1358 0.0471 0.1015 0.0810 -0.0591 0.0490 0.1350 0.1395 0.0714 0.0362 -0.1017 -0.0394 0.1306 0.0087 0.0244 0.0488 0.0173 0.1074 0.0666 1.0000 0.0471 0.3463 0.0554 0.0405 -0.0777 -0.1485 -0.0531 0.0659 0.0860 -0.0626 0.0358 0.1783 -0.0299 0.1711 0.0055 0.2402 -0.1183 0.0246 0.1933 1.0000 0.1015 0.0554 0.0824 0.0601 -0.0299 0.0633 0.0964 0.0996 0.0609 0.0412 -0.0422 0.0003 0.0971 0.0400 0.0447 0.0539 0.0306 0.0749 0.0698 1.0000 0.0810 0.0405 0.0601 0.1472 0.1509 -0.1006 0.1082 0.1238 0.0901 0.0451 0.0349 0.0232 0.0799 -0.0682 -0.0455 0.0484 0.0250 0.1607 -0.0047 1.0000 -0.0591 -0.0777 -0.0299 0.1509 0.4726 -0.0956 0.0262 -0.0075 0.0614 0.1200 0.3258 0.1300 -0.0189 -0.1009 -0.0269 -0.0247 0.1478 0.1283 -0.1217 1.0000 0.0490 -0.1485 0.0633 -0.1006 -0.0956 0.4901 0.0728 -0.0436 -0.0326 0.1445 0.0141 -0.0370 0.1061 0.1666 0.2572 -0.0826 0.2138 -0.0995 0.0624 1.0000 0.1350 -0.0531 0.0964 0.1082 0.0262 0.0728 0.1752 0.1441 0.0685 0.0743 -0.0813 -0.0776 0.1557 -0.0592 0.0159 -0.0141 0.0707 0.1402 0.0019 1.0000 0.1395 0.0659 0.0996 0.1238 -0.0075 -0.0436 0.1441 0.1657 0.0906 0.0248 -0.0935 -0.0327 0.1281 -0.0365 -0.0250 0.0627 -0.0064 0.1524 0.0479 1.0000 0.0714 0.0860 0.0609 0.0901 0.0614 -0.0326 0.0685 0.0906 0.0732 0.0329 0.0276 0.0484 0.0603 0.0176 0.0061 0.0753 0.0160 0.0958 0.0507 1.0000 0.0362 -0.0626 0.0412 0.0451 0.1200 0.1445 0.0743 0.0248 0.0329 0.1024 0.0915 0.0231 0.0680 0.0228 0.0841 -0.0212 0.1299 0.0452 -0.0022 1.0000 -0.1017 0.0358 -0.0422 0.0349 0.3258 0.0141 -0.0813 -0.0935 0.0276 0.0915 0.3552 0.2149 -0.0857 0.0826 0.0732 0.0437 0.1255 -0.0111 -0.0094 1.0000 -0.0394 0.1783 0.0003 0.0232 0.1300 -0.0370 -0.0776 -0.0327 0.0484 0.0231 0.2149 0.2037 -0.0653 0.1409 0.0573 0.1334 0.0196 -0.0114 0.0906 1.0000 0.1306 -0.0299 0.0971 0.0799 -0.0189 0.1061 0.1557 0.1281 0.0603 0.0680 -0.0857 -0.0653 0.1461 -0.0166 0.0413 -0.0004 0.0657 0.1083 0.0297 1.0000 0.0087 0.1711 0.0400 -0.0682 -0.1009 0.1666 -0.0592 -0.0365 0.0176 0.0228 0.0826 0.1409 -0.0166 0.2438 0.1568 0.1241 0.0291 -0.0924 0.1698 1.0000 0.0244 0.0055 0.0447 -0.0455 -0.0269 0.2572 0.0159 -0.0250 0.0061 0.0841 0.0732 0.0573 0.0413 0.1568 0.1667 0.0187 0.1167 -0.0563 0.0852 1.0000 0.0488 0.2402 0.0539 0.0484 -0.0247 -0.0826 -0.0141 0.0627 0.0753 -0.0212 0.0437 0.1334 -0.0004 0.1241 0.0187 0.1726 -0.0573 0.0383 0.1400 1.0000 0.0173 -0.1183 0.0306 0.0250 0.1478 0.2138 0.0707 -0.0064 0.0160 0.1299 0.1255 0.0196 0.0657 0.0291 0.1167 -0.0573 0.1755 0.0211 -0.0223 1.0000 0.1074 0.0246 0.0749 0.1607 0.1283 -0.0995 0.1402 0.1524 0.0958 0.0452 -0.0111 -0.0114 0.1083 -0.0924 -0.0563 0.0383 0.0211 0.1834 -0.0098 1.0000 0.0666 0.1933 0.0698 -0.0047 -0.1217 0.0624 0.0019 0.0479 0.0507 -0.0022 -0.0094 0.0906 0.0297 0.1698 0.0852 0.1400 -0.0223 -0.0098 0.1620 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 Sum The Sums on the margins just show that the rows and columns sum to 1, and are not part of the P matrix
Computing the Analysis of Variance Y is in Cells G31:G49, X Y is in O31:O34 b is in O38:O41 P is in S31:AK49 Total (Corrected) Sum of Squares: dfTotal = n-1 Y (I - (1/n)J)Y=Y Y - Y (1/n)JY Y Y: 1) =mmult(transpose(G31:G49),G31:G49) 2) =sumsq(G31:G49) Y (1/n)JY: =(sum(G31:G49))^2/count(G31:G49) Residual (Error) Sum of Squares: dfError = n-(p+1) Y (I - P)Y = Y Y Y PY = Y Y b X Y (Much easier for large n) Y PY: =mmult(transpose(G31:G49),mmult(S31:AK49,G31:G49)) b X Y: 1) =mmult(transpose(O38:O41),O31:O34) b X Y: 2) =sumproduct(O31:O34,O38:O41) Regression Sum of Squares: dfReg = p Y (P - (1/n)J)Y
Hotel Example ANOVA, F & t-tests Y'Y (1) Y'Y (2) 1221.52 Y'(1/n)JY Y'PY 891.17 b'X'Y (1) b'X'Y (2) 1153.67 1221.52 1153.67 1153.67 SSTotal dfTotal SSResid dfResid SSReg dfReg 330.35 18 67.85 15 262.50 3 ANOVA Source Regression Residual Total df SS MS F_obs Fcrit P-value 3 262.50 67.85 330.35 87.50 4.52 19.35 3.29 0.0000 15 18 s2{b} = s^2*INV(X'X) 3.17268107 -0.01642 -0.15832 -0.0041028 -0.0164204 0.000845 0.001081 -0.0001362 -0.158321 0.001081 0.018097 -0.0001431 -0.0041028 -0.00014 -0.00014 5.0351E-05 b s{b} t_obs -1.2647 5.1162 0.8403 0.8141 -2.2528 0.1487 0.1130 0.0058 1.7812 0.0291 0.1345 0.0071 Note: s2 = MSResid = 4.52 In the Companion EXCEL Spreadsheet, This is cells J45:Q60
Obtaining Fitted Values, Residuals and Diagonal Elements of P x x ' ' 1 ) ( ) ( 1 1 2 = = = P X X'X X' X'X x x x x ' 1 x x 1 2 n i 1 i ip x ' n ( ) ( ) 1 1 = = x ' X'X x x ' X'X x P P i i i j ii ij ^ ^ ^ ^ ( ) = = = = = 2 2 2 Y X ' s Y MSE P PY Y P IP P i ii ^ ( ) ( e ) ( ) ( ) s e ( ) = = = = = 2 2 2 e Y Y I P Y I P I I P I P ' 1 MSE P i ii (X X)-1 is in Cells: J38:M41 bis in O38:O41 New Xis in B71:E89 New Y is in F71:F89 Computing Y-hat: Highlight cells G71:G89: =mmult(B71:E89,O38:O41) Computing e: In Cell I71: =F71-G71 Then Double Click on box in Southeast corner of cell Computing Pii: In Cell J71: =MMULT(B71:E71,MMULT($J$38:$M$41,TRANSPOSE(B71:E71))) Ctrl-Shift-Enter & Double Click box in Southeast Corner of J71
Hotel Example X Y Y-hat e P_diag 0.1358 0.3463 0.0824 0.1472 0.4726 0.4901 0.1752 0.1657 0.0732 0.1024 0.3552 0.2037 0.1461 0.2438 0.1667 0.1726 0.1755 0.1834 0.1620 s{Y-hat} s{e} rstudent -1.8497 -1.3167 -1.0988 -0.1633 -0.6705 -2.1578 -0.0725 0.3755 0.0459 0.0506 -0.5650 -0.2256 0.7356 0.3891 1.3303 0.4911 1.5284 0.9609 1.4918 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 43.000 19.979 46.529 20.962 24.212 112.200 45.000 28.548 32.865 59.406 45.000 37.435 50.828 68.000 78.868 28.454 70.000 20.000 50.000 6 136.92 135.45 177.59 156.51 330.38 385.39 159.25 104.02 179.50 290.03 391.37 287.17 170.03 292.11 324.54 167.62 338.53 130.02 195.57 1.9539 1.0456 4.2453 2.1262 2.7860 13.8340 5.5581 4.0012 4.6698 8.9240 6.8655 6.0146 8.1857 11.7361 14.8374 5.3665 13.5162 3.8844 10.5734 5.6110 3.3095 6.4837 2.4469 3.8215 17.1109 5.6982 3.2718 4.5759 8.8221 7.8305 6.4427 6.7402 11.0165 12.2548 4.4165 10.5647 2.0377 7.6690 -3.6570 -2.2639 -2.2384 -0.3207 -1.0356 -3.2769 -0.1401 0.7295 0.0939 0.1019 -0.9649 -0.4281 1.4455 0.7196 2.5826 0.9500 2.9515 1.8467 2.9044 0.7836 1.2516 0.6106 0.8161 1.4620 1.4889 0.8902 0.8658 0.5756 0.6807 1.2675 0.9598 0.8130 1.0501 0.8682 0.8836 0.8910 0.9107 0.8559 1.9771 1.7195 2.0372 1.9639 1.5445 1.5186 1.9315 1.9425 2.0474 2.0149 1.7078 1.8978 1.9652 1.8494 1.9414 1.9345 1.9311 1.9219 1.9469 16 7 6 5 4 3 6 8 5 10 13 4 13 8 13 4 5 12 rstudent Studentized Residual = e/s{e}
Residuals vs Fitted Values 4.0000 3.0000 2.0000 1.0000 Residuals 0.0000 e -1.0000 -2.0000 -3.0000 -4.0000 0.0000 2.0000 4.0000 6.0000 8.0000 10.0000 12.0000 14.0000 16.0000 18.0000 Fitted Values
