Calculate an end date for fixed amount of work by a variable amount of monthly effort

Hi All,

I've lurked around and picked up a lot of information from you all (thanks!) in the past but I'm really struggling to find a way of solving a new problem I have and hoped someone could help me find a solution. I've been using Excel to track scope versus capacity for the project I'm currently working on.

- The project I'm working on has a fixed number of phases from start to completion.
- Each Phase requires a set number of man-days effort to be completed. I have already calculated this figure.
- I know how many man-days of effort I can expend on the phase by month.
- I know when the start date of the first Phase of the project is.

So as an example. My project has three Phases and starts on 01/12/2014. The first phase requires 124 man-days of effort to be completed, the second requires 207 man days of effort to be completed, the third requires 170 man days of effort. In December I have 80 man days available, in January I have 80 days available and in February I have 120 man days available.

My question is, is there any way of automatically calculating an end date for each phase based on the amount of effort I have available to me each month?

I hope I've explained the problem lucidly and look forward to any assistance that anyone can provide. Something tells me that I'm probably trying too hard to solve this and I'm hoping there is a really obvious formula that I'm missing...

Best regards
FatFingers.

One man day represents one day of effort. I always calculate one person each month can contribute 20 man-days of effort. So in the example above in December I have 80 man days of effort available which would represent 4 people working constantly on this for the whole month.

Does that make sense?