For any production company doing many projects at the same time, one of the main complications is getting a comprehensive report of time spent on different jobs by different people and therefore the money spent on that project. Also producers would like to do a quick comparison between projects to see which one has cost more than estimated. So while there may be a lot of paid apps that do this for you not always do you have the budget to buy these or you are not sure if that app is something that would suit your needs. At the place I currently work I was thrown this same challenge, so I worked out on Microsoft Excel a simple table that gives me all the information I need. (Get your FREE TEMPLATE of this, details below)
|Image 1: The Master Table|
So there are multiple things that I had to consider when making this sheet,
# There will be multiple projects being worked upon at the same time.
# Within each project there are different jobs to be done - Offline, Online, Grade etc
# The job / role of each person has to be considered, lots of times the same person performs different operations like sometimes as an Asst Editor and sometimes Editor
# The reason for knowing the different roles within the job would be because rates would cost different for different roles eg - transcoding rates would be different from a offline rate
So the main challenge was how to compress all this information into one table so that I do not have to keep adjusting data to look at various combinations. One look at Image 1 & 2 tells you that I broke up the table with the jobs on one side and each person working under each project on the other side which includes sub categories. In the sub category each persons individual contribution gets mentioned. All the data on this sheet gets automatically updated when the details are filled in.
|Image 2: Master table expanded|
The WorkingSo the Master table I designed gives me all the combination of information I need, the drop down options lets me filter the information, so I can choose from which months or month I want to see information of, or I can choose to hide some projects. Under each project people who worked on it get automatically added when they clock in hours and the role that they played also gets updated as I fill up the information on the next sheet (Detailed Report - Image 3)
|Image 3: Detailed Report|
So certain categories which would get repeated a lot are locked in on this sheet (Image 4) and any data under these topics cannot be written any differently on the detailed sheet, Image 5A & 5B show how only the data written on this sheet can be selected and nothing else. Of course I can keep adding data to this sheet by typing just under the last row and that automatically shows up under the options on the details page. All the titles under job in this case automatically create a new column on the master page and therefore it is best to limit this to as less as possible.
|Image 4: Look Up List|
The dropdown list helps keep the Master Sheet clean but also helps to limit mistakes if data is being entered by multiple people or at different times.
|Image 5A: Drop down options|
|Image 5B: Error message|
Another advantage of this Master Time Sheet is you can just keep on adding data and as time passes just keep hiding the months on the detailed report which will not affect the master page, that way you can have the projects to the whole year on the master page.
|Image 6: Individual Job Sheet|
It would be best if I get these sheets as typed information that way I can just copy the data and paste on the Master sheet. I do insist that as much information is given to me on this sheet so in case when the Producers / Bosses are looking at the data and feel that too many hours were spent on a certain job, a quick look at these sheets will let me know why and where these hours were spent and thats another advantage of individual project job sheets. Of course that leaves me to interpret each persons role based on the information given in these sheets.
Finally I could also add a column for rates and that way all data would come with the amount of money spent alongside the hours spent, but since I keep this information open for all in the office to access it is best to leave the money calculation separately.