Excel Project Ch 12 Grader Project Exam

Ch 12 Grader Project Exam

Project Description: 
You’ve been asked to automate a time sheet, and to automatically record a log of the weekly payments using Excel 2013. You have made great progress on the Time Card, and now need to add data validation, VLOOKUPs to automate Employee data entry, an IF function to calculate the daily payments, and a macro to copy the time card data to a payment log.

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step    Instructions    Points Possible
1    Open the TIA_Ch10_Start.xlsx file, and save as TIA_Ch10_LastFirst. Display the DEVELOPER tab, if necessary.    0.000
2    On the Time Card worksheet, in cell B3 type Ted Hoyt. In cell B4, use the VLOOKUP function to lookup up the value of cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value in the third column of that range. Use FALSE as the Range_Lookup argument.    10.000
3    In cell B5, create a VLOOKUP function to look up the value of cell B3 in the range A2:C16 on the Employee Data worksheet, and return the value from the second column. Use FALSE as the Range_Lookup argument.    10.000
4    On the Time Card worksheet, click cell C9, enter 4/11/2016, and then use the Fill Handle to complete the series of dates in cells C10:C13.    6.000
5    Select range D9:D13. On the DATA tab, select Data Validation. In the Data Validation dialog box, on the Settings tab, click the Allow arrow and select Decimal. Click the Data arrow, and select between; then enter 0 in the Minimum box and 12 in the Maximum box. Add a Stop Error Alert, with the title: Invalid Entry, and an Error message: Total hours worked cannot exceed 12 hours.    12.000
6    In cells D9 type 16. In the Data Validation Error box, click Retry, and then type 10. In cells D10:D13, type 8, 12, 8, and 9 respectively. Note the Overtime Hours fill in automatically, calculating the hours in excess of 8 hours.    10.000
7    In cell F9, use an IF function to test if the value in D9 is less than or equal to 8.  If this condition is true, then multiply D9 times E4 (the regular rate). If the condition is false, then multiply 8 times E4 and add to that E9*E5 (the overtime rate). Use the fill handle to copy the formula to F10:F13. Make sure you use absolute cell references and parentheses in the formula where needed.    10.000
8    Display the Payment Log worksheet. This is where you’ll record the payment data for each employee. Click cell A1. On the DEVELOPER tab, click Record Macro. Name the macro CopyTimeCard and assign a shortcut key of CTRL+t.    7.000
9    With the Macro recorder on, complete the following steps: Display the Time Card worksheet, and then select and copy the range A20:F20. Display the Payment Log worksheet. On the DEVELOPER tab, in the Code group, click Use Relative References. Press END, and then DOWN ARROW two times. Click Use Relative References again. On the HOME tab, click the Paste arrow, and select Values. Click cell A1, display the Time Card worksheet, press ESC, and then click cell B3. Click the DEVELOPER tab, the click Stop recording.    7.000
10    Test the Macro by typing Brett Martin in cell B3. In cells D9:D13, type 8, 10, 8, 8, and 9, respectively. Click cell B3, then press CTRL+T to run the Macro. Display the Payment Log worksheet to ensure your data has been successfully transferred.    16.000
11    On the DEVELOPER tab, click Macros, select the CopyTimeCard macro, and click Edit. Copy and paste the macro code to a blank worksheet. Name the worksheet Macro. Position the Macro tab to the right of the Payment Log tab.    12.000
12    Save the workbook. Click Yes when asked to save as a Macro-free workbook. Ensure that the worksheets are properly named and in the following order: Employee Data, Time Card, Payment Log, and Macro. Submit based on your instructor’s directions.    0.000
    Total Points    100.000