Payroll Sheet (Independent)

Student Learning Outcomes:

The student will demonstrate that they can Create a complex spreadsheet that includes

	Simple IF and a complex IF
	VLOOKUP function
	Stacked bar and pie graphs/charts
	Calculation with addition/subtraction and multiplication/division requiring parenthesis (mathematical order)

The student will understand how their own paycheck is calculated and thus be able to check their own pay.

Procedure

Assessment

Naming Cells

I IF function

VLOOKUP

PMT

Print Hints

Procedure:

Prepare a spreadsheet of a mock weekly payroll for a company. Title the sheet, include your Section, your Name, and the date.

Use column headings that occupy 2 lines, so you don't have to abbreviate too badly, as follows:

	Column Heading	Contents ---Instructions --(Hints)
A	Employee's Name	Have 4 or 5 rows of fictitious (Donald Duck, Donald Trump, ...???) employees that you made up (including yourself as the last).
B	Social Security Number	xxx-xx-xxxx
C	State of Residence	Have some folk live in NJ, some live in NY, some in CT some in PA
D	Number of Deductions	Dependants (kids, spouse, yourself, your mother-in-law are dependants who are deductions) How many? Choose 0, 1 or 3.
E	Hourly Pay Rate	Have some min wagers, some high flyers making over $50/hour. Have one person making $10 an hour who works 40 hours so that they make $400 a week and can test Federal taxes.
F	Total Week's Hours Worked	Some employees should work fewer than 40 hours, some work 40 hours and some work more
G	Overtime Hours	This and all columns after this MUST be calculated with formulae so that cells in column A to F can change and the cells in columns G to O (Net Pay) automatically adjust. Pretend that this is a payroll for 1,000s so the formula in row 2 must work for all other rows.
H	Regular Pay	Once all the numbers are complete: Chart this column and Overtime Pay in a bar or column chart
I	Overtime Pay	Paid at time and a half.
J	Total Pay	This column is to be charted with a Pie chart
K	Federal Taxes	(hypothetically:20% of all weekly wages that are part of wages OVER the first $20,000 of yearly wages- real USA IRS laws are more complex) For example: someone making $400 a week makes 52 times 400 or $20,800 a year. Of that $800, which is the portion of yearly wages that exceeds $20,000, is taxable. At 20% that would be $160 tax per year or $3.08 tax for this week, 1/52nd of a year. See further Hint
L	State Taxes	(10% for NY, 5% for NJ, 3.5% CT, 8% PA) use a LOOKUP function
M	Total Taxes
N	Health Insurance	(1=$10, 2=$18, 3=$25, 4 or more dependants=$30) use a LOOKUP function
O	Net Pay

All values in columns G to O are to be computed. All formula in a column should be the same (dragged down from row 2).
The entire final row, described below, is to be computed.
The last row is total of the column amounts for columns F to O (total hours worked, regular pay, overtime, etc.)
Adjust column widths. Format dollar cells with a $ in the 1st and last (total) row only.
Create two charts (graphs) on the same page as the values:
1. a total wage pie with your slice exploded
2. a stacked bar of regular and overtime pay of all employee
Be sure to make sure you did the assignment best that you can by validating your work against the checklist.
Submit a paper copy

	a printed copy of the sheet in landscape orientation and small fonts with two charts (graphs) completely labeled: a total wage pie with your slice exploded a stacked bar of regular and overtime pay of all employees.
	the formulas in columns G through O of your row and the total row ONLY

Check out the website for General Spreadsheet Hints on creating a perfect spreadsheet and getting a top grade. Also Payroll spreadsheet hints can help.
Use explanation sheets:

	Naming Cells
	IF
	VLOOKUP
	PMT

Category	Advanced - A	Proficient- B	Partially Proficient - C	Non-Proficient- F
Organization	Follows all the Cardinal, formatting, naming, printing and charting rules on the Checklist. Sheets is labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension. sheets are labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension.	Follows all the cardinal rules on the Checklist. Violates 1 or 2 formatting, printing or rules. Sheet is labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension. worksheets are used, labeled and ordered logically. Placement of data is logical, but could be improved.	Follows all the cardinal rules on the Checklist. Violates 3 or 4 formatting, printing or rules.	Chart missing. Only formatted values or formula sheet submitted. One cardinal spreadsheet rule or 5 or more formatting or charting rules ignored.
Formatting	Cell and text formatting options are used wherever possible to organize and emphasize data (including extras like borders, number and special formatting & input masks).	Cell and text formatting options (like font size, bold, justification and text color) effectively emphasize important values. Formatting of decimal places is not done.	Cell and text formatting options are used, but not effective in emphasizing data.	Cell and text formatting options are not utilized.
Chart Info	Chart title, labels and series names are well thought out, making the chart easy to understand. The chart includes multiple data series demonstrating firm grasp of charting in Excel.	The source data is correct and the chart has a title, axis labels, and legend; data series have been named.	The source data is correct, but the chart lacks one or more of the following: title, axis labels, legend. data series have not been renamed or are non-descript.	Chart was not attempted or the data range selected for the chart was completely irrelevant or inputted incorrectly.

Search this site or the web powered by FreeFind Site search Web search

Student Learning Outcomes:

Procedure:

Assessment

Search this site or the web powered by FreeFind

Site search Web search