INFS 2200 T5-Final Exam
duston83
T5-FinalExamV2.xlsxDocumentation
| Talent Tracs | T5 - Final Exam INFS-2200 |
| Author (1) | Enter your name here |
| Date (1) | Enter date here |
| Purpose | To track employee data on compensation and benefits |
Work Tables
| HealthPlanRates Table | Recognition Table | BONUS Table | INTERTAB Table | ||||||
| Service Date | 4/1/15 | Plan | Monthly Premium | Years of Service | Recognition Award | Pay Grade | Bonus | Loan Years | Interest |
| HMOF | $ 1,500 | 0 | $ - 0 | 1 | $ 2,750 | 5 | 4.25% | ||
| HMOI | $ 875 | 1 | $ 100 | 2 | $ 5,000 | 10 | 4.75% | ||
| None | $ - 0 | 3 | $ 200 | 3 | $ 7,500 | 15 | 5.25% | ||
| PPOF | $ 1,650 | 5 | $ 300 | 20 | 5.45% | ||||
| PPOI | $ 950 | 7 | $ 500 | 25 | 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 | Recognition | ||||||||
| J3:K5 | BONUS | ||||||||
| M3:N9 | INTERTAB |
EmployeeData
| ID | Last Name | Hire Date | Birth Date | Sex | Location | Job Status | Add Life Ins | Pay Grade | Pay Type | Annual Salary | Health Plan | Years Service | Life Ins Premium | 401(k) | Bonus | Health Cost | Award |
| 1024 | Hovey | 8/28/08 | 9/6/66 | M | Austin | FT | Y | 3 | S | $ 85,000 | HMOF | ||||||
| 1025 | Overton | 5/24/04 | 2/15/86 | F | Home | FT | N | 2 | S | $ 40,000 | HMOF | ||||||
| 1026 | Fetherston | 4/24/09 | 9/24/68 | M | New Orleans | FT | Y | 2 | S | $ 37,244 | HMOF | ||||||
| 1027 | Lebrun | 7/18/08 | 8/9/59 | F | Austin | FT | N | 3 | S | $ 80,000 | None | ||||||
| 1028 | Hanson | 8/21/09 | 7/15/50 | M | Austin | FT | Y | 3 | S | $ 65,000 | None | ||||||
| 1029 | Philo | 3/5/09 | 5/2/58 | M | New Orleans | FT | Y | 3 | S | $ 125,000 | PPOI | ||||||
| 1030 | Stolt | 3/1/07 | 12/7/77 | M | New Orleans | FT | N | 3 | S | $ 95,000 | HMOI | ||||||
| 1031 | Akhalaghi | 12/8/09 | 12/4/61 | M | Austin | FT | N | 2 | S | $ 36,000 | None | ||||||
| 1032 | Vankeuren | 8/11/05 | 1/10/59 | F | Austin | PT | N | 1 | H | $ 33,508 | PPOF | ||||||
| 1033 | Mccorkle | 6/12/03 | 1/30/42 | F | Nashville | FT | N | 1 | H | $ 21,840 | None | ||||||
| 1034 | Nightingale | 5/4/06 | 8/27/89 | M | Nashville | FT | N | 1 | H | $ 25,792 | PPOF | ||||||
| 1035 | Croasdale | 12/18/09 | 1/6/68 | F | Austin | FT | N | 1 | H | $ 32,011 | PPOI | ||||||
| 1036 | Lambrechts | 5/4/05 | 4/28/58 | F | Nashville | FT | Y | 1 | H | $ 23,920 | HMOF | ||||||
| 1037 | Palmer | 11/26/98 | 10/4/71 | F | Austin | FT | Y | 1 | H | $ 32,011 | None | ||||||
| 1038 | Tetreault | 2/22/02 | 1/4/60 | F | Nashville | FT | Y | 1 | H | $ 21,840 | PPOI | ||||||
| 1039 | Cugini | 12/4/09 | 1/16/70 | F | Austin | FT | Y | 2 | S | $ 55,000 | PPOF | ||||||
| 1040 | Dash | 10/12/09 | 12/2/85 | M | Nashville | FT | Y | 2 | S | $ 65,000 | HMOF | ||||||
| 1041 | Donnelly | 12/4/09 | 5/9/59 | F | New Orleans | FT | Y | 3 | S | $ 125,000 | HMOF | ||||||
| 1042 | Lucht | 11/26/04 | 5/10/66 | M | Austin | FT | Y | 3 | S | $ 80,000 | HMOI | ||||||
| 1043 | Tiernan | 3/19/07 | 8/31/73 | F | Home | FT | N | 3 | S | $ 60,000 | None | ||||||
| 1044 | Sheinin | 6/18/98 | 10/14/52 | M | Austin | FT | Y | 3 | S | $ 122,500 | HMOF | ||||||
| 1045 | Kenyon | 7/21/09 | 11/3/65 | M | Austin | FT | Y | 3 | S | $ 200,000 | HMOF | ||||||
| 1046 | Wichman | 11/27/08 | 6/8/52 | F | Home | PT | N | 1 | H | $ 31,761 | HMOI | ||||||
| 1047 | Husband | 2/19/10 | 4/11/62 | M | Austin | FT | Y | 3 | S | $ 65,000 | HMOF | ||||||
| 1048 | Watts | 7/5/02 | 3/17/59 | F | Austin | FT | N | 1 | H | $ 24,752 | None | ||||||
| 1049 | Longa | 7/10/08 | 4/11/61 | M | New Orleans | FT | Y | 3 | S | $ 175,000 | None | ||||||
| 1050 | Jacques | 11/6/09 | 11/23/91 | F | Austin | FT | Y | 1 | H | $ 29,120 | PPOF | ||||||
| 1051 | Joseph | 12/4/09 | 10/15/66 | M | New Orleans | FT | Y | 3 | S | $ 75,000 | HMOF | ||||||
| 1052 | Provost | 3/24/97 | 12/8/64 | M | Austin | FT | N | 3 | S | $ 60,000 | HMOF | ||||||
| 1053 | Limanni | 5/23/05 | 2/15/62 | M | New Orleans | FT | Y | 3 | S | $ 200,000 | HMOF | ||||||
| 1054 | Young | 8/12/96 | 10/15/68 | M | Austin | FT | Y | 3 | S | $ 110,000 | HMOI | ||||||
| 1055 | Damien | 11/6/09 | 3/20/73 | M | Nashville | FT | N | 1 | H | $ 22,880 | PPOF | ||||||
| 1056 | Squillante | 6/10/04 | 4/20/66 | F | Nashville | FT | N | 1 | H | $ 22,880 | HMOI | ||||||
| 1057 | Conrad | 1/8/10 | 3/21/57 | M | Austin | FT | Y | 3 | S | $ 85,000 | HMOI | ||||||
| 1058 | Myette | 5/4/06 | 6/28/67 | M | Home | PT | N | 2 | S | $ 33,000 | HMOF | ||||||
| 1059 | Nasse | 7/16/05 | 2/8/86 | F | Austin | FT | N | 3 | S | $ 65,000 | None | ||||||
| 1060 | Kusz | 4/12/02 | 7/13/86 | F | Nashville | FT | N | 1 | H | $ 21,299 | HMOF | ||||||
| 1061 | White | 3/13/06 | 7/8/49 | M | New Orleans | FT | Y | 3 | S | $ 95,000 | None | ||||||
| 1062 | Chamberlain | 8/8/09 | 12/1/56 | M | New Orleans | FT | Y | 3 | S | $ 80,000 | HMOI | ||||||
| 1063 | Anderson | 7/17/09 | 7/12/59 | M | Nashville | FT | N | 1 | H | $ 25,792 | PPOF | ||||||
| 1064 | Young | 1/8/09 | 3/6/70 | F | New Orleans | FT | N | 3 | S | $ 70,000 | HMOF | ||||||
| 1065 | Viator | 10/4/07 | 10/25/59 | F | Austin | FT | N | 3 | S | $ 73,500 | PPOF | ||||||
| 1066 | Law | 9/7/06 | 3/11/61 | F | Nashville | FT | N | 1 | H | $ 21,299 | PPOF | ||||||
| 1067 | Teliha | 8/7/08 | 10/28/56 | F | Austin | FT | N | 1 | H | $ 39,000 | None | ||||||
| 1068 | Carrington | 5/29/09 | 8/21/50 | M | Austin | FT | Y | 3 | S | $ 115,000 | HMOF | ||||||
| 1069 | Petro | 1/18/01 | 11/8/51 | F | Nashville | FT | N | 1 | H | $ 21,840 | PPOI | ||||||
| 1070 | Greenwood | 1/1/10 | 6/10/66 | M | Austin | FT | Y | 3 | S | $ 123,000 | PPOF | ||||||
| 1071 | Martuscelli | 1/8/98 | 12/23/91 | M | Austin | FT | Y | 3 | S | $ 90,000 | PPOI | ||||||
| 1072 | Mcgovern | 4/20/01 | 6/30/56 | M | Austin | FT | Y | 3 | S | $ 110,000 | HMOI | ||||||
| 1073 | Lapointe | 6/15/01 | 4/29/50 | F | Austin | FT | Y | 3 | S | $ 75,000 | None | ||||||
| 1074 | Sweet | 6/3/04 | 6/16/53 | M | New Orleans | FT | Y | 3 | S | $ 80,000 | HMOI | ||||||
| 1075 | Boisclair | 1/29/10 | 11/1/82 | F | Austin | FT | Y | 2 | S | $ 45,000 | PPOI | ||||||
| 1076 | Montagna | 8/2/04 | 7/26/62 | F | Nashville | FT | N | 1 | H | $ 22,048 | HMOF | ||||||
| 1077 | Engstrume | 8/28/09 | 5/24/74 | M | Austin | FT | N | 2 | S | $ 47,000 | HMOI | ||||||
| 1078 | Travison | 11/5/01 | 5/31/51 | F | Nashville | FT | N | 1 | H | $ 26,000 | None | ||||||
| 1079 | Kovacs | 7/17/09 | 10/15/84 | M | Nashville | PT | N | 1 | H | $ 25,792 | HMOI | ||||||
| 1080 | Patnoad | 7/1/04 | 10/9/91 | F | Nashville | FT | N | 1 | H | $ 27,560 | PPOF | ||||||
| 1081 | Disandro | 7/17/09 | 2/1/49 | M | Austin | FT | Y | 3 | S | $ 150,000 | PPOI | ||||||
| 1082 | Steyerl | 3/13/05 | 5/12/69 | M | Austin | FT | N | 3 | S | $ 54,000 | None | ||||||
| 1083 | Fagan | 7/24/09 | 10/25/59 | M | Austin | FT | Y | 3 | S | $ 85,000 | PPOF | ||||||
| 1084 | Abdullah | 2/5/08 | 1/18/47 | F | Austin | FT | N | 2 | S | $ 42,000 | HMOF | ||||||
| 1085 | Rigby | 3/15/03 | 3/3/61 | F | Nashville | FT | Y | 1 | H | $ 29,640 | HMOI | ||||||
| 1086 | Zisowitz | 11/11/99 | 5/13/78 | M | Austin | FT | Y | 3 | S | $ 110,000 | HMOF | ||||||
| 1087 | Weaver | 9/12/08 | 9/24/86 | F | Austin | CN | N | 3 | S | $ 66,000 | None | ||||||
| 1088 | Williams | 9/18/00 | 6/16/53 | F | Austin | FT | N | 2 | S | $ 52,000 | HMOF | ||||||
| 1089 | Gifford | 11/13/09 | 10/11/55 | F | Austin | FT | Y | 3 | S | $ 85,000 | HMOF | ||||||
| 1090 | Scott | 12/11/08 | 3/3/74 | M | Austin | FT | Y | 3 | S | $ 130,000 | None | ||||||
| 1091 | Tufts | 5/13/04 | 6/5/59 | M | New Orleans | FT | Y | 3 | S | $ 110,000 | PPOF | ||||||
| 1092 | Sherman | 11/26/07 | 3/2/55 | M | Austin | PT | N | 1 | H | $ 33,280 | HMOI | ||||||
| 1093 | Mulcahey | 8/25/98 | 5/13/64 | F | Austin | PT | N | 2 | S | $ 45,000 | None | ||||||
| 1094 | Lachapelle | 11/20/98 | 11/2/50 | F | Austin | FT | N | 2 | S | $ 35,000 | HMOF | ||||||
| 1095 | Hamilton | 4/10/09 | 7/15/82 | M | New Orleans | FT | N | 3 | S | $ 63,750 | None | ||||||
| 1096 | Silver | 9/6/02 | 6/26/67 | F | Austin | PT | N | 3 | S | $ 93,000 | None | ||||||
| 1097 | Schwegler | 10/17/03 | 3/20/67 | M | New Orleans | CN | N | 3 | S | $ 90,000 | None | ||||||
| 1098 | Hanson | 5/29/09 | 4/15/53 | M | Austin | FT | Y | 3 | S | $ 152,400 | PPOF | ||||||
| 1099 | Reels | 6/15/06 | 7/31/63 | F | Austin | PT | N | 1 | H | $ 26,520 | None | ||||||
| 1100 | Hazera | 7/10/09 | 5/5/60 | M | Austin | FT | Y | 2 | S | $ 40,000 | PPOI | ||||||
| 1101 | Hu | 1/22/10 | 7/11/53 | F | Austin | FT | N | 2 | S | $ 50,000 | HMOF | ||||||
| 1102 | Flores | 10/16/09 | 7/15/84 | F | Austin | FT | Y | 3 | S | $ 85,000 | PPOI | ||||||
| 1103 | Johnson | 8/28/09 | 11/24/51 | F | Austin | FT | N | 1 | H | $ 28,496 | HMOI | ||||||
| 1104 | Laplante | 9/22/00 | 1/5/59 | F | Home | FT | N | 1 | H | $ 24,752 | HMOI | ||||||
| 1105 | Mazzotta | 3/19/01 | 11/7/82 | M | Austin | FT | Y | 3 | S | $ 65,000 | HMOI | ||||||
| 1106 | Tucker | 3/11/04 | 3/3/58 | F | Nashville | PT | N | 1 | H | $ 22,880 | PPOF | ||||||
| 1107 | Brazil | 8/28/09 | 2/5/76 | F | Austin | FT | N | 1 | H | $ 29,016 | None | ||||||
| 1108 | Lowe | 8/11/97 | 4/2/67 | M | Austin | FT | Y | 3 | S | $ 102,500 | HMOI | ||||||
| 1109 | Fasching | 4/3/09 | 2/22/67 | F | Austin | FT | N | 2 | S | $ 43,260 | PPOF | ||||||
| 1110 | Veyera | 3/7/03 | 2/15/65 | M | New Orleans | FT | Y | 3 | S | $ 150,000 | None | ||||||
| 1111 | O'Donnell | 12/29/05 | 4/4/53 | F | Nashville | CN | N | 3 | S | $ 55,000 | None | ||||||
| 1112 | Dasilva | 2/12/10 | 3/20/79 | F | Nashville | FT | N | 1 | H | $ 22,880 | HMOF | ||||||
| 1113 | Dain | 6/7/09 | 5/8/76 | F | Home | FT | N | 1 | H | $ 22,880 | HMOI | ||||||
| 1114 | Fisher | 1/22/10 | 4/2/91 | F | Home | FT | N | 1 | H | $ 23,920 | None | ||||||
| 1115 | Golet | 10/30/09 | 4/27/56 | M | Austin | FT | Y | 3 | S | $ 100,000 | PPOF | ||||||
| 1116 | Howard | 9/4/09 | 11/7/74 | F | Austin | FT | Y | 2 | S | $ 43,000 | HMOF | ||||||
| 1117 | Zunjic | 10/12/07 | 1/17/70 | F | Austin | PT | N | 1 | H | $ 36,004 | HMOI | ||||||
| 1118 | Lynch | 2/12/04 | 7/12/74 | F | Austin | FT | N | 2 | S | $ 38,500 | HMOI | ||||||
| 1119 | Thibault | 7/16/05 | 6/11/70 | F | Nashville | FT | N | 1 | H | $ 27,851 | PPOI | ||||||
| 1120 | Baglama | 10/2/09 | 6/10/68 | M | Austin | FT | Y | 3 | S | $ 96,000 | None | ||||||
| 1121 | Winters | 2/14/02 | 3/1/53 | F | Nashville | FT | N | 1 | H | $ 33,800 | PPOF | ||||||
| 1122 | Wang | 8/24/98 | 8/11/66 | F | Austin | FT | N | 1 | H | $ 35,048 | PPOI | ||||||
| 1123 | Harrison | 6/19/09 | 11/25/63 | M | Austin | FT | N | 2 | S | $ 41,000 | PPOF |
Functions
| Employee Bonus Statistics | ||
| AVERAGE | (2) | |
| MAX | (2) | |
| MIN | (2) | |
| SUM | (2) | |
| Trip Transport | ||
| Van Seats available | ||
| Number of kids campers | ||
| Rate Campers to Chaperones | * Need one chaperone per every 6 campers on average | |
| Chaperones Needed | (2) | |
| Number of Vans needed | (2) | |
| Home Loan Payment Calculation | ||
| Loan Amount | ||
| Number of Years | ||
| Annual Interest Rate | (2) | |
| Monthly Interest Rate | (2) | |
| Number of Payments | (2) | |
| Monthly Loan Payment | (2) | * 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 | (2) | |
| Monthly Interest rate | (2) | |
| Monthly deposit of | (2) | * it should be a negative value to represent an outflow every period |
