New Perspectives Excel 2013 Tutorial 3: SAM Project 1a Spring Software ENHANCE A TIMESHEET PROJECT DESCRIPTION Devon Hastings is the human resources director for Spring Software, a software development company in Cambridge, Massachusetts

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2013 Tutorial 3: SAM Project 1a

Spring Software

ENHANCE A TIMESHEET

PROJECT DESCRIPTION

Devon Hastings is the human resources director for Spring Software, a software development company in Cambridge, Massachusetts. He has created a workbook for employees to use in recording the hours they work. He would like your help finishing the timesheet.

GETTING STARTED

Open the file you just downloaded and save it with the name:With the file NP_Excel2013_T3_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website. NP_Excel2013_T3_P1a_FirstLastName_2.xlsx Hint:

If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

PROJECT STEPS

1. On the Employee List worksheet, select cells A1:B1 and then use the Format Painter copy the format only to the range A2:B9.

2. In cell B1 enter bs001, in cell B2 enter cg001, in cell B3 type dh001, and then use Flash Fill to automatically fill in the values for the range B3:B9.

3. On the Employee Timesheet worksheet, merge and center the range A1:D1, and then format the range using the Title Cell Style (5th column, 1st row in the Titles and Headings section of the Cell Styles panel).

4. Merge and center the range A2:D2, and then change the font to Arial and the font size to 14 pt.

5. Format the range A9:D9 using the Heading 3 cell style (3rd column, 1st row in the Titles and Headings section of the Cell Styles panel) and then center the range A9:D9.

6. In cell C5, create a formula using the VLOOKUP function to look up the value of cell C4 (in the current worksheet) in the range A1:B9 on the Employee List worksheet and then return the value in the second column of that range. Use FALSE as value of the Range_Lookup argument to specify an exact match lookup.

7. In cell B7, use the TODAY function to display the current date.

8. Use AutoFill to fill the range B11:B23 based on cell B10.

'9. In cell D10, create a formula that uses the IF function to check if the value in cell C10 is equal to 0.

a. If this condition is true, the current cell should be made empty (that is, equal to "").

b. If this condition is false, the current cell should display the result of multiplying cell C10 (hours) by cell D7 (hourly wages). Use a relative reference to cell C10 and an absolute references to cell D7 so that the formula can be copied to other cells.

10. Copy the formula from cell D10 to all cells in the range D11:D23.

11. In cell C24, create a formula that uses the SUM function to total cells C10:C23.

12. In cell D24, enter a formula that uses the SUM function to total cell D10:D23.

13. Format C10:C24 with the Number format, showing two decimal places.

14. Format cell D7 with the Currency number format, showing two decimal places, and then format the range D10:D24 with the same format.

15. Format the range A24:D24 as bold and then add an outside border around this range.

16. In cell C26, create a formula that uses the MAX function to display the largest value from the range C10:C23.
17. In cell C27, create a formula that uses the MIN function to display the smallest value from the range C10:C23.

18. In cell C28, create a formula that uses the AVERAGE function to display the average of the values in the range C10:C23.

19. In cell C29, create a formula that multiplies the value in cell C28 by 7 (the number of days in a week).
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.