Summer 2008
Ismail Ceylan
UGBA 103
Discussion Section
June 16, 2008
Page 1 of 4
Discussion Section #6
Outline
•
MS Excel: The dollar sign and the sumproduct function
•
Questions from Final Exam Summer 07
1
MS Excel: The dollar sign
Please refer to the second worksheet (JanJun08 Numbers) in “Portfolio
Variance
Madeup+Estimated.xls”
posted on Bspace.
•
In the ±rst table (topleft), we ±nd the dollar amount and the fraction invested in each security.
We ±rst manually enter the number of shares purchased and the price of each security in
columns C and D. Then, we enter “=D2*C2” in cell E2. This yields the dollar amount
invested in MSFT. When we copy this formula (by dragging) to the cells corresponding to
other securities, we get:
D3*C3 in E3
D4*C4 in E4
D5*C5 in E5
D6*C6 in E6
As can be seen above, the formulas are replicated relative to the cell in which we want the
result. This feature is called
relative cell referencing
.
In the last column of this table, we calculate the fraction of the wealth invested in each
security. We again start with MSFT and enter “=E2/E7” in cell F2. This gives the fraction
invested in MSFT, however when we copy this to the other cells, we get:
E3/E8 in F3
E4/E9 in F4
E5/E10 in F5
E6/E11 in F6
This is wrong because we
always
want to refer to cell E7 (which is the total amount of money)
in the denominator. But, because of
relative cell referencing
, the denominator changes when
the formula is copied to another cell. To prevent Excel from changing the reference to cell
E7, we use the dollar sign:
$E$7 tells Excel you always want to refer to cell E7.
Thus, instead of “=E2/E7”, we enter “=E2/$E$7” in F2 and copy the formula to the other
cells to get:
E3/$E$7 in F3
E4/$E$7 in F4
E5/$E$7 in F5
E6/$E$7 in F6.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentSummer 2008
Ismail Ceylan
UGBA 103
Discussion Section
June 16, 2008
Page 2 of 4
•
Next, we calculate the VarianceCovariance matrix using the ”Estimated Correlation Matrix
and SDs”. We will use the following two rules:
$A1 tells Excel you always want to refer to column A.
A$1 tells Excel you always want to refer to row 1.
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '08
 MCCULLOUGH
 Standard Deviation, Variance, Ismail Ceylan

Click to edit the document details