Accounting

This file has two sheets. The Employee sheet provides data about the Employee. EID is the Employee Identification number; DeptID is the number of the department for which the employee works. The SelectPlan sheet provides information about the employee’s health insurance coverage.

DepCode stands for Dependents Code (i.e., the people covered by the insurance), and the codes are:
• E – employee only
• S – employee and spouse
• C1 – employee and 1 child
• C2 – employee and several children
• S1 – employee, spouse and 1 child
• S2 – employee, spouse and several children
InsCode stands for Insurance Code, and provides the name of the insurance company; the codes are:
• BHC – Basic Health Care
• AALC –Allied Atlantic Life Care
• MD – Metropolitan Dental
• PV – Physician’s Value

For #1 and #2 you will use a new function, the concatenate function; it allows you to create a new text string from other text strings. This function can take an almost unlimited number of arguments, and the format is as follows: =CONCATENATE(text1;text2;text3;…) If the text you want to use is stored in a worksheet cell, you simply refer to that text by its cell location. If you want to add text not already stored in a worksheet cell, you must surround that text with double quotes [“]. Use the Excel help function if you need additional detail.

You are required to do the following:
1. (10 pts) On the Employee sheet, adda column between the Last Name and Street Address columns. Label the column FullName. In this column, create a formula that shows the employee’s name as – first name followed by a space then the last name.
2. (10 pts) On the SelectPlan sheet, in column D,create a formula that shows the employee’s name as – last name followed by a comma and a space, then the first name. Label the column EmployeeName. (Hint – you must nest vlookups within a concatenate.)
3. (20 pts) Provide a list of employees who have coverage with Metropolitan Dental. Clearly, you may not do this manually by copy/paste. It must be done in such a fashion that it can be automatically updated as employees add and delete coverage.
4. (20 pts) On a new sheet, create alittle application where the user can enter the employee’s ID number to find the employee’s name. The application should return the full name provided on the Employee sheet (i.e., first last). Be sure everything is properly labelled and instructions are provided. Name this sheet “LookUps”.
5. (20 pts) On the LookUps sheet, create a little application where the user can enter the employee’s ID number to find out if the employee has dental coverage. If the employee does have dental coverage, return the message “Has Dental Coverage”; if not, return the message “Does Not Have Dental Coverage”. Be sure everything is properly labelled and instructions are provided.
EID DeptID EFirstName ELastName StreetAddress City State ZipCode OfficeExt
1 01 Sherman Rhames 2007 Logan Dallas TX 75261 4789
2 02 Marcie Roach 748 Birch Garland TX 75040 2792
3 03 Gordon Slovacek 2255 Woodcreek Grapevine TX 76051 5595
4 04 Perla Furukawa 19404 Golden Eagle Dallas TX 75261 5710
5 05 Jaque Votaw 203 Chowning Avenue Arlington TX 76004 3201
6 01 Tang Tong 1920 Timberlane Road Plano TX 75024 4788
7 01 Patricia Vyapuri 1412 Henny Penny Denton TX 76202 4777
8 02 Melinda Fogle 1312 Cherryville Garland TX 75040 2793
9 03 Eric Gonzalez 1921 Summerfield Drive Garland TX 75040 5596
10 04 Akiko Chee 817 Madison Grapevine TX 76051 5711
11 05 Chia-Yi Lou 301 Lancaster Grapevine TX 76051 3202
12 01 Terrance Estrada 14901 Geeta Grove Dallas TX 75261 4776
13 02 Antonio Enriquez 916 Kennifix Dallas TX 75261 2794
14 02 Donald D’Amico 200 W 14th Street Dallas TX 75261 2795
15 02 Vicky Carder 3901Santa Fe Dallas TX 75261 2796
16 03 Donatica Angelo 611 Praire Avenue Dallas TX 75261 5597
17 04 Lorraine Wakefield 3705 Sleepy Lane Dallas TX 75261 5712
18 05 James Lester 710 Ashecroft Plano TX 75024 3203
19 03 Rex Tickner 36095 Lincoln Arlington TX 76004 5598
20 03 Deona Timanus 1514 Washington Arlington TX 76004 5599
21 04 Gayle Yates 1300 Cleveland Arlington TX 76004 5713
22 05 Zee Yereshenko 7712 Martin Luther Garland TX 75040 3204
23 05 Xia Lujin 38292 Kickingbird Dallas TX 75261 3205
24 04 Wei Wang 1623 Hanah Parkway Plano TX 75024 5714
25 03 Ying Fang 2476 North Elm Grapevine TX 76051 5594
26 06 Brenda Fees 248 Lane Way Garland TX 75040 3734
27 06 Becky Hendrix 4971 Ranch Acres Garland TX 75040 1333
28 05 Bernard Landry 63658 Iowa Drive Grapevine TX 76051 8410
29 01 Carol Dawenport 918 Anabor Lane Grapevine TX 76051 8246
30 04 Dane Carnes 715 SW 60th Dallas TX 75261 3582
31 02 Erik Branning 7310 Deerhurst Dallas TX 75261 9953
32 01 Elliott Brainard 38475 Shady Lane Dallas TX 75261 6047
33 06 Henry Luft 261 Johnson Drive Dallas TX 75261 5676
34 02 Allan Robnett 817 Rushing Waters Dallas TX 75261 2528
35 06 Doyle Tysdell 7103 Royal Bridge Dallas TX 75261 3037
36 06 Elvera Shimp 8392 Avenda Avenue Plano TX 75024 1951
37 05 Rachelle Logue 6038 Village Street Arlington TX 76004 3114
38 04 Annie Hull 712 8th Street Arlington TX 76004 0745
39 01 Deka Delarosa 1604 Mohican Avenue Arlington TX 76004 5185
40 06 Ben Hartmiller 11590 Heritage Drive Dallas TX 75261 3007
41 03 Gerry Leffingwell 2020 Shining Willow Dallas TX 75261 1322
42 06 Alredo Meisikep 18808 Woodhaven Dallas TX 75261 6022