Exercise 3-creating a portfolio | Business & Finance homework help

Creating a portfolio (port.xls):
 In this exercise, you are given actual stock price data from an actual portfolio. [I used to update these data every semester, but I’d usually make a little error or two in the update, so you get an old data set that I’m fairly confident is clean.] You are to use the raw pps data (tier1) to generate market values (tier2) and daily returns (tier3). The three tiers together make a portfolio spread sheet that we’ll call port.xls. The daily returns data will be used in subsequent exercises to illus trate portfolio management concepts. Note: Prices have been adjusted for stock splits. Di vidends are ignored. Market data (last column) is the S&P500 Index
There are a couple of old screen shots (videos called Tier2 and Tier3) posted to the Moodle web site. You might find these helpful in visualizing the operations that you are being asked to per form, but be aware that the data sets in the screen shots are different than your data set.
A sample structure of port.xls can be seen in sample3. Your port.xls will be much larger than the sample. The old symbol for market data shown in sample3 is ^SPX whereas the current symbol for the S&P500 is ^GSPC.

1. Open the Excel spreadsheet file called “portmaster.xls”.
2. Save a copy to your local drive. This first block of data (approximately 250 trading days times 30 firms) is called “tier1”. The data comes from finance.yahoo.com, Historical Prices. Hint: As you build your formulas, you might try testing them in “sample3” to see if they work (i.e. see if you get the same results as in the sample)
3. Build a second block of data (tier2, market values) directly and immediately below tier1. Start by copying all the dates from tier1 into tier2 [I now notice that this has already been done for you]. Next, calculate the market value (pps x number of shares) for every day for every firm.
Only do this for the firms (not for the total portfolio or for the market index). Your formula should look roughly like “=C$3*C4” where C3 is the “no of shares” and “C4 is the “pps”. The dollar sign ($) in C$3 will hold the row constant at row 3 as you copy the formula down and across for every day and every firm.
4. Next, calculate the market value of the portfolio (col. B) = the sum of all the stocks’ market values. Note: The S&P500 data [^GSPC] should be copied from tier1 into tier2. This market data is being shown for comparison and is not part of the portfolio.
5. Build a third block of data (tier3, daily returns) directly and immediately below tier2. Again, make sure that you have the dates in Column A. Calculate the daily returns for every stock for every day (except for the first day). Use the basic model of (new-old)/old. [What happens if you use new-old/old?]. You cannot calculate a return for Day1 because you don’t have the data for Day0. The first daily return is on should be in Day2 and the formula should be =(day2 daily re turn minus day1 daily return) divided by (day1 daily return). But in your formula, use cell desig nations. Write the formula once, then copy it to all days and all firms. Also, calculate these daily
returns for the portfolio and for the S&P500 index (this needn’t be an extra step – you can calcu late daily returns for the portfolio, all the firms, and the market all in one copy- formula opera tion.

5) Calculate Standard Deviation using the Excel function =STDEVP on the daily returns for each stock, for the portfolio, and for the S&P 500. Place these data in a row under tier3 (see the sample3 for an example).
6) Calculate total annual returns by using “(lastday marketvalue – firstday marketvalue)/firstday marketvalue” to calculate total annual returns for each stock, for the portfolio, and for the S&P 500. Place these data immediately under the standard deviation data. In the sample3 the total re turns are only for six days. In your port.xls, the total returns are for a year.
7) Send your port.xls through Moodle2 “port.xls” assignment link, the same way you’ve been sending previous assignments. Remember- accompanying comments are appreciated.