Assignment 3

ASSIGNMENT 3
DUE IN CLASS ON TUESDAY, OCTOBER 19TH, 1999
There are two required parts to completing this assignment (details)
(a) email the spreadsheet to Betsy (section 3) or Dan (section4) with a copy to Arun, and
(b) submit the report in class

This assignment is to be done in teams of three to five students. We do not factor in the number of people in your team while grading.

Your project is to create an Internet/technology stock index. The ten companies in the index are AOL, Amazon, @Home, E*Trade, Yahoo, Microsoft, Sun, Cisco, Oracle and Lucent. These are the ten stocks you worked with in lab session 2. Start with the spreadsheet you created during the lab.

The index has a base value of 100 on 12/31/98. You already know what the share prices of these companies were on 12/31/98 (based on the spreadsheet from lab session 2). You need to track and record the closing prices (last trade) of the stocks on the following additional eight days: 10/05/99 (already in the spreadsheet), 10/06/99, 10/07/99, 10/08/99, 10/11/99, 10/12/99, 10/13/99 and 10/14/99. After you have recorded this information, compute the value of the index on each of these days. Also compute the value of each category index (TECH and INTERNET) on each of the days. Use the methodology we used in the lab session. To find the value of a category index, use the same methodology you use for the overall index, but for just the five stocks in the category.

Your report should contain:

Title page: Choose a name for your index, and put this, along with your names, on this page. A sample name, for instance, is 'The Stern Technology Index' (though it would be nice to see more creative names......)
Page 1: A four to five line description of your index. Include the names of the companies, the two categories of stocks, and a brief description of the methodology you used to create the index.
Page 2: A table showing the value of the overall index, and the two category indexes, for each of the nine days (12/31/98, and the subsequent eight)
Page 3: A table showing the prices of each of the ten stocks, for each of the nine days.
Page 4: A table (two columns) showing which of the stocks went up from 10/05/99 to 10/14/99, and which went down. Use the IF function. Use either + and - or UP and DOWN to depict which ones went up and down respectively.
Page 5: A bar graph showing the percentage increase or decrease in price of each of the ten stocks, from 12/31/98 to 10/14/99. The percentage increace/decrease is equal to { [(stock price on 10/14/99)/(stock price on 12/31/98)] - 1} * 100 (if the price went down, this would be a negative number). If you use the % formatting, you needn't multiply the fraction by 100.
Page 6: Two pie charts, both on the same page. The first should show the percentage each category contributed towards the index on 12/31/98. The second should show the percentage each category contributed to the overall index on 10/14/99. Include labels and percentages on both the pie charts.
Page 7: One line graph depicting the movement of all three indexes (overall, TECH and INTERNET), from 10/08/99 to 10/14/99. Each line must have five equally spaced data points, one each for the five dates 10/08/99 - 10/14/99. Mention at the bottom of the graph that 12/31/98 corresponds to an index value of 100.
Page 8: One line per item, describing:

The price that Cisco (CSCO) would have needed to reach on 10/14/99 for the overall index to have reached a value of 200
The price that Microsoft would have needed to reach on 10/11/99 for the overall index to have the same value as the TECH index
The price that AOL would have needed to hit on 10/13/99 for the INTERNET index to have the same value as the overall index
The amount of money a person would have gained or lost by the end of 10/14/99, had he or she invested

$2,000,000 in your overall index after the end of trading on 12/31/98 (i.e. the investment was made at the closing prices on 12/31/98).
$1,000,000 in each of your two category indexes (TECH and INTERNET)after the end of trading on 12/31/98 (i.e. the investment was made at the closing prices on 12/31/98).

Page 9: A blank sheet for our comments.

STAPLE YOUR SHEETS TOGETHER!! Use a folder if you wish. Try and make your report as neat and presentable as possible -- presentation and clarity count for a great deal in the real world, and you should work towards acquiring skills in this area (play around with formatting, page setup and so on -- there's lots of information in the Excel tutorial about these things).
Your spreadsheet should contain all numbers behind the information in your report. However, it need not be ordered in any way, or organized sequentially -- to create the report, you can either:

print the pages one by one
create a worksheet for each part, and print the entire workbook (this makes life a lot easier, and is recommended -- its good practice for the future)
copy the Excel information sequentially into Word to create a complete document.

If you choose to do the first (printing separate sheets), number the pages by hand; if you use Word, or create a separate worksheet for each part, you can use automatic page numbers. In either case, your report must have page numbers.

Deliverables:

The report, as described above, to be submitted in class on 10/19/99.
The spreadsheet which you used to generate the report.

This spreadsheet file should be emailed to Betsy (eas226@stern.nyu.edu) if you are in Section 3, or to Dan (drm215@stern.nyu.edu) if you are in Section 4, with Arun (asundara@stern.nyu.edu) copied in in either case.
Call the Excel file firstname_lastname.xls, and use the name of the first person (alphabetically by last name) in your group. Leave out your middle name. For instance, if your name is Simone deBeauvior, your spreadsheet should be named simone_debeauvior.xls, while if your name is Samuel Taylor Coleridge, your spreadsheet should be named samuel_coleridge.xls.
Your email message subject should be 'Assignment 3'. The message text should contain just all your names