# Investment Tracker Spreadsheet

Document Sample

```					Spreadsheet Integration Activity                      1                                   Kerry Rice
Stock Market Tracking Lesson Plan

Topic: Tracking Stock Performance/Spreadsheet

Lesson Goals:

    The learner will be able to utilize the formula and function components of an Excel
    The learner will track the performance of a chosen stock for an extended period of time.
    The learner will be able to create charts depicting data results using MSExcel.

Technology Model: Show relationships/What if thinking

Set Up: Groups of 4-5 – 55 minute class period for initial introduction/Small increments of time for the
next 6 weeks to several months.

Materials: Minimum of 1 computer per group

Procedures:
 This lesson is intended to follow the Stock Market WebQuest and research activities from
previous lessons. Groups should have chosen stocks meeting the criteria of their investment
roles. If students are working individually on the Excel portion of this unit, make sure that
groups have chosen enough different stocks so that each student has 1 stock to track. It is
assumed that students have rudimentary knowledge and skills regarding graphs and charts.

    Review spreadsheets. Open Excel. Discuss worksheets, workbooks, cells, etc. Students follow
the steps in the handout to create a uniform spreadsheet format.

    Students use the Stock Market Tracking handout to set up their tracking spreadsheet and make
subsequent entries of data.

    At the end of the stock market performance period, students will use the chart wizard to
create a line chart showing “profit or loss” for their stock.

    Groups will reconvene and create a display showing the performance record of their
“investment portfolios”.

Grouping Strategies:
Computers needed: In the initial activity, students will need access to computers one on one, if the
goal is to expose all students to instruction on the uses and functions of spreadsheets. If this is not the
case, and students are merely entering data, then 1-5 computers are sufficient for inputting data and
information as this can be accomplished in short amounts of time spread over the whole class period or
even days.

This lesson adapted from the Microsoft in Education lesson; Buy Low, Sell High found at
Spreadsheet Integration Activity              2                                      Kerry Rice
Track Your Investment Handout

Now that you’ve made the leap and decided in what stocks to invest your \$10,000.00, it’s important to
track how well your investment is doing. Over the next several weeks, we will check on our
investments periodically and record the data in an MSExcel Spreadsheet. Each individual in your group
will be held responsible for tracking 1 stock. At the end of our tracking period, your groups will
reconvene and prepare a presentation informing the rest of the class on the performance of your
investments.

Open MSExcel. Since you will only be tracking one stock, you will only need to utilize one worksheet.
For your company, you will input (a) some basic information about the company, such as the company
symbol and the stock exchange where listed; (b) an Initial Investment section; and (c) a Daily Record
section.

Getting started step by step:
1. In cell A1, type the company name.

2. Highlight A1:E1; then click Merge and Center on the Formatting toolbar to center the
company name.
3. In cell A3, type "Symbol:" followed by the company's four-letter symbol, for example,
ACME; in cell E3, type "Initial Investment" followed by the total amount of money (number
of shares times the price per share) you are investing in that company. Your total
investment equals the total price paid for the stock.

4. In cell A4, type "Exchange:" followed by the name of the exchange listing the stock.
TIP! To enlarge a cell, point to the column border, the cursor will change to a two-headed
Narrow. Now, drag the column to the desired width.

TIP! Format all currency for two decimal places by choosing Cells under the Format menu,
clicking the Number tab, and then selecting Currency under Categories. In the Decimals places
enter the number 2.
Spreadsheet Integration Activity                3                                      Kerry Rice

Initial Investment section:

1. In cell A6, type “Initial Investment and change to bold face type.

2. In cell A7, type “Date of Transaction”

3. In cell C7, type “\$/Share”:

4. In D7, “#/Shares”; in E7, “Total Investment”.

5. Highlight A7:E7

6. Right click the highlighted cells, then choose Format cells.

7. Click Alignment, select Center.

8. In cell A8, type the date of the initial transaction.

Format Cells dialog box
1. In cell C8, type the cost per share (e.g., 20 3/4) as listed on the stock exchange. Microsoft
Excel will automatically convert this figure to decimals. Be sure to format the number for two
decimal places by right clicking on the cell and choosing format cell.

2. In cell D8, type the number of shares bought. In cell E8, type the formula: =C8*D8 and
press enter. The total cost of your investment will automatically be calculated. Format to two
decimals places using the same procedure as above.
Spreadsheet Integration Activity                 4                                       Kerry Rice
The Daily Record Section:

1. In cell A9, type "Daily Record."

2. In cell A10, type "Date of Transaction"; in B10, "#/Shares"; in C10, "\$/Share"; in D10, "Current
Value"; in E10, "Profit/Loss".

3. Highlight A10:E10. Center, wrap text and make bold.

4. Beginning with cell A11, highlight the range of data in Column A you will be entering on your
worksheet, for example , A11:A22. Right click the highlighted cells. Choose Format Cells. Click
the Number tab. Click Date, then choose Type of Date. Click OK.

5. In cell B11, type the number of shares bought. Ask the Office Assistant "How do I fill down?"

6. In cell C11, record the price of your stock on the first date of transaction.

7. In cell D11, type the formula: =B11*C11.

8. In cell E11, create a formula that calculates Profit/Loss by taking the Current Value (D11)
minus the initial investment (E8). The formula should refer to D8 as an absolute reference-
=D11-\$E\$8.

Highlighted data to create Microsoft Excel chart
how to format Column E for currency.
Spreadsheet Integration Activity                          5                                Kerry Rice
Tracking Stocks

At the end of the stock performance period, use the Chart Wizard in Microsoft Excel to create a line chart
showing "profit or loss" for each stock you invested in.

Highlight the range of data you've entered on your worksheet, for example, E12:E23 . Click Chart from
the Insert menu, then choose Line and step through the procedures for creating a line chart. Be sure to
add a chart title. Label the chart's X and Y axes.

Line chart created in MSExcel showing profit/loss

Why does the line chart do a better job of representing the stock data than a pie chart? For more help,

This handout adapted from the Microsoft in Education lesson; Buy Low, Sell High found at