Geography 200: RESEARCH METHODS
Dr. Rodrigue
Graded Lab 1: SPREADSHEET BOOTCAMP
YOU are responsible for becoming proficient in the use of spreadsheet software. We'll be using Excel in the class. If you have your own computer and have become used to some other spreadsheet program (e.g., Open Office, Works), it should be okay for you to continue to use it for the lab exercises at home. You may or may not be able to work on it in our lab, though (see if you can Save As Excel 5.0/95 Workbook, which should read into our lab safely enough, or, failing that, as a CSV file, which can be imported into Excel pretty easily and then saved as an Excel file).
For those of you who want to work in our lab or who have no emotional commitment to some other spreadsheet, I would like you to learn Excel. You can do your computing on campus, and you have quite a few options here:
-
The Department of Geography instructional lab in
LA5 352.
In addition, the library
maintains an open access lab, "The Spidell Information Commons," on the first
floor
Academic
Computing runs a lab in the Horn Center (just north of Brotman Hall).
Academic
Computing also has a Wi-Fi hotspot in the Horn Center for those of you
with 802.11b compliant laptops.
Here are two projects to get you up to speed on spreadsheeting. To do these labs (and all subsequent ones), you need to invest in USB flash drives (thumb drives). Anything you do in the labs MUST BE SAVED ON YOUR OWN FLASH DRIVE OR YOUR OWN WEB ACCOUNT!!! (you can learn how to use WS_FTP LE to store files on your CSULB web account by clicking on the Crash Course in HTML). If you do all your work on your own computer, you still need to back things up in at least one other place than your own computer. Ignore this backup advice at your own peril. Sanity conservation tip #1.
LAB EXERCISE A: Weighting raw scores
You are a new college instructor facing the gut-wrenching agony of grading. You've put your students' raw scores for each assignment and test into your spreadsheet:
STUDENT NAME QUIZ RTP1 RTP2 PAPR MDTM FINL
Abiff, H. 149 7 10 19 50 65
Baker, N.J. 157 12 12 17 48 78
Bloe, J. 111 11 10 16 61 74
Carramba, J. 182 13 14 23 62 71
Clouseau, I. 88 8 10 19 49 58
Drakula, V. 200 9 13 25 70 79
Du Fus, G. 121 7 10 15 45 59
Kent, C. 200 10 13 28 68 88
Kirk, J.T. 165 13 15 24 66 75
Lane, L. 148 10 12 19 47 70
Schlimazel, S. 78 11 11 15 39 58
Schmo, J. 117 10 9 19 48 74
Worf, L. 190 6 9 22 55 80
Possible (raw) 200 15 15 30 75 100
Possible (wtd) 10 10 10 20 25 25
Plot Complication: You must convert these raw scores into proportionally weighted scores. The two exams were each worth 25 percent of the overall grade, but they didn't each have just 25 questions (one had 75 and the other 100). Each short report was worth 10 percent but, for grading convenience, you used a 0-15 point scale. The term paper had a 30 point grading system, but it was only worth 20 percent of the overall grade. Then, there were all those map quizzes you gave (what were you thinking?). Each map quiz had 20 map identifications, and there were 10 such quizzes. Altogether, however, these quizzes only amount to 10 percent of a student's grade.
You need to enter the raw data in the table above into your spreadsheet. Then, you need to insert seven columns for the weighted scores. You could insert them all in one block between the names column and the beginning of the raw scores columns. Or, you could alternate raw score with weighted score right next to it. Whatever looks nice. You might try File --> Page Setup --> Page Landscape --> Okay. Or you could try putting the seven raw score columns on the top of a regular (Portrait oriented) page and the seven weighted scores on the bottom half of the page? Put the weighted scores into these seven columns, however you arranged them.
- Hint #1: Weight each assignment (A) with the following
procedure:
- Raw score (r) times weight (w) of item, divided by number of raw points possible (p) on that item.
- A = rw/p
-
- Hint #2:
Pay attention to formatting each weighted column to the same number of decimal places and make sure the column of numbers is right-justified instead of centered in the column (æsthetic quality and readability count). And do check for typos.
LAB EXERCISE B: Federal Income Tax Form 1040
You'll thank me for this one, some day. You are to put the Federal Income Tax Form 1040 and its schedules A and B on one or more spreadsheets. Then, you'll calculate your imaginary taxes with the imaginary data below.
-
You get the 1040 form from the IRS at http://www.irs.gov/pub/irs-pdf/f1040.pdf.
Also collect the Schedule A and Schedule B forms from http://www.irs.gov/pub/irs-
pdf/f1040sab.pdf.
You'll also need the Tax Tables, which you can get from http://www.irs.gov/pub/irs-
pdf/i1040tt.pdf.
Each time you click on one of the links above, you'll see Adobe Acrobat Reader load (if Reader is on your machine) and the file will display. In some cases, you may, instead, be asked if it's okay to open or save your choice as a PDF file. Go on ahead and open them (I think it's safe to assume the IRS wouldn't send us buggy copies of the forms they want us to use to pour money on them!).
If you don't have the Acrobat Reader on your machine, you can download it for free by clicking here. If you have it, the reader will just come up automatically and show you the printable file, an exact duplicate of the forms the IRS mails you. You can print them or, ideally, save them on your diskette with names that you'll remember later.
After having a look at the forms, start thinking about how you will use your spreadsheet to format its columns, so that the reader understands your work. Like this reader, for instance!
You might, for example, reserve Column A for the IRS line numbering system (since it isn't straightforward: there are lots of lines 20a and 20b to deal with). In doing the labels for each line, you don't need to copy everything in the 1040, but you will probably need to abbreviate in some coherent manner (in Column B, probably). For Column C, you will have blank cells for you to enter the numbers below and the formulas connecting certain of those other cells (e.g., for line 36, you need to sum lines 23 through 35, put that sum in line 36, and then subtract line 36 from line 22 -- got all that?). There are all kinds of ways to do this, and it's your artistic call. I am perfectly happy with a neat and straightforward ledger with three or so columns (you don't need to be fancy here, just functional).
One thing, though -- I want you to make your spreadsheets have their page breaks at the same places the IRS forms do (e.g., for the 1040, right after line 37). Otherwise, your work is hard to read and looks a bit absurd (since line 38 is the same as line 37 to make it easier on you when you start on the back of the 1040, it would look goofy to have line 37 and 38 on the same page on your spreadsheet).
The data set for Lab B: Now that you've constructed the spreadsheet(s), here are numbers to enter, so that I can see from the final results whether your work is sound or not. Enter the following values in the appropriate cells and then use formulas to do the requisite math inside the spreadsheet (as for line 37).
FORM 1040
LINE VALUE
6 1 single (even if you aren't)
7 24,956 wages
8 see Schedule B data, below
9 see Schedule B data, below
13 119 capital gain distributions (I'll spare you Schedule D)
19 1,054 unemployment compensation
21 250 other miscellaneous income (bingo?)
SCHEDULE A
LINE VALUE
1 3,800 medical expenses
5 384 state and local income taxes
6 1,567 real estate taxes
10 7,014 home mortage interest
16 500 gifts to charity
21 1,589 unreimbursed employee expenses
22 250 tax preparation fees
SCHEDULE B
LINE VALUE
1
732 bank interest
260 credit union income
5
462 stock market investment dividends
96 stock and bond mutual fund investment dividends
7 no
8 no
last revised: 09/04/08
© Dr. Christine M.
Rodrigue