Adaptive forecasting based on variable project lengths

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

2. Re: Adaptive forecasting based on variable project lengths

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.

3. Re: Adaptive forecasting based on variable project lengths

Thank you, I thought it was just my computer!

Hopefully have managed to attach.

4. Re: Adaptive forecasting based on variable project lengths

Catches everyone out! I'll have a look.

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

5. Re: Adaptive forecasting based on variable project lengths

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.

6. Re: Adaptive forecasting based on variable project lengths

Glenn

This is looking great, thank you very much. Just one thing, I had a play with the dates a bit and when I do a whole year (01/04/17-31/03/18) the April section does not bring a cost through?

Date formulas have always been a bit over my head, I was just sat laughing at it!

Any more help you could give to sort that little issue would be much appreciated.

Scott

7. Re: Adaptive forecasting based on variable project lengths

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"),"")))

8. Re: Adaptive forecasting based on variable project lengths

Glenn

Very grateful for your efforts. I did think that even for advanced excel users, this might me a tricky one so I am VERY grateful for the help received - thank you.

Have attached again though (sorry!), just another query...

If I put the start date early in a particular month, I would expect there to be a significant percentage of the available days in the "days on project" bit (as there are more days in the month available to work if you start earlier), but it appears to give the opposite - giving the number of days passed in that month, rather than the number of days left to work. This only happens for the first month, for the last month, this approach is what is needed.

Hope that makes sense, attachment explains a bit better.

Scott

9. Re: Adaptive forecasting based on variable project lengths

How about an IF(ISBLANK that references the cell above and if it is blank, it must be the first month, so then do [Average days]-[your formula] if it is blank, and just [your formula] if not blank?

10. Re: Adaptive forecasting based on variable project lengths

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

11. Re: Adaptive forecasting based on variable project lengths

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

12. Re: Adaptive forecasting based on variable project lengths

MAGNIFICENT!

Thank you so very much Glenn!

13. Re: Adaptive forecasting based on variable project lengths

You're welcome and thanks for the Rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)