• 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

    Hui...

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

Resource loading

M

mediabay

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.

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

Here is a start for the project and load:


http://picasaweb.google.com/lh/photo/UipQI9Z6hK52vWMvIak02Q?feat=directlink


Here is an image with the formulas exposed:


http://picasaweb.google.com/lh/photo/iMXpMGgvnoEJ3HSYh1wc7g?feat=directlink


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.


-Phil
 
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.

Cheers,

Phil
 
You must log in or register to reply here.
Back
Top