Computer Applications I :: Projects :: Spreadsheet Exam
Rubric
Due: Database Error
Due: Database Error
Problem
The first part of the Spreadsheet Exam can be taken via this link. You cannot use any notes or resources
Instructions
- Type the title, Security Lock & Key Weekly Payroll, in Cell A1. Merge and center it above the table and make sure it stays centered if you add any more columns.
- Use the following formulas to determine the gross pay, federal tax, state tax, and net pay:
a. Gross Pay = Hourly Rate * Hours
b. Federal Tax = 20% * (Gross Pay - (Dependents * Federal Tax Rate ) )
c. State Tax = State Tax Rate * Gross Pay
d. Net Pay = Gross Pay - (Federal Tax + State Tax)
- Calculate totals only for hours, gross pay, federal tax, state tax, and net pay.
- Use functions to determine the average, highest, and lowest values of each column from step 3. Be sure you do not include the totals when calculating these values.
- Make sure all appropriate numbers are formatted in currency style.
- Create a new column called Employee Code and use the table below to create a formula that determines the employee code using VLOOKUP.
- Name the next column Title and use the table below to determine the employee’s title using the CHOOSE function.
- Create a pie chart that displays only the net pay column (no totals, averages, highest or lowest). Be sure that each slice of the pie has its value placed next to it and the chart has an appropriate title.
- A few errors were discovered in the spreadsheet. Make the following changes:
a. Harrison Ford only has two dependents.
b. Julia Roberts’ hourly rate is 16.55.
c. Brad Pitt worked 35 hours.
d. Jennifer Lopez worked 15.15 hours.
e. The Federal Tax Rate should be 34.86.
f. Chris Farley needs to be added with the following information:
Hourly Rate: 15.75, Hours: 37, Dependents: 0 - Now, sort the data from Z to A by employee name.
- $4,014.13 should be the total of the Net Pay column. If yours does not match you have made an error.
