• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Resource loading


New Member
Hi, I'm new to this site and am hoping you can help me find a solution to this problem. What I am interested in is estimating the amount of people I will need on various projects over time.

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.

Well I may have figured out a start to this problem.

Here is a start for the project and load:


Here is an image with the formulas exposed:


What I'd like to do next is provide a variable for high/low estimates. I may just do that with a simple copy/paste right now. Any thoughts would be appreciated.

Well done Phil

The only other thing to be aware of is that although the total hours divide by the number of people may be ok, If 1 person is doing Task A, B & C they may be over there daily allowable hours and the rest of the crew may be cruising.

You could check that by adding another column where you assign a person to each Task and then check each person's hours each week with a simple sumproduct.
Thanks Hui. I had built this on my home machine with 2007, but when I got into the office to work on it some more, I forgot I only had Excel 2003 loaded. Ran right into the 256 column limit which I had forgotten about.

Anyway, it seems to do what I need it to do right now. This is just a modeling tool to grossly gauge how many people we need on all the projects that we might be doing using data feeding off the analyst's P&L sheet. What I'll want to do next is setup the data model that is easy to change and update, and on another sheet setup a dashboard that summarizes what the data is saying. I'm sure to have questions since I haven't setup a dashboard before.