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?
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?
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
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
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
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
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
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.
Married filing jointly: $7600.00
Head of Household: $6650.00
Married filing separately: $3800.00
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)
Below is an abbreviated version of Schedule A-Itemized Deductions.
Fill in the yearly state and local taxes and SHOW WORK.
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?
Taxes Homer Paid
Enter state and all
Interest Homer Paid
Enter home mortgage interest
0 - I can't find this
info for Homer, so we must enter 0.
Gifts to Charity
Enter gifts by cash or check
ADD ALL ENTRIES
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?
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
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
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
Write the YEARLY Federal tax (52 weeks) (do not include
FICA, which is not tax deductible) withheld from
on lines 59 and 66, then determine
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
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.
This web site, its
operators, and any content contained on this site relating to "The Simpsons"
are not specifically authorized by Fox.