 |
 |
 |
 |
 |
Challenge Yourself
Multi-Page Payroll Lab
Skills used:
- calculating dates
- SUM function
- IF function
- AutoFill
- Copy / Paste
- Working with multiple spreadsheets
- Format Font, etc.
- conditional formatting
- relative VS absolute cell reference
- Open the Waterfront Bistro spreadsheet found in the lab files folder in my instructor folder for this class. (At least you don’t have to enter the data.)
- On the Week 2 sheet, make the “week ending” date equal to 7 days more than week one’s ending date.
Make the Week 3 ending date 7 days after Week 2’s ending date and Week 4’s ending date 7 days after Week 3’s. - Make the Date of Payment for each sheet 3 days after the “week ending” date for that week.
- Use the Sum function to calculate the total hours for the first employee on sheet one.
- Use the IF function to calculate the number of overtime hours for the first employee on sheet one. IF overtime > 0, hours-40, otherwise enter 0 for the number of overtime hours.
- Use the IF function to calculate gross pay: IF hours > 40, gross pay is calculated as (40*pay rate) + (overtime hours * pay rate * 1.5), otherwise gross pay is simply equal to hours * pay rate.
The pay rate is on the summary page.
Don’t forget that you need to make the pay rate cell reference ABSOLUTE! - Use AutoFill to fill the hours, overtime and gross pay for the rest of the employees.
- Copy the formulas you entered on sheet one and paste them on sheets 2 – 4.
- Use SUM function to calculate weekly totals in Row 13 for each week. Enter the totals for hours, overtime and gross pay on the summary sheet.
- Format the Gross pay as currency on the summary page.
- Add borders and other formatting. Format painter is useful here – format the title/subtitle on the summary sheet and then use format painter to apply the same formatting to the other sheets.
- Apply conditional formatting to any gross pay on the summary sheet that is over $1500. Format is up to you.