Excel Assignment 3
BUS 363, Assignment 3, Due Date: on Syllabus
You may submit this assignment using the iLearn assignment link.
John Smith, vice president of administration for ABC International, has asked you to help him
calculate the annual human resources budget. The ABC International’s benefits package
includes:
1. Medical plan: Insurance Company charges monthly premiums based on the following rules:
Individual (no dependent): $550 per month
Less than 3 dependents: $600 per month
At least 3 dependents: $750 per month
The company pays 80% of the cost of this insurance and the employee pays 20%. And you need
to multiply the monthly premium by 12 to get the annual cost.
2. Group Life Insurance: ABC International pays entirely for group life insurance. The annual
fee is $2.5 per $1000 of coverage. The benefit for employees varies. An exempt (not eligible for
overtime pay) employee’s benefit is two times his or her salary; a nonexempt (eligible for
overtime pay) employee’s benefit is one and one-half times his or her annual salary. For
example, if an exempt employee’s annual salary is $50,000, then the insurance coverage is
$100,000; the premium paid by the company is: $250.
3. 401K Retirement Plan: Employees may participate in the 401K plan. For those participating
employees, the company will pay 5% of their salary to the plan.
4. Worker’s Compensation: The worker’s compensation premium is based on a fee of $7.5 per
$1000 of annual salary.
5. FICA Taxes (Social Security): The Social Security (FICA) tax contains two parts. The Social
Security (Old Age, Survivors, and Disability Insurance) FICA tax is based on the first $117,000
of salary paid at the rate of 6.2% with a maximum amount withheld of $7254. The Medicare
(Medical Hospital Insurance) FICA tax is based on all earnings paid, at the rate of 1.45%. There
is no limit on the Medicare FICA gross.
6. Federal Unemployment Tax (FUTA): ABC International must pay an unemployment tax equal
to 6.2% of the first $7000 of each employee’s salary.
The employee database is given below:
Name Annual
Salary
Exempt 401K Number
of
Dependents
Buckingham $129,000 Y N 3
Cohen $95,000 N Y 0
Chen $65,990 Y Y 4
Green $56,000 Y Y 1
Jacobs $70,500 Y N 2
Kline $145,000 N Y 0
Lee $80,000 Y Y 4
Lewis $6,000 N Y 0
Strong $70,250 Y Y 3
Design a spreadsheet to compute employee benefits and produce an employee benefits summary
report that shows the costs for each program and the total benefit costs.
ABC International Annual Benefit Budget
Plan Cost
—————————————–
Medical Plan
Group Life Insurance
401K Plan
Worker’s Compensation
FICA Tax
FUTA
——————————————
Total Cost
A general principle in designing a spreadsheet is dividing the spreadsheet into sections. Some
typical sections are:
Input section: This section contains the variables used in formulas that are likely to change.
Sometime the input section is said to contain the worksheet’s assumption. In this assignment,
consider the following variables as input that could be changed:
Medical plan premiums
Group life insurance premium
The percentage of employer’s contribution to the 401K plan
Worker’s compensation premium
Social Security FICA tax rate and Medicare tax rate
The maximum taxable earnings amount for Social Security
FUTA tax rate
Calculation section: This section performs the calculations.
Report section: This section contains the employee benefits summary report.
Note that the three sections are linked so that changes in input variables will be automatically
reflected in the calculation section and the report section.
Create the spreadsheet to produce the report and submit the spreadsheet.
