Stock Portfolio project in excel, please read the requirements

Project 2:

Stock Portfolio $$$$$

DUE: _______________

MIDNIGHT 

You have $100,000 to invest in the stock market.  In this project you will use Excel to keep

track of your stocks or mutual funds, and monitor their performance, over the

period of ______________ to __________________.

1. (10 points) You must choose at least 10 stocks/mutual funds to purchase with at least 1 fund. Use websites such as www.cnbc.com or www.google.com/finance or www.marketwatch.com to research stocks and mutual funds http://time.com/money/5090045/best-mutual-funds-2018/ (ETF’s are OK). 
2. 
   
3. 
   

In the REAL-TIME QUOTE box, type in the name of the company you

are interested in, and click on Symbol Lookup. 

You will be given the stock

symbol for that company which you can type in to get a “QUOTE”.  A quote gives you the current price (“Last

trade”) for 1 share of the stock as well as other information.  The stock market is only open Monday through

Friday during business hours.  For

example, Apple has the symbol AAPL.

1. Create a workbook with multiple worksheets. 

a.

(5 points) The first sheet should list the names and symbols of the stocks/funds

you purchased as well as the purchase price and the number of shares.  The purchase

price will be the opening price

on ____________________.  You may use the “1 Month Chart” to trace along the curve and get prices for any days

you may have missed.

b.

(5 points) Create a column for the cost of

buying each stock which should include the $9.95 fee to buy the shares for each

company. 

c. 

(5 points) Create a column for the PE ratio

(Price to Earnings) of each Stock by looking up PE ratio in the stock quote.  Create a column for the earnings per share

for each stock, use the purchase price

and PE of each stock to compute

the earnings per share (do not look up EPS there should be a formula here).

d.

(5 points) You are required to spend as much

of the $100,000 as possible, being within a few dollars of $100,000 is fine.  No one stock may be worth more than $50,000

of the portfolio or less than $2,500.

1. (10 points) On the first sheet create columns for the business days over the length of this project. In these columns you will enter the closing price of the stock for each day. You may use the chart to trace along the curve and get prices for any days you may have missed. This is the ONLY sheet where you will enter stock prices, all other sheets will link to this first sheet!

1. Create worksheets for each day which are linked to first worksheet. You are going to format just one of these worksheets and then use the Move or Copy Sheet command under the Edit menu to create carbon copies.

a.

(2.5 points)

Label the sheet tab for each day with the date

for each business day, MON-FRI for the period of this project.

b.

(2.5 points)Each sheet will include the opening and closing price for all the

stocks that day.  You will link to the first sheet to get

these.  WARNING:  The first sheet has closing prices, you will

use the previous close for the new open, this is potentially confusing J but you can use your superior

quantitative reasoning skills to figure this out!

c. 

(2.5 points)Include columns for each stock

price’s change from the previous day

in both dollar amount and percent.

d.

(2.5 points)Include columns for each stock

price’s change from the original

purchase price in both dollar amount and percent. 

e.

(2.5 points)Create a column which computes the total $ value of each of your

stocks.   This entails the closing price that day and

the number of shares.  SUM this column to get the daily

portfolio $ value.

f. 

(2.5 points)Include columns which give the change from the previous day’s total for

each stock in both dollar amount and percent.

g.

(2.5 points)Include columns which give the change from the original total cost for

each stock in both dollar amount and percent.

h.

(2.5 points)Compute the total percentage gain

for your entire portfolio value relative to the original cost.  Make this cell nicely formatted and highly

visible!

1. (10 points) On each daily worksheet create a nicely labeled pie chart showing all of the stocks you own, and the percentage for each of the total portfolio value.

1. (10 points) Insert a line graph for each stock on a separate worksheet showing the closing price of each stock for every day in this period.

1. (10 points) Create cells on the first sheet that will tell you the highest price of each stock for this period and the highest value of your portfolio.

1. (10 points) Upload your completed workbook in Project 2 submission folder and Discussion Board forum in Blackboard. When you upload the project to the Discussion Board, include a 3-4 sentence reflection on the project. Reflections should show that you have thought about the actual information on the assignment and the results of your work. Review at least two of your classmates’ projects in the discussion forum and post a substantive reply to each.