Dr. Sarah's Lab 3 - Stock Market and Homer Tax

NAME_________________________________ Circle Class   9:30 or 11:00
Purpose: To learn about the stock market and taxes, learn about advance searches on the web, and transition from Financial Mathematics to Statistics, our next segment.
For homework you read about the stock market and completed a number of web searches that you will use today. The stock market is at the center of the United States economy. Even if you have no money in the stock market it does still affect you. You will track your stock over the course of the semester, so keep this packet handy. In future labs, we will use the data that you collected (and sent yourself on WebCT) and the graph that you printed out. Dr. Sarah will model the following for you. Afterwards, you should follow the directions yourself to complete the lab.
  • In lab, From INTERNET EXPLORER, click on this lab from the main class web page. Recall that underlined links can be accessed by clicking on them on the web, and that "Back" will take you back to this web page.

    Total "Purchase" Price

    On a SEPARATE PIECE OF PAPER, choose your favorite stock symbol from your homework. Then answer the following questions on that other piece of paper.
  • What is your stock symbols name?

  • What does this symbol stand for?

    Go to finance.yahoo.com, and enter your symbol in and then hit the "Get Quotes" key. Then click on "Chart", which is found under More Info. In the larger table of the stock below the picture, you may see Bid and Ask. Bid is the selling price, and Ask is the asking price that you must purchase at. The Ask price (purchase price) is higher than the Bid price (selling price). This makes sense since, for example, farmers sell their products for much less money than we pay for the products because distributors earn the difference. The stock market works the same way.
  • What is your Ask price? If you don't see an Ask price then follow these directions: (If you have your Ask price listed then go to the next bolded "dot".) Note that if the market is closed, or if your stock does not have 4 letters, then you may see n/a next to bid and ask. Each stock does have bid/ask prices, but only the Nasdaq stocks are publicly available (you could get the others by having an account at e-trade, or by asking a stock broker, but that is too complicated for now.)
    So, ONLY if you don't already have an Ask price, then take the Last Trade price and write that down here.
    Your ask price will be the Last Trade price +.125, so now fill in your ask price above. Show work.
  • Note that the Ask price is a price per share. We will "buy" 100 shares. But this isn't the total cost, since we must also pay a commission fee. The following is from e-trade.
    Nasdaq (4 letter name): 19.95                    Other: 1, 2 or 3 letter name: 14.95
    You pay these fees each time you buy or sell. They may seem high, but standard commission fees were a couple of hundred dollars before the (relatively recent) advent of internet trading sites.
  • What is the total cost of purchasing your stock ( Ask price x 100 + commission fee)? SHOW WORK!

  • Show me your correct work. First come-first served. Ie - the first person who correctly does this and gets approved by me gets to track that stock. Everyone in the class must have a different stock to track. Once Dr. Sarah has approved your choice and math, transfer the information from your piece of paper to this sheet and then continue.

    Getting Historical Data from Yahoo and Converting it to an Excel Chart Follow Carefully!

    Finding the Data Open up a new browser (under File release on New Window) in Internet Explorer and type in
    BUT replace nite with your stock symbol (the 1,2 3, or 4 letters).
  • What is the Start Date?                           What is the End Date?

    Scroll down to the bottom and click on the link Download Spreadsheet Format You will either see the data come up automatically or you must open it yourself from Word. Notice that commas separate the different data points. Excel only recognizes tabs as separators, so we are going (see below) to replace each comma with a tab before we put it in Excel.
    Under Edit, release on Select All, then under Edit release on Copy.

    Changing the Data In Word so that Excel Can Read It
    Open up Word, and under Edit, release on Paste.
    In Word, under Edit, release on Replace
    Next to Find what, type    ,           (We type a comma since we want to replace that with tabs.)
    Next to Replace with, type    ^t           (^t is Word's symbol for a tab.)
    Hit Replace All, then Ok. Close the Replace box.
    Under Edit, release on Select All. You will see that all of the data is now highlighted.
    Then go back under edit and release on copy.
    Under File, release on quit to quit Word (save the Word file in the public folder).

    Working With the Data in Excel to Create a Chart
    Open up Excel and under Edit, release on paste.
    Notice that the Date will be in the first column, Open price in the 2nd, High price in the 3rd, Low price in the fourth, Close price in the fifth, and Volume in the sixth.
    If you see ##### or something like 7E+05 anywhere, that just means that Excel does not have enough room to display the entry. Make the column wider by going to the top right of the column, clicking when you see a small letter t with the horizontal part of the t actually as arrowheads, and holding down as you make the column bigger by pulling it to the right. You will probably need to make columns B and F wider.

    If row 1 is a blank row, and the words "date, volume,..." don't begin until row 2, then click on the gray 1 at the left of the row and then under edit release on delete.

    Whenever we are working with data, we first want to put it in increasing order.
    Notice that our data is in decreasing order, as the date goes from the most recent to the least recent.
    Click on A1. Click on the Sort Ascending icon in Word (Under View, make sure that Toolbars/Standard is checked since the Sort Ascending button is located on this.)
    Notice that now the date is in increasing order.
    We want to change one more thing before we create an Excel graph.
    Click on B1
    Under Insert, release on Columns.
    Now there is an empty column between date and open.
    Click on G (which is now where the Volume is located)
    Under Edit release on Cut
    Click on B (the empty column)
    Under Edit release on Paste
    Notice that now our columns look like Date-Volume-Open - High -Low - Close

    Saving Your File Save your excel document into the public folder/save files here folder as
    for example drsarahnite.xls

    To create our Excel graph
    Click on B
    Click on the key on the keyboard that has an apple with a bite taken out of it and hold this key down.
    Continue to hold the apple key down as you click on C then D then E then F.
    Then columns B thru F will all be selected.
    Under Insert, release on Chart
    Scroll down to stock and click on it
    Under Chart sub-type, the bottom right picture should be highlighted (Volume-Open-high-low-close)
    Press and hold to view a sample to ensure that you see something like this NITE Excel graph link
    Click on Next
    Click on Next a second time
    Under Chart title - type your stock symbol and your name.
    For example NITE - Dr. Sarah
    Then click on Finish.

    Save your excel document again.
    Your excel document with the picture should look something like this link, although the dates, data and picture itself will be for your stock.
    Have Dr. Sarah check that it is correct before you print and send it as an attachment on WebCT.
    You will need a printing card in order to print your graph.
    Click on the graph (it will have little black boxes spaced around the edges when you do this) and then print a copy. If you do not have a printing card with you, then print this sometime outside of class by accessing it from WebCT.

    Send yourself and Dr. Sarah a copy via posting it to the forum containing just you and I.
    Go to the main WebCT page
    Click on the Bulletin Board
    Click on Compose Discussion Message
    Make sure that the Topic says that is is the forum containing only you and I.
    In the Subject field type in your first name and your stock symbol (example Dr. Sarah - NITE)
    In the Message field re-type your first name and your stock symbol (example Dr. Sarah - NITE)
    Scroll down and click on Browse... which is next to the Attach file button.
    Look in the public folder on the desktop for your file (example drsarahnite.xls) and click on Open
    Click on Attach
    Once the file has been added, click on OK
    Click on Send. Click on Post.
    Click on Update Listing.
    You will know if you have been successful if you see a paperclip next to the message.
    You will use this again in a future lab, so do not delete it.

    Staple your completed homework and the printed graph to the back of this lab.

    Dr. Sarah's Homer Tax Lab

    Homer realizing that he has a "to-do" pile with tax info on top.

    Homer frantically trying to do his taxes

    Homer throwing his tax return into the closing post office doors.

    Homer being hauled off by the IRS for a severe audit.

            While the Simpsons are humorous, we only use them in class when they can help us learn mathematics or serve some other important course goal. In this case, we use the Simpsons to learn about advanced searching techniques on the web, and to lighten up the tax return process! You will be responsible for advanced web searching techniques. Also, be careful to answer all questions beginning with a bold dot and to follow directions very carefully. We'll begin by watching Homer get into trouble when filling out a false tax return in the Trouble with Trillions 5F14 (4/5/98). If you are waiting for us to do this together, continue on ahead.

    You may have heard about Bush's tax cuts and the possible effects on the economy. Your taxable income determines the percentage that you are taxed at. The first part of everyone's taxable income is taxed at the lowest rate, and portions above a cutoff amount are taxed at higher rates. For tax years beginning in 2001, the 28%, 31%, 36%, and 39.6% graduated income tax rates have been reduced to 27.5%, 30.5%, 35.5%, and 39.1%, respectively. A portion of your income that would be subject to the 15% tax rate is reduced to 10%. We are going to fill out a tax return for Homer by using your homework.

    The government estimates the average amount of deductions (income that is not taxable) for each class below and calls this average the standard deduction. We want the highest possible deduction since this gets subtracted off of our pay before we pay taxes (and we want to pay the least possible taxes). For 2001, the standard deductions are

    Single: $4550.00 Married filing jointly: $7600.00
    Head of Household: $6650.00 Married filing separately: $3800.00
  • Notice that Married filling joint return is the largest deduction that applies to Homer, so we choose that one. I have filled in the first part of the tax return for you and have checked the "Married filing joint return" box. The amount the taxpayer takes as their deduction is the larger of the largest applicable standard deduction (above) OR the exact amount of tax deductible purchases made over the year (itemized deduction - for example, state and local taxes, interest paid on a house, gifts to charity, ...) in order to pay the least amount in taxes. We will itemize Homer's deductions and see whether he should itemize or take a standard deduction. Then we will fill out his taxes. Some definitions:
    Wages, tips, and other compensation
    :Total gross earnings after pretax deductions (i.e. pretax retirement contributions, pretax health insurance, etc...), but BEFORE federal tax, FICA, local taxes...
    Social Security Wages
    : Wages used to compute social security withholdings. Total gross earnings before pretax deductions.
    : Federal mandated social security and Medicare deductions. These may or may not be broken down separately on a paycheck.
    Note that you have Homer's Weekly Paycheck (from the homework you completed), so you must figure out the YEARLY info (52 weeks in a year) in order to get necessary info to do Homer's taxes.
  • Compute Homer's YEARLY (52 weeks) Itemized Deductions: Below is an abbreviated version of Schedule A-Itemized Deductions. Fill in the yearly state and local taxes and SHOW WORK.

    Taxes Homer Paid

    Enter state and all local taxes


    Interest Homer Paid

    Enter home mortgage interest (if any)

     0 - I can't find this info for Homer, so we must enter 0.

    Gifts to Charity

    Enter gifts by cash or check (if any)




  • Recall that we use the largest deduction in order to pay the least amount in taxes. Should we use the standard deduction or itemized deduction for Homer? What is this amount?

  • Do this for exploration and deep understanding (but do not use it for Homer's taxes): We don't have the home mortgage interest that Homer paid, so we must enter in 0 in the above for that amount. But, for deep exploration, let's see what would have happened if the year's mortgage interest was - around $6500 (similar to Dr. Sarah's condo first year total interest, but larger since Homer has a larger house). In this case would the standard deduction or itemized deduction be larger? Show work.

  • Start filling out Homer's tax return on the tax return sheet I handed out by following the directions below:
    Enter Homer's YEARLY GROSS Pay on line 7, 22, 33 and 34 (there are 52 weeks in a year). The rest of the lines don't apply to Homer, so you can leave them blank.
    Check to see that row 34 has 24949.60
    Enter Homer's standard or itemized deduction from our work above on line 36. Fill in lines 37, 38 (read the instructions carefully!), and 39.
    Check to see that line 39 has 2849.60
  • COMPUTE Homer's TAX:
    We can see that Homer's taxable income on line 39 is very small! This puts him into the lowest tax bracket. The lowest tax bracket used to be 15%, but now it is taxed at 10%. Hence, Homer's total tax is 10% of his taxable income.
  • Homer's total tax is: ___________________________
    Enter Homer's total tax on line 40, line 52, and also on line 58.
    Write the YEARLY Federal tax (52 weeks) (do not include FICA, which is not tax deductible) withheld from Homer's paycheck on lines 59 and 66, then determine line 68a. Sign the tax return.
  • Does Homer pay additional tax money or get a refund? How much?

  • WORTH MORE - I advise you to come back to this after you have finished Quiz 2. Given that Homer was audited in the episode that we watched, what advice do you have for Homer now that you have filled out his tax return? Relate your advice to the episode. First summarize what you saw in the episode relevant to his tax return, then explain how you obtained data about him, explain how you filled out the tax return (you may refer to these sheets) and then give him advice:

    The Remainder of Lab

    Get the handout for WebCT quiz 2 from Dr. Sarah. You may also use a cheat sheet with writing on both sides (such as the formula sheet handout, or a sheet that you have created yourself). Go into WebCt and take the quiz. Try to increase your speed on doing problems before the test on Thursday (which is also when retakes on this quiz are due).

    Finish up the "Worth More" response above and show this to Dr. Sarah at the end of lab, or finish it up so that you can show it to her in class tomorrow.

    If you have time left, then begin the Ben Franklin web readings.
    Legal Notice: "The Simpsons" TM and copyright Fox and its related companies.
    This web site is for private educational use only. To view this web site, you must agree to these terms. Private educational use of The Simpsons has been approved by Fox. A password is required to listen to audio files.
    Disclaimer: This web site, its operators, and any content contained on this site relating to "The Simpsons" are not specifically authorized by Fox.