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 OpenOffice Calc in the class. OpenOffice is an open-source comprehensive office software suite, which contains a spreadsheet, word processor, presentation program, database management program, graphics package, and an equation editor. It uses Open Document Foundation formatting. It is available, for free, at http://www.openoffice.org/ or as LibreOffice from http://www.libreoffice.org. You can use other software if you are already very proficient in it, such as Excel, but you're taking a risk of extra work and less help. OpenOffice/LibreOffice can seamlessly read Microsoft files, but the reverse is not true for Office 2003 or 2007 (2010 and 2013 can read Open Document Foundation files). I highly recommend that you download the OpenOffice or LibreOffice suite to minimize this kind of hassle. Did I mention it's free?
Here are two projects to get you up to speed on spreadsheeting. To do these labs (and all subsequent ones), you should invest in USB flash drives (thumb drives). Anything you do in the labs MUST BE SAVED ON YOUR OWN FLASH DRIVE OR E-MAILED TO YOURSELF!!! The reason for this is that the local D:\ and the network O:\ drives are purged regularly, sometimes weekly!
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. It's awfully easy to get complacent, and awfully hard to get over the shock of losing all your work.
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 Format --> Page --> Page tab --> Landscape and 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 may 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:
- 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 for sending our hard-earned money to 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: 01/19/15
© Dr. Christine M.
Rodrigue