Nate Nolands Candy Store You are the accountant for Nate Nolands Candy Store. The weekly payroll needs to be recalculated as something just does not seem right to the owner. All relevant facts are presented below. Your assignment is to take those facts and design your own workbook to present to the owner.

One Week of Payroll Data for Employees Employee Dependents Rate per Hour YTD Soc. Sec. Hours Worked (this week) Thomas, Reggie 2 9.25 1,527.00 26.54 Wilkirk, Danielle 4 9.45 1,342.25 31.28 Belitz, Dan 3 11.27 2,230.51 18.75 Jones, Trey 7 10.85 3,208.53 45.75 Sawall, Lori 1 11.58 2,165.23 61.25 Trap, Robert 4 13.93 1,257.89 31.75 Mount, Joni 5 11.59 3,139.70 41.75

1. BOLD the entire worksheet. 2. Create a TITLE and SUBTITLE. For the title, use the Title Style, Calibri Font size 36. For the subtitle, use the Heading Style, Bodini MT Font size 12. 3. Place a date on your file using the FUNCTION to return todays date. Format the date to MM/DD/YY. 4. Make an ASSUMPTION TABLE which will include items directly below. Your choice on whether your assumption table is located above the data or below the data. a. Social Security Tax Rate is 7.65% with a Maximum YTD Social Security is $3,208.53 and no employee should pay more than this YTD including this weeks pay. b. Medicare Tax Rate is 1.45%. c. State Tax Rate is 4.28%. d. Allowance Per Dependent is $20.09. e. Federal Tax Rate is 21%. f. You should use ABSOLUTE CELL REFERENCES to reference your Social Security Tax Rate, the Maximum Social Security figure, the Medicare Tax Rate, the State Tax Rate, the Allowance Per Dependent, and the Federal Tax Rate.

5. You will calculate the following columns of data, which are to be displayed directly to the right of Hours Worked in this exact order: a. Gross PayIf Hours Worked <= 40, then Rate per Hour * Hours Worked, otherwise Rate per Hour * Hours Worked + 0.5 * Rate per Hour * (Hours Worked-40). b. Soc. Sec.If Social Security Tax Rate * Gross Pay + YTD Soc. Sec. > Maximum Social Security, then Maximum Social Security YTD Soc. Sec., otherwise Social Security Tax Rate * Gross Pay. **NOTE SS formula will produce negative SS for those over the maximum, which is acceptable for full credit. If you want to fix the formula so it pulls $0 for those over the maximum, two bonus points are possible. The same formula should exist for all employees in this column. c. MedicareMedicare Tax Rate * Gross Pay d. Federal TaxIf (Gross Pay Dependents * Allowance Per Dependent > 0, then Federal Tax Rate * (Gross Pay Dependents * Allowance Per Dependent), otherwise 0. e. State TaxState Tax Rate * Gross Pay f. Net PayGross Pay (Soc. Sec + Medicare + Federal Tax + State Tax) g. % Taxes(Soc. Sec. + Medicare + Federal Tax + State Tax) / Gross Pay 6. Your worksheet should TOTAL the following columns: Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay 7. The % Taxes column should have a percentage formula in the total line showing the Total % of Taxes. 8. NUMBER FORMAT: a. Dependents (comma, no decimals) b. Maximum Social Security, Rate Per Hour, YTD Soc. Sec., Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay (comma, 2 decimals) c. % Taxes, Social Security Tax Rate, Medicare Tax Rate, Federal Tax Rate, and State Tax Rate (percent, 2 decimals) d. Allowance per Dependent (accounting, 2 decimals) 9. PREPARE the document to PRINT using Landscape, Fit to One Page. 10. SAVE the workbook using the normal naming convention "Your Name Nate Noland.xlsx", noting this tab as Weekly Payroll Report v1 11. SAVE the workbook.

12. COPY the contents of the v1 into a new tab, naming it v2. ADD the following new employees who transferred into our department from another this week. CALCULATE their items shown in the step above. FORMAT the page to print landscape orientation, fit to one page. Employee Dependents Rate per Hour YTD Soc. Sec. Hours Worked (this week Daley, Lizzie 2 8.75 1,907.74 27.52 Smith, Joseph 4 7.85 1,245.48 36.29 13. SAVE the workbook. 14. COPY the contents of v2 into a new tab, naming it v3. UPDATE the Medicare Tax Rate to reflect 2.85%. Notice the effect this has on each employee. FORMAT the page to print landscape orientation, fit to one page. 15. SAVE the workbook. 16. COPY the contents of v3 into a new tab, naming it v4. UPDATE the State Tax Rate to reflect 5.50%. Notice the effect this has on each employee. FORMAT the page to print landscape orientation, fit to one page. 17. SAVE the workbook.