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.