LCPtracker Free Excel Template Video Guide

Modified on Mon, 26 Jan at 1:14 PM

This guide will assist with utilizing the free Excel template we offer.

Legend

	Minimum Required - we recommend you complete as many fields as possible.
	REQUIRED in CA, other states may required
	Information for hours worked (value could be zero)
	Useful for multiple projects in one upload
	Not required for upload but may be required by contracting agency. You may wish to provide to avoid manual corrections
	Total Fringe Benefit & Voluntary Contribution Amounts – Enter if Applicable (Note: Only Hourly OR Total is necessary - not both)
	Hourly Fringe Benefit & Voluntary Contribution Amounts - Enter if Applicable (Note: Only Hourly OR Total is necessary - not both)

LCPtracker 'Payroll Entry' Form or 'Employee Setup' Label	Description of Data	Column ID	Column Header Name	DESCRIPTION	DATA TYPE	LENGTH
		A	payroll_number	DO NOT USE	Number	12
Project	If you upload multiple projects you need to use the LCPtracker project code. Use the project match feature in SETUP to match your project code to LCPtracker project code.	B	project_code	Project Code (from your accounting system) Multiple project payrolls can be entered. One project_code per row. All projects must be for the same account owner. Use the project match feature in SETUP to match your project code to LCPtracker project code. Please view under Training Materials, scroll to Contractor Support Documents and view CRAFT MATCHING (Required Function to use with Data Upload).	Text up to 50 characters	50
Contract ID	This is the contract ID between contractor and subcontractor. This number may appear under the Projects navigation tab to the right of the project name.	C	contract_id	Contract ID is a future capability, this is the contract ID between the You and the contractor you are a sub to (or between the account owner and the prime contractor) This value will be required in the future when a contractor has multiple subcontracts on the same project.	Text up to 50 characters	50
Work Order ID	This is a work order ID for work within a contract.	D	work_order	This will apply only to selected situations. Work orders are created by Prime or Administrator. You may view by creating 1 manual record and viewing the Work Order drop down, or contact your Prime or Administrator for this information.	Text up to 50 characters	50
Week End Date	Enter Week End Date of your payroll week. This must be a 7 day period, example ONLY Monday to Sunday is Sunday week-end-date; Friday to Thursday is Thursday week-end-date.	E	week_end_date	Week end date - several week end dates may be entered. If uploading several weeks at one time, do not pick a date using calendar icon under the 1.Payroll Records; Upload Records screen	Date	12
Check Number	Enter the check number.	F	check_num	Check number or value to indicate direct deposit	Apha numeric	12
SSN (include dashes)	Format 999-99-9999. The account owner may require the full ssn (999-99-9999) , the last 4-digits (xxx-xx-9999) or no ssn and use of employee id.	G	ssn	Social Security Number of employee - this is used to match the data in the employee setup table or to upload the employee initially. Employee_id is used in some locations. Format of 000-000-1234 is also accepted. Recommended to always have the value here. Some account owners require SSN and some do not. Entering one employee manually, upon saving will show required format. Each account owner can have different requirements.	SSN (including dashes)	11
Employee ID#	Text -May be required in addition, or in lieu of the ssn field.	H	employee_id	This is an alternative employee ID to SSN. Some account owners may require or use of instead of SSN. Best practice is to provide both if possible. If you do not have employee ids, you need to create an internal employee id structure. It should not look like or be the ssn.
contractor craft code	If not specified the system defaults to Not Available.	I	class_code	This is the contractor accounting system code for the workers craft/classification. This code must be mapped in LCPtracker to a Prevailing Wage Craft Code. Please view under Training Materials, scroll to Contractor Support Documents and view CRAFT MATCHING (Required Function to use with Data Upload). Account owners will not allow certification without the proper craft/classification being listed.	Alphanumeric 12 characters long	12
Gross Employee Pay This Project (Usually No Fringes)	Hourly rates of pay (regular, overtime or double time) multiplied by the appropriate hours for this project for this week only, not to include fringe benefits.	J	gross_employee_pay	All amounts paid on this project for this week - if taxable fringes are included indicate on the employee setup and include that as part of Basic Hourly Rate. If more the one check is paid - include the sum of the checks.	Decimal	16
Gross Pay All Projects	Enter the gross pay on the pay check for this week. If the employee was paid with multiple checks, then the appropriate amounts on the checks should be added together and entered here. (Only for time worked this week)	K	all_projects	This is the total Gross Pay for all work performed for the week regardless of where the employee worked, and paid on this check before pretax or taxes deducted out.	Decimal	16
Wages Paid in Lieu of Fringes (Total Cash Fringes)	Some agencies want the amount paid to the employee in lieu of fringes reported separately. Some do not allow this value.	L	wages_paid_in_lieu_of_fringes	Wages paid in lieu of fringes - this is the amount paid additionally to workers to meet the Total Hourly Rate required when you do not pay full fringe benefits, (Typically to third party/union).	Decimal	16
Paycheck Amount	Net pay that the employee takes home after pre-tax and taxes are deducted.	M	total_paid	Total paid - take home pay from all checks NOTE if employee is paid multiple checks for the week then all hours worked and all checks paid need to be combined for each craft - (future - we are working to remove this requirement)	Decimal	16
		N	st_hrs_date1	Regular hours worked on day 1	Decimal	16
		O	st_hrs_date2	Regular hours worked on day 2	Decimal	16
		P	st_hrs_date3	Regular hours worked on day 3	Decimal	16
		Q	st_hrs_date4	Regular hours worked on day 4	Decimal	16
		R	st_hrs_date5	Regular hours worked on day 5	Decimal	16
		S	st_hrs_date6	Regular hours worked on day 6	Decimal	16
		T	st_hrs_date7	Regular hours worked on day 7	Decimal	16
		U	ov_hrs_date1	Overtime hours worked on day 1 at 1.5 times	Decimal	16
		V	ov_hrs_date2	Overtime hours worked on day 2 at 1.5 times	Decimal	16
		W	ov_hrs_date3	Overtime hours worked on day 3 at 1.5 times	Decimal	16
		X	ov_hrs_date4	Overtime hours worked on day 4 at 1.5 times	Decimal	16
		Y	ov_hrs_date5	Overtime hours worked on day 5 at 1.5 times	Decimal	16
		Z	ov_hrs_date6	Overtime hours worked on day 6 at 1.5 times	Decimal	16
		AA	ov_hrs_date7	Overtime hours worked on day 7 at 1.5 times	Decimal	16
		AB	ov_hrsx2_date1	Overtime hours worked on day 1 at double time	Decimal	16
		AC	ov_hrsx2_date2	Overtime hours worked on day 2 at double time	Decimal	16
		AD	ov_hrsx2_date3	Overtime hours worked on day 3 at double time	Decimal	16
		AE	ov_hrsx2_date4	Overtime hours worked on day 4 at double time	Decimal	16
		AF	ov_hrsx2_date5	Overtime hours worked on day 5 at double time	Decimal	16
		AG	ov_hrsx2_date6	Overtime hours worked on day 6 at double time	Decimal	16
		AH	ov_hrsx2_date7	Overtime hours worked on day 7 at double time	Decimal	16
Total Hours All Projects	Enter ALL hours worked for week regardless of where employee worked. Note - this field is optional and Administrator may not choose to use/show.	AI	Total_Hours_All_Projects	All hours worked for the week, regardless of all this project or not. This includes regular, overtime and doubletime all together. This field may not show, if available may or may not be a required field.	Decimal	16
Health & Welf.	Total employer payment for fringe (hourly fringe value multiplied by hours worked on this project) - Heath & Welf. For the week. Do not include voluntary contributions.	AJ	ep_haw	Employer payments – Health and welfare - value may be zero	Decimal	16
Pension	Total employee payment for fringe (hourly fringe value multiplied by hours worked on this project) - Pension. Do not include voluntary contributions.	AK	ep_pension	Employer payments – Pension - value may be zero	Decimal	16
Vac/Hol/Dues	Total employer payment for fringe (hourly fringe value multiplied by hours worked on this project) - vac/hol/dues payment for hours work on this project.	AL	ep_vac_hol	Employer Taxable fringe benefit payments – vacation / holiday - / dues do not double count vac/hol/dues if it is include in Gross Pay This Project - value may be zero	Decimal	16
Training	Total employer payment for fringe (hourly fringe value multiplied by hours worked on this project) - Training is almost always required and almost never allowed to be paid to the employee.	AM	ep_train	Employer payments – training - in rare cases the value may be zero. Required in CA only - other states may require	Decimal	16
All Other	Total employer payment for any other fringes not already categorized (hourly fringe values multiplied by hours worked on this project).	AN	ep_all_other	Employer Payments - All Other Payments - use this field for miscellaneous amounts	Decimal	16
Voluntary Contributions for all Projects - Pension	Total dollar amount of Pension voluntarily contributed by the employee (for hours worked on this project for this week).	AO	vol_cont_pension	Employee voluntary pension payments if not included in the gross_employee_pay amount	Decimal	16
Voluntary Contributions for all Projects - Medical	Total dollar amount of Medical voluntarily contributed by the employee (for hours worked on this project for this week).	AP	vol_emp_pay_med	Employee voluntary medical insurance or medical plan payments if not included in the gross_employee_pay amount	Decimal	16
Fed Taxes	Enter federal taxes deducted	AQ	dts_fed_tax	Federal withholding taxes	Decimal	16
Social Security	Enter social security amounts deducted	AR	dts_fica	Social Security Payment	Decimal	16
Medicare	Enter Medicare amounts deducted. If this is combined with Social Security it is accepted.	AS	dts_medicare	Medicare this can be combined in reporting with DTS_FICA	Decimal	16
State Tax	Enter state taxes deducted.	AT	dts_state_tax	State tax withholding	Decimal	16
Local Taxes/SDI	Enter state disability insurance payments or other such state deductions.	AU	dts_sdi	State Disability Insurance or other local tax payments	Decimal	16
Vac/Dues	Enter union dues and vacation deduction.	AV	dts_dues	Union dues and vacation	Decimal	16
Savings	Enter savings amounts. Account Owners may want detailed explanation of this amount.	AW	dts_savings	Employee savings not voluntary pension	Decimal	16
Other	Enter other deductions such as child support payments. Account Owners may want detailed explanation of this amount.	AX	dts_other	Other deductions not entered above - use this field carefully as explanation may be requested by the Labor Compliance Officer	Decimal	16
Total Deductions	Enter total deductions	AY	dts_total	Total deductions - taxes and other deductions	Decimal	16
Notes	Enter any notes desired. This feature is most often used to record additional check numbers if the employee was paid by multiple checks.	BD	prnotes	Notes for this payroll record	Text up to 500 characters	500
Travel/Subs	Enter any travel subsistence payment. This amount should be included in the “Gross Pay All Projects”.	AZ	trav_subs	Traveling / subsistence payments if traveling subsistence payments are not included in all_projects amount	Decimal	16
Payment Date	Enter date employee receives check.	BE	Payment_Date	Date in which the employee receives the paycheck. This may be set as a required field.	Date
Base Hourly (Blue Field)	This is the Hourly Rate of pay, NOT to include any fringe benefits or Rate in Lieu of fringe.	BA	pay_rate	Basic Hourly Rate - best to supply, most Account Owners require	Decimal	16
Overtime Hourly (Blue Field)	This is the Hourly Overtime Rate of pay, NOT to include any fringe benefits or Rate in Lieu of fringe. Usually equal to the Base Rate multiplied by 1.5	BB	OT_rate		Decimal	16
Doubletime Hourly (Blue Field)	This is the Hourly Doubletime Rate of pay, NOT to include any fringe benefits or Rate in Lieu of fringe. Usually equal to the Base Rate multiplied by 2.0	BC	2OT_rate	Doubletime Hourly Rate - best to supply - most Account Owners will require when Doubletime is worked.	Decimal	16
Columns BF to CT are specifically for creating the Employees under the Add/Edit Employee Setup screen. Uploading this information will create an employee if not already in the system as well as update employees already in the system. Please be sure that if you are updating an employees information that you have the SSN and/or employee_ID exact as previously entered/uploaded; column G (SSN) or H (employee_ID). Note that blank fields do not update - zero is a valid value and any field with a zero will be update to zero. Column CX is an additional field that may appear on payroll record entry form, if your Administrator requires. Columns CU to CW are specifically for those working in the State of New York and Column .

First Name	First name of employee	BF	first_name	Required	text
Last Name	Last name of employee	BG	last_name	Required	text
Address1	Employee address 1	BH	address1	Required	text
Address2	Employee address 2	BI	address2		text
City	Employee city	BJ	city	Required	text
State	Employee state	BK	state	Required
ZIP	Employee ZIP	BL	ZIP	use 5 digit ZIP
Phone	Employee Phone - required some places	BM	phone	10 digit phone number xxx xxx xxxxx	text	20
Gender	employee gender	BN	gender	M / F required some places
Ethnicity	employee ethnicity	BO	ethnicity	Not Specified, Caucasian, African American, Asian, Hispanic, Native American, Other see list in LCPtracker. Could vary by Account Owner. May be required field.
Apprentice_ID	Apprentice ID may be blank - should be filled in if employee is apprentice - in the future this may be a required field in the employee setup for apprentices	BP	apprentice_id	State apprentice ID code required by some Account Owners - best to supply	text
Craft ID	State Electrician ID number maybe blank	BQ	craft_id	Used in CA for electricians, other States may have and some Account Owners may require.
Vac_hol_dues_rate	Hourly Rate of fringe. This may or may not be a taxable value.	BR	vac_hol_dues_rate	For further explanation see the Training Materials link, scroll to the Contractors Support Documents and view Vacation/Holiday/Dues document
Health & Welf.	Hourly Rate paid for medical etc. benefits. Does not include voluntary benefits	BS	emp_ep_haw	Hourly value of payments to health plans
Pension	Hourly Rate paid for pension funds. Does not include voluntary payments	BT	emp_ep_pension	Hourly value of pension, annuity, retirement plan rates
All Other	Hourly Rate paid for all other funds. Does not include voluntary benefits	BU	emp_ep_other	All other Hourly values not in other categorized fields - use this field for miscellaneous
Training_rate	Hourly Rate paid for training funds	BV	training_rate	Hourly value and may be requirement under some states
Vol_cont_pension_rate	Hourly Rate paid as voluntary contribution to pension funds	BW	vol_cont_pension_rate	Part of basic hourly wage. This is an hourly value that the employee elects to have taken out for voluntary contributions to pension.
Vol_cont_medical_rate	Hourly Rate paid as voluntary contribution to medical ins.	BX	vol_cont_medical_rate	Part of basic hourly wage. This is an hourly value that the employee elects to have taken out for voluntary contributions to medical.
In_lieu_payment_rate	Hourly Rate paid for in lieu of fringes benefits - This field will populate on the payroll record entry form.	BY	in_lieu_payment_rate	This is an amount paid to the employee in lieu of or in addition to fringe benefits in order to meet the required total hourly package. This is an hourly value
Work_County	The county in which the work was done	CD	work_county	Text - To use this feature, please make sure to do county matching under the Set Up tab>Add/Remove County match
Vac_chk_box	This box is checked if the vac/hol/dues amount is included in the Employee Gross Pay this Project - default is unchecked	BZ	vac_chk_box	Y/N	boolean
Fringes paid check box	This box is checked if the some or all of the fringe benefits are paid in cash. The paid in lieu field may or may not have a value. Default is unchecked	CA	fringe_paid_chk_box	Y/N	boolean
Date Hired	Date employee hired	CB	date_hired		date
Employee Status	Active/Inactive	CC	emp_status	Employee status by setting active or inactive you can switch the employee status. Default is active
IsForeman	This box is checked if the employee worked as a foreman for this payroll record.	CE	IsForeman	Y/N	boolean
IsDisadvantaged	This box is checked if the employee is disadvantaged.	CF	IsDisadvantaged	Y/N	boolean
VeteranStatus	Enter veteran status of employee	CG	VeteranStatus	Valid values are: Not a Veteran, Veteran, and Service Disabled Veteran.	Text
OtherDeductionNotes	Add notes related to other deductions when applicable.	CH	OtherDeductionNotes	Notes up to 500 characters	Text	500
num_exempt	Enter number of exemptions	CI	num_exempt	Up to 2 (two) characters	Text
DriversLicense	Employee's driver license number	CJ	DriversLicense	Up to 50 characters	Text
DriversLicenseState	Employee's driver license state	CK	DriversLicenseState	Up to 2 (two) characters	Text
Owner/Operator	This box is checked if the employee is an Owner/Operator.	CL	Owner/Operator	Y/N	boolean
I9 Verification	This box is checked if the employee has been I9 verified.	CM	I9Verification	Y/N	boolean
Geographic Ward	Some Account Owners may request / require this information. May be entered manually after upload. User should see employee setup for available titles and use copy/paste function	CN	Geographic_Ward	Typed text must match identically to what Agency has loaded, system will not populate. See your Set Up>Add/Edit Employee screen for information or contact your Prime or Labor Compliance Administrator	Text
Geographic Area		CO	Geographic_Area
Congressional District		CP	Congressional_District
State_Senate District		CQ	State_Senate_District
Other Deduction Categories	The OD_Category can be set up by your Administrator, and may be unique from one account to another. Please view Training Materials, scroll to Contractor Support documentation and view document title, 'Other Deductions' for more details.	CR	OD_Category	You may wish to log in, view Set Up then Add/Edit Employee to see what the Administrator, if anything, has created.	Apha numeric	50
Other Deduction Types	Itemized List of Other Deductions	CS	OD_Type	Each Other Deduction type can be listed from your accounting system. Please enter multiple items in pipe delimited format (i.e. Delta Dental \| vision insurance \| child support)	Alpha numeric	50
Other Deduction Amounts	Amounts for each of the Other Deductions	CT	OD_Amount	Each Other Deduction amount can be listed from your accounting system. Please enter amounts in pipe delimited format (i.e. 14.75 \| 10.25 \| 200.65)	Decimal	50
Fringes Paid/Provided to Employee by Employer	This box is checked if the Fringes Paid/Provided to Employee by Employer - Specifically used for Payrolls in the State of New York	CU	FringesProvidedByEmployer	Y/N	boolean
Fringes Paid to Union? Local/Union #	Enter Local/Union #, if fringes are Paid to a Union - Specifically used for Payrolls in the State of New York	CV	FringesPaidToUnion	Entering the Local Union Number will populate the Local/Union # as well as check the box stating that 'Fringes Paid to Union?' - Specifically used for Payrolls in the State of New York	Alpha numeric	12
YTD Sick Pay Time	Enter accumulative hours employee has earned towards sick time.	CW	YTD_SickPayTime	Enter the year to date sick pay hours an employee has accumulated. (Format is ###.## accepts up to 999.99)	Decimal	6
Email Address	Employee's email address	CX	Email	Enter the employee's current email address	Alpha numeric	320