CSC 170
Homework 4

Assigned 16 March, due 15 April

In brief, you'll convert a current Federal or State tax form into an Excel spreadsheet. Follow these steps, in order:

1. Choose a current Federal or State tax form to convert into a spreadsheet. I'll be more impressed if you don't choose the extremely short, simple 1040EZ form; I would suggest the Federal 1040, 1040A, Schedule A, or any of the main State forms.
2. Make up at least two scenarios, with specific numbers for various kinds of income and expenses.
3. Work out the right answers to these scenarios by hand, on paper (you can use a calculator).
4. Design your spreadsheet: identify what input cells (where you type in information from your wage statements, bank statements, etc.) and result cells (where you'll put a formula that automatically computes its value based on input cells and previous result cells) you'll need, and which ones depend on which. Write this down.
5. Write your spreadsheet. Be sure to have clear labels for all your input cells and result cells. I recommend putting all the input cells together, perhaps grouped by where you would get the information (e.g. one group for information from a W-2 wage statement, another for information from a bank statement, etc.). Each result cell must have a suitable Excel formula, so that whenever the input cells change, the contents of the result cells change accordingly.
6. Test your spreadsheet on the scenarios you made up before. Type in the numbers from your scenarios and see if it gives the same answers you got yourself. If so, you can have some faith that the formulæ in the spreadsheet are correct; if not, identify the discrepancies, figure out which one (if either) is right, and correct things until they match.

What to turn in

• The scenarios and the worksheets on which you figured out the right answers.
• The design document, listing input and result cells and which depend on which.
• The spreadsheet itself, either as an attached file by e-mail or on disk. (Just a printout will not suffice, as it doesn't show me the formulæ.)
• For each of your test scenarios, a copy of the spreadsheet with that scenario filled in (this may be turned in as an attachment, named something like SCENARIO1.XLS, or as a printout).

Why work out answers by hand?

Students are often reluctant to work out the answers by hand; after all, isn't that what the computer is for? In fact, some students in the past have written a spreadsheet, plugged the numbers in, and then copied those answers down onto their worksheets as though they had worked them out by hand. This, of course, misses the point of testing: it's very easy to type a formula incorrectly, and the testing step allows you to catch many such errors before you start using the spreadsheet for your own real taxes. If you take the spreadsheet's word, and copy its answers onto your "by hand" worksheet, you won't find any errors -- but I probably will, and you'll lose points. Much better for you to spot them and fix them first!

The experience of many computer professionals supports writing down test scenarios and correct answers before writing the spreadsheet, for several reasons:

• You have to think about the problem, and know how to solve it yourself, before you start trying to teach the computer to do it.
• It's much harder for you to miss or ignore a discrepancy when the right answer and the computer's answer are both staring you in the face.
• You're less likely to be tempted to skip the testing step at the last minute if you've already done most of the work (choosing numbers and figuring out the right answers).