Former employer we had an admin (forget her title, something like engineering accountant or something) one of whose tasks was to do this using Excel & Project.

It's nice to be able to see you're scheduled to work 109 hours a week to keep projects on track rather than just feeling like it.

Our office (full service A/S/M/E/C) does this with a spreadsheet that is updated monthly. In a nutshell the process we use is as follows:

Each project manager reviews their projects indicating the anticipated revenue and effort by each department that every project will require for each of the next 6 months.
These are compiled to determine the total anticipated revenue and required labor effort for each department for each of the next six months.
From this you can evaluate each department to determine if the current level of staffing is adequate or if there is too much work or too little work.
This also gives you a good idea of the overall workload and the trends (up or down) as the months go by.

The month to month effort is only 1 or 2 hours per PM plus another 2-4 for the individual compiling all the information.

I would imagine that in a structural only office you could do something similar but considering the position rather than the department.

Keep in mind that the information for something like this is only as good as the one providing it. This process was done for years before I started at our firm 9 years ago, so most of the bugs have been worked out and people understand how to read the information.

Regarding other software I've been told that Deltek has a module that does a lot of this. We have Deltek, but don't have this module so I can't speak to it's effectiveness or complexity.

I use MS Project and identify teams/people and project assignments with project end dates as the only milestone. Typically I take the project schedules and assigned team, roll it up and put it on an office wide schedule. This really helped us identify the weeks and months that were really slow for the office. one can get as detailed as one wants.