Question

				Corner Bakery
				Payroll Report
				31-Jan-20
Employees	Job position	Hours worked	Hourly wage	Regular pay	Retirement	Pay after pre-tax deduction	Social Security	Federal Tax	Net Pay
Jack Grey	Chef	35	$15.00	$525.00	15.75	$509.25	$28.01	$61.11	$420.13		Assumptions #1 Tax Information
Susan Grey	Chef	40	$15.00	$600.00	18	$582.00	$32.01	$69.84	$480.15		Social Security Tax	5.50%
Mary Wright	Chef	35	$10.00	$350.00	7	$343.00	$18.87	$41.16	$282.98		Federal Tax Annual Income Baseline	$ 34,500.00
John Berry	Cashier	33	$10.00	$330.00	6.6	$323.40	$17.79	$38.81	$266.81		Income Tax Bracket #1	27%
Collyn Bear	Cashier	32	$10.00	$320.00	6.4	$313.60	$17.25	$37.63	$258.72		Income Tax Bracket #2	12%
Chris Hill	Wait Staff	23	$7.00	$161.00	3.22	$157.78	$8.68	$18.93	$130.17
Laura Godwin	Wait Staff	30	$7.00	$210.00	4.2	$205.80	$11.32	$24.70	$169.79		Assumption Box #2
Samantha collins	Wait Staff	34	$7.00	$238.00	4.76	$233.24	$12.83	$27.99	$192.42		Retirement Contribution Scale
Ed Loyd	Wait Staff	25	$7.00	$175.00	3.5	$171.50	$9.43	$20.58	$141.49		Regular Pay	Retirement %
Steve Little	Driver	28	$8.00	$224.00	4.48	$219.52	$12.07	$26.34	$181.10		0	2.0%
Mike Nitsch	Driver	42	$8.00	$336.00	6.72	$329.28	$18.11	$39.51	$271.66		$                                         400.00	3.0%
Gwen Manchester	Manager	36	$15.00	$540.00	16.2	$523.80	$28.81	$62.86	$432.14		$                                         800.00	4.0%
Stephanie Baker	Manager	45	$15.00	$675.00	20.25	$654.75	$36.01	$78.57	$540.17
Total		438	$134.00	$4,684.00	$117.08	$4,566.92	$251.18	$548.03	$3,767.71
Average		33.69231	$10.31	$360.31	$9.01	$351.30	$19.32	$42.16	$289.82
Maximum		45	$15.00	$675.00	$20.25	$654.75	$36.01	$78.57	$540.17
Minimum		23	$7	$161.00	$3.22	$157.78	$8.68	$18.93	$130.17

  

1. You want to remember how certain things are calculated in the payroll reports. Insert a comment in the cell of the Federal

   Tax baseline number in assumption box 1 explaining how the IF statement is used to calculate the employees’ federal tax

   withholding.

Answer 1

Please find attached three images

- (1) payroll report

- (2) & (3) formulae to use in each cell:

A B C D E J K L 3 4 Employees Job position Hours worked Hourly wage Regular pay F G H I Payroll Report 31-Jan-20 Pay after pre- Pe Social Federal Retirement tax Security Tax deduction 15.75 509.25 28.01 61.11 61. 18.00 582.00 69.84 Net Pay 6 35.00 420.13 420. Jack Grey Susan Grey Chef Chef 15.00 15.00 525.00 600.00 Assumptions #1 Tax Information 5.50% 40.00 32.01 480.15 Social Security Tax Mary Wright Chef 35.00 10.00 350.00 7.00 343.00 18.87 41.16 34,500.00 33.00 10.00 9 John Berry Collyn Bear 11 Chris Hill Cashier Cashier Wait Staff 10 Cony De 330.00 320.00 161.00 32.00 23.00 6.60 6.40 3.22 10.00 7.00 Federal Tax 282.98 Annual Income Baseline Income Tax 266.81 Bracket #1 258.72 Income Tax Bracket #2 130.17 323.40 313.60 157.78 17.79 17.25 8.68 27% 12% 38.81 37.63 18.93 Laura Godwin Wait Staff 30.00 7.00 210.00 4.20 205.80 11.32 24.70 169.79 Assumption Box #2 12 238.00 4.76 233.24 Samantha 13 collins 14 Ed Loyd 15 Steve Little 16 Mike Nitsch Gwen 17 Manchester Stephanie 18 Baker Wait Staff Wait Staff Driver Driver 34.00 25.00 28.00 7.00 7.00 8.00 8.00 15.00 175.00 224.00 336.00 540.00 675.00 3.50 4.48 6.72 16.20 171.50 219.52 329.28 523.80 12.83 9.43 12.07 18.11 28.81 27.99 20.58 26.34 39.51 Retirement 192.42 Contribution Scale Retiremen 141.49 Regular Pay 181. 10 0 2.00% 271.66 400 3.00% 432.14 800 4.00% 540.17 42.00 Manager 36.00 62.86 Manager 45.00 15.00 20.25 654.75 36.01 78.57 19 20 Total 21 Average 22 Maximum 23 Minimum 438.00 33.69231 45.00 23.00 134.00 10.31 15.00 7.00 4,684.00 360.31 675.00 161.00 117.08 9.01 20.25 3.22 4,566.92 351.30 654.75 157.78 251.18 19.32 36.01 8.68 548.03 42.16 78.57 18.93 3,767.71 289.82 540.17 130.17 24

A B C D E F G H I J K Payroll Report 31-Jan-20 Pay after pre-tax deduction 5 Employees Job position Hours worked Hourly wage Regular pay Retirement Social Security Federal Tax Net Pay Jack Grey Chef 35.00 15.00 =(C6*D6) =IF(E6<$L$16,(E6*$M$15),IF(AND(E6 >$L$16,E6<$L$17).(E6*$M$16).(E16* $M$17))) -E6-F6 =G6*$M$7 =IF(G6<$M$8,(G6*$M$10).(G6 =G6-H6-16 *$M$9)) Assumptions #1 Tax Information Susan Grey Chef 40.00 15.00 =(C7*D7) =E7-F7 =G7*$M$7 =IF(E7<$L$16 (E7*$M$15),IF(AND(E7 >$L$16,E7<$L$17).(E7*$M$16).(E17* $M$17))) 5.50% =IF(G7<$M$8,(G7*$M$10).(G7 | =G7-H7-17 *$M$9) Social Security Tax Mary Wright Chef 35.00 10.00 =(C8*D8) =IF(E8<$L$16,(E8*$M$15),IF(AND(E8 >$L$16,E8<$L$17),(E8*$M$16).(E 18* $M$17))) =E8-F8 =G8*$M$7 =IF(G8<$M$8,(G8*$M$10),(G8 =G8-H8-18 *$M$9)) Federal Tax Annual Income Baseline 34,500.00 John Berry Cashier 33.00 10.00 =(C9*09) =E9-F9 =G9*$M$7 =IF(E9<$L$16,(E9*$M$15),IF(AND(E9 >$L$16,E9<$L$17).(E9*$M$16).(E19* $M$17))) =IF(G9<$M$8,(G9*$M$10),(G9 *$M$9)) 27% =G9-H9-19 Income Tax Bracket #1 Collyn Bear 32.00 10.00 =(C10*D10) - =E10-F10 =G10*$M$7 =IF(E 10<$L$16,(E10*$M$15),IF(AND E10>$L$16,E10<$L$17).(E10*$M$16 ).(E20*$M$17))) =IF(G10<$M$8,(G10*$M$10). G10*$M$9)) =G10-H10-110 Income Tax Bracket #2 12% 10 Chris Hill Wait Staff 23.00 7.00 =(C11*011) . =E11-F11 =IF(E11<$L$16,(E11*$M$15),IF(AND E11>$L$16,E11<$L$17).(E11*$M$16 (E21*$M$17))) =G11*$M$7 =IF(G11<$M$8,(G11*$M$10). | =G11-H11-111 G11*$M$9)) Laura Godwin Wait Staff 30.00 7.00 =(C12*D12) =IF(E 12<$L$16,(E12*$M$15),IF(AND E12>$L$16,E12<$L$17).(E12*$M$16 (E22*$M$17))) =E12-F12 =G12*$M$7 Assumption Box #2 =IF(G12<$M$8,(G12*$M$10). | =G12-H12-112 G12*$M$9)) Samantha collins Retirement Contribution Wait Staff 34.00 7.00 =(C13*013) =E13-F13 =IF(E13<$L$16,(E13*$M$15),IF(AND E13>$L$16,E13<$L$17),(E13*$M$16 ).(E23*$M$17))) =G13*$M$7 =IF(G13<$M$8,(G13*$M$10). | =G13-H13-113 G13*$M$9)) Scale 13 Ed Loyd 25.00 Wait Staff 7.00 =(C14*D14) =IF(E14<$L$16,(E14*$M$15),IF(AND E14>$L$16,E14<$L$17),(E14*$M$16 (#REF!*$M$17))) =E14-F14 =G14*$M$7 Retirement =IF(G14<$M$8,(G14*$M$10).=G14-H14-114 G14*$M$9)) Regular Pay Steve Little Driver 28.00 8.00 =(C15*D15) =IF(E15<$L$16.(E15*SMS15).JF(AND E15>$L$16,E15<$L$17).(E15*$M$16 (E24*$M$17))) =E15-F15 =G15*$M$7 2.00% =IF(G15<$M$8,(G15*$M$10). | =G15-H15-115 G15*$M$9)) 15

ДА в к Laura Godwin Wait Staff 30.00 7.00 =(C12*D12) =E12-F12 =IF(E12<$L$16,(E12*$M$15),IF(AND E12>$L$16,E12<$L$17).(E12*$M$16 ).(E22*$M$17))) =G12*$M$7 Assumption Box #2 =IF(G12<$M$8,(G12*$M$10). | =G12-H12-112 G12*$M$9)) 12 Samantha collins Wait Staff 34.00 7.00 =(C13D13) =E13-F13 Retirement Contribution =G13*$M$7 =IF(E13<$L$16,(E13*$M$15),IF(AND E13>$L$16,E13<$L$17).(E13*$M$16 ).(E23*$M$17))) =G13-H13-113 =IF(G13<$M$8,(G13*$M$10). G13*$M$9)) Scale 13 Retirement Ed Loyd 25.00 Wait Staff 7.00 =(C14*D14) =IF(E14<$L$16,(E14*$M$15),IF(AND E14>$L$16,E14<$L$17).(E14*$M$16 ).(#REF!*$M$17))) =E14-F14 =G14*$M$7 =IF(G14<$M$8,(G14*$M$10).( =G14-H14-114 G14*$M$9)) Regular Pay 14 Steve Little Driver 28.00 | 8.00 | =(C15*D15) =IF(E15<$L$16,(E15*$M$15),IF(AND E15>$L$16,E15<$L$17).(E 15*$M$16 ).(E24*$M$17))) =E15-F15 =G15*$M$7 0 =IF(G15<$M$8,(G15*$M$10). | =G15-H15-115 G15*$M$9)) 2.00% 15 Mike Nitsch Driver 42.00 8.00 =(C16*D16) =IF(E16<$L$16,(E16*$M$15),IF(AND E16>$L$16,E16<$L$17).(E16*$M$16 ).(E25*$M$17))) =E16-F16 =G16*$M$7 =IF(G16<$M$8,(G16*$M$10). | =G16-H16-116 G16*$M$9)) 400 3.00% 16 Gwen Manchester Manager 36.00 15.00 =(C17D17) =E17-F17 =G17*$M$7 =IF(E 17<$L$16,(E17*$M$15),IF(AND E17>$L$16,E17<$L$17),(E17*$M$16 ).(E26*$M$17))) =IF(G17<$M$8,(G17*$M$10). | =G17-H17-17 G17*$M$9)) 800 4.00% 17 Stephanie Baker Manager 45.00 15.00 =(C18*018) =E18-F18 =IF(E 18<$L$16,(E18*$M$15),IF(AND E18>$L$16,E18<$L$17).(E18*$M$16 .(E27*$M$17))) =G18*$M$7 =IF(G18<$M$8,(G18*$M$10). | =G18-H18-118 G18*$M$9)) 18 19 20 Total =SUM(C6:C19) =SUM(D6:D19) =SUM(E6:E19) =SUM(F6:F19) =SUM(G6: G19) =SUM(HO:H19) =SUM(16:119) =SUM(J6:J19) Average =AVERAGE(C6:C18) =AVERAGE(D6:D18 =AVERAGE(E6:E18) =AVERAGE(F6:F18) =AVERAGE(G6:G18) =AVERAGE(H6:H18) =AVERAGE(16:118) =AVERAGE(J6:J18) =MAX(16:118) 22 Maximum 23 Minimum 24 =MAX(C6:C18) =MIN(C6:C18) =MAX(D6:D18) =MIN(D6:D18) =MAX(E6:E18) =MIN(E6:E18) =MAX(F6:F18) =MIN(F6:F18) =MAX(G6:G18) =MIN(G6:G18) =MAX(H6:H18) =MIN(H6:H18) =MAX(J6:J18) =MIN(J6:J18) =MIN(16:118)