we work with excel here is the file she gave us to start working with!

Member Problem 8 - Payroll Register (PAYROLL) Learning Objectives Prepare and use a payroll register for a sole proprietorship firm that contains both social security and payroll withholdings. Use =lF and =ROUND functions to compute the Social Security tax, Medicare tax, and union dues formulas. Record journal entries for payroll related transactions Identify behavior patterns among various payroll taxes. Answer What If Scenarios. Adjust the template to include a new employee. Create a Chart showing the percentage of an employee's gross pay that is withheld for taxes and other deductions, including the amount taken home as net pay. Problem Data The Critter Crates Company has five employees and they are paid on the last day of each month. Payroll data for October of this year is as follows: Employee Gross Pay for October Federal Income Cumulative Pay at Union Tax Withheld 9/30/XX Amber Adilla $5,200 $785.00 $??? (See #1 below) Yes Bobby Bonilla 4,300 731.00 38,700 No Candice Chameleon 1,250 112.50 6,750 No Donna Donilla 12,000 1,875.00 119,000 Freddy Falcon 1,560.00 110,000 Other Information regarding payroll rates are as follows: a Social Security taxes are 6.2% of monthly gross pay up to a cumulative total pay of $118,500. Both employer and employees must pay this for each employee up to the maximum given. b. Medicare taxes are 1.45% on monthly gross pay with no upper limit. C. A $50 monthly deduction is made for union dues for all union members d. Unemployment taxes are paid on monthly gross pay up to the first $7,000 earned by each employee each year. State and local rates are 5.4% and 8% respectively, REQUIREMENTS: 1. Based on the rates provided, use a calculator to compute how much would be withheld from Adilla's October paycheck in the following three cases. (Round to the nearest penny.) No Yes 10,000 Cumulative Gross Pay at 9/30/xX Social Security Medicare Case 1 $90,000 Case 2 $115,000 Case 3 $119,000 2. You have been asked to record the October payroll information using a payroll register and a general journal Review the file PAYROLL which is a computerized payroll register that follows these requirements. The columns will automatically re-total as new entries are made. Assume Adilla's Cumulative Gross Pay at September 30 could be $90,000, $115,000 or $119,000 3. To make the worksheet re-usable each month, the Social security tax formulas should be designed to automatically compute whether (1) the full tax is due (2) no tax is due le.g. the cumulative gross wages are over the maximum) or (3) some of the tax is due. =IF statements will be required. The =ROUND function should also be used on any formulas that multiply by decimals to eliminate rounding errors. PLEASE SEE THE SEPARATE HANDOUT THAT WILL EXPLAIN THESE FORMULAS IN MORE DETAIL 4. Open up the PAYROLL file. Enter the gross pay and federal income tax withheld for each employee. Enter $90,000 for Adilla's cumulative gross pay. (Cell 113). Then enter the formulas where indicated on the worksheet. As the formulas are entered the cells that contain zeroes will be automatically filled. Enter your name as a header on the workbook. Save the completed file as PAYROLLA. Make sure the worksheet prints to one page. Check figure: Adilla's net pay in cell H13 is $3,967.20. 5. What If Analysis - Verify that the formulas for Adilla work by entering $115,000 and $119,000 in cell 113. Compare your withholding amounts to the answers you computed in requirement 1. Correct any formulas as necessary. (Reset 113 back to $90,000 after you have verified that your formulas are working correctly) Save as PAYROLLS. 6. Journal Entries: a. On the tab called Payroll JE, record the journal entry to record the October payroll for the employees assuming that the payroll is paid on 10/31/XX and that Adilla's Cumulative Gross Pay through 9/30 is $90,000 b. On the tab called Employer's Tax JE prepare the journal entry to record the employer's payroll taxes for October. c. In addition, on the tab called Payment je, also prepare the journal entries to record the PAYMENT of all taxes and union dues assuming they will be paid on November 15. Save your updated file as PAYROLL6. 7. Update for Current Rates: Copy the contents of your Worksheet" into a new tab that you will call "Update". Then revise the data in cells G23-G25 using updated tax information for the current year. 8. Analyze: Click the "Analyze" Tab. On the screen is a chart of the four payroll taxes that a company might incur in total during the year. The four employer taxes are federal unemployment, state unemployment, social security, and Medicare. By the behavior shown on the chart, Identify IN THE TAB where indicated which of the four taxes each represents. 9. Manipulate the Data: Copy the most recent register over to the tab called "New Employee." To that payroll register, add the information for a new employee as follows: Her name is Gloria Gazelle. She is a union member. Her gross pay for October was $6,700. Her federal income tax is $990. Adjust any formulas. (HINT. Consider copying the formulas you created for Adilla down to the new employee. Make sure you TEST your formulas to make sure they work. HINT 2: Make sure that the totals of the register capture the new employee that you added. If they do not, then don't forget to adjust them so that they are accurate.) Preview the printout to make sure it fits on one page. Save the file as PAYROLLS. 10. Create a Chart: Create a new tab called Chart. In that tab, using the most recent file, (PAYROLL9) prepare a 3-D pie chart showing the percentage of Freddie Falcoln's Gross Pay that is withheld for income taxes, social security and all other deductions and how much is left over as net pay. (Consider creating a data table to make this chart easier.) Make sure you use a proper 3-line title on your chart (Who, What, When.) Make sure that your chart will print out correctly when activated. Save the file as PAYROLL10. 11. SHOW YOUR FORMULAS: Create a new tab called "Formulas." Copy the PAYROLL REGISTER from your most recent file over to the new tab. Use Control to show the formula contents of the register. You may have to widen the columns so I can see the entire formula. Re-save your work. Upload your File : After reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as PAYROLL FINAL + Your last name. Upload the final file using Canvas for grading. Payroll Supplemental Handout ACCT 133 Spreadsheet Accounting Payroll Register OBJECTIVE: This project develops an automated payroll register that will calculate the deductions and net pay (take-home pay) for employees, given the gross (or total) pay. It will also introduce you to one of the most powerful features in Excel; the IF function. In fact, this problem requires the use of "nested" =IF functions, because there are more than two possible results. Note: The =IF feature is one of the most powerful features built into Excel. Take the time to familiarize yourself with it. It can be VERY USEFUL in many business applications. (I use it to compute grades. Think about that. There are FIVE grade possibilities...so I use a series of nested IF statements. More about this in a later problem.) You may think this problem is complicated...but it will teach you VALUABLE information that you can use throughout your business (and personal) life. You will need to spend additional time on this problem. INVEST THE TIME! CONCEPTS: The payroll function is important for both employees and employers, and therefore is something that you need to understand. Since most of you will contribute to the Federal Insurance Contributions Act (which is commonly referred to as Social Security or FICA or OASDI), you need to understand how deductions are calculated. Both employees and employers pay Social Security taxes, and the 2017 rate for the tax is 6.2% for employees and employers on all earnings up to a maximum of $118,500. Medicare taxes are 1.45% on all earnings with no upper limit, paid by both employees and employers. (Don't worry about any Medicare "surcharges." Rates are continuously changing. We will complete the problems with rates quoted in the instructions.) As you will discover in this problem, the S118,500 limit (possibly outdated, but used in your problem) on Social Security taxes can create three possible different situations: Here are the different situations in this problem. 1. An employee (paid monthly) has cumulative earnings of S90,000 before this payroll period and earns $5,200 during the month. The amount of Social Security taxes for the month to be withheld from the employee would be $322.40 (85,200 X 6.2%) since the employee has not reached the $118,500 limit. 2. An employee (paid monthly) has cumulative earnings of $115,000 before this payroll period and eams $5,200 during the month. The amount of Social Security taxes for the month would be $217.00 (53,500 times 6.2%). Note that the $3,500 taxable amount increases the earnings to the maximum for Social Security which is $118,500 in your text. (In other words, she is only $3,500 away from the max prior to this October pay check. The next check will not be taxed at all since she has now reached the max.) 3. An employee (paid monthly) has cumulative earnings of $119,000 before this payroll period and eams $5,200 during the month. The amount of Social Security taxes for the month of October would be 0 since the employee has already reached and passed the maximum limit. Her checks will not be taxed for the month of October, nor for the remainder of the year. Developing a formula in a payroll register that can be used to meet all three situations given above requires the use of an IF function. A typical and simple=IF function is set up like this: (Three variables separated by commas.) =IF(condition to be tested, true result, false result) The =IF function needed to compute the Social Security tax gets a bit more complex, because there is more than just a true or false result. There are actually THREE POSSIBILITIES (as described above), so we will need to use a "nested =if" function to handle all three possibilities. Think about the three possibilities mentioned above, and consider this formula for FORMULA 1 to compute the Social Security Tax: =ROUND(IF(113>G25,0,1F((113+C13)>G25,(G25-113)*G23.C13*G23)).2) (a) (b) (c) (d) (e) Here is the logic of this formula: (a) This is the rounding function and rounds to a specified number of decimal places. Since payroll calculations involve dollars and cents, this function is necessary. The number of decimal places specified is placed at the very end of the formula. See letter (e). In this case the number of decimal places is 2, which will round to the nearest cent. (b) This is the first condition of the IF statement. It says if 113 (which is the cumulative gross pay prior to this pay period) is greater than G25 (which is the maximum amount subject to the FICA tax), then the amount to be placed in the cell is 0. In other words, since the employee is over the limit, he/she is not taxed and a 0 will be placed in the cell. (c) This says that if 113 (the cumulative gross pay) plus C13 (which is the pay for this period) adds up to more than the maximum amount for Social Security, then only tax the amount below the limit and do not tax the amount above the limit. The amount which is subject to the tax is calculated by taking the difference between G25 (the maximum) and 113 (which is the cumulative gross pay). This difference is the amount that is subject to the tax.... And then this amount is multiplied by the tax rate found in G23. (d) This says that if the other two conditions mentioned above are not met, then the employee MUST BE under the limit and the entire gross pay for the period must be taxed. The gross pay (C13) will be multiplied by the tax rate found in G23. (e) This specifies the number of decimal places in this formula, round to 2 places.) A very simple example of the IF function is FORMULA 4 (and formula 5) which can be used to calculate the union dues. The following formula would insert $50 in the cell for a union employee or in the cell for a nonunion employee. So look at this formula and see if you can follow it. =IF(B13="Yes",50,0) It says that if the answer in cell B13 is Yes then charge the employee the $50 in union dues....and if the answer is NOT Yes... then charge them zero. When entering this formula in cell G13, be sure that you type it exactly as shown. Do not leave any spaces between the numbers or symbols. FORMULA 3 and FORMULA 6 are very straight forward and are like formulas that we have written before. One last helpful hint - You must complete all the formulas before any of the author designated formulas will become active. If you insert your formulas first, and complete the calculations for Amber Adilla (row 13), then you will notice that the rest of the calculations will be completed automatically. The use of absolute references within a formula can be utilized so that the formulas can be copied to the new employee that the problem will have you add to your template. Also, utilizing absolute references where necessary will allow you to copy this same formula down to the second employee, rather than have you re-entering it again. Absolute references will allow certain designated cells to remain constant -- or not change -- as they are copied, (as opposed to relative cell addresses that will change as they are copied.) Absolute references can be designated by either placing a dollar sign before the part of the address that you do not want to change. Or you can use the F4 key as you are entering the cell to automatically place the absolute reference on the cell address. In this case, if you use the F4 key on anything that appears in the tax information data boxes ( in cells G23-G25) you can copy the formula down to the other employees, as well as the new employee that you will add later. That way, you won't have to re-enter it again. This allows you optimal flexibility when creating the formulas. Lapboard Font Alignment Number AS f Critter Crates Company D PR Puro Rou Critter Crates Company Payrol Register October 0 Federal income Tax Gross Pay Union Yes 10 11 12 Employee 13 Amber Adila 14 Bobby Bonita 15 Candice Chameleon 10 Donna Danilo 17 Freddy Falcon 18 10 20 21 No NO No Yes Deductions Social Cum Securty Medicare Union Gross Pary Tax Tax Dues Net Pay to Sept 30 FORMULA 1 FORMULA 3 FORMULA 4 FORMULAG 292 FORMULA 2 000 FORMULA 000 38,700 000 0.00 000 0.00 0.750 0.00 000 0.00 0.00 110,000 000 000 0.00 0.00 110,000 0.00 000 000 0.00 0.00 0.00 Ta information Current Yem Social security tax rate 2011 6204 FICA maximum income 5118500 23 24 25 20 27 28 20 30 Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee Ready Calculate Wrap Text General Paste BIU ili A ill M 3 Merge & Center $ %) 78-98 Conditional Formatting Clipboard 5 Font Alignment Number so A1 X f A 00 D E F G H L N O M TE o 1 GENERAL JOURNAL DATE PAGE CREDIT DESCRIPTION PR DETS 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 2 3 4 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee Arial MT - 12 A A B TUBE aA 25 Wrap Text General Pasto III lili 10 il lil Dil Merge & Center $ % 988 Conditio Formattin Clipboard Font Alignment 2 Number 15 A1 fr F F N R GENERAL JOURNAL DATE 5 6 Z 2 a 4 5 9 10 11 12 13 14 16 16 17 18 19 20 21 22 23 24 BESIGN Gelas 1 2 3 4 5 B 7 B 9 10 11 12 13 14 15 10 17 18 19 20 20 27 28 29 30 31 Worksheet Ready Calculate Payroll JE Employers Payroll JE Payment JE Analyze New Employee AN 8 BTU EEEEE Merge & Center % 000 Clipboard Font Nurnber Alignment 2 Q7 . fo B D E F G AA 1 H K L M N o P NAME GENERAL JOURNAL PAGE CREDITS DATE DESCRIPTION PR DEBITS 2 3 4 5 6 7 B g 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 1 2 3 4 5 6 7 B 9 10 11 12 13 ON - 7 B 9 10 11 12 13 14 15 16 17 18 19 20 15 16 17 18 19 20 Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee Ready Calculate AutoSave @ 00 PAYROLL - Excel File Home Insert Rosita Lady Hernande Draw Page Layout Formulas Data Review View Help Arial MI -12 - A A General Paste BTU- o A- 23 Wrap Test Merge Center $ . % *888 Selete- Format Conditional Format Call Formatting Table Styles Styles NO Font Allt 19 ube Cele L19 D H 1 2 3 4 5 Cumulative Payroll Taxes Series 1 7 8 Series 1 Series 2 g $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 -Series2 10 Series 3 Series 3 Series4 Series 4 0 2 4 6 8 10 12 12 13 14 15 16 17 18 19 Months Payroll JE Employers Payroll JE Worksheet ra Salle Payment JE Analyze New Employee 0