I need help to the excel

IMPLEMENTATION Use Table 10.1 and Figure 10.1 to help you understand the parameter values and to enter the employees' input data correctly. Make sure you format the numeric cells, choosing the appropriate Number, Currency, or Percentage format. TABLE 10.1 Payroll Component Regular pay Retirement Contribution Comments Formula (Hours worked *Hourly wage) (Vlookup function)"Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale. Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax If Statement=Logical test: Pay after pretax deduction"52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False: Pay after pretax deduction Assumption box 1, Tax Bracket 2 (Pay after deductions)-(Social Security + Federal tax) Net Pay DSS-2 Project, Page 2 Total, Max, Min, Average Insert the formulas for Total, Average, Maximum, Minimum, statistics for Hours Worked, Regular Pay, and Net Pay, etc., in the worksheet Figure 10.1 Corner Bakery Payroll Report Date Hem Why Pey Are Tool ob www takaran 20.00 Chat Federal 5 6. Say 15.00 Team 315.00 235.00 05 15.00 $15.00 Rem Be Cash 10 w 11 God w 12 sath 13 dvd 14 Wave 15 h 16. Owen Manchester Mana 17 S 15.00 215.00 els 150 100 Sabe 20.30 000 SIIN 19 29 MS 21.00 S16 22 M 23 DESIGN PRINCIPLE You will create the spreadsheet in Figure 10.1 from scratch. The layout of the payroll calculations is arranged as a rectangle, with rows of calculations corresponding to the employee information input rows. Similarly, the statistics calculations are arranged in rectangular fashion corresponding to the appropriate variable columns. The formulas must be correct; otherwise the spreadsheet application will calculate incorrect answers and be of little value to the user. After you create or copy a formula, you should check to make sure the answer is correct. All the numbers should be formatted consistently to improve readability IMPLEMENTATION Use Table 10.1 to help you think about the formulas you need to enter for each employee. There are two ways you could enter the formulas. One way is to enter the Regular Pay formula for the first employee, Jack Grey, verify that the formula is correct, and then copy the formula to the other employees; then repeat the procedure for each of the other six variables. A second way is to enter the formulas for Jack Grey, verify that the formulas are correct, and then copy them all at once to the other employees. Make sure you do not type the parameter values in the formulas but rather use the parameter cell addresses in the formulas correctly. Make sure you use the appropriate statistical functions for the formulas in the statistical summary area in the bottom rows of the report Use the Audit Formulas tool to verify that you have entered all the formulas correctly. Make sure you think about whether or not the formulas are calculating appropriate values. DSS-2 Project, Page 3 USING THE SPREADSHEET APPUCATION As the bakery's human resource manager, you can use the weekly payroll edhe pse anter a variety of manager what questions. On a new worksheet in the same workbook create other worksheet, named Anwesheet" fornering the followigestion. The should have the then the one that incudes the text of the question and one that only has the answer the counter should be in a different text color than the other two columns where appropriate, create a formato calculate the correct toestion should te fully answered. My advice that you make a copy of the original Pool worksheet and proporomething similar. This copy will not be graded it only for you to the question below the guestion, reset the copy of the Payroll worksheet tos nga ales so you can see the next gestion interna drecta mustill have a reference on your wer sheetso know where to find the completed 1. for the current What were the total osworted by the eyes? What was the pay? 2. You are considering some change for some particular employees Wack Grey wore 40 hours per wenn Show, what would be b. Compared to the current weet werk. Somosogard to but she worked just 30 hours, what would be the difference interneta The baleriscondering in engloyees contribute ihr proporre ay to the retirement. What would be the amount for me, Manchester Stephanie Baker the retirement percentage was for each of the three gregones? You want to remember how certain things are called in the paroleportachelore Federal Tabbermotorang wees om det withholding. Bere you reference this on your we short 4 You want to the payroll report information coming on www Create a stacked barchart to compare the three che showing each chelsement contribution, andet values. Mow the chartanew worest and tame the worksheet te bere that is worksheet and not an object. You want Indude added features such as dy dependendo chama Create an appropriate pechaninis own the showing are God's contribution Social Security, federal depoy Pease bear when you will points is inserted object Use value-added features such as criticart, and this Mow the chart to a new worksheet and name the chart worksheets As the human resource manage. You are interested in exploring various cose Gold See tool to calculate the res to the following in Conhear would be to have a weet of $500. How many houn does she need to work to that As the human resource manager you need to come the payroll of your conecting the verlap hours for Managers on Duty. The total amount of you need to each manager so hoursWhat the amount that would savinnet pay on a weekly basis and over the course of the year you need this? 2. You are thinking of the bring one driver in the morning and are in the women for all toon day for nach driver dow) What the amount you would save is reparayon de cette Year you implement the 08527 Paget Aher using the weekly parroll sheet, you would like to spend to be the shodd be done in the original parall worksheet Move the two rectangles comprising the nations are there to the below the payroll report Use the Audit tool to verify that the cell reference in the formas have been modified comedy Modify the engine pool predetto indude column to the Social Security and federal combined to right before et Pay. Create the appropriate for modify het Pay formulato account for the new columitat Modify the Answers Sheet by setting printares that includes only the three columns with the bustion text of the As the bakery's human resource manager, you can use the weekly payroll spreadsheet application to analyze the payroll budget and answer a variety of managerial what-if questions. On a new worksheet in the same workbook create another worksheet, named "Answer Sheet," for answering the following questions. The answer sheet should have three columns, one for the question number, one that includes the text of the question and one that only has the answers. The column with the answers should be in a different text color than the other two columns. Where appropriate, create a formula to calculate the correct answer. Each question should be fully answered. My advice is that you make a copy of the original Payroll worksheet and label it "Payroll Copy" or something similar. This copy will not be graded, and it is only for you to answer the questions below. After answering each question, reset the copy of the Payroll worksheet to its original values so you can answer the next question. Questions not requiring a direct answer must still have a reference on your answer sheet so I know where to find the completed task. 1. For the current week: a. What were the total hours worked by the employees? b. What was their average net pay? 2. You are considering some changes for some particular employees. a. If Jack Grey worked 40 hours per week and got a raise to $25 per hour, what would be his net pay? b. Compared to the current week, if next week Samantha Collin's hourly wage was raised to $18 but she worked just 30 hours, what would be the difference in her net pay? c. The bakery is considering having employees contribute a higher percentage of their regular pay to their retirement. What would be the new retirement amount for Mike Nitsch, Gwen Manchester, and Stephanie Baker, if the retirement percentage was increased 0.5 % for each of the three regular pay categories? 3. 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. Be sure you reference this on your answer sheet. 4. You want to visualize the payroll report's information, comparing employees in a variety of ways. a. Create a stacked bar chart to compare the three chefs, showing each chef's retirement contribution, and net pay values. Move the chart to a new worksheet and name the worksheet an appropriate title. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Include value-added features such as a title, x-axis and y-axis titles, legend, labels, and color scheme. b. Create an appropriate pie chart in its own worksheet showing Laure Godwin's retirement contribution, Social Security, federal tax, and net pay. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Use value-added features such as descriptive chart title, legend, and labels. Move the chart to a new worksheet and name the chart worksheet Laura Godwin 5. As the human resource manager, you are interested in exploring various what-if situations. Use the Goal Seek tool to calculate the answers to the following question. a. Collyn Bear would like to have a weekly net pay of $500. How many hours does she need to work to earn that amount? 6. As the human resource manager you need to optimize the payroll of your managers by correcting the overlap hours for Managers on Duty. The total amount of time you need to each manager is 30 hours a week. What is the amount that would save in net pay on a weekly basis and over the course of the year if you implemented this strategy? 7. You are thinking of scheduling one driver in the morning and one driver in late afternoon for a total of 3 hours a day for each driver (7 days a week). What is the amount you would save in regular pay on a weekly basis and over a course of the year if you implement this strategy? 8. After using the weekly payroll spreadsheet, you would like to slightly modify it to improve its usability (this should be done in the original payroll worksheet): a. Move the two rectangles comprising the assumptions area from the right side to the area below the payroll report. Use the Audit tool to verify that the cell reference in the formulas have been modified correctly. b. Modify the original payroll spreadsheet to include a column that shows the Social Security and federal taxes combined total, right before Net Pay. Create the appropriate formula and modify the Net Pay formula to account for the new column instead. 9. Modify the Answers Sheet by setting a print area that includes only the three columns with the question #, text of the question, and the answer. IMPLEMENTATION Use Table 10.1 and Figure 10.1 to help you understand the parameter values and to enter the employees' input data correctly. Make sure you format the numeric cells, choosing the appropriate Number. Currency, or Percentage format Comments TABLE 10.1 Payroll Component Regular pay Retirement Contribution Formula (Houts worked "Hourly ware) Viookup function"Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax of Statement Logical test: Pay after pretax deduction"52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False Pay after pretax deduction Assumption box 1 Tax Bracket 2 Net Pay (Pay after deductions Social Security + Federal tax) Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary DSS-2 Project, Page 2 Total, Max, Min, Average Insert the formulas for Total, Average, Maximum, Minimum, statistics for Hours Worked, Regular Pay and NetPay, etc. In the worksheet Figure 10.1 Corner Bakery Payal Report Du RA wwww 7 May 13.00 Compte 10 w Wat WS 2. 14 13 16 werden 17 Sehat 1 19 1000 es mes 71 22 11 DESIGN PRINCIPLE You will create the spreadsheet in Figure 10.1 from scratch. The layout of the payroll calculations is arranged as a rectangle, with rows of calculations corresponding to the employee information input rows. Similarly, the statistics calculations are arranged in rectangular fashion corresponding to the appropriate variable columns. The formulas must be correct; otherwise the spreadsheet application will calculate incorrect answers and be of little value to the user. After you create or copy a formula, you should check to make sure the answer is correct. All the numbers should be formatted consistently to improve readability. IMPLEMENTATION Use Table 10.1 to help you think about the formulas you need to enter for each employee. There are two ways you could enter the formulas. One way is to enter the Regular Pay formula for the first employee, Jack Grey, verify that the formula is correct, and then copy the formula to the other employees; then repeat the procedure for each of the other six variables. A second way is to enter the formulas for Jack Grey, verify that the formulas are correct, and then copy them all at once to the other employees. Make sure you do not type the parameter values in the formulas but rather use the parameter cell addresses in the formulas correctly. Make sure you use the appropriate statistical functions for the formulas in the statistical summary area in the bottom rows of the report Use the Audit Formulas tool to verify that you have entered all the formulas correctly. Make sure you think about whether or not the formulas are calculating appropriate values. DSS-2 Project, Page 3 USING THE SPREADSHEET APPLICATION As the bakery's human resource manager, you can use the weekly payroll spreadsheet application to analyze the payroll budget and answer a variety of managerial what-if questions. On a new worksheet in the same workbook create another worksheet, named "Answer Sheet," for answering the following questions. The answer sheet should have three columns, one for the question number, one that includes the text of the question and one that only has the answers. The column with the answers should be in a different text color than the other two columns. Where appropriate, create a formula to calculate the correct answer. Each question should be fully answered. My advice is that you make a copy of the original Payroll worksheet and label it "Payroll Copy" or something similar. This copy will not be graded, and it is only for you to answer the questions below. After answering each question, reset the copy of the Payroll worksheet to its original values so you can answer the next question. Questions not requiring a direct answer must still have a reference on your answer sheet so I know where to find the completed task. 1. For the current week: a. What were the total hours worked by the employees? b. What was their average net pay? 2. You are considering some changes for some particular employees. a. tf Jack Grey worked 40 hours per week and got a raise to $25 per hour, what would be his net pay? b. Compared to the current week, if next week Samantha Collin's hourly wage was raised to $18 but she worked just 30 hours, what would be the difference in her net pay? The bakery is considering having employees contribute a higher percentage of their regular pay to their retirement. What would be the new retirement amount for Mike Nitsch, Gwen Manchester, and Stephanie Baker, if the retirement percentage was increased 0.5 % for each of the three regular pay categories? 3. 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 i explaining how the IF statement is used to calculate the employees federal tax withholding. Be sure you reference this on your answer sheet. 4. You want to visualize the payroll reports information, comparing employees in a variety of ways. a. Create a stacked bar chart to compare the three chefs, showing each chet's retirement contribution, and net pay values. Move the chart to a new worksheet and name the worksheet an appropriate title. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Include value-added features such as a title x-axis and y-axis titles, legend, labels, and color scheme. b. Create an appropriate pie chart in its own worksheet showing Laure Godwin's retirement contribution, Social Security, federal tax, and net pay, Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Use value-added features such as descriptive chart title, legend, and labels. Move the chart to a new worksheet and name the chart worksheet Laura Godwin 5. As the human resource manager, you are interested in exploring various what-if situations. Use the Goal Seek tool to calculate the answers to the following question a. Collyn Bear would like to have a weekly net pay of $500. How many hours does she need to work to earn that amount? 6. As the human resource manager you need to optimize the payroll of your managers by correcting the overlap hours for Managers on Duty. The total amount of time you need to each manager is 30 hours a week. What is the amount that would save in net pay on a weekly basis and over the course of the year if you implemented this strategy? 7. You are thinking of scheduling one driver in the morning and one driver in late afternoon for a total of 3 hours a day for each driver (7 days a week). What is the amount you would save in regular pay on a weekly basis and over a course of the Year if you implement this strategy? DSS-2 Project, Page 4 8. After using the weekly payroll spreadsheet, you would like to slightly modify it to improve its usability (this should be done in the original payroll worksheet): a Move the two rectangles comprising the assumptions area from the right side to the area below the payroll report. Use the Audit tool to verify that the cell reference in the formulas have been modified correctly b. Modify the original payroll spreadsheet to include a column that shows the Social Security and federal taxes combined total, right before Net Pay. Create the appropriate formula and modify the Net Pay formula to account for the new column instead. 9. Modify the Answers Sheet by setting a print area that includes only the three columns with the question, text of the question, and the answer. IMPLEMENTATION Use Table 10.1 and Figure 10.1 to help you understand the parameter values and to enter the employees' input data correctly. Make sure you format the numeric cells, choosing the appropriate Number, Currency, or Percentage format. TABLE 10.1 Payroll Component Regular pay Retirement Contribution Comments Formula (Hours worked *Hourly wage) (Vlookup function)"Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale. Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax If Statement=Logical test: Pay after pretax deduction"52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False: Pay after pretax deduction Assumption box 1, Tax Bracket 2 (Pay after deductions)-(Social Security + Federal tax) Net Pay DSS-2 Project, Page 2 Total, Max, Min, Average Insert the formulas for Total, Average, Maximum, Minimum, statistics for Hours Worked, Regular Pay, and Net Pay, etc., in the worksheet Figure 10.1 Corner Bakery Payroll Report Date Hem Why Pey Are Tool ob www takaran 20.00 Chat Federal 5 6. Say 15.00 Team 315.00 235.00 05 15.00 $15.00 Rem Be Cash 10 w 11 God w 12 sath 13 dvd 14 Wave 15 h 16. Owen Manchester Mana 17 S 15.00 215.00 els 150 100 Sabe 20.30 000 SIIN 19 29 MS 21.00 S16 22 M 23 DESIGN PRINCIPLE You will create the spreadsheet in Figure 10.1 from scratch. The layout of the payroll calculations is arranged as a rectangle, with rows of calculations corresponding to the employee information input rows. Similarly, the statistics calculations are arranged in rectangular fashion corresponding to the appropriate variable columns. The formulas must be correct; otherwise the spreadsheet application will calculate incorrect answers and be of little value to the user. After you create or copy a formula, you should check to make sure the answer is correct. All the numbers should be formatted consistently to improve readability IMPLEMENTATION Use Table 10.1 to help you think about the formulas you need to enter for each employee. There are two ways you could enter the formulas. One way is to enter the Regular Pay formula for the first employee, Jack Grey, verify that the formula is correct, and then copy the formula to the other employees; then repeat the procedure for each of the other six variables. A second way is to enter the formulas for Jack Grey, verify that the formulas are correct, and then copy them all at once to the other employees. Make sure you do not type the parameter values in the formulas but rather use the parameter cell addresses in the formulas correctly. Make sure you use the appropriate statistical functions for the formulas in the statistical summary area in the bottom rows of the report Use the Audit Formulas tool to verify that you have entered all the formulas correctly. Make sure you think about whether or not the formulas are calculating appropriate values. DSS-2 Project, Page 3 USING THE SPREADSHEET APPUCATION As the bakery's human resource manager, you can use the weekly payroll edhe pse anter a variety of manager what questions. On a new worksheet in the same workbook create other worksheet, named Anwesheet" fornering the followigestion. The should have the then the one that incudes the text of the question and one that only has the answer the counter should be in a different text color than the other two columns where appropriate, create a formato calculate the correct toestion should te fully answered. My advice that you make a copy of the original Pool worksheet and proporomething similar. This copy will not be graded it only for you to the question below the guestion, reset the copy of the Payroll worksheet tos nga ales so you can see the next gestion interna drecta mustill have a reference on your wer sheetso know where to find the completed 1. for the current What were the total osworted by the eyes? What was the pay? 2. You are considering some change for some particular employees Wack Grey wore 40 hours per wenn Show, what would be b. Compared to the current weet werk. Somosogard to but she worked just 30 hours, what would be the difference interneta The baleriscondering in engloyees contribute ihr proporre ay to the retirement. What would be the amount for me, Manchester Stephanie Baker the retirement percentage was for each of the three gregones? You want to remember how certain things are called in the paroleportachelore Federal Tabbermotorang wees om det withholding. Bere you reference this on your we short 4 You want to the payroll report information coming on www Create a stacked barchart to compare the three che showing each chelsement contribution, andet values. Mow the chartanew worest and tame the worksheet te bere that is worksheet and not an object. You want Indude added features such as dy dependendo chama Create an appropriate pechaninis own the showing are God's contribution Social Security, federal depoy Pease bear when you will points is inserted object Use value-added features such as criticart, and this Mow the chart to a new worksheet and name the chart worksheets As the human resource manage. You are interested in exploring various cose Gold See tool to calculate the res to the following in Conhear would be to have a weet of $500. How many houn does she need to work to that As the human resource manager you need to come the payroll of your conecting the verlap hours for Managers on Duty. The total amount of you need to each manager so hoursWhat the amount that would savinnet pay on a weekly basis and over the course of the year you need this? 2. You are thinking of the bring one driver in the morning and are in the women for all toon day for nach driver dow) What the amount you would save is reparayon de cette Year you implement the 08527 Paget Aher using the weekly parroll sheet, you would like to spend to be the shodd be done in the original parall worksheet Move the two rectangles comprising the nations are there to the below the payroll report Use the Audit tool to verify that the cell reference in the formas have been modified comedy Modify the engine pool predetto indude column to the Social Security and federal combined to right before et Pay. Create the appropriate for modify het Pay formulato account for the new columitat Modify the Answers Sheet by setting printares that includes only the three columns with the bustion text of the As the bakery's human resource manager, you can use the weekly payroll spreadsheet application to analyze the payroll budget and answer a variety of managerial what-if questions. On a new worksheet in the same workbook create another worksheet, named "Answer Sheet," for answering the following questions. The answer sheet should have three columns, one for the question number, one that includes the text of the question and one that only has the answers. The column with the answers should be in a different text color than the other two columns. Where appropriate, create a formula to calculate the correct answer. Each question should be fully answered. My advice is that you make a copy of the original Payroll worksheet and label it "Payroll Copy" or something similar. This copy will not be graded, and it is only for you to answer the questions below. After answering each question, reset the copy of the Payroll worksheet to its original values so you can answer the next question. Questions not requiring a direct answer must still have a reference on your answer sheet so I know where to find the completed task. 1. For the current week: a. What were the total hours worked by the employees? b. What was their average net pay? 2. You are considering some changes for some particular employees. a. If Jack Grey worked 40 hours per week and got a raise to $25 per hour, what would be his net pay? b. Compared to the current week, if next week Samantha Collin's hourly wage was raised to $18 but she worked just 30 hours, what would be the difference in her net pay? c. The bakery is considering having employees contribute a higher percentage of their regular pay to their retirement. What would be the new retirement amount for Mike Nitsch, Gwen Manchester, and Stephanie Baker, if the retirement percentage was increased 0.5 % for each of the three regular pay categories? 3. 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. Be sure you reference this on your answer sheet. 4. You want to visualize the payroll report's information, comparing employees in a variety of ways. a. Create a stacked bar chart to compare the three chefs, showing each chef's retirement contribution, and net pay values. Move the chart to a new worksheet and name the worksheet an appropriate title. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Include value-added features such as a title, x-axis and y-axis titles, legend, labels, and color scheme. b. Create an appropriate pie chart in its own worksheet showing Laure Godwin's retirement contribution, Social Security, federal tax, and net pay. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Use value-added features such as descriptive chart title, legend, and labels. Move the chart to a new worksheet and name the chart worksheet Laura Godwin 5. As the human resource manager, you are interested in exploring various what-if situations. Use the Goal Seek tool to calculate the answers to the following question. a. Collyn Bear would like to have a weekly net pay of $500. How many hours does she need to work to earn that amount? 6. As the human resource manager you need to optimize the payroll of your managers by correcting the overlap hours for Managers on Duty. The total amount of time you need to each manager is 30 hours a week. What is the amount that would save in net pay on a weekly basis and over the course of the year if you implemented this strategy? 7. You are thinking of scheduling one driver in the morning and one driver in late afternoon for a total of 3 hours a day for each driver (7 days a week). What is the amount you would save in regular pay on a weekly basis and over a course of the year if you implement this strategy? 8. After using the weekly payroll spreadsheet, you would like to slightly modify it to improve its usability (this should be done in the original payroll worksheet): a. Move the two rectangles comprising the assumptions area from the right side to the area below the payroll report. Use the Audit tool to verify that the cell reference in the formulas have been modified correctly. b. Modify the original payroll spreadsheet to include a column that shows the Social Security and federal taxes combined total, right before Net Pay. Create the appropriate formula and modify the Net Pay formula to account for the new column instead. 9. Modify the Answers Sheet by setting a print area that includes only the three columns with the question #, text of the question, and the answer. IMPLEMENTATION Use Table 10.1 and Figure 10.1 to help you understand the parameter values and to enter the employees' input data correctly. Make sure you format the numeric cells, choosing the appropriate Number. Currency, or Percentage format Comments TABLE 10.1 Payroll Component Regular pay Retirement Contribution Formula (Houts worked "Hourly ware) Viookup function"Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax of Statement Logical test: Pay after pretax deduction"52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False Pay after pretax deduction Assumption box 1 Tax Bracket 2 Net Pay (Pay after deductions Social Security + Federal tax) Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary DSS-2 Project, Page 2 Total, Max, Min, Average Insert the formulas for Total, Average, Maximum, Minimum, statistics for Hours Worked, Regular Pay and NetPay, etc. In the worksheet Figure 10.1 Corner Bakery Payal Report Du RA wwww 7 May 13.00 Compte 10 w Wat WS 2. 14 13 16 werden 17 Sehat 1 19 1000 es mes 71 22 11 DESIGN PRINCIPLE You will create the spreadsheet in Figure 10.1 from scratch. The layout of the payroll calculations is arranged as a rectangle, with rows of calculations corresponding to the employee information input rows. Similarly, the statistics calculations are arranged in rectangular fashion corresponding to the appropriate variable columns. The formulas must be correct; otherwise the spreadsheet application will calculate incorrect answers and be of little value to the user. After you create or copy a formula, you should check to make sure the answer is correct. All the numbers should be formatted consistently to improve readability. IMPLEMENTATION Use Table 10.1 to help you think about the formulas you need to enter for each employee. There are two ways you could enter the formulas. One way is to enter the Regular Pay formula for the first employee, Jack Grey, verify that the formula is correct, and then copy the formula to the other employees; then repeat the procedure for each of the other six variables. A second way is to enter the formulas for Jack Grey, verify that the formulas are correct, and then copy them all at once to the other employees. Make sure you do not type the parameter values in the formulas but rather use the parameter cell addresses in the formulas correctly. Make sure you use the appropriate statistical functions for the formulas in the statistical summary area in the bottom rows of the report Use the Audit Formulas tool to verify that you have entered all the formulas correctly. Make sure you think about whether or not the formulas are calculating appropriate values. DSS-2 Project, Page 3 USING THE SPREADSHEET APPLICATION As the bakery's human resource manager, you can use the weekly payroll spreadsheet application to analyze the payroll budget and answer a variety of managerial what-if questions. On a new worksheet in the same workbook create another worksheet, named "Answer Sheet," for answering the following questions. The answer sheet should have three columns, one for the question number, one that includes the text of the question and one that only has the answers. The column with the answers should be in a different text color than the other two columns. Where appropriate, create a formula to calculate the correct answer. Each question should be fully answered. My advice is that you make a copy of the original Payroll worksheet and label it "Payroll Copy" or something similar. This copy will not be graded, and it is only for you to answer the questions below. After answering each question, reset the copy of the Payroll worksheet to its original values so you can answer the next question. Questions not requiring a direct answer must still have a reference on your answer sheet so I know where to find the completed task. 1. For the current week: a. What were the total hours worked by the employees? b. What was their average net pay? 2. You are considering some changes for some particular employees. a. tf Jack Grey worked 40 hours per week and got a raise to $25 per hour, what would be his net pay? b. Compared to the current week, if next week Samantha Collin's hourly wage was raised to $18 but she worked just 30 hours, what would be the difference in her net pay? The bakery is considering having employees contribute a higher percentage of their regular pay to their retirement. What would be the new retirement amount for Mike Nitsch, Gwen Manchester, and Stephanie Baker, if the retirement percentage was increased 0.5 % for each of the three regular pay categories? 3. 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 i explaining how the IF statement is used to calculate the employees federal tax withholding. Be sure you reference this on your answer sheet. 4. You want to visualize the payroll reports information, comparing employees in a variety of ways. a. Create a stacked bar chart to compare the three chefs, showing each chet's retirement contribution, and net pay values. Move the chart to a new worksheet and name the worksheet an appropriate title. Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Include value-added features such as a title x-axis and y-axis titles, legend, labels, and color scheme. b. Create an appropriate pie chart in its own worksheet showing Laure Godwin's retirement contribution, Social Security, federal tax, and net pay, Please be sure this chart is a full worksheet and not an object. You will lose points if it is inserted as an object. Use value-added features such as descriptive chart title, legend, and labels. Move the chart to a new worksheet and name the chart worksheet Laura Godwin 5. As the human resource manager, you are interested in exploring various what-if situations. Use the Goal Seek tool to calculate the answers to the following question a. Collyn Bear would like to have a weekly net pay of $500. How many hours does she need to work to earn that amount? 6. As the human resource manager you need to optimize the payroll of your managers by correcting the overlap hours for Managers on Duty. The total amount of time you need to each manager is 30 hours a week. What is the amount that would save in net pay on a weekly basis and over the course of the year if you implemented this strategy? 7. You are thinking of scheduling one driver in the morning and one driver in late afternoon for a total of 3 hours a day for each driver (7 days a week). What is the amount you would save in regular pay on a weekly basis and over a course of the Year if you implement this strategy? DSS-2 Project, Page 4 8. After using the weekly payroll spreadsheet, you would like to slightly modify it to improve its usability (this should be done in the original payroll worksheet): a Move the two rectangles comprising the assumptions area from the right side to the area below the payroll report. Use the Audit tool to verify that the cell reference in the formulas have been modified correctly b. Modify the original payroll spreadsheet to include a column that shows the Social Security and federal taxes combined total, right before Net Pay. Create the appropriate formula and modify the Net Pay formula to account for the new column instead. 9. Modify the Answers Sheet by setting a print area that includes only the three columns with the question, text of the question, and the