BUS782,
Spring 2012, Assignment 2, Due Date: 2/22/12
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 two premiums, one for individual employees and one for employees with families. The company pays 80% of the cost of this insurance and the employee pays 20%. The total monthly premiums are as follows:
Individual:
$450 per month
With dependents: Less
than 3 dependents: $500
At
least 3 dependents: $550
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 $87,900 paid at the rate of 6.2% with a maximum amount withheld of $5450. 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 |
Married Status |
Annual Salary |
Exempt |
401K |
Number |
|
Buck |
M |
$89,000 |
Y |
N |
3 |
|
Cohen |
M |
$95,000 |
N |
Y |
0 |
|
Chen |
M |
$65,990 |
Y |
Y |
4 |
|
Green |
S |
$56,000 |
Y |
Y |
1 |
|
Jacobs |
S |
$70,500 |
Y |
N |
2 |
|
Kline |
M |
$45,000 |
N |
Y |
0 |
|
Lee |
M |
$80,000 |
Y |
Y |
4 |
|
Lewis |
S |
$6,000 |
N |
Y |
0 |
|
Strong |
M |
$70,250 |
Y |
Y |
3 |
Design a spreadsheet to 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.
Calculation section: This section performs the calculations.
Report section: This section contains the reports.
In this assignment, place each section on a separate spreadsheet. Name the first spreadsheet “Input” and enter the following input variables:
Medical plan premiums
group life insurance premium
percentage of employer’s contribution to the 401K plan
worker’s compensation premium
Social Security FICA tax rate
the maximum taxable earnings amount for Social Security
Social Security Medicare tax rate
FUTA tax rate
Name the second spreadsheet “Calculation”. This spreadsheet contains the employee data and formulas to compute employee benefit costs. The formulas should reference the input variables in the Input spreadsheet. To reference a cell in another spreadsheet, you specify the spreadsheet name and the cell. For example, to reference the cell B3 on the Input spreadsheet, you do: Input!B3.
Name the third spreadsheet “Report”. This spreadsheet contains the summary report. You should reference the Calculation spreadsheet to get the total cost for each benefit plan.
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.
Turn in:
1. Worksheet views.
2. Worksheet formula views.