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.

SCG – Custom Tools for Deltek Vision – in beta

What is the “Reversal Tool” built by Saunders Consulting Group for Deltek Vision?  (versions 7.x)

Good morning!

I just wanted to let you know about another little gem that has come out of a pretty extensive Vision stabilization project I’ve been working on. This particular firm had a couple years’ worth of accounting transactions they needed to correct because of incorrect entries, use of general ledger accounts or other issues.  Since the idea of un-posting and re-posting all these entries (in order to re-class items) or even generating re-classing entries for each file is rather daunting… I developed a very useful tool to help us through the process. It’s called at the moment the “reversal tool”.

What does it do? It allows you to:
1. See a list of posted files for any period. (currently only journal entries or cash disbursements)
2. Choose from the list which ones you want to “reverse”
3. Click a workflow button and autogenerate reversing files (journal entries or cash disbursements) for those posted files.

Why is this useful?

Rather than just unposting files (which has its own merit) sometimes it’s better to leave a transaction trail of “what happened” and add additional entries to correct errors that were made.  This is the whole idea behind journal entries in the first place. Thus, the reversal tool allows you to “reverse” any Deltek Vision standard journal entry or cash disbursement you’ve posted, by generating a matching file which reverses the debits and credits to the same accounts. This allows you to leave the original file there in case you need to refer back to it.

Some of the cool features:
1. You don’t have to remember any file name, period or posting sequence… it provides you with a list of what’s been posted

2. It re-creates the file if the file was not archived… (What?  It re-creates the file?  Yes… it does.) This means it works for recurring transactions where Vision doesn’t actually store each file. (annoying)

3. It’s easy peazy, and of course, has a good log to show you what you’ve already done.

Interested in providing this power tool to your accounting department using Deltek Vision?

Get in touch.  I’d love to hear from you.  See screen shot below to see an example in action… as always names have been blocked out to protect the guilty.

Looking forward to hearing from you soon!

SCG_reversal_tool

SCG Custom ASJC Process: (Adjust Salary Job Cost) – even better

Whenever I’m trying to build on an idea, develop a product or service, or help someone identify the key points about their business that makes them stand out, I aim for finding the 3 strongest attributes.

In the case of the custom ASJC tool I developed, here are the 3 things that make it great:

1. You can run the process for any prior period you need to, and at any point in the processing cycle after timesheets are posted, and it will be correct.
This also means you can correct job cost for prior periods without having to unpost, modify, repost timesheets.  The reason it works this way is because unlike Deltek Vision’s standard ASJC process, our custom process relies on a historical salary grid to get its cost data.

2. You can UNDO it.
This is NOT at all possible with Vision’s standard ASJC process.  The SCG Custom ASJC process does not alter the actual LD (labor data) table like the Vision standard one does.  Instead, our process uses labor adjustments, which can be unposted, deleted, re-written or even modified at any point.

3. It’s easy to use and has a great informative log.
The log (pictured below) shows which employee is getting the cost adjustment, which week it’s for, the assigned cost (initially from time on projects), and the amount of adjustment for that week.  It also shows the file name this happened in; in case you want to crack it open and take a peek.  This is all shown BEFORE the labor adjustments are posted (and after) so you can check for accuracy if you need to.

One other thing… you can tell it which day of the week you consider as your start day, and it handles the rest.

It’s awesome. Contact me for details if you would like to have this installed on your Vision implementation.

Capture

SCG’s Custom Adjust Salaried Job Cost log

Custom ASJC Process completed – Link to “how to” documentation

Today I completed the first implementation of my custom ASJC process, which uses Labor Adjustments to re-align labor costs with actual payroll costs in Vision. This custom process is very robust, and can be un-done and re-processed at any time, even for periods way back in time in previous years.

If you would like to read the documentation I drafted today which explains how it works you can find that here:

Custom ASJC documentation

https://docs.google.com/document/d/1h_iFD6fX_Z_6LpH2U_1xaqw9FeiVnK4_asYDJJdwpBk/edit?usp=sharing

If you would like this implemented on your Vision implementation for your organization please contact me.

Loren… what are you doing? Why haven’t you been writing more blog entries lately?

Happy Holidays!

I have been a bit remiss with this blog. I apologize. My excuse is being busy with the following projects:

CALIFORNIA:
1. Automating labor rates in Deltek Vision with an outside platform being the system of record. This includes automation of prevailing wage rate schedules, dealing with differentials and changing benefits elections. I will be using lots of workflows and webservices to pull in employee pay rates, benefits selections and related data to reside in Vision.  Then more workflows to automate maintenance of labor cost tables based upon employee selections of day/night shift, labor category (staff type) and reg, ovt, or double time.   I will be making extensive use of Vision’s new custom infocenter features.

This project should be completed some time in the spring.  If you’re suffering from prevailing wage rates and schedules maintenance in your own organization let me know and maybe I can help.
NEW YORK:
2. A client has been using Deltek Vision for a few years but they do not feel they are getting everything out of it that they can.  Thus, this is kind of a re-implementation project.  They previously converted from Advantage and need help making the conceptual and practical transition from a pure accounting system to a project accounting system.  They have yet to implement timesheets, expense reports and workable methods for project Work Breakdown Structure.

Both of these projects require a lot of work just scoping and defining what needs to be done. It’s very challenging!

It’s much more challenging than just building a custom report or a small workflow process with a relatively narrow set of requirements and small set of users. This is essentially taking a set of ad-hoc defined business process, and designing a proper automated solution to manage the operational data of that process (or those processes). This normally requires the integration of existing applications or solutions, importing or incorporating historical data, transforming historical data when data architecture corrections are made, and designing new processes, policies and procedures for using the new tools we put in place.

Lastly, training and documentation will be required to make sure that the processes and tools put in place actually get used!

Luckily most of the work can be done off site, thus keeping down travel costs and allowing me to work in relative isolation so I can concentrate!

If you’re ever in down town Austin, TX give me a call and I can show you my office and some cool stuff!

 

 

Custom Infocenter + Custom Invoice = Good invoice expense groupings

Recently I had a client making a lot of requests about how to group expenses on their custom invoice.

The requests looked something like this…

“Can you sum up all Per Diem expenses using these accounts… 5010, 5050, 7050”

“Can you sum up mileage expenses using these accounts… 5025, 5033”

“Lodging expenses use these accounts, they need to be summed up as well:
lodging 5027
Meals 5028
Lodging/Meals combined 5030”

Custom Invoices in Vision are just another custom report. They are one of the more difficult reports to create b/c of the complexity of the invoice. An invoice in Vision actually is comprised of 25 or so sub reports all on one master report. Each sub report has its own underlying SQL statement, formatting, etc… That’s why customizing the invoice is so expensive!

In any case, here is what a grouped/summarized piece of the invoice looks like when it comes out. Notice it’s grouped by employee, then also by expense category (so this section probably represents a week or two of transactions… which would take up a lot of space on an invoice if not grouped).

expense_group_example

Keep in mind it’s easy enough to create sql statements to group on particular account number values in an invoice… once you’ve mastered creating custom invoices in Vision 7 that is.

However, what happens when you want to add more categories or an account gets changed? If you have the account values hard coded into the statements, then every time an account number changes or one gets added, or even if the client wants to add a grouping category, it’s going to cost them money to update the report. While some consultants may enjoy that type of relationship with their clients, I prefer to build things that last.

Thus, instead of putting actual account values in sql statements or formatting the report around groups that would most likely change, I did it using a custom infocenter. That infocenter looks like this:

expense_group_infocenter

Here’s how it all works:

1. When clicking on the “Magic Button” a stored procedures goes and gets all accounts from any transaction that has appeared on an invoice and populates the “expense groups” grid with those accounts.

2. The user assigns any or all account to an “Invoice Group” by selecting from a drop down.

3. The user then identifies the type of unit that will be used when describing that group… like “Miles” for a mileage account or “Days” for a per diem account.

4. They click the “Magic Button” again which populates the “expense sort” grid on the right, where they can put the order in which they want those expense groups to appear on the invoice for that particular employee. The “Expense Sort” grid on the right will only populate with actual expense group categories used in the grid on the left.

5. Clicking on the “Magic Button” at any time will update both grids with any new accounts used in expense section on invoices, as well as update the Expense Sort grid with any new categories created or assigned.

6. Any expense item that gets billed to an invoice will show up in this matter.

Now… you might be saying to yourself “why didn’t they just use expense categories for all this?” Well, what if the expense categories you use internally are different from what your client wants to see on invoices? Or what if you have different requirements for different clients?

This allows you to set up multiple expense category configurations and assign that set of configurations to any project… that’s the last part:

7. Assign this template for invoice expense grouping to any project, and the invoices will use that grouping.

Feel free to comment with questions or contact me directly.

Thanks for stopping by!