Forest Hills High School



Ray’s Ready Mix Concrete Weekly Payroll

Problem: Ray’s Ready Mix Concrete has hired you as an intern in its software applications department. Because you took an Excel course last semester, the assistant manager has asked you to prepare a weekly payroll report for the six employees listed in the table below.

|EMPLOYEE |RATE |HOURS |DEPENDENTS |

|Sanchez, Edgar |32.25 |49.50 |6 |

|Wright, Felix |23.50 |28.00 |1 |

|Wreath, Christy |22.40 |70.00 |4 |

|Elamain, Al |29.75 |18.00 |5 |

|Pedal, Rose |21.35 |36.00 |4 |

|Space, Si |16.25 |42.00 |2 |

Instructions: Perform the following tasks to create a worksheet.

1. Enter the worksheet title Ray’s Ready Mix Concrete in cell A1. Enter the column titles in row 2, the data from the table above in columns A through D.

Enter Totals in A9, Average in A10, Highest in A11 and Lowest in A12.

2. Use the following formulas to determine the gross pay, federal tax, state tax, and net pay for the first employee:

a. Gross Pay (cell E3) = Rate * Hours or =B3 * C3.

b. Federal Tax (cell F3) = 20% * (Gross Pay – Dependents * 38.46) or =20% *(E3 – D3 * 38.46).

c. State Tax (cell G3) =3.2% * Gross Pay or =3.2% * E3

d. Net Pay (cell H3) = Gross Pay – (Federal Tax + State Tax) or =E3 – (F3 + G3)

Copy the formulas for the first employee to the remaining employees.

3. Calculate totals for hours, gross pay, federal tax, state tax, and net pay in row 9.

4. Use the appropriate functions to determine the average, highest, and lowest values of each column in rows 10 through 12.

5. Use Range Finder to verify each of the formulas entered in row 3.

6. Change the worksheet title to 26-point Arial Black bold orange font (or a font of your choice). Center the worksheet title across columns A through H. Vertically center the worksheet title. Use buttons on the Formatting toolbar to assign the Column style with two decimal places to the range B3:H12. Bold, italicize, and assign a bottom border (column 2, row 1 on the Borders palette) to the range A2:H2. Align right the column titles in the range B2:H2. Bold and italicize the range A9:A12. Assign a top and thick bottom border (column 1, row 3 on the Border palette) to the range A9:H9.

7. Change the width of column A to 18.00 characters. If necessary, change the widths of columns B through H to best fit. Change the heights of row 1 to 39.75 points and rows 2 and 10 to 30.00 points.

8. Use the conditional Formatting command on the Format menu to display bold white font on an orange background for any net pay less than $550.00 in the range H3:H8.

9. Enter your name, class, assignment (ME Exercise 2), date, and teacher’s name in the range A14:A18.

10. Spell check the worksheet. Save the workbook using the file name Ray’s Ready Mix Concrete.

11. Preview and then print the worksheet.

12. Press CTRL + ACCENT MARK (`) to change the display from the values version to the formulas version. Print the formulas version of the worksheet in landscape orientation using the Fit to option on the Page Tab in the Page Setup dialog box. After the printer is finished, Press CTRL + ACCENT MARK (`) to reset the worksheet to display the values version. Reset the Scaling option to 100% by clicking the Adjust to option button in the Page sheet in the Page Setup dialog box and then setting the percent value to 100%.

13. Use the keyboard to increase manually the number of hours worked for each employee by 16 hours. The total net pay in cell H9 should equal $6,418.42. If necessary, increase the width of column F to best fit to view the new federal tax total. Preview and print the worksheet with the new values.

14. Click cell A1. Try to click cells B1 through H1. You can’t because cells B1 through H1 were merged into cell A1 in Step 6. With cell A1 selected, click the Merge and Center button to split cell A1, into cells A1, B1, C1, D1, E1, F1, G1, and H1. Now click cells B1 through H1. Close the workbook without saving changes.

Part 2:

Problem: Your supervisor in the Payroll Department has asked you to modify the payroll workbook developed in beginning of Exercise 1.

The major modifications requested by your supervisor include: (1) reformatting the worksheet; (2) adding computations of time-and-a-half for hours worked greater than 40; (3) removing the conditional formatting assigned to the range E3:E8; (4) charging no federal tax in certain situations; (5) adding Social Security and Medicare deductions; (6) adding and deleting employees; and (7) changing employee information.

Instructions Part 2: Open the workbook, Ray’s Ready Mix Concrete. Perform the following tasks.

1. Select the range A2:H12. Point to Clear on the Edit menu and use the Format command on the Clear submenu to clear all formats. Change the worksheet title font to the color red.

2. Delete rows 10 through 12. Select rows 2 through 15 by dragging through the row headings and click the Bold button. Insert a row above row 2, click the Insert Options button that displays immediately below the new row 2, and select the Format Same as Below option. Enter the worksheet subtitle Weekly Payroll Report for in cell A2.

3. Insert a new column between columns D and E by right-clicking the column E heading and inserting a column. Enter the new column E title, YTD Soc. Sec, in cell E3. Insert two new columns between columns F and G. Enter the new column G title Soc. Sec. in cell G3. Enter the new column H title Medicare in cell H3. Freeze the panes (titles) in column A and rows 1 through 3.

4. Change the column widths and row heights as follows: A = 25.00; B = 9.43; C = 6.43; D = 6.00; E = 13.14; F through K = 9.71; and row 3 = 18.00. Right-align the column titles in the range B3:K3. Assign the NOW function to cell B2 and format it to the 3/14/2001 style.

5. Delete row 8 (Pedal, Rose). Change Felix Wright’s hours worked to 12 and number of dependents to 10.

6. In column E, enter the YTD Social Security values listed in the table below.

|NAME |YTD SOC. SEC |

|Sanchez, Edgar |4,974.00 |

|Wright, Felix |5,540.20 |

|Wreath, Christy |4,254.00 |

|Elamain, Al |5,553.90 |

|Space, Si |4,825.50 |

7. Insert two new rows immediately above the Totals row. Add the new employee data as listed in the table below.

|EMPLOYEE |RATE |HOURS |DEPENDENTS |YTD SOC. SEC. |

|Tuf, Chang |22.15 |48 |6 |4,825.50 |

|Knob, Doris |29.15 |36.25 |4 |5,553.90 |

8. Use the Format Cells dialog box to assign a Comma style and two decimal places to the ranges B4:C11 and E4:K11. Center the range D4:D10.

9. Enter the Social Security and Medicare tax information headings in the range A13:A15 as seen below. Enter the values in the range B13:B15. Use format symbols to format the numbers with either the %, currency and/ or two decimal places.

|Social Security Tax |6.2% |

|Medicare Tax |1.45% |

|Maximum Social Security | $5,553.00 |

10. Change the formulas to determine the gross pay in column F and the federal tax in column I.

a. In cell F4, enter and IF function that applies the following logic:

If Hours 0, then Federal Tax = 20% * (Gross Pay – Dependents * 38.46), otherwise Federal Tax = 0

d. Copy the IF function in cell I4 to the range I5:I10.

11. An employee pays Social Security tax only if his or her YTD Social Security is less than the maximum Social Security in column E. Use the following logic to determine the Social Security tax for Edgar Sanchez in cell G4:

If Soc. Sec. Tax * Gross Pay + YTD Soc. Sec. > Maximum Soc. Sec., then Maximum Soc. Sec. – YTD Soc. Sec., otherwise Soc. Sec. Tax * Gross Pay

12. Make sure references to the values in the social security tax table (B13:B15) are absolute, and then copy the IF function to the range G5:G10).

13. In cell H4, enter the following formula and then copy it to the range H5:H10:

Medicare = Medicare Tax * Gross Pay

14. Copy the state tax in J4 to the range J5:J10.

15. In cell K4, enter the following formula and copy it to the range K5:K10.

Gross Pay – (Soc. Sec. + Medicare + Fed. Tax + State Tax)

16. Determine any new totals in row 11.

17. Enter your name, class, assignment, date, and teachers name in the range A18:A22.

18. Unfreeze the panes (titles). Save the workbook using the file name Ray’s Ready Mix Concrete 2.

19. Use the Zoom box on the Standard toolbar to change the view of the worksheet. One by one select all the percents in the Zoom list. When you are done, return the worksheet to 100% magnification.

20. Use the Page Setup command on the File menu to change the orientation to landscape. Preview the worksheet. Save the worksheet using the same file name.

21. Preview and print the formulas version in landscape orientation using the Fit to option button in the Page Setup dialog box. Close the worksheet without saving the latest changes.

Instructions Part 3: Using the numbers in the following table, analyze the effect of changing the Social Security tax in cell B13 and the Medicare tax in cell B14. Print the worksheet for each case. The first case should result in a total Social Security tax in cell G11 of $509.07. The second case should result in a total Social Security tax of $632.91.

|CASE |SOCIAL SECURITY TAX |MEDICARE TAX |

|1 |9.00% |3.25% |

|2 |11.25% |2.75% |

Instructions Part 4: Open Ray’s Ready Mix Concrete 2.

1. Select cell F4. Write down the formula that displays in the formula bar. Select the range C4:C10. Point to the border surrounding the range and drag the selection to the range D13:D19. Click cell F4, and write down the formula that displays in the formula bar below the one you wrote down earlier. Compare the two formulas. What can you conclude about Excel when you move cells involved in a formula. Click the Undo button on the Standard toolbar.

2. Right-click the range C4:C10 and click Delete. When the Delete dialog box displays, click Shift cells left. What displays in cell F4? Use the Excel Help system to find a definition of the display in cell F4. Write the definition down. Click the Undo button on the Standard toolbar.

3. Right-click the range C4:C10 and click Insert. When the delete dialog box displays, click Shift cell right. What displays in the formula bar when you click cell F4? What displays in the formula bar when you click cell G4? What can you conclude about inserting cells? Close the workbook without saving changes.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download