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 |
The Challenge
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 Working
So 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)
Here on this sheet information is broken up under the various categories shown in Image 3, by doing this the Master page gets all the information it needs as a break up to automatically combine it. One problem with this is if you do not type the same exact words in excel (exact spelling and even spaces between make a difference) the sheet will produce multiple lines which will confuse you when reading the data,
eg if I type Sound & sounds the sheet will take them as 2 separate lines. In order to make sure that the sheet does not do this and produce multiple lines under the same person for the same job there is a 3rd sheet created Look Up List (Image 4).
 |
| 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.
To avoid too many mistakes it is always best if only one person fills up this sheet and so I get each individual to fill up job sheets and ask them to fill it up project wise (Image 6), initially I had got the employees to fill up all data on one sheet and had one more column for project but then it was easier for me to see a persons day by selecting their name on the detailed report and it will give me day wise data of that individual. So getting a separate sheet per person on each project helps to have a quick look at each persons contribution to the project, though that too can be looked at using 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.
For those who are interested in getting a copy of this hit Like / Follow on the blog button on the right hand side or you can click Like on the Facebook page Auggielicious and leave your email id in the comments and I will send you FREE TEMPLATE copy of this
