Multiple Regression in Matrix Form Using EXCEL Energy Consumption of Luxury Hotels in - PowerPoint Presentation

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)

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

X Matrix and Y Vector Note: In my current EXCEL Worksheet: 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(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,bX is in Cells B31:E49, Y is in Cells G31:G49X’X and (X’X)-1 are (p+1)x(p+1) ≡ 4x4 in this exampleX’Y and b are (p+1)x1 ≡ 4x1 in this exampleComputing 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 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) -1 to 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)-1 is in J38:M41Highlight (say) Cells S31:AK49 and Type:=MMULT(B31:E49,MMULT(J38:M41,TRANSPOSE(B31:E49))) 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:O41P is in S31:AK49Total (Corrected) Sum of Squares: dfTotal = n-1Y’(I - (1/n) J)Y=Y’Y - Y’(1/n)JYY’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: 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(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: 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 J45:Q60

Obtaining Fitted Values, Residuals and Diagonal Elements of P (X’X) -1 is in Cells: J38:M41 b is in O38:O41 “New” X is 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 P ii : 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 rstudent ≡ Studentized Residual = e/s{e}