Each team is required to create fictitious stock portfolio by investing a total of $100,000 (paper money, of course! - not real money) in five companies in the Information Technology Industry, analyze the portfolio's performance using Excel, create chart to visualize the relative performance of the stocks in the portfolio and of the Dow Jones Industrial Average (DJIA) index. If you do not know what the DJIA is, please read about the index in Wikipedia or a financial news site.

You must design the Excel files so it is easy to read. Make sure all amounts are in a numeric format with two decimal places.

Step 1 - Create Excel file with the file name being your class, section and your team number (such as MGS3040Section05Team2Excel.xls). 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.

Step 2 - Now, pick five technology companies like Apple, Oracle, SAP, etc. These stocks will represent your portfolio which you will track for a period of the past six consecutive months (not including the current month since we don't have the closing numbers yet - so if the current month is February use the previous August to January months for example).

Step 3 - Create worksheet (tab) in the Excel workbook that is labeled Companies. On this tab, include the stock symbol of your five companies along with the company name, corporate headquarters city, state, and country, company Web site, and number of employees. Make sure that city, state and country are three separate columns. 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 the sites.

Step 4 - Obtain the stock prices of your selected companies for any prior six consecutive months. For each month, retrieve the open, high, low, close prices 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 aworksheet of your spreadsheet and enter the name Monthly Stock Price in the index tab. The format of this worksheet should be 6 columns with headings for ticker, date, open, high, low and volume. Turn this data into a table.

Step 5 - Create own (hypothetical) portfolio by distributing $100,000 across your five selected companies. Figure out the number of shares you want to buy from each company. One way to do this 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, July buy at the opening prices and in December sell at closing prices.

In another worksheet of your Excel workbook, design a spreadsheet 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 on this worksheet.

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 format 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 profit (loss) on the portfolio divided by the total amount invested.)

Put meaningful labels in the cells and format the spreadsheet appropriately.

Step 6 - Invest (about - as close as you can without going over) $100,000 in the Dow Jones Industrial Average (DJIA). 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 23,700, then the price is $237.00). You will buy the index at its value at the opening of the market the first trading day, and sell it at its value at the end of the last trading day.

Using the bottom part of the Portfolio spreadsheet 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 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 time you held the stocks. Also copy the closing value of the 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

Jul-31

$ 57.28

$ 23.62

$ 100.95

$ 4.41

$ 19.98

$168.25

Aug-31

$ 58.28

Sep-30

$ 57.55

Oct-31

$ 67.29

Nov-30

$ 63.28

Dec-31

$ 65.33

Portfolio:this is how the portfolio looks like

Company Ticker Number of Shares Purchased Unit Purchase Price Total Purchase Price Unit Selling Price Total Selling Price Gain/Loss ROI

Apple Inc. AAPL 97 $ 206.43 $ 20,023.71 $ 273.36 $ 26,515.92 $ 6,492.21 32.42%

Facebook, Inc. FB 108 $ 184.00 $ 19,872.00 $ 192.47 $ 20,786.76 $ 914.76 4.60%

Microsoft Corporation MSFT 146 $ 136.61 $ 19,945.06 $ 162.01 $ 23,653.46 $ 3,708.40 18.59%

Alphabet Inc. GOOGL 17 $ 1,181.85 $ 20,091.45 $ 1,339.25 $ 22,767.25 $ 2,675.80 13.32%

Intel Corporation INTC 425 $ 47.12 $ 20,026.00 $ 55.52 $ 23,596.00 $ 3,570.00 17.83%

793 $ 1,756.01 $ 99,958.22 $ 2,022.61 $ 117,319.39 $ 17,361.17 17.37%

Entire Portfolio Maximum Gain Minimum Gain Maximum ROI Minimum ROI Average ROI Total Gain Total ROI

$ 6,492.21 $ 914.76 32.42% 4.60% 17.35% $17,361.17 17.37%

Company Ticker Number of Shares Purchased Unit Purchase Price Total Purchase Price Unit Selling Price Total Selling Price Gain/Loss ROI

Dow Jones Industrial Average DJIA $2,544.90

Monthly data

Ticker Date Open High Low Close Volume

AAPL 9/1/2019 $ 206.43 $ 226.42 $ 204.22 $ 223.97 542,567,100

AAPL 10/1/2019 $ 225.07 $ 249.75 $ 215.13 $ 248.76 608,302,700

AAPL 11/1/2019 $ 249.54 $ 268.00 $ 249.16 $ 267.25 448,331,500

AAPL 12/1/2019 $ 267.27 $ 293.97 $ 256.29 $ 293.65 597,198,700

AAPL 1/1/2020 $ 296.24 $ 327.85 $ 292.75 $ 309.51 733,592,600

AAPL 2/1/2020 $ 304.30 $ 327.22 $ 256.37 $ 273.36 754,962,800

FB 9/1/2019 $ 184.00 $ 193.10 $ 175.66 $ 178.08 264,538,500

FB 10/1/2019 $ 179.15 $ 198.09 $ 173.09 $ 191.65 326,207,200

FB 11/1/2019 $ 192.85 $ 203.80 $ 188.54 $ 201.64 258,303,900

FB 12/1/2019 $ 202.13 $ 208.93 $ 193.17 $ 205.25 276,257,100

FB 1/1/2020 $ 206.75 $ 224.20 $ 201.06 $ 201.91 347,314,600

FB 2/1/2020 $ 203.44 $ 218.77 $ 181.82 $ 192.47 317,576,800

MSFT 9/1/2019 $ 136.61 $ 142.37 $ 134.51 $ 139.03 472,544,800

MSFT 10/1/2019 $ 139.66 $ 145.67 $ 133.22 $ 143.37 549,523,400

MSFT 11/1/2019 $ 144.26 $ 152.50 $ 142.97 $ 151.38 392,371,800

MSFT 12/1/2019 $ 151.81 $ 159.55 $ 146.65 $ 157.70 450,303,300

MSFT 1/1/2020 $ 158.78 $ 174.05 $ 156.51 $ 170.23 558,530,000

MSFT 2/1/2020 $ 170.43 $ 190.70 $ 152.00 $ 162.01 887,625,300

GOOGL 9/1/2019 $ 1,181.85 $ 1,248.02 $ 1,163.71 $ 1,221.14 25,853,600

GOOGL 10/1/2019 $ 1,222.49 $ 1,299.24 $ 1,163.14 $ 1,258.80 30,181,500

GOOGL 11/1/2019 $ 1,265.80 $ 1,333.92 $ 1,259.71 $ 1,304.09 26,438,500

GOOGL 12/1/2019 $ 1,302.56 $ 1,367.05 $ 1,277.05 $ 1,339.39 27,955,300

GOOGL 1/1/2020 $ 1,348.41 $ 1,500.58 $ 1,346.49 $ 1,432.78 33,679,700

GOOGL 2/1/2020 $ 1,461.65 $ 1,530.74 $ 1,268.21 $ 1,339.25 41,560,800

INTC 9/1/2019 $ 47.12 $ 53.33 $ 46.50 $ 51.53 387,955,800

INTC 10/1/2019 $ 51.97 $ 57.24 $ 48.53 $ 56.53 438,267,300

INTC 11/1/2019 $ 55.94 $ 59.13 $ 55.62 $ 58.05 319,751,400

INTC 12/1/2019 $ 58.55 $ 60.48 $ 55.75 $ 59.85 419,629,000

INTC 1/1/2020 $ 60.24 $ 69.29 $ 58.52 $ 63.93 534,150,100

INTC 2/1/2020 $ 64.46 $ 68.09 $ 53.60 $ 55.52 441,521,000

Section B.

Transcribed Image Text:

(b) Sailor Transport Company bought a new ship and had the following revenue and expenses during the first year: (ii) Revenue: Operating expenses: Salaries: Fuel cost: Depreciation: Interest expense: $200,000 $ 80,000 $ 15,000 $ 10,000 $ 12,000 $ 8,000 There are no other revenues and costs except as stated above. Sailor's corporate tax rate is 30%. Assume depreciation was the only non-cash item and ignore other working capital changes in the cashflow calculation. (i) Compute the company's net income for the year. Show the calculations. (5 marks) Present the company's net cashflow. Show the calculations. (5 marks) (b) Sailor Transport Company bought a new ship and had the following revenue and expenses during the first year: (ii) Revenue: Operating expenses: Salaries: Fuel cost: Depreciation: Interest expense: $200,000 $ 80,000 $ 15,000 $ 10,000 $ 12,000 $ 8,000 There are no other revenues and costs except as stated above. Sailor's corporate tax rate is 30%. Assume depreciation was the only non-cash item and ignore other working capital changes in the cashflow calculation. (i) Compute the company's net income for the year. Show the calculations. (5 marks) Present the company's net cashflow. Show the calculations. (5 marks)

Answer rating: 100% (QA)

ANSWER To address this assignment well follow the steps outlined Step 1 Create an Excel file named MGS3040Section05Team2Excelxlsx with separate worksh... View the full answer