Learning In Progress #5: Instant overview of contractor and budget information!

Here’s a fun one: How do you keep track of a team of external artists?

First, define what needs to be tracked:

  1. Who’s working for me?
  2. What do they do?
  3. Are they active?
  4. How much are they being paid?
  5. What contracts are they working on?
  6. What date was that contract started?
  7. Has the contract been signed?
  8. Have they finished the work?
  9. Have they invoiced for the work?
  10. Have they been paid for the work?
  11. How much money am I spending?

This is a dizzying amount of information to keep track of, on top of simply managing everything they do at a low level. So what do I do? Click the jump to read more! I’m not very experienced with Microsoft Project, so I made my own little homebrew solution using an Excel spreadsheet! Click on the image below to see it:

This is how I break it all down:

  • CONTRACTOR – This is a complete list of my contractors. Active contractors have a bright yellow background and inactive contractors have a faded grey background. Each cell is a working hyperlink to that artist’s submissions folder on our server so I can access their work quickly and easily. Next to their name is the type of work they’re doing for me.

  • CONTRACTED WORK – I detail exactly the contents of each contract.

  • ID# – This is the number designation of the contract filename. i.e., Consulting Agreement (Bill Vertexpusher – SM1).doc. This includes a hyperlink directly to the Word document that contains the contract and the bill of assets for the contracted work.

  • DATE SENT – The date I issued that contract.

  • $ PER CONTRACT – The dollar amount of that particular contract.

  • SIGNED – A YesNo indication of whether or not that contract has been signed. If yes, I have a hyperlink to the signed and scanned PDF version of that agreement. I include this so our Finance department can quickly and easily find the signed copy of the contract so the contractor can eventually be paid once he’s invoiced.

  • DONE? – A YesNo indication of whether the contract is complete or not.

  • INVOICED? – A YesNo indication of whether or not the contractor has invoiced for the work. If yes, I have a hyperlink to the filled-out Excel spreadsheet invoice that the artist filled out. I have a standard invoice template that I send to everyone to fill out when they complete their work.

  • PAID? – AYesNo indication of whether or not that contractor has been paid.

  • TOTAL SPENT – At the very bottom of all this, you see exactly how much money has been spent on the project so far. This is all added up automatically via an Excel function I created, so everything in that column is instantly updated here.

That’s a lot of information. Here are a couple extremely important points that may be overlooked:

  1. Colors are important. Bright yellow = good = active. Bright red = bad = needs to be addressed. I can see at a glance exactly what the most important information is.
  2. There’s a reason for the progression of SIGNED – DONE – INVOICED – PAID. These are discrete steps that must go in order from left to right. If they don’t get completed in order from left to right, I have made a mistake, because these things should not happen in any other sequence. If I mark any one of them as a big red ‘NO’ and there’s a ‘YES’ after it, I know there’s a problem.
  3. Hyperlinks are king. Why would I waste my time manually tabbing through Explorer windows to go to my asset submissions directory, or my unsigned contracts directory, or my signed contracts directory, or my invoices directory? I hyperlink all of these files to their locations on the server, which I keep in constant sync. This way, my boss, my producers and Finance can quickly tab through this data without having to go digging for stuff that only I know the location of. This makes everyone’s job easier, and it takes virtually no time for me to do.

Basically, this Excel file is my constantly-updated birds-eye view of the project, all my contractors, the work that’s being sent out, instant access to all the relevant contractual data, and helps me keep track of my budget. I’ve opened up this file to my boss, our producers and the Finance department so they can always know what’s going on and where I keep things. On top of this, I sync all this data from my work PC directly to the server every night so it’s constantly updated. Another idea I have is creating separate, parallel fields for artist subtotals.

This has been tremendously useful for me in keeping track of everything and everybody. 🙂 I hope someone else finds this useful. I’m pretty psyched about it!

One thought on “Learning In Progress #5: Instant overview of contractor and budget information!”

Leave a Reply