2 short projects

Lesson 5 Project: Microsoft Excel Spreadsheet for Stock Evaluation

Student Learning Outcomes:

• Research and find relevent and reliable information using the Internet. • Demonstrate a basic descriptive and operational knowledge of major office

productivity software (Microsoft Excel) • Find, select, analyze and present information using major office productivity

software (Microsoft Excel)

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: o current stock price and the date o stock price 52 week range o price/earnings ratio for each of the past 5 years plus the trailing 12 months o earnings per share for each of the past 5 years plus the trailing 12 months o dividends per share for each of the past 5 years plus the trailing 12

months o calculate the average for each of the above for each stock o create a bar chart that shows the price/earnings ratio over the entire time

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

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

time period for all three stocks o create appropriate titles and headings for your spreadsheet o format the spreadsheet so that it looks professional and is easy to read o include your name and date at the bottom of the spreadsheet o include the URL of the website where you collected the data (i.e.

Morningstar)

Tips and Preparation:

• A great resource for stock information is http://morningstar.com

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

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

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

o 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:

• Your project should be on one spreadsheet. You should have just 3 charts.

• If you have questions or need assistance, let your instructor know.

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 6

Excel spreadsheet has appropriate and complete information as specified above for all 3 stocks

30

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 10

Spreadsheet is formatted in a professional manner 10

Average calculations are present and correct (1 point each) 9

Charts are created with the correct type (1 point each) 9

Information is charted correctly with clear labels (2 points each) 18

Spelling and grammar are correct throughout 8

Total 100