What I need to do is estimate the amount of time (work hours) that each functional area needs to complete a project and need to see if we will have enough people during that time or will we need to hire more. This is also used for P&L analysis with # of hours/rate by person. The P&L portion is easy to create and plan using excel. Where it gets more tricky is adding time into the equation. Here is what we work from:

Project A

Developer Adam estimates 200 hours between March 1 through end of April. The estimate could have a range of 250 high, 180 low and a projected 200 hours for P&L and resource planning.

Project B

Developer Betty estimates 100 hours work between March 15 and April 20.

Developer Adam is also on the project for 60 hours between March 10 and April 10.

What I can do is use NETWORKDAY function to determine how many workdays are available and average out the work needed over each day. What I'd like to do is setup a routine that will enable me to create a chart showing the individual work commitment by day given the Start & End dates and the # of hours needed on a project. I would like the chart to show the allocation by each day and a visual indicator when there is too much work for one person in a day e.g. 8 hours. (It would be great if this could be a global variable, ie Adam is available 7 hours every work day, but Betty works part-time and is only available 5 hours /day. This is NOT a requirement.)

Any thoughts on how to do this in Excel would be appreciated.

Thanks!