The Payroll Calculation exercise is designed to simultaneously impart Excel skills and reinforce managerial concepts. Students pay $9.95 for the exercise are guided through the following techniques by way of numbered steps onscreen and video demonstrations:
The students are taught 50 different Excel concepts.
- Enable the legacy From Text Wizard in Excel for Windows
- Importing Tab-Delimited Text files
- Importing Fixed-Width Text files
- Importing Comma-Separated value files
- Filtering a list within a worksheet
- Using Ctrl-A to select contiguous cells within a worksheet
- Inserting blank worksheets in a workbook
- Using Ctrl-C and Ctrl-V to paste
- Using VLOOKUP to look up information in a worksheet
- Using the MIN function to return the smaller of two or more numbers
- Using the MAX function to return the larger of two or more numbers
- Employing Conditional Formatting to to identity the top 10 ten amounts in a list
- Employing Conditional Formatting to to identity the bottom 10 ten amounts in a list
- Freezing worksheet panes
- Using the CONCATENATE function to combine first and last names
- Double-clicking the Fill Handle to efficiently copy formulas down a column
- Summarizing information with pivot tables
- Applying Tabular Form to a pivot table
- Hiding subtotals and grand totals within a pivot table
- Sorting and filtering a pivot table
- Saving one’s work in Excel multiple ways
- Adding columns to pivot tables
- Changing a sum within a pivot table to a count
- Wrapping text within a worksheet cell
- Using SUMIF to create a weighted allocation
- Using the ROUND function to round numbers to 2 decimal places
- Using SUMIF to create check figures
- Understanding the DIV/0! error in Excel
- Using the SUMIFS function to look up information based up on multiple criteria
- Using the AutoSum feature in Excel
- Using the AVERAGEIF function to average amounts based upon criteria
- Using the COUNTIF function to count instances based upon criteria
- Calculating payroll tax amounts
- Using pivot charts to summarize data from a list in chart form
- Formatting a pivot chart
- Drilling down within a pivot table
- Setting the page orientation for a printout
- Using Page Break Preview and returning to Normal view
- Managing page breaks in Excel
- Adding page footers to printouts that include date/time and page numbers
- Sorting lists of data in a worksheet
- Using the Subtotal feature to insert totals within a list
- Interacting with the outline created by the Subtotal feature
- Inserting a Text Box into a worksheet to add a paragraph or more of text
- Creating an organization chart
Students carry out 10 different payroll calculation tasks in the spreadsheet on their own.
- Use VLOOKUP to return the minimum salary by paygrade
- Use the MAX function to return the greater of a current salary amount or the minimum for a paygrade
- Apply the bottom 10 conditional formatting feature
- Use VLOOKUP to return performance scores for each employee from a separate list
- Use the SUMIF function to total salaries by department
- Allocate bonus pools among departments to individual employees based upon performance
- Add check figures to a worksheet
- Calculate pay increase amount and total compensation that includes bonus
- Research payroll tax percentages for FICA and Medicare and calculate payroll taxes
- Create a pivot table to summarize payroll amounts and taxes by general ledger account
Each student is assigned a unique 4-digit number known as their Spreadsheet ID for use during the exercise. This ID number is incorporated several times into the exercise and helps ensure that each student turns in their own work.
The students submit their work to us and we handle all of the grading. We give you three scores:
- Completion: This score indicates if the student attempted a given step.
- Accuracy: This score indicates carried out the step accurately.
- Combined: The average of the two Completion and Accuracy scores.
Within one week of an assignment’s due date we provide instructors with a spreadsheet showing all student results. Partial credit in our scoring is given if a student at least attempts a step. Instructors determine final credit for each student, and whether late submissions are allowed. Students receive written feedback confirming which steps were completed successfully, as well as providing guidance on steps that they did not attempt or did not complete correctly.
Some instructors give more weight to the Completion score than the Accuracy score, while other instructors use the combined average when determining credit. We’re happy to provide any additional information that can help you as an instructor.
Please direct questions to David Ringstrom (david@studentsxl.com) or 404.784.0275.
