For this assignment each Group is required to create a fictitious stock portfolio by investing a total of $100,000
(paper money, of course! – not real money) in five companies in the Information Systems/Information Technology
(IS/IT) Industry, analyze the portfolio's performance using Excel, create a chart to visualize the relative performance of
the stocks in the portfolio and of the Dow Jones Industrial Average (DJIA) index and use the Goal Seek and pivot table
capabilities in Excel. If you do not know what the DJIA is, please read about the index in Wikipedia or a financial news
site.
The assignment will be graded based on the completion of the required parts according to the instructions and not
on the financial performance of the portfolio. You must design the Excel file so it is easy to read. Make sure all
amounts are in a numeric format with two decimal places.
The completion of this assignment obviously requires the usage of Excel. In order to learn and practice Excel,
students can use materials from their CPS 1032 class, or one of the tutorials at Lynda.com (usage is described
here), or other places on the web.
Step 1 – Create an Excel file with the file name being your Group, such as GroupA.xlsx . The file you submit must be in
this format. In Microsoft Excel, each file is called a workbook. You are to submit one workbook with separate
worksheets (tabs) as described below. You should upload the workbook to Google Docs (or OneDrive, etc.) and share
it with your team members as well as with the instructor.
Step 2 – Pick five companies in the IS/IT industry from a leading stock exchange (e.g. NYSE or NASDAQ) like Apple,
Oracle, SAP, etc. These stocks will represent your portfolio which you will track for the last six consecutive months.
Step 3 – Create a worksheet (tab) in your Excel file that is labeled COMPANIES. On this tab, include the stock (or
ticker) symbols of your five companies along with the exchange, company name, corporate headquarters street
address, city, state, and country, zip code, phone, primary business function and number of employees. Note there
should be a separate column for each item of data separated by a comma. This information can be located in the
Company Profile section of the Yahoo! Finance Web site (http://finance.yahoo.com). You may use other sites if you
are familiar with them. Turn this data into a table.
Step 4 – Obtain the stock prices of your selected companies for the last six consecutive months. For each month,
retrieve the open, high, low, close price and volumes, for each of your selected stocks. You may retrieve this
information from the Yahoo! Finance page by entering the stock symbol and selecting “Historical Prices”. Put all the
monthly data in a worksheet and enter the name MONTHLY STOCK PRICE in the tab. The format of this worksheet
should be 6 columns with headings for ticker, date, open, high, low and volume. The close price, to be used in steps
below, can be included as a hidden column. Turn this data into a table.
Step 5 – Create your own (hypothetical) portfolio by distributing $100,000 across your five selected companies. Figure
out the number of shares you want buy from each company. One way to do this is to decide the amount to invest, and
then divide it by the stock price. Do not buy fractions of shares. You may allocate different amounts of money to each
stock, but you must invest money in each of the five companies, and you must invest about $100,000 without going
over. You will hold the stocks for 6 months and then sell them.
You will invest in your portfolio by "purchasing" the shares at the opening prices on the first month and
liquidate it by "selling" all the shares on the last month at the closing prices. Ignore the commission that you would
have to pay, had these been real transactions. For example, on March 1st buy at the opening prices and on August
31st sell at closing prices.
Design another worksheet of your Excel workbook to enter the information and analyze the performance of
your portfolio. Give it the name of PORTFOLIO. Information about both each company and the entire portfolio should
be included.
1. For each company:
a. Display the number of shares purchased (do not enter the formula you used to calculate the
number of shares, enter the actual number of shares purchased), the unit purchase price and the
unit selling price.
b. Develop and enter the appropriate formulas to calculate for each company: the total purchase
price, the gain (loss) and Return on Investment (ROI). The ROI formula is: (sale price – buy price) /
(buy price). Label the columns appropriately. Use percent with 2 decimals for the ROI.
2. For the entire portfolio:
a. Among the 5 stocks, enter the appropriate Excel formulas to calculate the Maximum Gain (or
minimum loss), Minimum Gain (or maximum loss), Maximum ROI, and Minimum ROI (see the
functions MAX and MIN).
b. Enter Excel Formulas to calculate average ROI, Total Gain (Loss), and ROI of the entire portfolio.
(Hint: To compute the ROI on the entire portfolio, use the total gain (loss) on the portfolio and the
total amount invested.)
Put meaningful labels in the cells and format appropriately.
Step 6 – Invest (about – as close as you can without going over) $100,000 in the Dow Jones Industrial Average (DJIA)
for the last 6 months. In order to do that, assume you can “buy” the index at a price equal to its value divided by 100
(e.g. if the index is 17900, then the price is $179.00). You will buy the index at its value at the opening of the market
the first trading day of month 1, and sell it at its value at the end of the last trading day of month 6.
Using the bottom part of the PORTFOLIO worksheet you have already designed, display the number of units
of the DJIA purchased, the opening price, the closing price, the total purchase price, and compute your profit (loss) and
the ROI.
Step 7 – Create a graph to visualize the trading data of the 5 stocks and the DJIA. In a new worksheet, named ALL
CLOSING, organize the monthly closing prices of the 5 stocks in your portfolio, for the period you held the stocks. Also
copy the closing value of the DJIA index divided by 100 to get the "price" of the index for the same time period. The top
of the worksheet should look like the following example:
Date Stock1 Stock2 Stock3 Stock4 Stock5 DJIA
2-Jan-13 $ 57.28 $ 23.62 $ 100.95 $ 4.41 $ 19.98 $ 159.00
Then, create a line graph with all five closing prices and the index, each one being represented by a line of
different color. Make sure the graph has a title, the appropriate legends, and is properly formatted. The graph should be
created in a separate worksheet named GRAPH.
Step 8 – Based on the formulas for ROI and the purchase and selling price for each stock and the Dow (taken from the
Portfolio worksheet), set up a new worksheet called Goal Seek with 4 columns. The first column should be the Ticker
symbol, the second the purchase price, the third the selling price and the fourth the ROI. Select one stock that has
close to the average ROI. Using goal seek, determine the selling price for this selected stock if you wanted an
overall ROI of 10, 8, 5, 2, and -2% respectively for all of your stocks. Show a screen shot of your usage of goal
seek.
Step 9 – Based on the worksheet called Monthly Stock Price, create a pivot table in a separate worksheet called Pivot
Table. Organize the pivot table by company and then by date. Summarize the average open, close and volumes, the
maximum of high prices and the minimum of low prices.
Submit for grading
Your Excel file with tabs labeled COMPANIES, MONTHLY STOCK PRICE, PORTFOLIO, ALL CLOSING,
GRAPH, Goal Seek and Pivot Table. It should be submitted to Blackboard (do NOT convert to Google Sheets format).
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
