B D G H J Net Pay 679.00 A E F 1 Fox Lake Country Club - Employee Salary Template 2 3 Weekly Sales $ 25,000 4 Social Security Rate 8.2% 5 Federal Tax Rate 29% 6 6 Hours Hourly Employee Commision 7 Gross Pay Worked Federal Tax Soc Sec Tax Wage 8 Rick 35 $ 15.00 2025.00 $ 587 25$ 9 Linda 40 $ 12.00 8% $ 2.480.00 719.20 S 10 Stan 35 $ 10.00 5% $ 1,600.00 464.00 $ 11 Elaine 33 $ 13.00 1% $ 196.91 $ 12 Burton 32 $ 10.00 0% $ 320.00 92.80 $ 13 Don 23 $ 11.00 3%$ 1,003.00 290.87S 14 Trides 30 $ 10.00 10% $ 2800.00 812.00 $ 15 Paul 12.00 0% $ 408.00 118.32S 16 Laurie 33 $ 12.00 5% $ 1.646.00 477.34 $ 17 Ray 28 $ 11.00 0% $ 308.00 89.32 $ 18 Sunder 13.00 6% $ 2.046.00 593.34 $ 19 Bachong 36 $ 12.00 3% $ 1,182.00 342.78S 20 Michael 50 $ 10.00 3% $ 1.250.00 362.50 $ 21 Payroll Totals 451 Is $ 17.747.00 $ 5,147 $ 22 23 Instructions: 24 Insert a function to calculate Gross Pay (In other words, if an employee receives a commission, 25 then his/her Gross Pay will equal (Hours Worked * Hourly Wage) + (Commission % * Weekly Sales). . 26 Insert a formula to calculate the amount of Federal Tax (Note: Federal and Soc Sec Tax are a percentage of the gross pay). 27 Insert a formula to calculate the amount of Soc Sec Tax (Note: Federal and Soc Sec Tax are a percentage of the gross pay). 28 Insert a formula to calculate each employees Net Pay (the difference between gross pay and the total tax). 29 Insert a formula to calculate the company totals for Hours Worked, Gross Pay, and Net Pay. 30 31 Questions: 32 Which employee received the largest paycheck (Net Pay)? 33 What is the total number of hours worked for all employees? 34 What is the minimum Net Pay? 35 What is the average Net Pay? 36 How much did Fox Lake pay in payroll (use the Gross Pay column)? 37 If Weekly Sales drop to $8,000, which employee will receive the largest paycheck? (Type the value and change weekly sales back to 25k after you answer) 38 What would the total Net Pay equal if Weekly Sales was $40,0007Type the value and change weekly sales back to 25k after you answer) 166.05 $ 1,271.70 203.36 $ 1.557.44 131.20$ 1,004,80 55.68 $ 426,41 26.24 $ 200.96 82.25$ 629.88 229.60 $ 1,758,40 33.46 $ 256,22 134.97$ 1,033.69 25.26 $ 193,42 167.77 $ 1.284.89 96.92 $ 742.30 102.50 $ 785.00 1,455.25 $ 11,145.12 Tridas $451.00 $193.42 857.32 17.747.00 $ 39 40 41 Directions Book_Order Pivot Table Charity_Donations Employee_Payroll Pricing_Info Golf Distribution