+ Reply to Thread
Results 1 to 13 of 13

Adaptive forecasting based on variable project lengths

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    6

    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.
    Last edited by sjez27; 07-27-2017 at 05:41 AM. Reason: Attached spreadsheet

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    6

    Re: Adaptive forecasting based on variable project lengths

    Thank you, I thought it was just my computer!

    Hopefully have managed to attach.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    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.
    Last edited by AliGW; 07-27-2017 at 05:33 AM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    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.
    Attached Files Attached Files
    Glenn



  6. #6
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    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
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    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. #8
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    6

    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    6

    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. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Adaptive forecasting based on variable project lengths

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

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Adaptive forecasting based on variable project lengths

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

  12. #12
    Registered User
    Join Date
    07-27-2017
    Location
    Gloucester, England
    MS-Off Ver
    2013
    Posts
    6

    Re: Adaptive forecasting based on variable project lengths

    MAGNIFICENT!

    Thank you so very much Glenn!

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: Adaptive forecasting based on variable project lengths

    You're welcome and thanks for the Rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-12-2017, 09:20 PM
  2. Forecasting Overtime Hours Required to Complete a Project
    By VAred in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2015, 11:04 AM
  3. Replies: 1
    Last Post: 08-28-2015, 12:37 PM
  4. Replies: 4
    Last Post: 09-09-2014, 01:15 PM
  5. Task \Project Tracker = determine output time based on variable inputs
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-10-2013, 04:37 PM
  6. [SOLVED] Extracting Text From Cells of Variable Lengths
    By stinkstik in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-25-2013, 02:46 PM
  7. [SOLVED] Parsing data of variable lengths
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-02-2011, 04:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1