Microsoft Excel 2010 Chapter 2 – Lab Test B

Performing Payroll Analysis

Purpose:  To demonstrate the ability to enter and copy formulas, and apply formatting to a worksheet.

Problem:  You work for Café Ali Jewel. You are in charge of maintaining the payroll for the store. You have been giving the basic info for the employees and will use functions to calculate the remaining payroll information.

Instructions:  Perform the following tasks to create the worksheet shown in Figure E2B-1.

  1. Enter and format the worksheet title Café Ali Jewel and worksheet subtitle Pay Summary Report in cells A1 and A2. Change the theme of the worksheet to the Waveform theme. Apply the Title cell style to cells A1 and A2. Change the font size in cell A1 to 36 points. Merge and center the worksheet title and subtitle across columns A through G. Change the background color of cells A1 and A2 to the Blue, Accent 1, Lighter 60%color. Draw a thick box border around the range A1:A2.
  2. Change the width of column A to 14.25 points. Change the widths of columns B through G to13.00 points. Change the heights of row 3 to 39.00 points and row 11 to 30.00 points.
  3. Enter the column titles in row 3 and row titles in the range A13:A16, as shown in Figure E2B-1.Center the column titles in the range A3:G3. Apply the Heading 3 cell style to the range A3:G3.Apply the Total cell style to the range A13:G13. Bold the titles in the range A14:A16. Change the font size in the range A3:G16 to 12 points. Center the range B4:B16.
  4. Enter the data for the Employee, Dependents, Hourly Rate, and Hours Worked columns (A4:D12) using the column info from Figure E2B-1.
  5. Use the following formulas to determine the Gross Pay in column E, Taxes in column F, and Net Pay in column G for the first employee. Copy the two formulas down through the remaining employees.
    1. Gross Pay (cell E4) =  Hourly Rate * Hours Worked
    2. Taxes (F4) = 20% * (Gross Pay – Dependents*23.17)
    3. Net Pay (G4) = Gross Pay - Taxes
  6. Determine the totals in row 13 for the range D13:G13.
  7. Determine the maximum, average, and minimum values in cells B14:B16 for the range B4:B9, and then copy the range B14:B16 to C14:G16
  8. Format the numbers as follows: (a) assign the Currency style with a floating dollar sign to the cells containing numeric data in the ranges C14:C16, E4:G4 and E13:G16 and cell C4, and (b) assign a number style with two decimal places and a thousand’s separator (currency with no dollar sign) to the ranges C5:C12, D4:D16, and E5:G12, and (c) assign a number style with zero decimal places to the ranges B4:B12, and B14:B16.
  9. Use conditional formatting to change the formatting to white font on a Dark Blue standard background in any cell in the range D4:D12 that contains a value greater than 30.
  10. Change the worksheet name from Sheet1 to Pay Summary Report and the sheet tab color to the Light Blue standard color. Change the document properties, as specified by your instructor. Change the worksheet header with your name, course number, and other information as specified by your instructor.
  11. Spell check the worksheet. Preview the worksheet in landscape orientation. Save the workbook using the file name, Excel Chapter 2 – Lab Test B in the compapps/excel/chapter 2 folder on your Z:/ drive.

Figure E2B-1