Description
You are the owner of a family practice which also sells fitness equipment and wish to convert the payroll account to an Excel worksheet, which will automatically calculate gross pay, taxes, healthcare costs, commissions, and net pay for your five employees.
Directions:
1. Build a worksheet to calculate the payroll for any week.
For each employee, the worksheet should calculate gross pay, taxes, commission, healthcare, and net pay. All employees work at least twenty hours per week, with wages ranging from $22.50 to $34.00 per hour. Employees who work more than thirty hours per week earn overtime pay, calculated at one and one-half times the employee's hourly rate. Employees also receive a three percent commission for fitness equipment sales greater than $100.
a. Enter a descriptive worksheet title, column titles, and row labels
b. Enter employee names and data required to calculate the items noted below
c. Write formulas and functions to complete the items noted below
2. Given the following guidelines, enter data into the worksheet.
a. There are five employees, each of whom worked at least 20 hours
b. Three of the five employees worked more than 30 hours, with none exceeding 34 hours
c. Two of the five employees are eligible for 3% commission based on fitness equipment sales
3. Write and copy formulas and functions to show:
a. For each employee:
1. Gross pay
2. Taxes deducted (26% flat rate)show the tax rate in a cell which is held absolute when calculating taxes deducted from gross pay
3. Healthcare deduction (12% of gross pay)
4. Commission (3% if fitness equipment sales exceed $100)
5. Net pay
b. Column totals: gross pay, taxes deducted, healthcare deducted, commission, net pay
c. Average: hours worked, gross pay, taxes, healthcare, commission, net pay
4. Format the spreadsheet, using features which you feel will enhance its overall appearance
5. Include footer in your printout with the following information:
a. your name: left side
b. worksheet name: center
c. date: right side
6. Save the worksheet in landscape mode.
7. Rename a duplicate copy of your worksheet (remember to add a footer with your name, worksheet name, and date in the
duplicate copy). Using the duplicate copy of the worksheet, select the Display Formulas option, adjust the width of the columns
as needed, and save the worksheet in Landscape mode.
Submit
1. Worksheet with footer (landscape mode)
2. Worksheet formulas (landscape mode)