Deltek Vision and Prevailing Wage Labor Tables – Entry 1: The problem

This is the first of a few or several blog entries giving some insight into:
– how I develop custom solutions for Deltek Vision, as well as
– high-lighting the extreme flexibility of Deltek Vision and custom infocenters, as well as
– letting you know that soon there will be a package solution available to any company using Deltek Vision, that automates the management of labor cost tables for prevailing wage projects.

Vali Cooper and Associates, Inc., headquartered in Emeryville, CA provides construction management and inspection services to public agencies in the State of California.

1. Most of their contracts fall under the “prevailing wage” laws in California.
The prevailing wage laws mandate that employees be paid a minimum amount hourly on certain types of projects depending upon their function and when they work.

2. Prevailing wage labor schedules are issued by the state, sometimes several per year. They have multiple labor categories with pay rates in groups. When a project is won, the prevailing wage rules to abide by depend upon the area of the state the project resides in, and the date the project starts. Thus each project could potentially have it’s own set of rules, but invariably, some projects share the same set.

3. A particular employee can work on any number of these types of contracts and thus have varying pay rates throughout any pay period depending upon which contract they work on.

4. The calculation of the actual pay rates that get paid to each individual employee is a complex process driven by the employee’s actual base pay + benefit elections, as well as what they actually are doing on the project. In Valicooper’s case there are currently only 4 classifications: Sr. Inspector, Inspector, Tester, and Apprentice. (example pictured below)

PW determination issued

5. Because of the nature of prevailing wage, and the nature of the work on prevailing wage contracts we need to capture several things about each time entry. Those things are:
Shift (day or night)
Category (activity code or labor code)
Reg, Ovt, or Double time

The crux of the issue is being able to assign the appropriate labor cost amount to each hour depending upon all the attributes named above as well as the formulas which are based upon the employees’ individual pay rates.

In each case, the payment rates are determined by a formula which includes something called a “fringe” and a “differential”.  The fringe is a sum of benefits calculation, and the differential is there if the employee is paid less than what the determination dictates.. you can imagine it could get complex to manage the cost tables in Vision.

To calculate the formula for an employee’s pay rate on a particular project at a certain time… you use the following elements:
The Prevailing Wage determination or set of rules the project falls under: (or that part of the project)
Employee’s benefit selections or “fringe” rate
Employee’s base pay rate
What the employee is doing on the project (Inspection, Testing etc.)
Which shift (day/night)
Reg, Ovt, Double time multipliers against the base

One thing to keep in mind is when all cost/pay rates for any employee need to be calculated, adjusted, corrected… here are a few cases:

1. employee changes benefit settings, like 401K contribution, health plan, or base pay

2. the prevailing wage determination has a scheduled increase that takes place

3. an employee is hired or fired

4. a new project is won, with a new prevailing wage schedule, thus requiring the creation of a new labor cost table for that project.

So you can imagine a company like Valicooper with about 150 regular project employees and just as many projects… the amount of administrative work needed just to keep the cost tables up to date.

There are however a few great things about this particular challenge:
1. The prevailing wage rules are concrete, documented rules that change slowly if at all over time.

2. Valicooper uses UltiPro for their Human Resources functions, where employee pay settings and benefit elections are stored, and can be imported into Vision daily.

3. Vision is super flexible when it comes to labor cost tables on projects. There are many options.

Thus, this project is a ripe candidate for two things, automating the management of employee cost rates on prevailing wage projects, and developing a solution that other companies with the same problem can use.
Lastly, the fourth great thing about this problem is that I am the one building a solution for it. Thus, the end result will be robust and require little maintenance even when you upgrade Vision. (see my recommendations section for some other customer’s opinions: click here for LinkedIn profile )

As of this date, I’ve made considerable progress, and have built a solution which imports the employee pay settings from UltiPro, calculates the employee base, fringe, reg, ovt and ovt2 rates, and populates per employee rates for each prevailing wage determination.  Up to this point, I’ve used three custom infocenters which store the following data:  UltiPro configuration settings, Employee Pay Settings, and Prevailing Wage rates.

The data is imported into the Employee Pay Settings infocenter, using mappings from the configuration infocenter, and finally populates per employee labor rates for any prevailing wage rules that are relevant. Relevance means that the employee has some relationship to the project that falls under that prevailing wage rule.

The automation also takes into account the complex set of rules that govern dates and employee rates.  There are effective dates for different rates in the prevailing wage determination, as well as the employees’ own effective dates for increases, changes in benefits etc. The automation I built takes all this into account.

The next piece to work on is the set of activity code (called labor codes in Vision) tables and workflows to auto-generate and update them so that projects using prevailing wage will have correct labor cost tables that are maintenance free.

Here are a couple screen shots of what’s been built, tested and implemented so far:

Employee Pay Settings

pay categories

Prevailing Wage Determination

PW Determination

Stay tuned for the next part, automating the labor code labor rate tables that store per employee per project pay rates based upon the prevailing wage rules. This will also include the use of multiple levels of labor codes for employee time sheet entry that make the process easy to understand for employees.

This solution is compatible with Deltek Vision 7.0 and above.

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s