Create a spreadsheet in Microsoft Excel.

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.)

1. Identify three companies within that market sector to evaluate.

1. Create a spreadsheet in Microsoft Excel that contains the following information for each of the three stocks:

2. current stock price and the date

2. stock price 52 week range

2. price/earnings ratio for each of the past 5 years plus the trailing 12 months

2. earnings per share for each of the past 5 years plus the trailing 12 months

2. dividends per share for each of the past 5 years plus the trailing 12 months

2. calculate the average for each of the above for each stock

2. create a bar chart that shows the price/earnings ratio over the entire time period for all three stocks

2. create a line chart that shows the earnings per share over the entire time period for all three stocks

2. create a column chart that shows the dividends per share over the entire time period for all three stocks

2. create appropriate titles and headings for your spreadsheet

2. format the spreadsheet so that it looks professional and is easy to read

2. include your name and date at the bottom of the spreadsheet

2. include the URL of the website where you collected the data (i.e. Morningstar)

Tips and Preparation:

1. A great resource for stock information is Morningstar.

1. 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.

1. The quote page will be displayed. Here you can find the current stock price and the 52 week range

1. 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.

1. Click on the Valuation tab. Scroll down to find the price earnings ratio over the past 5 years and trailing 12 months (TTM)

1. 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.

1. Use the Excel chart wizard to create the required charts.

1. 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!