CIS120/Excel Homeworks/HW10

From MCIS Wiki

Jump to: navigation, search


Checkbook

  • Format the following columns:
    • Date as a date in the form mm/dd/yy
    • Description as 12pt bold text
    • Debit / Credit / Balance as Dollars
    • Percent as a percent (duh!)
  • Validate expense types in column D as one of the types in C19 and below. This should include an option to leave this field blank. Enter an appropriate expense type for each check.
  • Add running totals in the balance column (note that an empty cell is treated as "0" in math operations)
  • Use conditional formatting to add a light red background to a balance that is negative
  • Use the "sumif" function to compute totals in each expense catagory. The first argument to sumif is the expense type (column D), the second is the type of expense (one of the items in C), and the third is the location of the numbers to sum (column E)
  • Calculate total expenses and percentages of each expense type
  • Create a pie chart that breaks down expenses as shown
  • Create a scatter chart with a straight line to show the day by day balance. Fill the background of the plot area and make sure that the dates appear as shown. You will have to format the vertical axis to indicate that the crossing axis is at -200.

image:120-s12-expenses.png

Tax Return

  • Name all cells used in calculations. Grey cells are filled in by the user
  • Validate the filing status must be one of the three alternatives in column I
  • Validate "yourself" and "spouse" as either blank or X (these are in I7 and I8)
  • Validate # of kids as a number between 0 and 10
  • Calculate # of exemptions as number of kids + 1 if "yourself" is checked + 1 if "spouse" is checked. Use the "if" for this: IF(yourself = checked, 1, 0) assuming you name "yourself" and I7 is named "checked".
  • Format all dollar amounts using $
  • Calculate the standard deduction by looking the deduction with VLOOKUP - the "range lookup" parameter must be FALSE
  • The deduction for the exemptions is the number of exemptions time $3400
  • The adjusted income is the max of 0 and the gross income less the standard deduction and deductions for exemptions
  • The tax due is 0 of the adjusted income is less than 10000, 10% of the adjusted income less $10000 if the adjusted income is less than $20,000, and $1000 + (the adjusted income less $20,000) * 20% for incomes over $20,000. You can do this with vlookup or with two if statements
  • Withholding amount is entered by the user
  • If total due is greater than the withholding, amount to pay is the difference, otherwise leave this blank. You can say IF(x, y, " ") to get a blank.
  • If the total due is less than the withholding, the refund amount is the withholding - total due; otherwise leave blank

image:120-s12-tax1.png

Another test:

image:120-s12-tax2.png

Personal tools