Explore BrainMass
Share

Payroll in Excel

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

See attached file.

1. The Orange colored cells on the worksheet indicate places where you will put in the formulas and functions.

2. Calculate the total Gross Pay for each employee. Keep in mind that employees are paid time and a half for all over-time hours. Here is an example formula that demonstrates how you would make such a calculation: =(b4*d4)+(c4*d4*1.5) These must be formulas and will be entered into the range E11:E14
E.g. (regular hours * hourly rate) + (overtime hours * hourly rate * 1.5)

3. In #5, #6, and #7 below you must include an absolute reference cell in each formula!!! Imagine while you are completing these steps that there were 150 employees at the company.

4. Calculate the total amount each employee must pay in Federal Taxes in the range F11:F14. These calculations must be completed using formulas. These formulas MUST contain an absolute reference to the cell holding the percentage amount. The percentage figure used in the formula is above the word Federal in cell F9.

5. Calculate the total amount each employee must pay in State Taxes in the range G11:G14. These calculations must be completed using formulas. These formulas MUST contain an absolute reference to the cell holding the percentage amount. The percentage figure used in the formula is above the word State in cell G9.

6. Calculate the total amount each employee must pay in Social Security Taxes in the range H11:H14. These calculations must be completed using formulas. These formulas MUST contain an absolute reference to the cell holding the percentage amount. The percentage figure is above the word Social in cell H9.

7. Calculate the total Net Pay for each employee in the range: I11:I14. These calculations must be completed using a formula.

8. Calculate the totals for all of the employee hours and pay categories in the range: B16:I16. (It is not necessary to sum the "Hourly Rate" column.) These calculations must be completed using SUM functions.

9. Format all of the cells that represent a dollar amount as Currency with 2 decimal places.

© BrainMass Inc. brainmass.com October 25, 2018, 2:21 am ad1c9bdddf
https://brainmass.com/business/accounting/296410

Attachments

Solution Summary

The solution explains payroll calculations using excel

$2.19
See Also This Related BrainMass Solution

Payroll Tax Entries in Excel

(Payroll Tax Entries) The payroll of Auber Corp. for September 2008 is as follows: Total payroll was $495,000. Pensionable (CPP) and insurable (EI) earnings were $375,000. Income taxes in the amount of $90,000 were withheld, as was $9,000 in union dues. The employment insurance tax rate was 1.80% for employees and 2.52% for employers and the CPP rate was 4.95% for employees and 4.95% for employers.

Instructions:
(a) Prepare the necessary journal entries to record the payroll if the wages and salaries paid and the employer payroll taxes are recorded separately.
(b) Prepare the entries to record the payment of all required amounts to the CRA and to the employees' union.
(c) For every dollar of wages and salaries that Auber commits to pay, what is the actual payroll cost to the company?
(d) Discuss any other costs, direct or indirect, that you think would add to the company's costs of having employees.

View Full Posting Details