Follow recent stocks/funds for 20 days. You don’t have to wait each day and could use the past 20 days from now. Follow the steps of the questions and form an excel format with it.
Unformatted Attachment Preview
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/ (ETFs are OK).
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.
2. 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
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.
3. (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!
4. 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 ? but you can use your superior quantitative reasoning skills to figure this
c. (2.5 points)Include columns for each stock prices change from the previous day in
both dollar amount and percent.
d. (2.5 points)Include columns for each stock prices 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 days 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!
5. (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.
6. (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.
7. (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.
8. (10 points) Part II (explained under the Project 2 directions) Project 2 Discussion.
Answer the prompt following the instructions for the discussion.
Purchase answer to see full
Need a custom written plagiarism free solution? Click here to order now.