Exam II Practice.xls

Spreadsheet Questions Employee Payroll and Taxes Summary Tucker Bay National Bank Tax Rate Calendar Year 2006 30% Employee Job Classification Salary Bonus Other Income Status HealthCare Total Income 2007 Taxes Henson Clerk $36,000.00 $1,080.00 $3,544.00 Single $1,800.00 $38,824.00 $11,863.20 Hopkins Teller $29,000.00 $870.00 $2,190.00 Family $3,480.00 $28,580.00 $9,357.00 Fernandez Accountant $47,000.00 $1,410.00 $1,550.00 Single $2,350.00 $47,610.00 $14,565.00 Porter Security $53,000.00 $1,590.00 $845.00 Single $2,650.00 $52,785.00 $16,153.50 Thomkins Teller $24,000.00 $720.00 $3,597.00 Family $2,880.00 $25,437.00 $8,279.10 Number of Items 5 Average $37,800.00 $1,134.00 $2,345.20 $2,632.00 $38,647.20 $12,043.56 Maximum $53,000.00 $1,590.00 $3,597.00 $3,480.00 $52,785.00 $16,153.50 Total Family 2 (1) Write Excel formulas, with functions as needed, for the following: H12: Total income for Thomkins, equal to the salary, bonus and other income minus healthcare I16: Average taxes paid by all employees D9: Bonus earned by Hopkins, equal to three percent of salary B15: Number of cells containing a job classification F19: Number of employees classified as family status I8: Taxes paid by Henson, equal to salary and other income times tax rate, held absolute G9: Health care paid by Hopkins, equal to five percent of salary if classified as single or twelve percent of salary if classified as family (2) If the original worksheet is sorted in ascending order by Column I (2007 Taxes), what value will now appear in cell D9? (3) If the original worksheet is sorted in descending order with C (Salary) as primary key and Column B in ascending order as secondary key, what label will now appear in B11? Spreadsheet Formulas Employee Payroll and Taxes Summary Tucker Bay National Bank Tax Rate Calendar Year 2006 30% Employee Job Classification Salary Bonus Other Income Status HealthCare Total Income 2007 Taxes Henson Clerk $36,000.00 $1,080.00 $3,544.00 Single $1,800.00 $38,824.00 $11,863.20 Hopkins Teller $29,000.00 $870.00 $2,190.00 Family $3,480.00 $28,580.00 $9,357.00 Fernandez Accountant $47,000.00 $1,410.00 $1,550.00 Single $2,350.00 $47,610.00 $14,565.00 Porter Security $53,000.00 $1,590.00 $845.00 Single $2,650.00 $52,785.00 $16,153.50 Thomkins Teller $24,000.00 $720.00 $3,597.00 Family $2,880.00 $25,437.00 $8,279.10 Number of Items 5 Average $37,800.00 $1,134.00 $2,345.20 $2,632.00 $38,647.20 $12,043.56 Maximum $53,000.00 $1,590.00 $3,597.00 $3,480.00 $52,785.00 $16,153.50 Total Family 2 (1) Write Excel formulas, with functions as needed, for the following: H12: Total income for Thomkins, equal to the salary, bonus and other income minus healthcare I16: Average taxes paid by all employees D9: Bonus earned by Hopkins, equal to three percent of salary B15: Number of cells containing a job classification F19: Number of employees classified as family status I8: Taxes paid by Henson, equal to salary and other income times tax rate, held absolute G9: Health care paid by Hopkins, equal to five percent of salary if classified as single or twelve percent of salary if classified as family (2) If the original worksheet is sorted in ascending order by Column I (2006 Taxes), what value will now appear in cell D9? (3) If the original worksheet is sorted in descending order with C (Salary) as primary key and Column B in ascending order as secondary key, what label will now appear in B11? Database Questions Record Name Major GPA GradYear Honors Program 1 anna english 3.6 2007 yes 2 thomas history 2.8 2005 no 3 ellen english 2.9 2008 yes 4 james psychology 2.6 2006 no 5 peter chemistry 3.9 2006 no 6 kevin english 3.6 2005 no 7 joanna history 2.8 2005 yes 8 ellis english 2.4 2008 yes 9 patricia psychology 2.6 2007 no 10 michael chemistry 3.7 2006 yes Searching: For each of the requests listed below, identify which record(s) from the datafile will be included in a subset of the datafile 1. Psychology majors graduating in 2006 2. Students with GPA of 2.8 or graduating in 2005 3. Students in the honors program with GPA greater than 3.5 Sorting 1. If the original datafile is sorted with GPA as the primary field (in descending order) and Name as the secondary field (in descending order) which record will now be the third record in the datafile? 2. If the original datafile is sorted with Major as the primary field (in ascending order) and Honors Program as the secondary field (in ascending order), which record will be the first record in the datafile? Sheet2 Sheet3