Assigned: 12/07/04
Due: 12/14/04 at the start of final exam
Assignment:
Imagine that you have been asked to create the spreadsheet shown on the attached page. This is a simplification of actual tax calculation. DO NOT try to use this on your taxes! Note that formulas are required in all cells except for the basic info that cannot be calculated. Print the Spreadsheet.
Turn In:
1) Print Out of Spreadsheet
2) Disk
3) Answer to the question below
Details:
a) The info to be typed in is: Title, Headings, Taxpayer Names and Number of Dependents, all Assumptions, Monthly Incomes, Deductions (except for dependents).
· Put your actual name anywhere that the sample has YOUR NAME.
· Note that some of these have patterns that may make it possible to save time by using the fill handle instead of typing (e.g. month names, some of the incomes, …)
· All other numbers should be calculated using formulas
b) Totals and averages should be calculated where shown
c) Withholding must be calculated using the appropriate monthly income, withholding percent, and withholding child allowance. Take the withholding percent of the relevant income then subtract the withholding child allowance for the appropriate number of dependents. YOU MUST USE ABSOLUTE REFERENCES for Withholding Pct and Withholding Child Allowance!!! YOU MUST USE MIXED REFERENCES for Num Dependents!!! By using absolute and mixed references as I have said, you should only have to enter the formula once, and then copy it to all of the other withholding cells.
d) Deduction for dependents must be calculated using the Deduction per Dependent and the Num Dependents. YOU MUST USE ABSOLUTE REFERENCES for Deduction Per Dependent!!!
e) Adjusted Gross Income must be calculated from total income and total deductions
f) Tax First Income must be calculated using a formula including an IF function. All Income up to the Low Income Cutoff should be taxed at the Low Tax Rate. If the person is making more than the low income cutoff, then the tax first income is the entire low income cutoff times the low tax rate. Otherwise, the person’s whole income is taxed at that rate. YOU MUST USE ABSOLUTE REFERENCES for Low Income Cutoff and Low Tax Rate!!!
g) Tax Rest must be calculated using a formula including an IF function. All income above the Low Income Cutoff should be taxed at the High Tax Rate. If the person is making less than the low income cutoff, this tax is zero. Otherwise, the person’s extra income above the low income cutoff is taxed at the high rate. YOU MUST USE ABSOLUTE REFERENCES for Low Income Cutoff and High Tax Rate!!!
h) Difference must be calculated using Total Tax and Total Withholding. Greater withholding than total tax should result in a positive difference
i) Refund and Tax Due must both be calculated using formulas involving Ifs. If more money was withheld than the total tax, then the difference (above step) is the refund amount (otherwise no refund). If less money was withheld than the total tax, then the difference (above step) is the negation of amount owed (otherwise nothing owed). NEITHER Refund NOR You Owe should EVER come out with a negative number.
j) Formatting:
· Autoformatting is unlikely to be of any benefit to you in this project unless you are very clever in how you do it.
· Title should be merged and centered above the whole table. Make the font size 18pt
· Income and Withholding headings are merged and centered over their tables.
· Thick dividing lines below headings and above totals should be as shown.
· All dollar amounts should be formatted as currency, as shown. NOTE that in currency format negative amounts are shown in parentheses and zeros are shown as dashes. Under NO CIRCUMSTANCES should these be typed in as text. All such instances in the sample are calculated using formulas!!!!
· All percents should be formatted as percents, as shown
· Column widths should be such that all contents fit nicely
· Headers and footers should be put in as shown using Excels custom header and footer capabilities – header containing filename and sheet name, footer containing date, time and page# of total pages
· Assumptions should be surrounded by a thick border and highlighted by a light blue background color..
· Setup the document to print in Landscape. Try to fit the document on three pages as I have done (but minor differences in column width could cause your printout to be different than mine. Don’t stress over page breaks)
k) What If Analysis Using Goal Seek: It would seem fair if the average taxpayer got neither a refund or a tax due. Question: Use the Goal Seek capability to determine what the withholding percent would have to be in order to accomplish that objective. Answer on your print out (cancel so that sheet remains as it was)
l) Turn in the disk and print out