# UGBA06 - Summer 2008 Ismail Ceylan UGBA 103 Discussion...

This preview shows pages 1–3. Sign up to view the full content.

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 (Jan-Jun08 Numbers) in “Portfolio Variance Made-up+Estimated.xls” posted on Bspace. In the ±rst table (top-left), 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 Document
Summer 2008 Ismail Ceylan UGBA 103 Discussion Section June 16, 2008 Page 2 of 4 Next, we calculate the Variance-Covariance 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.

## This note was uploaded on 02/12/2012 for the course UGBA 101A taught by Professor Mccullough during the Spring '08 term at Berkeley.

### Page1 / 4

UGBA06 - Summer 2008 Ismail Ceylan UGBA 103 Discussion...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online