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 __________________.
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.
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.
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!