INFS 2200 T2-MS Excel Exam V2 Instructions (250 pts) You must submit your final Excel file to the D2L Dropbox . Download the T2-ExcelExamV2.xlsx file from the D2L Dropbox or T2 MS Excel Exam section of the Content area and complete the following exercises. Save the downloaded file with the name LastName-ExcelExam.xlsx , where LastName is your last name. Submit your file to the D2L Dropbox by the deadline. On the Documentation worksheet: ( 6 max ) Enter your name in cell B3. (2) Enter the date in cell B4 using a function. (2) Apply the short date format to that date. (2) The Work Tables worksheet contains the tables you will use in this exam. Take a few minutes to familiarize yourself with the tables range names and possible uses. The EmployeeData worksheet contains payroll data. Complete the payroll report information by entering the required formulas in the specified columns using the steps below. Be sure to use absolute (or range names) and relative address ranges properly in your formulas. Sample output is below for spot checking your formula output. ( 60 max ) Use the DATEDIF () function in column M to calculate the Years of Service as the difference between the Hire Date (column C ) and the SERVICE_DATE range name. Return the difference in years . You may need to use Google to find examples of this function we covered this in class! (5) Copy the formula down all employee rows (M3:M102). (5) Use the IF () function in column N to calculate the life insurance premium value. Use the following logic: IF the Add Life Ins (column H ) cell value equals Y then the premium is one tenth of 1% (0.001) of the annual salary, otherwise is 0. (5) Copy the formula down all employee rows without formatting (N3:N102). (5) Use a combination of the IF () and AND () functions in column O to calculate the 401(k) value. Use the following logic: If the employee Job Status equals full time (FT in column G ) and the employees years of service (column M ) is greater than or equal to 1 then the 401(k) value is 3% of the annual salary, otherwise is 0. (5) Copy the formula down all employee rows (O3:O102). (5) Use the VLOOKUP () function in column P to calculate the Bonus value which is based on Pay Grade (column I ). Use the BONUS range name table. Use an exact match. (5) Copy the formula down all employee rows (P3:P102). (5) Use the VLOOKUP () function in column Q to calculate the Health Cost value. The Health Cost is based on the Health Plan code in column L . Get the monthly premium from the HealthPlanRates range name table and multiply this value by 12 to get the annual cost. Use an exact match. (5) Copy the formula down all employee rows (Q3:Q102). (5) Use the VLOOKUP () function in column R to calculate the Award value which is based on the years of service (column M ). Get the award value from the Recognition range name table. Use an approximate match. (5) Copy the formula down all employee rows (R3:R102). (5) Create a Subtotals page using Excels built in subtotals functionality. ( 14 max ) Copy the EmployeeData worksheet as a new sheet and rename it Subtotals . (2) Move the Subtotals worksheet after the EmployeeData worksheet. (2) Sort the data on the Subtotals worksheet by Location in ascending order. (2) Use the Subtotal Excel option to create subtotals at each change in Location using the SUM function for the Annual Salary , Life Ins Premium , 401(k) , Bonus , Health Cost , and Award columns. (6) Use the “+” and / or “-” signs in the left margin to set the report to display level 2 only. Adjust the column widths as necessary to view the data. (2) Create a PivotTable on a New Worksheet that is based on the EmployeeData worksheet. Name the worksheet PVT Salary . Use the following settings for the PivotTable. ( 26 max ) Add the Location and Sex fields, in that order, to the ROWS area. (2) In the VALUES area add the following. i. Count of Annual Salary : (2) Update to display the name Num. Employee . (2) Update the Numbe…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |