$100 USD: Calculate Monthly Forecast Values

  • I am looking for some formulas that will calculate monthly project forecast totals based on 3 data elements [Start Date, Duration (Months), Weighted $ Value]. The template I have contains rows of project info and contains rolling 12 monthly values across the top with Month 1 being the current Month (e.g. Sep-2013). Would like to have formulas for these monthly columns that would calculate the monthly breakout values based on the 3 data elements. The part I am struggling with is that I would like to apply the amount proportionate to time of the project is contained in each applicable month.


    For example, in the 1st row in the table below, the project started on Sep 14, 2013 and the duration in months is 2. I calculate the duration in days to be 52 daily rate of $19.23 (1,000/52). I want to apply ONLY the portion (16 days out of 30 total for Sep: 16 * 19.23 = 308) applied to the Sep-2013 column. The for Oct-2013 column, apply the entire 31 days of the month (31 * 19.23 = 596). And for Nov-2013 (the remaining 5 days: 5 * 19.23 = 96).



    [TABLE="class: grid, width: 100, align: left"]

    [tr]


    [td]

    Calc Start Date

    [/td]


    [TD="align: center"]Wtd.Value ('000s) [/TD]

    [td]

    Months

    [/td]


    [td]

    Sep-2013

    [/td]


    [td]

    Oct-2013

    [/td]


    [td]

    Nov-2013

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]14-Sep-13[/TD]
    [TD="align: center"]1,000[/TD]

    [td]

    2

    [/td]


    [td]

    308

    [/td]


    [td]

    596

    [/td]


    [td]

    96

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]05-Oct-13[/TD]
    [TD="align: center"]2,000[/TD]

    [td]

    1

    [/td]


    [td]

    0

    [/td]


    [td]

    1,677

    [/td]


    [td]

    323

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]22-Jul-13[/TD]
    [TD="align: center"]500[/TD]

    [td]

    4

    [/td]


    [td]

    122

    [/td]


    [td]

    126

    [/td]


    [td]

    89

    [/td]


    [/tr]


    [/TABLE]










    I attached my file to use for reference. Also, another criteria would be IF there is a NULL value in the Calc Start Date column (Col S), then the monthly values should be set to 0.


    I hope this makes sense. If this can be done with formulas that would be great. But if it has to be done with VB code, that works as well. Feel free to add any helper columns or rows as needed.



    Thank you in advance for your help.

  • Re: $100 USD: Calculate Monthly Forecast Values


    Hello


    This seems like a a nice challenge. I will review and post back in about an hour after I have reviewed the requirements and the feasibilty.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $100 USD: Calculate Monthly Forecast Values


    Hi there


    This is certainly feasible, and normally with only regular Excel functions. No VBA needed.
    Question: how do you calculate the days? For example, 14 September and 2 months means 52 days.
    What is your calculation there please?


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $100 USD: Calculate Monthly Forecast Values


    I just realized the 52 days is for the 2nd line. The correct duration for line 1 that I have is 61 days. For this I used: =EDATE(14-Sep-13, 2) = 14-Nov-13. Then used =DATEDIF(14-Sep-13,14-Nov-13,"D") = 61. Daily Rate then becomes 61/1,000 = 16.39


    [TABLE="class: cms_table_grid, width: 100, align: left"]

    [tr]


    [td]

    Calc Start Date

    [/td]


    [TD="align: center"]Wtd.Value ('000s)[/TD]

    [td]

    Months

    [/td]


    [td]

    Sep-2013

    [/td]


    [td]

    Oct-2013

    [/td]


    [td]

    Nov-2013

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]14-Sep-13[/TD]
    [TD="align: center"]1,000[/TD]

    [td]

    2

    [/td]


    [td]

    262

    [/td]


    [td]

    508

    [/td]


    [td]

    230

    [/td]


    [/tr]


    [tr]


    [/tr]


    [/TABLE]

  • Re: $100 USD: Calculate Monthly Forecast Values


    Okay, I set up the formulas... using helper columns (starting in column AS).
    The result can be found in attached file.
    I will also send you a Private messsage the 2 payments (10% to Ozgrid, 90% to me).


    Thanks in advance,


    Wigi

    Files

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $100 USD: Calculate Monthly Forecast Values


    Can you clear your inbox please? I cannot send my message. Thanks.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $100 USD: Calculate Monthly Forecast Values


    Thank you, the PM was sent.
    Can you give me your feedback on the suggested solution?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: $100 USD: Calculate Monthly Forecast Values


    You're welcome, thank you for the warm and great feedback!

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --