Taxes

About this time of year, if you were really working for a desktop publishing company, you would start to send out and/or receive tax forms. Companies are required to send forms to their employees by the end of January stating how much the employee has earned in the last year so that employees can in turn fill out their tax forms. You would also receive forms from the bank where you have your checking account, from your mortgage company, etc. We're going to take a look at the form that you will probably be filling out in a couple of years, the 1040EZ.

Instructions

You will be given a printed version of the tax form. The "simple" 1040 is both sides of a single page plus lots of instructions and tax tables. We won't concern ourselves with the detailed tax instructions for this project, but will take the calculations seriously. To simplify the situation, you will be assumed single and dependent. You will therefore need to complete the spreadsheet that automates front side of the form and the Worksheet for Line 5 on the back. The Worksheet for Line 8 can be skipped. An example exhibit will eventually be provided for comparison, but you need to begin this one on your own and come up with most of the formulas yourself. The instructions below offer guidance. Note: Do not write down your Social Security Number (SSN) or bank account numbers for this project! Those parts will not be automated. We are only interested in the dollar amounts and calculations.

General

    1. Start a new Excel workbook and call it Taxes.xlsx (for newer versions of Excel) or Taxes.xls (for older versions).

    2. Make three worksheets named 1040EZ, Line5, and Summary in order.

Worksheet 1040EZ

    1. On the first worksheet create column headers Location, Description, Value, [Blank], Notes, and Name. The blank column, D, does not need a name. It can be used as a separator.

    2. In the location column describe where on the page the value you are about the enter is located. For example, you could use Line1, Line2, Line9b, etc. or StepA, StepB, etc.

    3. In the second column describe the value to be entered. In general, the description can closely follow the instructions on the form. For example, for Line1 you might have "Wages, salaries, and tips." Do this for all lines.

    4. Enter either the dollar amounts for a formula for the cells in column C. (Line5 will require you to complete a separate worksheet, so just leave that blank for now.) You can make up values, but keep them reasonable. Get a feel for what you would actually be paying. Reasonable numbers will also help you when you look up the tax in the table. The formulas should match the instructions on the tax form. In general, only addition and subtraction are necessary.

    5. Some values you enter yourself, others are calculated, and the tax you have to look up. Use one fill color (e.g., green) for cells where you have to enter an amount.

    6. Use a different fill color, potentially none, for cells that contain formulas.

    7. Highlight the tax line in yet another color to remind yourself to look up the tax in the tax table anytime the numbers change.

    8. In the notes column for Line5 add "Assumed single and dependent" and for Line8, ""Assumed dependent". You need only follow instructions for these cases and do not need to handle others.

    9. Name the values in significant cells. This will allow the values to be reused without requiring their cells to be located. For this worksheet, significant names are AdjustedGrossIncome, TaxableIncome, TotalPaymentsAndCredits, Tax, Refund, and AmountYouOwe.

    10. Record these names in the Name column of the worksheet. This allows you to view the names without selecting the cell and reading the Name Box.

    11. For the tax, compare the value you found in the tax table with the formulaic result. If you earn too much, you will need the formula instead of the table. In the notes column, enter the formula

      1. ="Estimated at $"&ROUND( IF(TaxableIncome<=0,0, IF(TaxableIncome<=8375,0+0.1*(TaxableIncome-0), IF(TaxableIncome<=34000,837.50+0.15*(TaxableIncome-8375), IF(TaxableIncome<=82400,4681.25+0.25*(TaxableIncome-34000), IF(TaxableIncome<=171850,16781.25+0.28*(TaxableIncome-82400), IF(TaxableIncome<=373650,41827.25+0.33*(TaxableIncome-171850), 108421.25+0.35*(TaxableIncome-373650) ) ) ) ) ) ),0 )

    12. You might want to use a text editor to enter this and then copy it into Excel's formula bar in a separate step. Notice that the formula refers to TaxableIncome, which should be a named cell. Use the result to double check the value from the tax table.

Worksheet Line5

    1. Use the same headings as the first worksheet.

    2. Record the locations and descriptions. Some values are not on their own line and may be given a blank location, but the description is required.

    3. At least one value needs to be taken from the front worksheet. Use a formula to do this rather than copying the value. The formula is something like ='1040EZ'!C2. The value from LineG needs to be transferred to the front where the formula is something like =Line5!C10. If you can think up names for these cells and use them, you will get ahead faster.

    4. Format the Value column for accounting.

    5. Add notes for LineD and LineF that you are assumed single and dependent. No names are used on this worksheet.

    6. When you are finished, you should be able calculate your tax on the front page.

Summary

    1. On the third page, summarize your calculations. In column A, write the names of all the named cells: TaxableIncome, AdjustedGrossIncome, Tax, TotalPaymentsAndCredits, AmountYouOwe, Refund.

    2. In the second column enter the corresponding formula like =TaxableIncome for each row.

    3. Format this column for accounting.

Delivery

We won't be waiting until April 15 (traditional tax day) or April 18 to hand this in. You want your refund as soon as possible. The due date will be announced in class. Email or USB your file to the teacher.

Grading

Your work will be tested by entering values into important cells and confirming that the results match expected values. Points are awarded in the categories below.

    1. General

        1. Taxes.xlsx or Taxes.xls

        2. Worksheets named 1040EZ, Line5, and Summary

    2. Worksheet 1040EZ

        1. Column headers Location, Description, Value, [Blank], Notes, and Name

        2. Line1, Line2, Line9b, etc. or StepA, StepB, etc.

        3. Describe the value to be entered.

        4. Enter either the dollar amounts for a formula for the cells in column C.

        5. Use one fill color (e.g., green) for cells where you have to enter an amount.

        6. Use a different fill color for cells that contain formulas.

        7. Highlight the tax line in yet another color.

        8. In the notes column for Line5 add notes.

        9. Name the values AdjustedGrossIncome, TaxableIncome, TotalPaymentsAndCredits, Tax, Refund, and AmountYouOwe.

        10. Record these names in the Name column of the worksheet.

        11. In the notes column, enter the formula for the tax.

    1. Worksheet Line5

        1. Use the same headings as the first worksheet.

        2. Record the locations and descriptions.

        3. Use a formulas ='1040EZ'!C2 and =Line5!C10.

        4. Format the Value column for accounting.

        5. Add notes for LineD and LineF.

        6. Calculate your tax on the front page.

    1. Summary

        1. In column A, write the names of all the named cells.

        2. In the second column enter the corresponding formula.

        3. Format this column for accounting.