Resource Planning / Resource Allocation

[akki4989]

Hi,

I am developing a template for planning resources based on their availability and task scheduled.
My first input will be resource availability calender, which shows available hour of each resource on a given day.
Second input will be start and finish date of task with budgeted hour.
Required Output:

If a day falls between start and finish date then the available hours of the assigned resource on that day must be populated in the cell
till the budgeted hours get exhaust.
Condition: If overhead hours are added for a resource for a given day then those hours should be deducted from the available hours.
The sum of allocated hours should be equal to budgeted hours.
At any day the allocated hours cannot be more than available hours. The allocated hour on the last day of the task can be less than available hours.
In the attached file green would be the desired output based on orange inputs

-- removed inline image ---

-- removed inline image ---

Thanks!!

 

[pbornemeier]

Welcome to the Board!

There was no file attached. Please use DropBox (or something similar) to make the file available or use Excel Jeanie (see link in my sig) to post appropriate portions of your worksheets.