1. For the current week, what were the total hours worked by the employees and what was their average net pay?

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 Collins hourly wage was raised to $10 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.50% 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.

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 chefs retirement contribution, and net pay values. Move the chart to a new worksheet and name the worksheet an appropriate title. Include value-added features such as a title, x-axis and y-axis titles, legend, and color scheme.

B Create an appropriate pie chart in its own worksheet showing Laure Godwins retirement contribution, Social Security, federal tax, and net pay. Use value-added features such as descriptive chart title and percentage data 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 questions.

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.

A. Move the two rectangles comprising the assumptions area from the right side to the below the payroll report. Use the Audit tool to verify that the cell reference in the formulas have been modified correctly.

B. Modify the payroll spreadsheet to include a column that shows the total Social Security and federal taxes. Create the appropriate formula and modify the Net Pay formula.

Up the Hill Bakery Payroll Report Job Position Hours Worked Hourly WageRegular Pay Chef Pre-Tax Deductions Social Federal TaxNet $169.20 $464.96 S405.60 $28.20 $42.64 $15.00 $15.00 5676.80 6 Susan Grey S590.40 $37.20 $147.60 $10.00 $10.00 $10.00 Federal Tax Annual ncome Baseline Income Tax Bracket #1 Income Tax Bracket #2 $34,500.00 25% 15% $350.00 S330.00 $320.00 $161.00 $210.00 S238.00 $343.00 S323.40 $313.60 S157.78 $205.80 S233.24 Mary Wright Chef $21.61 $235.64 $85.75 $80.85 $78.40 8 John Berry $6.60 $6.40 $3.2 $4. $4. Cashier $215.44 $19.76 S9.94 $12.97 $14.69 32 10 Chris Hill 11 Laura Godwin Wait Staff 12 Samantha Collins Wait Staff Wait Staff $51.45 $141.38 Box #2 34 $7.00 $58.31 $160.24 Retirement Contributio Retirement $7.00 $175.00 $3. $4. $6.7 $171.50 S219.52 $329.28 S518.40 $42.88 $117.82 $150.81 $226.22 S356.14 $494.64 Wait Staff 25 $10.80 14 Steve Liitle 15 Mike Nitsh 6 Gwen Manchester Manager $13.83 $336.00 S540.00 $750.00 $400.00 $800.00 42 $82.32 $15.00 $32.66 $129.60 $180.00 50 $15.00 $45.36 $1,200.68 $92.36 $180.00 $134.00 $4,954.00 $151.28 54,802.72 $3,299.47 $253.81 494.64 $108.39 35.08 50 $23.27 15.00 $750.00 $161.00 2 Minimum $3.22 $157.78 9.94 Up the Hill Bakery Payroll Report Job Position Hours Worked Hourly WageRegular Pay Chef Pre-Tax Deductions Social Federal TaxNet $169.20 $464.96 S405.60 $28.20 $42.64 $15.00 $15.00 5676.80 6 Susan Grey S590.40 $37.20 $147.60 $10.00 $10.00 $10.00 Federal Tax Annual ncome Baseline Income Tax Bracket #1 Income Tax Bracket #2 $34,500.00 25% 15% $350.00 S330.00 $320.00 $161.00 $210.00 S238.00 $343.00 S323.40 $313.60 S157.78 $205.80 S233.24 Mary Wright Chef $21.61 $235.64 $85.75 $80.85 $78.40 8 John Berry $6.60 $6.40 $3.2 $4. $4. Cashier $215.44 $19.76 S9.94 $12.97 $14.69 32 10 Chris Hill 11 Laura Godwin Wait Staff 12 Samantha Collins Wait Staff Wait Staff $51.45 $141.38 Box #2 34 $7.00 $58.31 $160.24 Retirement Contributio Retirement $7.00 $175.00 $3. $4. $6.7 $171.50 S219.52 $329.28 S518.40 $42.88 $117.82 $150.81 $226.22 S356.14 $494.64 Wait Staff 25 $10.80 14 Steve Liitle 15 Mike Nitsh 6 Gwen Manchester Manager $13.83 $336.00 S540.00 $750.00 $400.00 $800.00 42 $82.32 $15.00 $32.66 $129.60 $180.00 50 $15.00 $45.36 $1,200.68 $92.36 $180.00 $134.00 $4,954.00 $151.28 54,802.72 $3,299.47 $253.81 494.64 $108.39 35.08 50 $23.27 15.00 $750.00 $161.00 2 Minimum $3.22 $157.78 9.94