The value of hours billed could also be the cost of hours billed i.e. what you have to pay each crew member / team member.
The goal of this tracking tool in excel is to have something that is really easy to input data and displays straight forward performance results. The two main datapoints that are tracked will be amount of hours billed and the value of those hours billed (amount charged to client).
There are 3 filters for those two data points. You can report by a given job ID, a given crew, or a given crew member or some variation of those 3. It is also setup so you have an 'all' selector for each filter. I used the pass/fail logic that was implemented a few months back in order to cleanly create the right identifier to know what to actually reference when doing the sum calculations.
The data is summarized over a 52 week period as well as a monthly period. Based on the date entered for the first ending week, all other dates will update.
The visuals will show the monthly number of hours billed and the value of those hours billed. These charts automatically update when a filter is changed.
There is data validation implemented as well as mapping for the hourly rate of each crew member. That will make data entry more efficient and reduce data entry errors.
Finally, there is a start/end date and the total hours and value of hours accumulated in that time. This also runs off the filter selection. A 'months active' input is also available so the avg. hours / value billed per month can be shown over the specific date range.
A related template is this crew tracking gantt chart tool.
Screenshots of Template:
