PRAU4DS4P2

jfmysjvfes691981

prau4ds4p2cpp_b_template-3.xlsx

Home >Business & Finance homework help >Accounting homework help >PRAU4DS4P2

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_B_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-B

Name:
CAUTION: See "round" rules in Excel Instructions before calculating OT for slaried 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-B
OLNEY COMPANY, INC.
PAYROLL REGISTER
FOR PERIOD ENDING		January 8, 20 - -
																																oasdi	HI		sit	suta	cit
		MARITAL STATUS	NO. OF W/H ALLOW.	REGULAR EARNINGS			OVERTIME EARNINGS				DEDUCTIONS										NET PAY		TAXABLE EARNINGS								ee	0.062	0.0145		0.0307	0.0007	0.0135
				HOURS WORKED	RATE PER HOUR		HOURS WORKED	RATE PER HOUR																							er	0.062	0.0145									TAXABLE
										TOTAL	FICA								GROUP	HEALTH	CHECK																					0.062	0.0145	0.006	0.036785
EMPLOYEE						AMOUNT			AMOUNT	EARNINGS	OASDI		HI	FIT	SIT	SUTA	CIT	SIMPLE	INSURANCE	INSURANCE	NO.	AMOUNT	OASDI		HI		FUTA	SUTA	Reg	OT earning	Totat	OASDI	HI	FIT	SIT	SUTA	CIT	Simple	Group In	Health In	Net	OASDI	HI	FUTA	SUTA
11 Mangino, R.						Mark Sears: Enter as a formula of regular hours worked x regular rate per hour															313								340.00		340.00	21.08 Mark Sears: Enter as a formula of taxable earnings x OASDI rate	4.93 Mark Sears: Enter as a formula of taxable earnings x HI rate	23.00	10.44	0.24	4.59	20.00	0.85	1.65	253.22	340.00	340.00	340.00	340.00
12 Flores, I.							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								370.00	138.80	508.80	31.55	7.38	53.00	15.62	0.36	6.87	50.00	0.85	1.65	341.52	508.80	508.80	508.80	508.80
13 Palmetto, C.																					315								300.30		300.30	18.62	4.35	0.00	9.22	0.21	4.05	40.00	0.00	1.65	222.20	300.30	300.30	300.30	300.30
21 Waters, R.																					316								428.00	112.35	540.35	33.50	7.84	10.00	16.59	0.38	7.29	60.00	0.85	1.65	402.25	540.35	540.35	540.35	540.35
22 Kroll, C.																					317								552.00		552.00	34.22	8.00	43.00	16.95	0.39	7.45	20.00	0.85	1.65	419.49	552.00	552.00	552.00	552.00
31 Ruppert, C.						Mark Sears: Insert stated weekly salary				Mark Sears: Enter as a formula totaling regular and overtime earnings																318								800.00	37.50		837.50	51.93	12.14	44.00	25.71	0.59	11.31	40.00	0.85	1.65	649.32	837.50	837.50	837.50	837.50
32 Scott, W.						Mark Sears: For salaries stated as monthly or yearly, enter a formula converting to a weekly amount					Mark Sears: Enter as a formula of taxable earnings x OASDI rate	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. =(ROUND(Weekly salary/40,2))*1.5				Mark Sears: Enter as a formula of taxable earnings x HI rate																319												780.00			780.00	48.36	11.31	28.00	23.95	0.55	10.53	50.00	0.85	1.65	604.80	780.00	780.00	780.00	780.00
33 Wickman, S.																					320								807.69		807.69	50.08	11.71	87.00	24.80	0.57	10.90	50.00	0.00	1.65	570.98	807.69	807.69	807.69	807.69
51 Foley, L.																					321								1,038.46	194.70	1,233.16	76.46	17.88	83.00	37.86	0.86	16.65	30.00	0.85	1.65	967.95	1,233.16	1,233.16	1,233.16	1,233.16
99 Olney, M.																					322								1,500.00		1,500.00	93.00	21.75	93.10	46.05	1.05	20.25	80.00	0.85	1.65	1,142.30	1,500.00	1,500.00	1,500.00	1,500.00
Totals						Mark Sears: Enter as a formula totaling column		Ros Hill: 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. =(ROUND(Weekly salary/40,2))*1.5							Mark Sears: Enter as a formula of taxable earnings x state tax rate		Mark Sears: Enter as a formula totaling column							Mark Sears: Enter as a formula of taxable earnings x SUTA tax rate		Mark Sears: Enter as a formula totaling column								Mark Sears: Enter as a formula of taxable earnings x city tax rate		Mark Sears: Enter as a formula totaling column		Mark Sears: Enter as a formula totaling column		Ros Hill: $1,500.00-$80.00=$1,420.00 $1,420.00-7($71.15)=$921.95 $921.95-$479.00=$442.95 ($442.95X.15)+$32.70=$99.14		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 of total earnings less the sum of deductions		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 of taxable earnings x OASDI rate	Mark Sears: Enter as a formula of taxable earnings x HI rate	Mark Sears: Enter as a formula totaling column	Mark Sears: Enter as a formula totaling column	6,916.45 Mark Sears: Enter as formula totaling column	483.35 Mark Sears: Enter as formula totaling column	7,399.80 Mark Sears: Enter as formula totaling column	458.80 Mark Sears: Enter as formula totaling column	107.29 Mark Sears: Enter as formula totaling column	464.10 Mark Sears: Enter as formula totaling column	227.19 Mark Sears: Enter as formula totaling column	5.20 Mark Sears: Enter as formula totaling column	99.89 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	5,574.03 Mark Sears: Enter as formula totaling column	7,399.80 Mark Sears: Enter as formula totaling column	7,399.80 Mark Sears: Enter as formula totaling column	7,399.80 Mark Sears: Enter as formula totaling column	7,399.80 Mark Sears: Enter as formula totaling column
	JOURNAL													Taxable		Net
														Earnings	Rate	FUTA Tax
		DATE			DESCRIPTION				DEBIT	CREDIT			Net FUTA
		20--														SUTA Tax
		Jan. 12		Wages and Salaries									SUTA																	7,399.80						Taxable		Net
					FICA Taxes Payable - OASDI																										458.80					Earnings	Rate	FUTA Tax
					FICA Taxes Payable - HI																										107.29					7,399.80	0.006	44.40
					Employees FIT Payable																										464.10							SUTA Tax
					Employees SIT Payable																										227.19					7,399.80	0.036785	272.20
					Employees SUTA Payable																										5.20
					Employees CIT Payable																										99.89
					SIMPLE Deductions Payable																										440.00
					Group Insurance Premiums Collected																										6.80
					Health Insurance Premiums Collected																										16.50
					Salaries Payable																										5,574.03
		Jan. 12		Payroll Taxes																										882.69
					FICA Taxes Payable - OASDI																										458.79
					FICA Taxes Payable - HI																										107.30
					FUTA Taxes Payable																										44.40
					SUTA Taxes Payable																										272.20
		Jan. 14		Salaries Payable																										5,574.03
					Cash																										5,574.03