ExcelExercise2.docVIP

Microsoft Excel – Exercise 1 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. 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. Use the following formulas to determine the gross pay, federal tax, state tax, and net pay for the first employee: Gross Pay (cell E3) = Rate * Hours or =B3 * C3. Federal Tax (cell F3) = 20% * (Gross Pay – Dependents * 38.46) or =20% *(E3 – D3 * 38.46). State Tax (cell G3) =3.2% * Gross Pay or =3.2% * E3 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. Calculate totals for hours, gross pay, federal tax, state tax, and net pay in row 9. Use the appropriate functions to determine the average, highest, and lowest values of each column in rows 10 through 12. Use Range Finder to verify each of the formulas entered in row 3. 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 ti