Help needed in reveue projection model

[abhradipchoudhuri]

Hi All,

I am stuck in the logic of a calculation. Bit brief on what I am working on - it's a revenue projection model. The entire sheet is ready for me but i'm stuck in the logic of calculating the revenue details on the monthly breakup. Attaching an excel file for reference were I need help on. it's just an example.

The idea is, there is a standard bill rate, a standard work hrs (this remains constant in the example for calculation sake). And I have created a network days row for entire year of 2014. Now there are three resources I have taken example off, who start in a project from any given time of the year. Need help in generating one formula that I can use for all cells to capture the revenue value for the entire year. It needs to consider the following conditions -

1) Since resource 1 starts at 1/10/2014 - the networkdays should get calculated based on the table above(refer excel), multiply with the Rate and hrs/day and give the result month on month till end of year.
2) Resource 2 starts from Feb so ideally the formula should somehow put Jan value as zero and calculate till May 8th with a value(for the month of May and show the rest of the year as zero value
3) If resource start date coincides with the start date and end date of a month

would really appreciate some help on this. If this gets done my tool will be ready as I have already done the projection collation part of it.

Thanks a lot in advance. If someone can update the sheet for me I will really appreciate, I have been trying to get this fixed for over a week now and my wits have failed finally.

Regards
Abhradip