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.

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more