Guardian Roofing is a family-owned company in Lafayette, Louisiana, that produces roofing materials. It has 4,000 employees and an operating budget of $19,000,000. All employees except the eight members of the companys founding family are on the firms automated payroll system. The family members are paid through a separate confidential payroll, which is processed manually because it contains highly sensitive information, such as salaries and bonuses. Like other salaried employees, they are paid monthly. Timothy Schell, executive assistant to the founder, Jim Landry, is in charge of preparing this confidential payroll. He must make all of the calculations for salary changes, deductions, and net pay using a calculator. He then enters the results onto a Payroll Register sheet. A Payroll Register is a report prepared for each payroll period that lists the names, gross pay, deductions, and net pay of all employees, and the total gross pay, deductions, and net pay for that payroll period as well as the net amounts deducted and paid to date. Schell writes out the checks by hand. This is a very time-consuming process that prevents Schell from fulfilling other responsibilities. Jim Landry would like to use his time more productively for coordinating meetings and managing correspondence. Also, there is a danger of miscalculations, which would incite the wrath of the family members. Landry and Schell would like to automate the process as much as possible while maintaining strict confidentiality. Schell feels there are so few checks to write that this part of the process could remain manual. However, any hours could be saved if all of the payroll calculations and the preparation of the Payroll Register report could be automated. Load guardian_q.xlsx, which displays the names of the executive family members, their social security numbers, and annual salaries. You should develop a worksheet that creates a Payroll Register report for these employees. The worksheet should automatically calculate monthly gross pay, net pay, and all deductions. It should also provide totals for each of these categories for the current pay period and add this periods amounts to the year-to-date totals. Monthly gross pay can be computed by dividing annual salary by 12. Federal withholding tax should be set to 26% of gross pay. State withholding tax should be set to 9% of gross pay. For the tax year 2009, FICA (the employee Social Security deduction) is 7.65% of the first $94,200 earned during the year. The Medicare deduction is 1.45% of gross pay for all wages earned during the tax year. Since this is the first pay period of 2009, FICA and Medicare deductions must be taken for all employees for this payroll. Group health insurance is $100 per month. All of these family members have elected to participate in profit sharing plan to which they contribute 7.5% of their gross pay each month. Tasks. There are five tasks to this problem:

1. Complete the column labels to include gross pay, all deductions, and net pay. Also, include a column for gross pay to date that will be used in future months.

2. Make all appropriate format changes for numbers and percentages. Columns containing numbers should be formatted to show two decimal places to the right of the decimal point.

3. Create an assumptions section for all deductions and other variables in the upper left of the worksheet and label it Assumptions. This way, you can easily make changes in deductions and formulas using the addressing function of spreadsheet software. By keeping all assumptions in a clearly defined CIS 300 Excel Case Guardian Roofing - Payroll Register 2 / 2 Computer Information Systems Program assumptions section, you can make changes rapidly in the worksheet to respond to changing tax laws or other regulations. Listing variables also allows all assumptions to be clearly visible and reported.

4. Use formulas to calculate monthly gross pay, all deductions, and net pay. Be sure these formulas reference the appropriate cells in the assumptions section of your worksheet (e.g., $A$56) rather than actual values (e.g., 20%). Provide totals for gross pay, net pay, and each deduction category so that Schell can track the companys expenses for the pay period. Widen columns if necessary. Additional Problem 1. The company feels it should be withholding 35% of gross pay for federal tax. Modify your worksheet to change the federal withholding tax deduction.