INFS 2200 T5-Final Exam
Page 1 of 8
INFS 2200 T5-Final Exam Instructions 200pts
Get the “T5-FinallExamV2.xlsx” file and complete the exercises as indicated in these instructions. Save your file as LastName-FinalExam.xlsx”. Submit your file by the deadline.
This is an “open book” exam – use any resources to find information on the DATEDIF() Excel function.
The 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’ payroll and generate some reports for the directors.
1. In the “Documentation” sheet, enter your name and date. (2)
2. The “Work Tables” sheet contains the tables you will use in this exam. Familiarize yourself with the tables’ range names and possible uses.
ServiceDate – the base date for the Years of Service in the report
HeatlhPlanRates – lists the monthly premium per plan
Recognition – lists the award amount based on years of service
Bonus – lists the bonus amount based on pay grade
Intertab – lists the annual interest rate per loan duration (years)
3. The “EmployeeData” 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)
4. 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)
5. Copy the formula down to all employee rows.(8)
6. 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 “Y” then the premium is one-tenth of 1% of the annual salary, otherwise is 0. Hint: use the IF() function. (5)
7. Copy the formula down to all employee rows.(5)
8. In column O, use a formula to compute the 401(k) value. Use the following logic: if the employee Job Status is full time (“FT” in column G) and the employee’s 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)
9. Copy the formula down to all employee rows.(5)
10. 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)
11. Copy the formula down to all employee rows.(5)
Page 2 of 8
12. 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)
13. Copy the formula down to all employee rows.(5)
14. 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)
15. Copy the formula down to all employee rows.(5)
16. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Salary” (26 max.)
a. Use the following settings for the “PVT Salary” pivot table:
b. Location and Sex in the “Rows” section (2)
c. In the Values section add:
i. Count of Annual Salary: (2)
1. Custom name: Num. Employee (2)
2. Format value with 0 decimals (2)
ii. Sum of Annual Salary: (2)
1. Custom name: Sum of Salary (2)
2. Format Currency with 2 decimals (2)
iii. Average of Annual Salary: (2)
1. Custom name: Average of Salary (2)
2. Format Currency with 2 decimals (2)
d. Set the Report Layout to Show in Outline form (2)
e. Add the title “SALARY STATISTICS” in cell A1, Style Heading 1 (2)
f. Make column A1 width AutoFit (2)
17. Create a pivot chart graph. (16 max) * Must use Excel Windows version to create Combo Style Chart.
a. Style Combo (2)
b. Sum of Salary series, clustered column (2)
c. Average Salary series, clustered column (2)
d. Num. Employee series, Line with markers, use secondary axis (2)
e. Chart Style 8 (2)
f. Move the top left corner of the graph to cell A18 and lower right corner to cell E32 (2)
18. Move the “PVT Salary” sheet after the “EmployeeData” sheet. (2)
19. 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)
Page 3 of 8
20. See sample below.
Page 4 of 8
21. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Pay Grade”. (26 max)
a. Use the following settings for the “PVT Pay Grade” pivot table:
b. Pay Grade in the “Rows” section (2)
c. In the Values section add:
i. Count of Pay Grade: (2)
1. Custom name: Count (2)
2. Format value with 0 decimals (2)
ii. Sum of Bonus: (2)
1. Format Currency with 2 decimals (2)
iii. Sum of 401(k): (2)
1. Format Currency with 2 decimals (2)
iv. Sum of Health Cost: (2)
1. Format Currency with 2 decimals (2)
d. Set the Report Layout to Show in Outline form (2)
e. Add the title “PAY BENEFITS ANALYSIS” in cell A1, Style Heading 1 (2)
f. Make column A1 width AutoFit (2)
22. Create a pivot chart graph. (18 max) * Must use Excel Windows version to create Combo Style Chart
a. Style Combo (2)
b. Count series, line with markers, use secondary Axis (2)
c. Sum of Bonus series, clustered column (2)
d. Sum of 401(k) series, clustered column (2)
e. Sum of Health Cost series, clustered column (2)
f. Chart Style 8 (2)
g. Move the top left corner of the graph to cell A10 and lower right corner to cell E24 (2)
23. Move the “PVT Pay Grade” sheet after the “PVT Salary” sheet (2)
24. Reflection: In cell C1, explain any inferences of the Bonus and the Heath Costs among the different pay grades? Please elaborate. (2)
Page 5 of 8
25. See sample below.
Page 6 of 8
26. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT HealthPlans”. (10 max)
a. Use the following settings for the “PVT HealthPlans” pivot table:
b. Health Plan in the “Rows” section (2)
c. In the Values section add:
i. Sum of Health Cost: (2)
1. Format Currency with 2 decimals (2)
d. Add the title “HEALTH PLAN COSTS” in cell A1, Style Heading 1 (2)
e. Make column A1 width AutoFit (2)
27. Create a pivot chart graph. (10 max)
a. Style Pie, 3 D Pie (2)
b. Change title to “Health Plan Costs” (2)
c. Move the top left corner of the graph to cell A11 and lower right corner to cell E24 (2)
28. Move the “PVT HealthPlans” sheet after the “PVT Pay Grade” sheet (2)
29. 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)
See picture below
Page 7 of 8
30. The following exercises are located in the “Functions” sheet. The cells with shaded orange background represent input values deducted from the problems’ descriptions. (26 max)
31. 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” sheet. (2×4= 8)
32. 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)
33. 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 (“Work Tables” 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×4= 8)
34. 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)
35. See sample below.
Page 8 of 8
>Documentation
– inal Exam I F -22 0
)
ealth Rates Table
Table
Table
Table
Plan onthly Premium
ears of Service
0 1 50
5 %
1 0
2 10 $ – 0 0
3 15 5 0
20 7 25 Recognition BONUS INTERTAB Pay Grade Bonus Award M Y 3 S HMOF F FT N 2 S HMOF M FT Y 2 S HMOF F Austin FT N 3 S None M Austin FT Y 3 S None M New Orleans FT Y 3 S PPOI M New Orleans FT N 3 S HMOI M Austin FT N 2 S None F Austin N 1 H PPOF F FT N 1 H None M Nashville FT N 1 H PPOF F Austin FT N 1 H PPOI F Nashville FT Y 1 H HMOF F Austin FT Y 1 H $ 32,011 None F Nashville FT Y 1 H $ 21,840 PPOI F Austin FT Y 2 S PPOF M Nashville FT Y 2 S $ 65,000 HMOF 12/4/09 F New Orleans FT Y 3 S $ 125,000 HMOF M Austin FT Y 3 S $ 80,000 HMOI F Home FT N 3 S None M Austin FT Y 3 S HMOF M Austin FT Y 3 S HMOF F Home PT N 1 H HMOI sband
M Austin FT Y 3 S $ 65,000 HMOF F Austin FT N 1 H None M New Orleans FT Y 3 S None F Austin FT Y 1 H PPOF 12/4/09 M New Orleans FT Y 3 S HMOF M Austin FT N 3 S $ 60,000 HMOF M New Orleans FT Y 3 S $ 200,000 HMOF M Austin FT Y 3 S HMOI 11/6/09 M Nashville FT N 1 H PPOF F Nashville FT N 1 H $ 22,880 HMOI M Austin FT Y 3 S $ 85,000 HMOI 5/4/06 M Home PT N 2 S HMOF F Austin FT N 3 S $ 65,000 None F Nashville FT N 1 H HMOF M New Orleans FT Y 3 S $ 95,000 None M New Orleans FT Y 3 S $ 80,000 HMOI M Nashville FT N 1 H $ 25,792 PPOF Young F New Orleans FT N 3 S HMOF F Austin FT N 3 S PPOF F Nashville FT N 1 H $ 21,299 PPOF F Austin FT N 1 H None M Austin FT Y 3 S HMOF F Nashville FT N 1 H $ 21,840 PPOI M Austin FT Y 3 S PPOF M Austin FT Y 3 S PPOI M Austin FT Y 3 S $ 110,000 HMOI F Austin FT Y 3 S $ 75,000 None M New Orleans FT Y 3 S $ 80,000 HMOI F Austin FT Y 2 S PPOI F Nashville FT N 1 H HMOF M Austin FT N 2 S HMOI F Nashville FT N 1 H None 7/17/09 M Nashville PT N 1 H $ 25,792 HMOI F Nashville FT N 1 H PPOF 7/17/09 M Austin FT Y 3 S PPOI M Austin FT N 3 S None 10/25/59 M Austin FT Y 3 S $ 85,000 PPOF F Austin FT N 2 S HMOF F Nashville FT Y 1 H HMOI M Austin FT Y 3 S $ 110,000 HMOF F Austin N 3 S None 6/16/53 F Austin FT N 2 S HMOF F Austin FT Y 3 S $ 85,000 HMOF M Austin FT Y 3 S None M New Orleans FT Y 3 S $ 110,000 PPOF M Austin PT N 1 H HMOI F Austin PT N 2 S $ 45,000 None F Austin FT N 2 S HMOF M New Orleans FT N 3 S None F Austin PT N 3 S None M New Orleans CN N 3 S $ 90,000 None Hanson 5/29/09 M Austin FT Y 3 S PPOF F Austin PT N 1 H None M Austin FT Y 2 S $ 40,000 PPOI Hu F Austin FT N 2 S HMOF F Austin FT Y 3 S $ 85,000 PPOI 8/28/09 F Austin FT N 1 H HMOI F Home FT N 1 H $ 24,752 HMOI M Austin FT Y 3 S $ 65,000 HMOI F Nashville PT N 1 H $ 22,880 PPOF 8/28/09 F Austin FT N 1 H None M Austin FT Y 3 S HMOI F Austin FT N 2 S PPOF M New Orleans FT Y 3 S $ 150,000 None F Nashville CN N 3 S $ 55,000 None F Nashville FT N 1 H $ 22,880 HMOF F Home FT N 1 H $ 22,880 HMOI 1/22/10 F Home FT N 1 H $ 23,920 None M Austin FT Y 3 S PPOF F Austin FT Y 2 S HMOF F Austin PT N 1 H HMOI F Austin FT N 2 S HMOI 7/16/05 F Nashville FT N 1 H PPOI M Austin FT Y 3 S None F Nashville FT N 1 H PPOF F Austin FT N 1 H PPOI M Austin FT N 2 S PPOF (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) Page 1 of 8
Get the “T5-FinallExamV2.xlsx” file and complete the exercises as indicated in these instructions. Save your file as LastName-FinalExam.xlsx”. Submit your file by the deadline. The Case: You are the payroll manager for T5 Warehouse, a retailer of gourmet products with 100 1. In the “Documentation” sheet, enter your name and date. (2) range names and possible uses. 3. The “EmployeeData” 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 4. In column M, use a formula to compute the Years of Service as the difference between the Hire Date 5. Copy the formula down to all employee rows.(8) Add Life Ins (column H) cell value equals “Y” then the premium is one-tenth of 1% of the annual salary, 7. Copy the formula down to all employee rows.(5) Status is full time (“FT” in column G) and the employee’s years of service (column M) is greater or equal to 9. Copy the formula down to all employee rows.(5) (column I), see the BONUS range name table. Hint: use the VLOOKUP() function. Make sure you use an 11. Copy the formula down to all employee rows.(5) Page 2 of 8
12. In column Q, use a formula to compute the Health Cost value. The Health Cost is based on the Health Plan 13. Copy the formula down to all employee rows.(5) (column M). Use the VLOOKUP function to get the award value from the Recognition range name table. 15. Copy the formula down to all employee rows.(5) 16. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Salary” a. Use the following settings for the “PVT Salary” pivot table: i. Count of Annual Salary: (2) ii. Sum of Annual Salary: (2) iii. Average of Annual Salary: (2) d. Set the Report Layout to Show in Outline form (2)
17. Create a pivot chart graph. (16 max) * Must use Excel Windows version to create Combo Style Chart.
a. Style Combo (2)
18. Move the “PVT Salary” sheet after the “EmployeeData” sheet. (2) implications among our work force? Please explain. (2) Page 3 of 8
20. See sample below.
Page 4 of 8
21. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Pay Grade”. a. Use the following settings for the “PVT Pay Grade” pivot table: i. Count of Pay Grade: (2) ii. Sum of Bonus: (2) iii. Sum of 401(k): (2) iv. Sum of Health Cost: (2) d. Set the Report Layout to Show in Outline form (2)
22. Create a pivot chart graph. (18 max) * Must use Excel Windows version to create Combo Style Chart
a. Style Combo (2)
23. Move the “PVT Pay Grade” sheet after the “PVT Salary” sheet (2) grades? Please elaborate. (2) Page 5 of 8
25. See sample below.
Page 6 of 8
26. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT HealthPlans”. a. Use the following settings for the “PVT HealthPlans” pivot table: i. Sum of Health Cost: (2) d. Add the title “HEALTH PLAN COSTS” in cell A1, Style Heading 1 (2)
27. Create a pivot chart graph. (10 max)
a. Style Pie, 3 D Pie (2)
28. Move the “PVT HealthPlans” sheet after the “PVT Pay Grade” sheet (2) Family plans are HMOF and PPOF, individual plans are HMOI and PPOI. Please elaborate. (2) See picture below
Page 7 of 8
30. The following exercises are located in the “Functions” sheet. The cells with shaded orange background
represent input values deducted from the problems’ descriptions. (26 max) B6) to compute the appropriate values using the Bonus column, in the “EmployeeData” sheet. (2×4= 8) number of van seats available, the number of kid campers and the rate of campers to chaperones required. In 33. Home Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template 34. Calculating a periodic payment with a goal Problem. You want to figure out how much you need to 35. See sample below.
Page 8 of 8
2
Talent Tracs
T
5
F
N
S
0
Author (
1
Enter your name here
Date (1)
Enter date here
Purpose
To track employee data on compensation and benefits
Work Tables
H
Plan
Recognition
BONUS
INTERTAB
Service Date
4/1/
15
M
Y
Recognition
Award
Pay Grade
Bonus
Loan
Years
Interest
HMOF
$ 1,500
$ – 0
$ 2,
7
4.
25
HMOI
$ 875
$
10
$ 5,000
4.75%
None
3
$
20
$ 7,500
5.25%
PPOF
$ 1,650
$
30
5.45%
PPOI
$ 950
$ 500
6.00%
30
6.50%
35
7.00%
* This page contains the tables you are going to use in this exam.
Cell Range
Name
B2
SERVICE_DATE
D3:E7
HealthPlanRates
G3:H7
J3:K5
M3:N9
EmployeeData
ID
Last Name
Hire Date
Birth Date
Sex
Location
Job Status
Add Life Ins
Pay Type
Annual Salary
Health Plan
Years Service
Life Ins Premium
401(k)
Health Cost
1024
Hovey
8/28/08
9/6/66
Austin
FT
$ 85,000
1025
Overton
5/24/04
2/15/86
Home
$ 40,000
1026
Fetherston
4/24/09
9/24/68
New Orleans
$ 37,244
1027
Lebrun
7/18/08
8/9/59
$ 80,000
1028
Hanson
8/21/09
7/15/50
$ 65,000
1029
Philo
3/5/09
5/2/58
$ 125,000
1030
Stolt
3/1/07
12/7/77
$ 95,000
1031
Akhalaghi
12/8/09
12/4/61
$ 36,000
1032
Vankeuren
8/11/05
1/10/59
PT
$ 33,508
1033
Mccorkle
6/12/03
1/30/42
Nashville
$ 21,840
1034
Nightingale
5/4/06
8/27/89
$ 25,792
1035
Croasdale
12/18/09
1/6/68
$ 32,011
1036
Lambrechts
5/4/05
4/28/58
$ 23,920
1037
Palmer
11/26/98
10/4/71
1038
Tetreault
2/22/02
1/4/60
1039
Cugini
12/4/09
1/16/70
$ 55,000
1040
Dash
10/12/09
12/2/85
1041
Donnelly
5/9/59
1042
Lucht
11/26/04
5/10/66
1043
Tiernan
3/19/07
8/31/73
$ 60,000
1044
Sheinin
6/18/98
10/14/52
$ 122,500
1045
Kenyon
7/21/09
11/3/65
$ 200,000
1046
Wichman
11/27/08
6/8/52
$ 31,761
1047
Hu
2/19/10
4/11/62
1048
Watts
7/5/02
3/17/59
$ 24,752
1049
Longa
7/10/08
4/11/61
$ 175,000
1050
Jacques
11/6/09
11/23/91
$ 29,120
1051
Joseph
10/15/66
$ 75,000
1052
Provost
3/24/97
12/8/64
1053
Limanni
5/23/05
2/15/62
1054
Young
8/12/96
10/15/68
$ 110,000
1055
Damien
3/20/73
$ 22,880
1056
Squillante
6/10/04
4/20/66
1057
Conrad
1/8/10
3/21/57
1058
Myette
6/28/67
$ 33,000
1059
Nasse
7/16/05
2/8/86
1060
Kusz
4/12/02
7/13/86
$ 21,299
1061
White
3/13/06
7/8/49
1062
Chamberlain
8/8/09
12/1/56
1063
Anderson
7/17/09
7/12/59
1064
1/8/09
3/6/70
$ 70,000
1065
Viator
10/4/07
10/25/59
$ 73,500
1066
Law
9/7/06
3/11/61
1067
Teliha
8/7/08
10/28/56
$ 39,000
1068
Carrington
5/29/09
8/21/50
$ 115,000
1069
Petro
1/18/01
11/8/51
1070
Greenwood
1/1/10
6/10/66
$ 123,000
1071
Martuscelli
1/8/98
12/23/91
$ 90,000
1072
Mcgovern
4/20/01
6/30/56
1073
Lapointe
6/15/01
4/29/50
1074
Sweet
6/3/04
6/16/53
1075
Boisclair
1/29/10
11/1/82
$ 45,000
1076
Montagna
8/2/04
7/26/62
$ 22,048
1077
Engstrume
8/28/09
5/24/74
$ 47,000
1078
Travison
11/5/01
5/31/51
$ 26,000
1079
Kovacs
10/15/84
1080
Patnoad
7/1/04
10/9/91
$ 27,560
1081
Disandro
2/1/49
$ 150,000
1082
Steyerl
3/13/05
5/12/69
$ 54,000
1083
Fagan
7/24/09
1084
Abdullah
2/5/08
1/18/47
$ 42,000
1085
Rigby
3/15/03
3/3/61
$ 29,640
1086
Zisowitz
11/11/99
5/13/78
1087
Weaver
9/12/08
9/24/86
CN
$ 66,000
1088
Williams
9/18/00
$ 52,000
1089
Gifford
11/13/09
10/11/55
1090
Scott
12/11/08
3/3/74
$ 130,000
1091
Tufts
5/13/04
6/5/59
1092
Sherman
11/26/07
3/2/55
$ 33,280
1093
Mulcahey
8/25/98
5/13/64
1094
Lachapelle
11/20/98
11/2/50
$ 35,000
1095
Hamilton
4/10/09
7/15/82
$ 63,750
1096
Silver
9/6/02
6/26/67
$ 93,000
1097
Schwegler
10/17/03
3/20/67
1098
4/15/53
$ 152,400
1099
Reels
6/15/06
7/31/63
$ 26,520
1100
Hazera
7/10/09
5/5/60
1101
1/22/10
7/11/53
$ 50,000
1102
Flores
10/16/09
7/15/84
1103
Johnson
11/24/51
$ 28,496
1104
Laplante
9/22/00
1/5/59
1105
Mazzotta
3/19/01
11/7/82
1106
Tucker
3/11/04
3/3/58
1107
Brazil
2/5/76
$ 29,016
1108
Lowe
8/11/97
4/2/67
$ 102,500
1109
Fasching
4/3/09
2/22/67
$ 43,260
1110
Veyera
3/7/03
2/15/65
1111
O’Donnell
12/29/05
4/4/53
1112
Dasilva
2/12/10
3/20/79
1113
Dain
6/7/09
5/8/76
1114
Fisher
4/2/91
1115
Golet
10/30/09
4/27/56
$ 100,000
1116
Howard
9/4/09
11/7/74
$ 43,000
1117
Zunjic
10/12/07
1/17/70
$ 36,004
1118
Lynch
2/12/04
7/12/74
$ 38,500
1119
Thibault
6/11/70
$ 27,851
1120
Baglama
10/2/09
6/10/68
$ 96,000
1121
Winters
2/14/02
3/1/53
$ 33,800
1122
Wang
8/24/98
8/11/66
$ 35,048
1123
Harrison
6/19/09
11/25/63
$ 41,000
Functions
Employee Bonus Statistics
AVERAGE
(2)
MAX
MIN
SUM
Trip Transport
Van Seats available
Number of kids campers
Rate Campers to Chaperones
* Need one chaperone per every 6 campers on average
Chaperones Needed
Number of Vans needed
Home Loan Payment Calculation
Loan Amount
Number of Years
Annual
Interest Rate
Monthly Interest Rate
Number of Payments
Monthly Loan Payment
* it should be negative to represent an outflow
Calculating a periodic payment with a goal
How much you need to deposit monthly in an account with $300,000 so in 20 years it reaches $1,000,000?
Given an interest rate of 3%
Current balance
* Needs to be negative value to represent that you already have “borrowed”…
Savings Goal
* Amount you want to achieve in X number of years
Interest Rate
Years
Total Number of Monthly Payments
Monthly Interest rate
Monthly deposit of
* it should be a negative value to represent an outflow every period
This is an “open book” exam – use any resources to find information on the DATEDIF() Excel function.
employees. You are in charge of preparing the employees’ payroll and generate some reports for the directors.
2. The “Work Tables” sheet contains the tables you will use in this exam. Familiarize yourself with the tables’
ServiceDate – the base date for the Years of Service in the report
HeatlhPlanRates – lists the monthly premium per plan
Recognition – lists the award amount based on years of service
Bonus – lists the bonus amount based on pay grade
Intertab – lists the annual interest rate per loan duration (years)
formulas with absolute (or range names) and relative address ranges. See sample output below. (66 max)
(column C) and the SERVICE_DATE range name; return the difference in years. Use the DATEDIF()
function. (8)
6. In column N, use a formula to compute the life insurance premium value. Use the following logic: if the
otherwise is 0. Hint: use the IF() function. (5)
8. In column O, use a formula to compute the 401(k) value. Use the following logic: if the employee Job
1 then, the 401(k) value is 3% of the annual salary, otherwise is 0. Hint: Use IF() and AND() functions. (5)
10. In column P, use a formula to compute the Bonus value. The Bonus value is based on the Pay Grade
exact match. (5)
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)
14. In column R, use a formula to compute the Award value. The Award is based on the years of service
Make sure you use an approximate match. (5)
(26 max.)
b. Location and Sex in the “Rows” section (2)
c. In the Values section add:
1. Custom name: Num. Employee (2)
2. Format value with 0 decimals (2)
1. Custom name: Sum of Salary (2)
2. Format Currency with 2 decimals (2)
1. Custom name: Average of Salary (2)
2. Format Currency with 2 decimals (2)
e. Add the title “SALARY STATISTICS” in cell A1, Style Heading 1 (2)
f. Make column A1 width AutoFit (2)
b. Sum of Salary series, clustered column (2)
c. Average Salary series, clustered column (2)
d. Num. Employee series, Line with markers, use secondary axis (2)
e. Chart Style 8 (2)
f. Move the top left corner of the graph to cell A18 and lower right corner to cell E32 (2)
19. Reflection: In cell C1, explain the impact of the work from home (Home) company policy and the
(26 max)
b. Pay Grade in the “Rows” section (2)
c. In the Values section add:
1. Custom name: Count (2)
2. Format value with 0 decimals (2)
1. Format Currency with 2 decimals (2)
1. Format Currency with 2 decimals (2)
1. Format Currency with 2 decimals (2)
e. Add the title “PAY BENEFITS ANALYSIS” in cell A1, Style Heading 1 (2)
f. Make column A1 width AutoFit (2)
b. Count series, line with markers, use secondary Axis (2)
c. Sum of Bonus series, clustered column (2)
d. Sum of 401(k) series, clustered column (2)
e. Sum of Health Cost series, clustered column (2)
f. Chart Style 8 (2)
g. Move the top left corner of the graph to cell A10 and lower right corner to cell E24 (2)
24. Reflection: In cell C1, explain any inferences of the Bonus and the Heath Costs among the different pay
(10 max)
b. Health Plan in the “Rows” section (2)
c. In the Values section add:
1. Format Currency with 2 decimals (2)
e. Make column A1 width AutoFit (2)
b. Change title to “Health Plan Costs” (2)
c. Move the top left corner of the graph to cell A11 and lower right corner to cell E24 (2)
29. Reflection: In cell C1, explain any inferences of the cost ratio of family plans vs the individual plans.
31. Employee Bonus Statistics Problem. Enter the respective Excel functions (in cells B2, B3, B4, B5, and
32. Trip Transport Problem. Enter the formulas required using the template shown. The variables are the
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)
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
(“Work Tables” 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×4= 8)
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)
INFS 2200 T5-Final Exam Instructions 200pts