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 250256 ID: 425648
Download Presentation The PPT/PDF document "Multiple Regression in Matrix Form Using..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
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.Slide2
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)Slide3
Regression Model (n = #
obs
, p=#predictors)Slide4
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.Slide5
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
ABCSlide6
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-EnterSlide7
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
.Slide8
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
matrixSlide9
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
)
YSlide10
Hotel Example – ANOVA, F & t-tests
Note: s
2
=
MSResid = 4.52 In the Companion EXCEL Spreadsheet, This is cells J45:Q60Slide11
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 J71Slide12
Hotel Example
rstudent
≡
Studentized
Residual = e/s{e} Slide13