21 Aug INFS 2200 T2-MS Excel Exam (250 Points)

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.

   

 

1. On the Documentation worksheet: (6 max)

 

1. 1. 1. Enter your name in cell B3. (2)
      2. Enter the date in cell B4 using a function. (2)
      3. Apply the short date format to that date. (2)

 

1. 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.
2. 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)

    

   

 

1. 1. 1. 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)
      2. Copy the formula down all employee rows (M3:M102). (5)
      3. 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)
      4. Copy the formula down all employee rows without formatting (N3:N102). (5)
      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 employee’s 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)
      6. Copy the formula down all employee rows (O3:O102). (5)
      7. 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)
      8. Copy the formula down all employee rows (P3:P102). (5)
      9. 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)
      10. Copy the formula down all employee rows (Q3:Q102). (5)
      11. 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)
      12. Copy the formula down all employee rows (R3:R102). (5)

 

1. Create a Subtotals page using Excel’s built in subtotals functionality. (14 max)

 

1. 1. 1. Copy the EmployeeData worksheet as a new sheet and rename it Subtotals. (2)
      2. Move the Subtotals worksheet after the EmployeeData worksheet. (2)
      3. Sort the data on the Subtotals worksheet by Location in ascending order. (2)
      4. 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)

      5. 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)

          

          

 

1. 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)

 

1. 1. 1. Add the Location and Sex fields, in that order, to the ROWS area. (2)
      2. In the VALUES area add the following.

         i.Count of Annual Salary: (2)

 

1. 1. 1. 1. 1. 1. Update to display the name Num. Employee. (2)
               2. Update the Number Format to Number with 0 decimal places. (2) ii.Sum of AnnualSalary: (2)

 

1. 1. 1. 1. 1. 1. Update to display the name Sum of Salary. (2)
               2. Update the Number Format to Currency with 2 decimal places. (2) iii.Average of Annual Salary: (2)

 

1. 1. 1. 1. 1. 1. Update to display the name Average of Salary. (2)
               2. Update the Number Format to Currency with 2 decimal places. (2)

 

1. 1. 1. Change the Report Layout to Show in Outline Form. (2)
      2. Add the title SALARY STATISTICS in cell A1 and apply the Heading 1 cell style. (2)
      3. AutoFit the column width of cell A1. (2)

 

1. Create a PivotChart on the PVT Salary worksheet based on the PivotTable. (12 max)

 

1. 1. 1. Choose the Combo Chart Style. (2)
      2. Set the Chart Type for the Num. Employee series to Clustered Column. (2)
      3. Set the Chart Type for the Sum of Salary series to Clustered Column. (2)
      4. Set the Chart Type for the Average of Salary series to Line and check the Secondary Axis box. (2)
      5. Apply the Style 8 Chart Style. (2)
      6. Move the PivotChart so the top left corner is in cell A18. (2)

 

1. Move the PVT Salary worksheet after the Subtotals worksheet. (2)

              

   Sample: PVT Salary worksheet

    

   

    

2. Create a PivotTable on a New Worksheet that is based on the EmployeeData worksheet.

   Name the worksheet PVT Pay Grade. Use the following settings for the PivotTable. (26 max)

 

1. 1. 1. Add the Pay Grade field to the ROWS area. (2)
      2. In the VALUES area add the following.

         i.Count of Pay Grade: (2)

 

1. 1. 1. 1. 1. 1. Update to display the name Count. (2)
               2. Update the Number Format to Number with 0 decimal places. (2) ii.Sum of Bonus: (2)

                  1.Update the Number Format to Currency with 2 decimal places. (2) iii. Sum of 401(k): (2)

                  1.Update the Number Format to Currency with 2 decimal places. (2) iv.Sum of Health Cost: (2)

                  1.Update the Number Format to Currency with 2 decimal places. (2)

 

1. 1. 1. Change the Report Layout to Show in Outline Form. (2)
      2. Add the title PAY GRADE & BENEFITS ANALYSIS in cell A1 and apply the Heading 1 cell style. (2)
      3. AutoFit the column width of cell A1. (2)

 

1. Create a PivotChart on the PVT Pay Grade worksheet based on the PivotTable. (14 max)

 

1. 1. 1. Choose the Combo Chart Style. (2)
      2. Set the Chart Type for the Count series to Line with Markers and check the Secondary Axis box. (2)
      3. Set the Chart Type for the Sum of Bonus series to Clustered Column. (2)
      4. Set the Chart Type for the Sum of 401(k) series to Clustered Column. (2)
      5. Set the Chart Type for the Sum of Health Cost series to Clustered Column. (2)
      6. Apply the Style 8 Chart Style. (2)
      7. Move the PivotChart so the top left corner is in cell A10 and the bottom right corner is in cell E24. (2)

 

1. Move the PVT Pay Grade worksheet after the PVT Salary worksheet. (2)

    

   Sample: PVT Pay Grade worksheet

   

2. The following exercises will be completed on the Functions worksheet. The cells with orange background represent input values provided for the respective problems.  (88 max)

 

1. 1. 1. Employee Bonus Statistics Problem. Enter the respective Excel functions (in cells B2, B3, B4, B5 and B6) to compute the appropriate values using the Bonus column in the EmployeeData worksheet (P3:P102). (5×5= 25)
      2. Trip Transport Problem. Compute the number of chaperones and vans required for a trip using the template provided. The variables are the number of van seats available (12 seats per van), the number of kid campers (30 kid campers), and the rate of campers to chaperones required (1 chaperone per every 6 kids). (2+2+2+5+5= 16)

         i.Enter the number of van seats available in cell B10, the number of kid campers in cell B11, and the rate of campers to chaperones required in cell B12. ii.Enter a formula in cell B13 to calculate the number of chaperones needed.

         iii.Enter a formula in cell B14 to calculate the actual number of vans needed for the trip based on the supplied variable values. Use a function to ROUNDUP the number of vans to a whole number.

 

1. 1. Home Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template provided given that the loan amount is $150,000 for a 30 year term. (2+2+5+5+5+5= 24)

 

1. 1. 1. 1. Enter the loan amount in cell B18 and the number of years in cell B19.
         2. Use a function in cell B20 to lookup the annual interest rate using the INTERTAB table on the Work Tables worksheet.
         3. Use a formula in cell B21 to calculate the monthly interest rate.  iv.Use a formula in cell B22 to calculate the total number of payments.

            v.Use the PMT function in cell B23 to compute the monthly loan payment amount.

 

1. 1. Calculating a Periodic Payment with a Goal Problem.  Calculate the monetary amount to deposit monthly into an account using the template provided given that your starting balance is $300,000 and your savings goal is to reach $1,000,000 having an interest rate of 3% for a period of 20 years. (2+2+2+2+5+5+5= 23)

      i.Enter the current account balance in cell B29, the savings goal in cell B30, the interest rate in cell B31, and the number of years in cell B32.  ii.Use a formula in cell B33 to calculate the total number of monthly payments.  iii.Use a formula in cell B34 to calculate the monthly interest rate.

      iv.Use the PMT function in cell B35 to calculate the monthly deposit.  Sample: PVT Pay Grade worksheet