Tuesday, September 27, 2011

The Project Invoice Log

Time & Materials (T&M) projects can produce a lot of paperwork! As opposed to measuring the percentage of completion of an agreed upon sum, the Contractor is basically providing receipts for reimbursement. These receipts can take a lot of time to qualify and review... How do you make sure that you are not paying for the same receipt twice? How do you track which invoices go into which AFP? How do you verify that your calculated total matches the Contractor's The Project Invoice Log is a great way to keep an organized system of arranging materials invoices submitted by the Contractor. Here is how I use it:

Discuss the process in the project manual. Make sure that the contract documents discuss the process . I have created a "T&M Processes" Section in Division 01 29 00 - Payment Procedures.

Review the process in your preconstruction meeting so that all project participants are familiar with it.

Do a dummy draw if you need to. Have the Contractor take a dozen of his old invoices and do dry run.

Each project Invoice is assigned a unique Project Invoice Number. This number is written on the top of an invoice as shown below. It is another way of preventing double-billing.

With each pay application, the Contractor submits an updated Project Invoice Log. My preferred method is via Google Docs. This allows all parties to see the document updated in real time from any location. Distribution of the document is no longer necessary.

The log has at least 6 columns that are to be filled out for each invoice:
  • Project Invoice Number (the red #)
  • Project AFP - Notes in which AFP the invoice is located
  • Vendor
  • Vendor Invoice Date - will be useful to identifying double billings
  • Amount - should be obvious
Use Excel's/Google's sort & filter tools to indentify the invoices for the most recent AFP. You can now confirm totals.

View all invoices to date and use Conditional Formatting to identify duplicated Amounts or Vendor Invoice #s. This should find 99.9% of double-billings. In the image below, you can see that the conditional formatting has identified that the same invoice was included in both AFP #3 and #4.

There it is! The Project Invoice Log is a great way to keep track of the multitude of invoices on a T&M project. If you have a big project, be careful and be ready to have thousands of line items to sort through...

Advanced Reading:
  • For the Project Invoice # column, I would recommend using the  =A1+1 trick to add 1 tot he row above. From here, I would selectteh column and Pase Special -- Values. This will ensure taht Invoice numbers do not ge rearranged as you sort and filter.
  • Advanced Excel users can use VLOOKUP and HLOOKUP to create executive summaries of AFP totals. I have only tinkered with this and have not had time to perfect it.