Deltek Vision: How to calculate WIP (work in process)

WIP is defined as the billable value of work you haven’t billed for yet.  Some companies will include this in their revenue calculations. Vision even has a feature where you can have it “generate revenue” based on this number.

This is great, but what if you are operating under the following conditions?

1. 95% of your projects are fixed fee projects that last several weeks or months
2. Presently you don’t have a project management tool that gathers percent complete data enterprise wide, thus Vision doesn’t know about percent complete on any particular project.
3. Since you don’t bill by the hour much, the possibility of calculating WIP by looking at the “billing value” of your unbilled labor is well… useless (also because you never know how one project will perform as compared to another when it comes to hours vs. estimated hours or actual labor billing vs. estimated labor billing).

Well you can find me, Loren Saunders, to come up with a round about way of calculating your WIP based on some basic assumptions which are as follows:

1. Projects on average will “spend” hours at the same rate as compared to % complete on that project (remember, we’re talking about the life of the entire project here. It’s understood that the spend rate will vary throughout the life… but in general we’re going to homogenize the rate for simplicity).
2. Your project managers report actual % complete on the project, and that’s what’s being billed for when invoices are produced. If your % billed vs. % complete is completely out of whack then this formula won’t work.
3. Your billing terms and Compensation amount field in projects are in synch. Meaning, for any given part of your project, the amount shown in “Compensation” is the sum of the fees in billing terms.

To calculate the WIP for fixed fee projects under these kinds of conditions you can use the following formula:

Find your % billed:
% Billed = Total Billed / Total Compensation

Come up with an EAC hours from % billed:
EAC Hours = 1 / % billed * Total Hours Billed

Figure out what the unbilled hours represent as compared to EAC Hours:
WIP Hours % = Unbilled Hours / EAC Hours

Multiply WIP Hours % by Total Compensation:
WIP Value = WIP Hours % * Total Compensation

There you have it. It’s rough, but it’s an OK estimation when you’re dealing with a limited information environment.

If you would like a SQL script that will do this for you, or perhaps a report to get this, get in touch. I’d love to hear from you.

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