Adaptive forecasting based on variable project lengths

Hello! First post! ATTACHMENT BELOW!
I'm afraid I don't know how to describe this problem in one line - I think it's more complicated than I've made out in the title. I hope you can help!

Back story: I'm dealing with projects and basically I want to forecast (*by month*) how much people are going to cost. First of all, I list each person, how much they cost, how many days they are forecasted to work and then a total. Then I take a total of everybody, simple. At the top of the sheet I have a start and end date of the project - this is variable but will never be longer than a year.

So I've got a total cost figure and a start and end date. Separately, I have the average number of days that are worked per month (fixed) to help give me a more detailed forecast by month. For example, people work less at xmas and easter so those months will cost less. If every project I had was 12 months, I could just multiply the total cost by the percentage of average days worked per month - only I don't always have 12 month projects, and they don't always start in April (tax year start)

The problem: my issue is that I don't know how to make the forecast adapt to differing project lengths, and how to match up the correct months (and thus taking into account the corresponding average days worked per month). They can start anytime and last anywhere up to a year (first of April earliest start, end of March latest finish). I also don't know how to match the starting month to the correct one in the forecast.

I apologise for the awful description, I'm frustrated at myself for not being more articulate.Example1.JPG

Please feel free to ask questions.

Thank you in advance.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Catches everyone out! I'll have a look.

Mmm - not my sort of problem, unfortunately, but others will be along to help.

This works out the actual numnber of days per month, taking intoi account partial months at the START and END of the project:

=IF(AND(A17> $B$2,A17< EOMONTH($B$3,-1)),B17,IF(MONTH(A17)=MONTH($B$2),($B$2-A17)*B17/DATEDIF(A17,EOMONTH(A17,0)+1,"d"),IF(MONTH(A17)=MONTH($B$3),($B$3-A17)*B17/DATEDIF(A17,EOMONTH(A17,0)+1,"d"),"")))

I have added an error trap to the other two formulae to remove the ugly error messages. happy to explain, if you cannot dissect it yourself.

I was not looking forward to answering this... My head was spinning trying to cover all the bases first time round. But it turned out to be simple, I think:

=IF(AND(A17> =$B$2,A17< EOMONTH($B$3,-1)),B17,IF(MONTH(A17)=MONTH($B$2),($B$2-A17)*B17/DATEDIF(A17,EOMONTH(A17,0)+1,"d"),IF(MONTH(A17)=MONTH($B$3),($B$3-A17)*B17/DATEDIF(A17,EOMONTH(A17,0)+1,"d"),"")))

OK. I understand that and can quicklly correct it!!

OK. Here we go. Break this one!!!

You're welcome and thanks for the Rep.