Construction Contract Work Flow/Best Practices

Jennifer Z

I work for a construction firm and would like some guidance in the first steps to creating a "contract health" dashboard. I am managing all contract packages and the documents that supplement them for all of the projects within the company i work for and would like to find consistency and a more "automated" checks and balances for items that may be missed.

here is an example of a workflow i'd like to create:

Bids are all received and entered into a "bid tracking" solution/grid

PM can "send" me a row/rows that are ready for subcontracting

I generate the subcontract package along with all supplemental documents for approval by PM and issue through smartsheet to subcontractor

subcontractor signs and returns all documents, they are uploaded into smartsheet and status is updated

change order generation as it applies to a subcontract and the prime contract as a whole

perhaps a bit of lien waiver management, invoicing management etc.

i've been trying to work through the contract management template set and am finding it difficult.

Brent Wilson

@Jennifer Z you are probably going to want to have a few "sheets"

I did it with a Parent and Child kind of arrangement

You could have for example a Subcontractor Sheet and then it can use a VLOOKUP to pull data from your Project Master Sheet

This way there is a workflow on the Master Sheet say when the PM click Approved for SubContractor.. You could even use the Copy row to have it create a row in the SubContractor Sheet

You can also create a separate Change Order Sheet and Tie it to the Project Master Sheet and the same as invoice masters etc..

Have a look at this

https://community.smartsheet.com/discussion/73950/budget-management

Feel free to reach out if you have questions

Jennifer Z

bcwilson.ca wow this is such great information! so on your Budget example - the parent/child rows that are dedicated to your individual cost codes (2.1, 2.2 etc) do you have each of those codes then assigned to a subcontractor or vendor as well? I'd love to present something like this where you can pull up say a vendor name and see each division/cost code assigned to that vendor for that job, OR pull up a cost code and see which vendors are applied to that cost code for that job. essentially creating a schedule of values so everyone knows where to code incoming costs.

this is very far in the future but i could see your example leading this way - do you them utilize these sheets/examples to create an invoice/draw/billing to your client?

seeing this in a real world example is exactly what i needed - i hope i get more responders!

Jennifer Z

@bcwilson.ca here is what i have so far:

Grid #1 - Contract health. i have a separate contract health grid for each job with its corresponding subcontracts, all of the documents that supplement that contract package and a status option. I've then created a report that pulls ALL of these grids for all sheets into one location. so far i like the looks of this. what I'd like to see happen with this is somehow communicating from the bid tracking grid, into this grid for an easy handoff from Project Manager/Bid Phase to Contract Generation/Administration phase. would also love to issue contract packages at this stage for approvals to Owner as well as signatures from Subcontractors.

Grid #2 - Change Orders. i haven't created this yet but my goal is to create a grid that tracks all change orders based on cost code, how it applies to the budget and/or subcontract, if its approved by Owner, maybe even have a change order form that i can issue from Smart Sheet for approvals/signatures.

Grid #3 - Draw Status - I currently have a very basic grid that tracks a draw sent to client/lender, amount, status etc.

Grid #4 - Invoicing and Lien Waivers - i want to create a basic WIP(work it process) that allows me to track all costs and apply them to cost codes and contracts, the lien waivers that pertain to those invoices and their status, an over/under notice, paid status, etc.

this seems like a mountain of things to track at the moment.

Brent Wilson

@Jennifer Z

The Cost codes are MasterFormat codes for the breakdown of the budget.

https://en.wikipedia.org/wiki/MasterFormat

A lot of My linking is using VLOOKUPS

So if you look at my invoice file Under Budget Line Vendor "Superior Security" submitted an invoice to me and then I coded it to the "Security - Physical Locks"

My Budget sheet then has a VLOOKUP to look in the invoice file (Your Grid#4) and total all values that are posted to the same description under the Master Format

This allows a single vendor to bill against multiple MasterFormat lines.. Physical locks I had under one item but Card Readers were in my budget outside of construction but in FF&E

Now if I wanted to see the Vendors that billed under the MasterFormat is when I would use a report and join both files and since both Files had the same "Budget Lines" then I would see "Superior Security" Invoicing me both under Physical Locks and Card Readers

It is a bit of work making sure the MasterFormat descriptions match the descriptions in the Invoice File..

Brent Wilson

@Jennifer Z

If you look at my budget sheet under A/V

You see we approved a budget for $35,000 for Audio Visual on the Project

I have a Change Order/Purchase Order Note: They resided in the same file. I thought of them as future commitments for fund allocation (planned or otherwise) This might be your Grid #2

But if you look again at the A/V line you can see I have committed $3243.24 in this case as a PO and have already paid $11,946.00 in invoices. Then you can see that I have a remaining $19,810.76 against this budget line.

There was a bit of a manual process when my Vendor invoiced me the $3243.24 to take it from the "Purchase Order" File

A little Trick

I issued the Request ID in the Change Order/Purchase Order File to the vendor/subcontractor as a PO and said it must be on the Invoice. Then I had a VLOOKUP on the Change Order/Purchase Order File to see if it found that PO in the Invoice file and total it. You can see it in the INVOICE SYSTEM COLUMN that it pulls the invoiced amount back to the "PO"

I then had to do a bit of a manual reconciliation to make sure that it was reporting correctly (hence the Has been invoiced complete columns, etc.)

If you look at the first two lines you will see that those are uncommitted change orders. They were quoted but never approved. Kinda like PO never issued..

If you look a bit further down you can see another Change Order that was for exterior wall pack replacement that was realised and also came in a bit over the estimate.

I code these to contingency so they report against my contingency in the Budget. In some cases, I did create a line under the MasterFormat so we could see more detail. In this case, if you look at the Wall Pack it went under Contingency - Lighting

These could be work flowed through for approval as well using workflow rules

Brent Wilson

@Jennifer Z

Finally, another trick I did is I created a separate Form for Each Vendor in the Invoice File and sent the link to their Accounts receivable. I would pre-fill in the vendor names and some other codes depending on the vendor.

Then the subcontractor accounting department would open the form and upload it (along with the PDF invoice and supporting documentation) and populate data for me.

It then came to me where I would then code it with the Master Format date (link to my budget and where I wanted it to come from). I was also required to put a wet signature on it which I could probably do now with proofing tools or something

Then it could workflow for approval and then it was sent to my accounting for payment processing.

Slick and Auditable.

I even created a field and worked with accounting so they could put the "DrawID" against the invoice so they could compile the draws to the lenders.

This way we knew that the "Exterior wall pack Change Order" Went in the Draw3 package to the bank for payment