Multiple Regression in Matrix Form Using EXCEL Energy

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 (1 M*kwh) • Predictors: § X 1 = Area (1000 s of square meters) § X 2 = Age (Years) § X 3 = Effective # of Guest Rooms (#Rooms*Occupancy Rate)

Regression Model (n = #obs, p=#predictors)

X Matrix and Y Vector Note: In my current EXCEL Worksheet: • X is in cells B 31: E 49 • Y is in cells G 31: G 49 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(Range. A, Range. B) computes AB § =mmult(transpose(Range. A), Range. A) computes A’A § =minverse(Range. A) computes A-1 for square (full rank) A § =mdeterm(Range. A) computes det(A) for square A § =mmult(Range. A, mmult(Range. B, Range. C)) computes ABC

Computing X’X, X’Y, (X’X)-1, b • • X is in Cells B 31: E 49, Y is in Cells G 31: G 49 X’X and (X’X)-1 are (p+1)x(p+1) ≡ 4 x 4 in this example X’Y and b are (p+1)x 1 ≡ 4 x 1 in this example Computing X’X: § Highlight a 4 x 4 range (say J 31: M 34) and Type: § =mmult(transpose(B 31: E 49), B 31: E 49) Ctrl-Shift-Enter • Computing X’Y: § Highlight a 4 x 1 range (say O 31: O 34) and Type: § =mmult(transpose(B 31: E 49), G 31: G 49) Ctrl-Shift-Enter • Computing (X’X)-1: § Highlight a 4 x 4 range (say J 38: M 41) and Type: § =minverse(J 31: M 34) Ctrl-Shift-Enter • Computing b: § Highlight a 4 x 1 range (say O 38: O 41) and Type: § =mmult(J 38: M 41, O 31: O 34) Ctrl-Shift-Enter

Hotel Example For the truly hard-core types, b can be computed in one step (after highlight 4 x 1 range): =MMULT(MINVERSE(MMULT(TRANSPOSE(B 31: E 49), B 31: E 49)), MMULT(TRANSPOSE(B 31: E 49), G 31: G 49)) However, you need to have (X’X)-1 to obtain the Variance-Covariance Matrix and Standard Errors for b.

Computing the Projection (Hat) Matrix • P = X(X’X)-1 X’ and is nxn. § X is in B 31: E 49, (X’X)-1 is in J 38: M 41 § Highlight (say) Cells S 31: AK 49 and Type: § =MMULT(B 31: E 49, MMULT(J 38: M 41, TRANSPOSE(B 31: E 49))) 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 G 31: G 49, X’Y is in O 31: O 34 b is in O 38: O 41 • P is in S 31: AK 49 • Total (Corrected) Sum of Squares: df. Total = n-1 § Y’(I - (1/n)J)Y=Y’Y - Y’(1/n)JY • Y’Y: 1) =mmult(transpose(G 31: G 49), G 31: G 49) 2) =sumsq(G 31: G 49) • Y’(1/n)JY: =(sum(G 31: G 49))^2/count(G 31: G 49) • Residual (Error) Sum of Squares: df. Error = 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(G 31: G 49), mmult(S 31: AK 49, G 31: G 49)) • b’X’Y: 1) =mmult(transpose(O 38: O 41), O 31: O 34) • b’X’Y: 2) =sumproduct(O 31: O 34, O 38: O 41) • Regression Sum of Squares: df. Reg = p § Y’(P - (1/n)J)Y

Hotel Example – ANOVA, F & t-tests Note: s 2 = MSResid = 4. 52 In the Companion EXCEL Spreadsheet, This is cells J 45: Q 60

Obtaining Fitted Values, Residuals and Diagonal Elements of P (X’X)-1 is in Cells: J 38: M 41 b is in O 38: O 41 “New” X is in B 71: E 89 “New” Y is in F 71: F 89 Computing Y-hat: Highlight cells G 71: G 89: =mmult(B 71: E 89, O 38: O 41) Computing e: In Cell I 71: =F 71 -G 71 Then Double Click on box in Southeast corner of cell Computing Pii: In Cell J 71: =MMULT(B 71: E 71, MMULT($J$38: $M$41, TRANSPOSE(B 71: E 71))) Ctrl-Shift-Enter & Double Click box in Southeast Corner of J 71

Hotel Example rstudent ≡Studentized Residual = e/s{e}

Residuals vs Fitted Values 4. 0000 3. 0000 2. 0000 Residuals 1. 0000 0. 0000 e -1. 0000 -2. 0000 -3. 0000 -4. 0000 0. 0000 2. 0000 4. 0000 6. 0000 8. 0000 10. 0000 Fitted Values 12. 0000 14. 0000 16. 0000 18. 0000