Payroll Accounting Unit3 Assignment Part 2

jfmysjvfes691981

cpp_a_templateunit3assignmentpart2.xlsx

Home >Business & Finance homework help >Accounting homework help >Payroll Accounting Unit3 Assignment Part 2

Excel Instructions

Excel Instructions using Excel 2010:

1. Enter the appropriate numbers/formulas in the shaded (gray) cells. An asterisk (*) will appear to the right of an incorrect answer.

2. A formula begins with an equals sign (=) and can consist of any of the following elements:

Operators such as + (for addition), - (for subtraction), * (for multiplication), and / (for division).

Cell references, including cell addresses such as B52, as well as named cells and ranges

Values and text

Worksheet functions (such as SUM)

3. You can enter a formula into a cell manually (typing it in) or by pointing to the cells.

To enter a formula manually, follow these steps:

Move the cell pointer to the cell that you want to hold the formula.

Type an equals sign (=) to signal the fact that the cell contains a formula.

Type the formula, then press Enter.

4. Rounding: These templates have been formatted to round numbers to either the nearest whole number or the nearest cent. For example,

17.65 x 1.5=26.475. The template will display and hold 26.48, not 26.475. There is no need to use Excel's rounding function.

EXCEPTION: Continuing Payroll Problems A & B: CHAPTER 2

When calculating over-time rate for weekly salary, round regular rate to TWO decimals BEFORE calculating overtime rate.

Rounding can be accomplished by using Number function (using arrows) on Excel Home menu or by entering the formula

=(Round(Weekly/40,2))*1.5 (where "Weekly" entered as either the weekly pay or cell reference.)

Failure to use the ROUND function will cause the OT rate to be incorrect.

5. Remember to save your work. When saving your workbook, Excel overwrites the previous copy of your file. You can save your work at any time.

You can save the file to the current name, or you may want to keep multiple versions of your work by saving each successive version under a different name.

To save to the current name, you can select File, Save from the menu bar or click on the disk icon in the standard toolbar.

It is recommended that you save the file to a new name that identifies the file as yours, such as CPP_A_Your_Name.xlsx

To save under a different name, follow these steps:

Select File, Save As to display the Save As Type drop-box, chose Excel Workbook (*.xlsx)

Select the folder in which to store the workbook.

Enter the new filename in the File name box.

Click Save.

Continuing Problem-A

Name:
Caution: See "round" rules in Excel Instructions before calculating OT for salaried employees.
Enter the appropriate numbers/formulas in the shaded (gray) cells. An asterisk (*) will appear to the right of an incorrect answer.
Continuing Payroll Problem-A
KIPLEY COMPANY, INC.
PAYROLL REGISTER
FOR PERIOD ENDING	January 8, 20 - -
MARITAL STATUS	NO. OF W/H ALLOW.	REGULAR EARNINGS	OVERTIME EARNINGS	DEDUCTIONS	NET PAY	TAXABLE EARNINGS	OASDI	HI	FIT	SIT	SUTA	CIT	SIMPLE	GROUP	HEALTH	Net
HOURS WORKED	RATE PER HOUR	HOURS WORKED	RATE PER HOUR	0.062	0.0145	0.0307	0.0007	0.03	INS	INS	pay	oasdi	hi	futa	suta
TOTAL	FICA	GROUP	HEALTH	CHECK
EMPLOYEE	AMOUNT	AMOUNT	EARNINGS	OASDI	HI	FIT	SIT	SUTA	CIT	SIMPLE	INSURANCE	INSURANCE	NO.	AMOUNT	OASDI	HI	FUTA	SUTA
11 Carson, Fran M.	Mark Sears: Enter as a formula of regular hours worked x regular rate per hour			Mark Sears: Enter as a formula totaling regular and overtime earnings		Mark Sears: Enter as a formula of taxable earnings x OASDI rate		Mark Sears: Enter as a formula of taxable earnings x HI rate			Mark Sears: Enter as a formula of taxable earnings x state tax rate		Mark Sears: Enter as a formula of taxable earnings x SUTA tax rate		Mark Sears: Enter as a formula of taxable earnings x city tax rate					313	Mark Sears: Enter as a formula of total earnings less the sum of deductions						300	300	18.60 Mark Sears: Enter as a formula of taxable earnings x OASDI rate	4.35 Mark Sears: Enter as a formula of taxable earnings x HI rate	17.00	9.21	0.21	9.00	20.00	0.85	1.65	219.13	300.00	300.00	300.00	300.00
12 Wilson, William A.		Mark Sears: Note: Only the Amount column in this section will be graded.	Mark Sears: For hourly workers, insert as a formula of regular rate x 1.5	Mark Sears: Enter as a formula of overtime hours worked x overtime rate per hour												314						290	87.04	377.04	23.38	5.47	34.00	11.58	0.26	11.31	50.00	0.85	1.65	238.54	377.04	377.04	377.04	377.04
13 Utley, Harry T.													315						303.75	303.75	18.83	4.40	- 0	9.33	0.21	9.11	40.00	0.85	1.65	219.37	303.75	303.75	303.75	303.75
21 Fife, Lawrence R.													316						316	71.1	387.1	24.00	5.61	- 0	11.88	0.27	11.61	50.00	0.85	1.65	281.23	387.10	387.10	387.10	387.10
22 Smith, Lucy K.													317						390	390	24.18	5.66	19.00	11.97	0.27	11.70	20.00	- 0	1.65	295.57	390.00	390.00	390.00	390.00
31 Fay, Gretchen R.	Mark Sears: Insert stated weekly salary		Mark Sears: Note: For salaried workers, enter a formula converting the weekly amount in column I to an hourly amount and multiplying by 1.5. To be graded correctly, you must use the ROUND command to two digits in the formula.												318						515	24.15	539.15	33.43	7.82	11.00	16.55	0.38	16.17	40.00	0.85	1.65	411.30	539.15	539.15	539.15	539.15
32 Robey, Glenda B.	Mark Sears: For salaries stated as monthly or yearly, enter a formula converting to a weekly amount													319						392.31	392.31	24.32	5.69	- 0	12.04	0.27	11.77	50.00	0.85	1.65	285.72	392.31	392.31	392.31	392.31
33 Schork, Thomas K.													320						542.31	542.31	33.62	7.86	47.00	16.65	0.38	16.27	60.00	- 0	1.65	358.88	542.31	542.31	542.31	542.31
51 Hardy, Barbara T.													321						348.46	52.28	400.74	24.85	5.81	- 0	12.30	0.28	12.02	30.00	0.85	1.65	312.98	400.74	400.74	400.74	400.74
99 Kipley, Carson C.													322						1000	1000	62.00	14.50	24.00	30.70	0.70	30.00	80.00	0.85	1.65	755.60	1,000.00	1,000.00	1,000.00	1,000.00
Totals	Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		4,397.83 Mark Sears: Enter as formula totaling column	234.57 Mark Sears: Enter as formula totaling column	4,632.40 Mark Sears: Enter as formula totaling column	287.21 Mark Sears: Enter as formula totaling column	67.17 Mark Sears: Enter as formula totaling column	152.00 Mark Sears: Enter as formula totaling column	142.21 Mark Sears: Enter as formula totaling column	3.23 Mark Sears: Enter as formula totaling column	138.96 Mark Sears: Enter as formula totaling column	440.00 Mark Sears: Enter as formula totaling column	6.80 Mark Sears: Enter as formula totaling column	16.50 Mark Sears: Enter as formula totaling column	3,378.32 Mark Sears: Enter as formula totaling column	4,632.40 Mark Sears: Enter as formula totaling column	4,632.40 Mark Sears: Enter as formula totaling column	4,632.40 Mark Sears: Enter as formula totaling column	4,632.40 Mark Sears: Enter as formula totaling column
JOURNAL
DATE	DESCRIPTION	DEBIT	CREDIT	Taxable	Net
20--	Earnings	Rate	FUTA Tax
Jan. 12	Wages and Salaries		Net FUTA				4,632.40
FICA Taxes Payable - OASDI			SUTA Tax	287.21
FICA Taxes Payable - HI			SUTA				67.17	Taxable
Employees FIT Payable			152.00	Earnings	Rate	Net FUTA
Employees SIT Payable		142.21	4,632.40	0.006	27.79
Employees SUTA Payable			3.23	SUTA
Employees CIT Payable			138.96	4,632.40	0.036785	170.40
SIMPLE Deductions Payable			440.00
Group Insurance Premiums Collected		6.80
Health Insurance Premiums Collected			16.50
Salaries Payable		3,378.32
Jan. 12	Payroll Taxes		552.57
FICA Taxes Payable - OASDI			287.21
FICA Taxes Payable - HI			67.17
FUTA Taxes Payable			27.79
SUTA Taxes Payable		170.40
Jan. 14	Salaries Payable		3,378.32
Cash			3,378.32