Computer Science homework help.

Microsoft Excel Spreadsheet for Stock Evaluation

This project supports Student Learning Outcome 2 from your Course Syllabus.

Purpose:

Create a spreadsheet in Microsoft Excel that will compare stock information for three companies within the same market sector.

Requirements:

1. Select a market sector that you would like to evaluate (i.e. health services, technology, energy, etc.)
2. Identify three companies within that market sector to evaluate.
3. Create a spreadsheet in Microsoft Excel that contains the following information for each of the three stocks:
   • current stock price and the date
   • stock price 52 week range
   • price/earnings ratio for each of the past 5 years plus the trailing 12 months
   • earnings per share for each of the past 5 years plus the trailing 12 months
   • dividends per share for each of the past 5 years plus the trailing 12 months
   • calculate the average for each of the above for each stock
   • create a bar chart that shows the price/earnings ratio over the entire time period for all three stocks
   • create a line chart that shows the earnings per share over the entire time period for all three stocks
   • create a column chart that shows the dividends per share over the entire time period for all three stocks
   • create appropriate titles and headings for your spreadsheet
   • format the spreadsheet so that it looks professional and is easy to read
   • include your name and date at the bottom of the spreadsheet
   • include the URL of the website where you collected the data (i.e. Morningstar)

Tips and Preparation:

• A great resource for stock information is Morningstar.
• To find stock information, enter the stock symbol or company name in the Quote box in the top center of the page to the right of the Morningstar logo.
  • The quote page will be displayed. Here you can find the current stock price and the 52 week range
  • Click on the Key Ratios tab. Here you will find the Earnings/Share and Dividends/Share for the past 10 years and trailing 12 months (TTM), but you only need the past 5 years.
  • Click on the Valuation tab. Scroll down to find the price earnings ratio over the past 5 years and trailing 12 months (TTM)
• Use Excel functions to calculate the average dividends per share, earnings per share and price earnings ratio for the past 5 years data that you collected.
  Note: Do not include TTM (trailing 12 months) in the averages, since this data overlaps the data from the most recent year and would skew the results.
• Use the Excel chart wizard to create the required charts.
• Your final spreadsheet should look similar to this:

Grading Rubric:

The following Grading Rubric will be used to evaluate your work. Use it to help maximize your score!

Requirements	Points
Selected 3 stocks within the same market sector	10
Excel spreadsheet has appropriate and complete information as specified above for all 3 stocks	15
Current stock price and date
52 week price range
Price/Earnings ratio – 5 years + Trailing 12 Months (TTM)
Earnings/share – 5 years + Trailing 12 Months (TTM)
Dividends/share – 5 years + Trailing 12 Months (TTM)
Spreadsheet is clearly organized and formatted in a professional manner	5
Average calculations are present and correct	5
Charts are created with the correct type	5
Information is charted correctly with clear labels	5
Spelling and grammar are correct throughout	5
Total	50

 

Computer Science homework help