Page 1 of 8
\nINFS 2200 T5-Final Exam Instructions 200pts
\nGet the \u201cT5-FinallExamV2.xlsx\u201d file and complete the exercises as indicated in these instructions. Save your file as LastName-FinalExam.xlsx\u201d. Submit your file by the deadline.
\nThis is an \u201copen book\u201d exam \u2013 use any resources to find information on the DATEDIF() Excel function.
\nThe Case: You are the payroll manager for T5 Warehouse, a retailer of gourmet products with 100 employees. You are in charge of preparing the employees\u2019 payroll and generate some reports for the directors.
\n1. In the \u201cDocumentation\u201d sheet, enter your name and date. (2)
\n2. The \u201cWork Tables\u201d sheet contains the tables you will use in this exam. Familiarize yourself with the tables\u2019 range names and possible uses.
\nServiceDate \u2013 the base date for the Years of Service in the report
\nHeatlhPlanRates \u2013 lists the monthly premium per plan
\nRecognition \u2013 lists the award amount based on years of service
\nBonus \u2013 lists the bonus amount based on pay grade
\nIntertab \u2013 lists the annual interest rate per loan duration (years)
\n3. The \u201cEmployeeData\u201d sheet contains payroll data. You are tasked with completing the payroll report information by entering all the required formulas in the required columns (M to R). Make sure to use proper formulas with absolute (or range names) and relative address ranges. See sample output below. (66 max)
\n4. In column M, use a formula to compute the Years of Service as the difference between the Hire Date (column C) and the SERVICE_DATE range name; return the difference in years. Use the DATEDIF() function. (8)
\n5. Copy the formula down to all employee rows.(8)
\n6. In column N, use a formula to compute the life insurance premium value. Use the following logic: if the Add Life Ins (column H) cell value equals \u201cY\u201d then the premium is one-tenth of 1% of the annual salary, otherwise is 0. Hint: use the IF() function. (5)
\n7. Copy the formula down to all employee rows.(5)
\n8. In column O, use a formula to compute the 401(k) value. Use the following logic: if the employee Job Status is full time (\u201cFT\u201d in column G) and the employee\u2019s years of service (column M) is greater or equal to 1 then, the 401(k) value is 3% of the annual salary, otherwise is 0. Hint: Use IF() and AND() functions. (5)
\n9. Copy the formula down to all employee rows.(5)
\n10. In column P, use a formula to compute the Bonus value. The Bonus value is based on the Pay Grade (column I), see the BONUS range name table. Hint: use the VLOOKUP() function. Make sure you use an exact match. (5)
\n11. Copy the formula down to all employee rows.(5)
\nPage 2 of 8
\n12. In column Q, use a formula to compute the Health Cost value. The Health Cost is based on the Health Plan code in column L. Use the VLOOKUP function to get the monthly premium from the HealthPlanRates range name table and multiply this value by 12 to get the annual cost. Make sure you use an exact match. (5)
\n13. Copy the formula down to all employee rows.(5)
\n14. In column R, use a formula to compute the Award value. The Award is based on the years of service (column M). Use the VLOOKUP function to get the award value from the Recognition range name table. Make sure you use an approximate match. (5)
\n15. Copy the formula down to all employee rows.(5)
\n16. Create a Pivot Table, based on the \u201cEmployeeData\u201d, in a New Sheet and label it \u201cPVT Salary\u201d (26 max.)
\na. Use the following settings for the \u201cPVT Salary\u201d pivot table:
\nb. Location and Sex in the \u201cRows\u201d section (2)
\nc. In the Values section add:
\ni. Count of Annual Salary: (2)
\n1. Custom name: Num. Employee (2)
\n2. Format value with 0 decimals (2)
\nii. Sum of Annual Salary: (2)
\n1. Custom name: Sum of Salary (2)
\n2. Format Currency with 2 decimals (2)
\niii. Average of Annual Salary: (2)
\n1. Custom name: Average of Salary (2)
\n2. Format Currency with 2 decimals (2)
\nd. Set the Report Layout to Show in Outline form (2)
\ne. Add the title \u201cSALARY STATISTICS\u201d in cell A1, Style Heading 1 (2)
\nf. Make column A1 width AutoFit (2)
\n17. Create a pivot chart graph. (16 max) * Must use Excel Windows version to create Combo Style Chart.
\na. Style Combo (2)
\nb. Sum of Salary series, clustered column (2)
\nc. Average Salary series, clustered column (2)
\nd. Num. Employee series, Line with markers, use secondary axis (2)
\ne. Chart Style 8 (2)
\nf. Move the top left corner of the graph to cell A18 and lower right corner to cell E32 (2)
\n18. Move the \u201cPVT Salary\u201d sheet after the \u201cEmployeeData\u201d sheet. (2)
\n19. Reflection: In cell C1, explain the impact of the work from home (Home) company policy and the implications among our work force? Please explain. (2)
\nPage 3 of 8
\n20. See sample below.
\nPage 4 of 8
\n21. Create a Pivot Table, based on the \u201cEmployeeData\u201d, in a New Sheet and label it \u201cPVT Pay Grade\u201d. (26 max)
\na. Use the following settings for the \u201cPVT Pay Grade\u201d pivot table:
\nb. Pay Grade in the \u201cRows\u201d section (2)
\nc. In the Values section add:
\ni. Count of Pay Grade: (2)
\n1. Custom name: Count (2)
\n2. Format value with 0 decimals (2)
\nii. Sum of Bonus: (2)
\n1. Format Currency with 2 decimals (2)
\niii. Sum of 401(k): (2)
\n1. Format Currency with 2 decimals (2)
\niv. Sum of Health Cost: (2)
\n1. Format Currency with 2 decimals (2)
\nd. Set the Report Layout to Show in Outline form (2)
\ne. Add the title \u201cPAY BENEFITS ANALYSIS\u201d in cell A1, Style Heading 1 (2)
\nf. Make column A1 width AutoFit (2)
\n22. Create a pivot chart graph. (18 max) * Must use Excel Windows version to create Combo Style Chart
\na. Style Combo (2)
\nb. Count series, line with markers, use secondary Axis (2)
\nc. Sum of Bonus series, clustered column (2)
\nd. Sum of 401(k) series, clustered column (2)
\ne. Sum of Health Cost series, clustered column (2)
\nf. Chart Style 8 (2)
\ng. Move the top left corner of the graph to cell A10 and lower right corner to cell E24 (2)
\n23. Move the \u201cPVT Pay Grade\u201d sheet after the \u201cPVT Salary\u201d sheet (2)
\n24. Reflection: In cell C1, explain any inferences of the Bonus and the Heath Costs among the different pay grades? Please elaborate. (2)
\nPage 5 of 8
\n25. See sample below.
\nPage 6 of 8
\n26. Create a Pivot Table, based on the \u201cEmployeeData\u201d, in a New Sheet and label it \u201cPVT HealthPlans\u201d. (10 max)
\na. Use the following settings for the \u201cPVT HealthPlans\u201d pivot table:
\nb. Health Plan in the \u201cRows\u201d section (2)
\nc. In the Values section add:
\ni. Sum of Health Cost: (2)
\n1. Format Currency with 2 decimals (2)
\nd. Add the title \u201cHEALTH PLAN COSTS\u201d in cell A1, Style Heading 1 (2)
\ne. Make column A1 width AutoFit (2)
\n27. Create a pivot chart graph. (10 max)
\na. Style Pie, 3 D Pie (2)
\nb. Change title to \u201cHealth Plan Costs\u201d (2)
\nc. Move the top left corner of the graph to cell A11 and lower right corner to cell E24 (2)
\n28. Move the \u201cPVT HealthPlans\u201d sheet after the \u201cPVT Pay Grade\u201d sheet (2)
\n29. Reflection: In cell C1, explain any inferences of the cost ratio of family plans vs the individual plans. Family plans are HMOF and PPOF, individual plans are HMOI and PPOI. Please elaborate. (2)
\nSee picture below
\nPage 7 of 8
\n30. The following exercises are located in the \u201cFunctions\u201d sheet. The cells with shaded orange background represent input values deducted from the problems\u2019 descriptions. (26 max)
\n31. 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 \u201cEmployeeData\u201d sheet. (2\u00d74= 8)
\n32. Trip Transport Problem. Enter the formulas required using the template shown. The variables are the number of van seats available, the number of kid campers and the rate of campers to chaperones required. In cells, B13 and B14 enter the formulas required to compute the number of chaperones needed and the actual number of vans needed for the trip based on 12 seats per van, 30 kid campers and 1 chaperone per every 6 kids. In cell B14 use the ROUND() function. (2+2= 4)
\n33. Home Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template shown. You want to loan 150,000 for a 30-year term. The variables are loan amount and number of years. In B20, enter VLOOKUP formula to get the annual interest rate using the table INTERTAB range name (\u201cWork Tables\u201d sheet.) In cell B21 enter formula to compute the monthly interest rate. In cell B22 enter formula to compute the total number of payments. In cell B23 enter PMT() function to compute the monthly loan payment amount. (2\u00d74= 8)
\n34. Calculating a periodic payment with a goal Problem. You want to figure out how much you need to deposit monthly in an account with $300,000 so in 20 years it reaches $1,000,000 dollars (saving goal). Using the template shown. In cell B29 enter the current balance in the account. In cell B30 enter the savings goal. In cell B31 enter the interest rate, in this case is 3%. In cell B32 enter the number of years. In cell B33 enter the formula to compute the total number of monthly payments. In cell B34 enter the formula to compute the monthly interest rate. In cell B35 enter the function to calculate the monthly deposit. Hint: use the PMT function. (2+2+2= 6)
\n35. See sample below.
\nPage 8 of 8<\/p>\n","protected":false},"excerpt":{"rendered":"
Page 1 of 8 INFS 2200 T5-Final Exam Instructions 200pts Get the \u201cT5-FinallExamV2.xlsx\u201d file and complete the exercises as indicated in these instructions. Save your…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-102297","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts\/102297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/comments?post=102297"}],"version-history":[{"count":0,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts\/102297\/revisions"}],"wp:attachment":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/media?parent=102297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/categories?post=102297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/tags?post=102297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}