Employees who elect additional life insurance coverage (Add Life Ins) pay a fee equal to a percentage of their annual salary. The life insurance premium rate is in cell U5. Employees who do not elect additional life insurance pay nothing. In the Life Insurance Premium column, enter an IF function to calculate the life insurance premiums; use an absolute reference to cell U5 to obtain the life insurance rate and enter 0 for employees who do not elect coverage.All full-time employees over the age of 30 are eligible for the 401(k) benefit. In the 401(k) column, enter the IF and AND functions to calculate the 401(k) benefit as 3 percent of annual salary; use an absolute reference to cell U6 to obtain the 401(k) matching percent rate (3 percent). If the employee is not eligible, enter 0. (HINT: the AND function is used as the logical test in the IF function)All employees with one or more years of service are eligible for a bonus. Pay Grade 1 employees receive $3,000 (cell U2), Pay Grade 2 employees receive $6,000 (cell U3), and Pay Grade 3 employees receive $8,000 (cell U4). In the Bonus column, use the IFS function to calculate the bonus. For employees in pay grades over 3, there is no bonus. (HINT: the AND function is used in the logical test within the IFS function because you have to test if the years of service >=1 AND each specific pay grade)In the Salary Increase column, enter IF and OR functions to insert the text “Not Eligible” if the individual’s job status is a consultant (CN) or the pay grade is 4. Leave the cell blank if the employee is eligible for a salary increase. (HINT: the logical test for the IF function requires the OR function) JobAddPayStatus Life Ins Grade TypeEmp ID Last NameHire DateBirth DatePayAnnualHealth YearsLife InsSalarySex LocationPlanService AgePremium401(k)Salary$54,0001082 SteyerlBonusIncrease3/13/20089/12/201110/17/200612/29/20086/12/200611/6/20125/24/20074/24/20155/12/1969 M Austin9/24/19863/20/1967 M New Orleans4/4/19531/30/19423/20/1973 M NashvilleCN31087 WeaverF AustinNone847CN3$ 6,000 None$ 90,000 None$5,000$ 21,840 None$22,880$40,000 HMOF$ 37,244 HMOF$ 36,000 None$55,0001097 Schwegler429CN31111 O'Donnell949FNashvilleCN31033 MccorkleNone763F NashvilleFT11055 Damien1074FT11025 Overton2/15/1986PPOF43FHomeFT21026 Fetherston9.309/24/1968 M New Orleans12/4/1961| M Austin1/16/1970 F12/2/1985 M Nashville11/1/1982 F5/9/1959 F5/10/1966 M Austin8/31/1973 F Home6/18/2001 10/14/1952 M Austin11/3/1965 M Austin4/11/1962 M Austin4/11/1961 M New Orleans7/8/1949 M New Orleans12/1/1956 M New Orleans3/6/1970FTYS1471031 Akhalaghi1039 Cugini12/8/2015FT5412/4/201210/12/20121/29/201512/4/201511/26/2015F AustinFTY1040 DashPPOF346FTY$65,000 HMOF$ 45,000$ 125,000 HMOF$ 80,000 HMOI$60,000$ 122,500 HMOF$ 200,000 | HMOF3301075 Boisclair1041 Donnelly1042 LuchtAustinFTY2PPOI133New OrleansFTYS57FTY3S501043 Tiernan3/19/2010FTSNone421044 Sheinin1045 Kenyon1047 HusbandFTY315637/21/20122/19/20137/10/20113/13/20098/8/2012FTY3S50FTY3S$65,000 HMOF541049 Longa1061 WhiteFTY3.$ 175,000$ 95,000$ 80,000 HMOI$ 70,000 HMOF$ 73,500None455FTY3None7661062 ChamberlainFTY3591064 Young1/8/2012FNew OrleansFT3.S4461065 Viator10/4/2010 10/25/19595/29/20121/1/20161/8/2001| 12/23/19914/20/20046/15/2004FAustinFTPPOF561068 Carrington1070 Greenwood1071 Martuscelli8/21/1950 M Austin6/10/1966 M Austin| M Austin6/30/1956 M Austin4/29/19506/16/1953 M New Orleans6/5/1959 M New Orleans7/15/1982 M New Orleans4/15/1953 M Austin7/15/19843/2/1955 M Austin7/31/19633/3/1958FTYS$ 115,000 | HMOF$ 123,000 PPOF$ 90,000 PPOI$ 110,000 | HMOI465FTY50FTY15241072 McgovernFTY1260Austin1073 Lapointe1074 SweetFTYS$75,000None12666/3/2007FTY3.$ 80,000 HMOI$ 110,000 PPOF$ 63,750$ 152,400$85,000963FTY45/13/20074/10/20125/29/20121091 Tufts9.571095 HamiltonFT3None4331098 HansonFTY3PPOF463AustinFTY3S10/16/201511/26/20106/15/20091102 FloresPPOI311092 ShermanPT1H$33,280 HMOI611099 ReelsF AustinPT1H$26,520None752NashvillePTH1106 Tucker1117 Zunjic1058 Myette1093 Mulcahey1096 SilverF3/11/200710/12/20105/4/2009$ 22,880$ 36,004 HMOI$33,000 HMOFPPOF958H.1/17/19706/28/1967 M5/13/19646/26/1967AustinPTN46м HomeAustinAustinPT47498/25/2001PTN$ 45,000 None$ 93,00014529/6/2005FPTNone10493.3.M333.3. Bonus Pay Grade 1 $ 3,000Bonus Pay Grade 2 $ 6,000Bonus Pay Grade 3 $ 8,000Life Ins Premium0.10%401(k) matching %years of service 7/1/2016 7936.00054.0000$ 65,000 KMOF45,00057

In the first step of optimizing the employee compensation spreadsheet, attention is directed toward…

employees who do not elect coverage. All full-time employees over the age of 30 are eligible for the 401(k) benefit. In the 401(k) column, enter the IF and AND functions to calculate the 401(k) benefit as 3 percent of annual salary; use an absolute reference to cell U6 to obtain the 401(k) matching percent rate (3 percent). If the employee is not eligible, enter 0. (HINT: the AND function is used as the logical test in the IF function) All employees with one or more years of service are eligible for a bonus. Pay Grade 1 employees receive $3,000 (cell U2), Pay Grade 2 employees receive $6,000 (cell U3), and Pay Grade 3 employees receive $8,000 (cell U4). In the Bonus column, use the IFS function to calculate the bonus. For employees in pay grades over 3, there is no bonus. (HINT: the AND function is used in the logical test within the IFS function because you have to test if the years of service >=1 AND each specific pay grade) In the Salary Increase column, enter IF and OR functions to insert the text “Not Eligible” if the individual’s job status is a consultant (CN) or the pay grade is 4. Leave the cell blank if the employee is eligible for a salary increase. (HINT: the logical test for the IF function requires the OR function)

FINANCIAL ACCOUNTING

10th Edition

ISBN:9781259964947

Author:Libby

Publisher:Libby

Chapter1: Financial Statements And Business Decisions

Section: Chapter Questions

Problem 1Q

See similar textbooks

Related questions

Question

Employees who elect additional life insurance coverage (Add Life Ins) pay a fee equal to a percentage of their annual salary. The life insurance premium rate is in cell U5. Employees who do not elect additional life insurance pay nothing. In the Life Insurance Premium column, enter an IF function to calculate the life insurance premiums; use an absolute reference to cell U5 to obtain the life insurance rate and enter 0 for employees who do not elect coverage.
All full-time employees over the age of 30 are eligible for the 401(k) benefit. In the 401(k) column, enter the IF and AND functions to calculate the 401(k) benefit as 3 percent of annual salary; use an absolute reference to cell U6 to obtain the 401(k) matching percent rate (3 percent). If the employee is not eligible, enter 0. (HINT: the AND function is used as the logical test in the IF function)
All employees with one or more years of service are eligible for a bonus. Pay Grade 1 employees receive $3,000 (cell U2), Pay Grade 2 employees receive $6,000 (cell U3), and Pay Grade 3 employees receive $8,000 (cell U4). In the Bonus column, use the IFS function to calculate the bonus. For employees in pay grades over 3, there is no bonus. (HINT: the AND function is used in the logical test within the IFS function because you have to test if the years of service >=1 AND each specific pay grade)
In the Salary Increase column, enter IF and OR functions to insert the text “Not Eligible” if the individual’s job status is a consultant (CN) or the pay grade is 4. Leave the cell blank if the employee is eligible for a salary increase. (HINT: the logical test for the IF function requires the OR function)

Job
Add
Pay
Status Life Ins Grade Type
Emp ID Last Name
Hire Date
Birth Date
Pay
Annual
Health Years
Life Ins
Salary
Sex Location
Plan
Service Age
Premium
401(k)
Salary
$54,000
1082 Steyerl
Bonus
Increase
3/13/2008
9/12/2011
10/17/2006
12/29/2008
6/12/2006
11/6/2012
5/24/2007
4/24/2015
5/12/1969 M Austin
9/24/1986
3/20/1967 M New Orleans
4/4/1953
1/30/1942
3/20/1973 M Nashville
CN
3
1087 Weaver
F Austin
None
8
47
CN
3
$ 6,000 None
$ 90,000 None
$5,000
$ 21,840 None
$22,880
$40,000 HMOF
$ 37,244 HMOF
$ 36,000 None
$55,000
1097 Schwegler
4
29
CN
3
1111 O'Donnell
9
49
F
Nashville
CN
3
1033 Mccorkle
None
7
63
F Nashville
FT
1
1055 Damien
10
74
FT
1
1025 Overton
2/15/1986
PPOF
43
F
Home
FT
2
1026 Fetherston
9.
30
9/24/1968 M New Orleans
12/4/1961| M Austin
1/16/1970 F
12/2/1985 M Nashville
11/1/1982 F
5/9/1959 F
5/10/1966 M Austin
8/31/1973 F Home
6/18/2001 10/14/1952 M Austin
11/3/1965 M Austin
4/11/1962 M Austin
4/11/1961 M New Orleans
7/8/1949 M New Orleans
12/1/1956 M New Orleans
3/6/1970
FT
Y
S
1
47
1031 Akhalaghi
1039 Cugini
12/8/2015
FT
54
12/4/2012
10/12/2012
1/29/2015
12/4/2015
11/26/2015
F Austin
FT
Y
1040 Dash
PPOF
3
46
FT
Y
$65,000 HMOF
$ 45,000
$ 125,000 HMOF
$ 80,000 HMOI
$60,000
$ 122,500 HMOF
$ 200,000 | HMOF
3
30
1075 Boisclair
1041 Donnelly
1042 Lucht
Austin
FT
Y
2
PPOI
1
33
New Orleans
FT
Y
S
57
FT
Y
3
S
50
1043 Tiernan
3/19/2010
FT
S
None
42
1044 Sheinin
1045 Kenyon
1047 Husband
FT
Y
3
15
63
7/21/2012
2/19/2013
7/10/2011
3/13/2009
8/8/2012
FT
Y
3
S
50
FT
Y
3
S
$65,000 HMOF
54
1049 Longa
1061 White
FT
Y
3.
$ 175,000
$ 95,000
$ 80,000 HMOI
$ 70,000 HMOF
$ 73,500
None
4
55
FT
Y
3
None
7
66
1062 Chamberlain
FT
Y
3
59
1064 Young
1/8/2012
F
New Orleans
FT
3.
S
4
46
1065 Viator
10/4/2010 10/25/1959
5/29/2012
1/1/2016
1/8/2001| 12/23/1991
4/20/2004
6/15/2004
F
Austin
FT
PPOF
56
1068 Carrington
1070 Greenwood
1071 Martuscelli
8/21/1950 M Austin
6/10/1966 M Austin
| M Austin
6/30/1956 M Austin
4/29/1950
6/16/1953 M New Orleans
6/5/1959 M New Orleans
7/15/1982 M New Orleans
4/15/1953 M Austin
7/15/1984
3/2/1955 M Austin
7/31/1963
3/3/1958
FT
Y
S
$ 115,000 | HMOF
$ 123,000 PPOF
$ 90,000 PPOI
$ 110,000 | HMOI
4
65
FT
Y
50
FT
Y
15
24
1072 Mcgovern
FT
Y
12
60
Austin
1073 Lapointe
1074 Sweet
FT
Y
S
$75,000
None
12
66
6/3/2007
FT
Y
3.
$ 80,000 HMOI
$ 110,000 PPOF
$ 63,750
$ 152,400
$85,000
9
63
FT
Y
4
5/13/2007
4/10/2012
5/29/2012
1091 Tufts
9.
57
1095 Hamilton
FT
3
None
4
33
1098 Hanson
FT
Y
3
PPOF
4
63
Austin
FT
Y
3
S
10/16/2015
11/26/2010
6/15/2009
1102 Flores
PPOI
31
1092 Sherman
PT
1
H
$33,280 HMOI
61
1099 Reels
F Austin
PT
1
H
$26,520
None
7
52
Nashville
PT
H
1106 Tucker
1117 Zunjic
1058 Myette
1093 Mulcahey
1096 Silver
F
3/11/2007
10/12/2010
5/4/2009
$ 22,880
$ 36,004 HMOI
$33,000 HMOF
PPOF
9
58
H.
1/17/1970
6/28/1967 M
5/13/1964
6/26/1967
Austin
PT
N
46
м Home
Austin
Austin
PT
4
7
49
8/25/2001
PT
N
$ 45,000 None
$ 93,000
14
52
9/6/2005
F
PT
None
10
49
3.
3.
M33
3.
3.

Bonus Pay Grade 1 $ 3,000
Bonus Pay Grade 2 $ 6,000
Bonus Pay Grade 3 $ 8,000
Life Ins Premium
0.10%
401(k) matching %
years of service 7/1/2016 7
936.000
54
.0
000
$ 65,000 KMOF
45,000
57

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 3 images

SEE SOLUTION Check out a sample Q&A here