To Complete the following Practice Test, you will need to be familiar with the following:
For the real Lab test, you will receive 50 minutes and 15 questions. For this practice test, there are 10 questions. As such, try allotting yourself 30 - 35 minutes to complete it.
As you complete each step, it is a good idea to use the Fill tool to replicate the formulas you create to all relevant cells, since information determined in a previous question may be required to answer the next question.
To begin the Practice Lab Test, first download Employee_Finances.xls. Once you have saved this file to your Documents, open it and you may begin. Use the spreadsheet to answer the following questions:
1. What is the correct way of referring to the range of cells which record both Regular and Overtime Hours for the employees? a. A5,B20 b. B5:C20 c. 5B:20C d. B5,C20 2. In cell B23, use a function to calculate the total hours worked (both Regular and Overtime). How many hours were worked in total? a. 515 b. 528 c. 542 d. 551 3. In cell B24, use a function to calculate the Average Pay Rate for employees. What is the Average Pay Rate? a. $12.69 b. $13.02 c. $13.56 d. $14.73 4. Subtotal is equal to an employee's Pay Rate multiplied by their Hours, plus their Overtime Hours at a rate that is 1.5 times their Pay Rate. What is John Dole's Subtotal? a. $343.00 b. $351.00 c. $359.00 d. $362.00 5. Subtotal is equal to an employee's Pay Rate multiplied by their Hours, plus their Overtime Hours at a rate that is 1.5 times their Pay Rate. What is Lyle O'Toole's Subtotal? a. $675.00 b. $690.50 c. $704.00 d. $712.50 6. In cells C30 and C31 are listed the Income Tax rates for employees earning less than $450.00 and greater than or equal to $450.00. In column F, create an IF function, which evaluates which tax bracket an employee falls into and uses Absolute References to return the amount of their total taxes paid. How much does John Abhot pay in Income tax? a. $106.96 b. $107.81 c. $134.31 d. $134.92 7. In cells C30 and C31 are listed the Income Tax rates for employees earning less than $450.00 and greater than or equal to $450.00. In column F, create an IF function, which evaluates which tax bracket an employee falls into and uses Absolute References to return the amount of their total taxes paid. How much does Fred Gretchen pay in Income Tax? a. $192.61 b. $193.28 c. $241.96 d. $242.50 8. Pay After Taxes is equal to the Subtotal minus Income Tax. How much does Alex Zimmerman make after taxes? a. $467.01 b. $467.21 c. $509.98 d. $510.13 9. In cell B25, use a function to determine the highest Pay After Taxes value. Which of the following is the correct value? a. $513.50 b. $522.50 c. $537.50 d. $565.50 10.In cell B27, use a function to determine the average Pay After Taxes value. Which of the following is the correct value? a. $320.11 b. $342.18 c. $351.56 d. $366.76
In Lab Test Procedure
Questions are drawn from material presented in seminars, your textbook, and the course notes.
There are numerous multiple choice questions in each test, and each lab section will do a separate test.
It is an open book exam. You may use any resources other than another person. Do not ask for additional
help during the examination. You do have the whole of your notes and the Internet available. If you are in doubt
about the meaning of an exam question, read it again, and then answer it to the best of your ability.