Closed Thread
Results 1 to 15 of 15

Cost distribution across months , Normal & non-normal (skewed) way !

  1. #1
    Registered User
    Join Date
    12-26-2013
    Location
    Dubai
    MS-Off Ver
    Excel 365
    Posts
    21

    Cost distribution across months , Normal & non-normal (skewed) way !

    I have a project with many types of cost line items in Excel. Each cost needs to be spread over a number of months in something approximating a bell curve where I can set a parameter for the skew and "flatness" of the curve. i.e.: are costs front-loaded or end-loaded and how much greater is the most expensive month from the least expensive month in my time series.

    The distribution "shape" depends on the kind of cost. Obviously this is easy to evenly distribute (total cost / number of months for each month). My formula can also "normally" distribute these costs in a typical bell-curve shaped distribution. In construction this called an "s-curve" for obvious reasons. By adjusting the Standard Deviation for a cost, I can tell the formula how "steep" or "flat" I want my curve. My formula derives the % under the curve for a time period and multiplies by the total cost to get a normally-distributed cost curve.

    My problem is that most of my costs aren't normally distributed (bell-curve) or flat. They are very often front-loaded or rear loaded in a skewed bell-curve. Excel doesn't appear to support any skew for a normal distribution. What are some alternative approaches?

  2. #2
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    An interesting question:

    I am posting the attachment only as a 'work around' solution that I came up with for a few projects at work, for one or two simple projects it works quite well, but, I too would like to see a different solution. There must be one out there
    Attached Files Attached Files

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    NASA's Beta cost distribution polynomial:: https://app.box.com/shared/ipkvdyc4bt
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Quote Originally Posted by shg View Post
    NASA's Beta cost distribution polynomial:: https://app.box.com/shared/ipkvdyc4bt
    Thanks SHG now I will be spending all day trying to dissect the formulas and creating a 'Laymans' mock up example

    Later: One question I have SHG, is what does the 'BetaPer' part of the formula refer to ? When I insert the formula I get a #NAME? error. I do not have that Function in my Library & a Google search just returns an earlier 2011 thread answered by you :-)

    Later still: Aaah! VBA :-) So far I have inserted your VBA code into Module 1, renamed my tab name as UDFs, checked the cell references and
    VOILA ! it works. Awesome SHG.

    Just for the record in the Example my formula Row 22 Column H across needs changing from $C$21 to $C$22
    Attached Files Attached Files
    Last edited by BlindAlley; 03-28-2016 at 08:30 AM. Reason: A few hours later after checking the formula my forehead is furrowed

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Good job.

    If you change the formula in H21 to

    =$C21 * BetaPer($F21,$G21,$D21, $E21, H$20)

    ... it will copy across and down.

  6. #6
    Registered User
    Join Date
    12-26-2013
    Location
    Dubai
    MS-Off Ver
    Excel 365
    Posts
    21

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Is there to do the same Without the use of VBA ?

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    =SUM(N(OFFSET($C4,$A4:$A8-1,MMULT(--$X4:X8,ROW(A1:INDEX(A:A,X$3))^0)-1))*X4:X8*$T$4:$T$8) CSE
    if i get it right
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-26-2013
    Location
    Dubai
    MS-Off Ver
    Excel 365
    Posts
    21

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    I found this :
    http://faculty.washington.edu/kenyulin/

    the file to download at the bottom of the page (Cash Flow Charting Tool)
    and it is very helpful ...

  9. #9
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    yes, almost the same
    this more flexable
    =IF(OR($U4=0,E$3>$U4),,NORM.DIST(E$3,$B4*10/$U4,$C4*10/$U4,)/SUM(NORM.DIST($E$3:INDEX($E$3:$P$3,$U4),$B4*10/$U4,$C4*10/$U4,)))
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-04-2015
    Location
    Malaysia
    MS-Off Ver
    Microsoft Office 365
    Posts
    57

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Quote Originally Posted by tim201110 View Post
    yes, almost the same
    this more flexable
    =IF(OR($U4=0,E$3>$U4),,NORM.DIST(E$3,$B4*10/$U4,$C4*10/$U4,)/SUM(NORM.DIST($E$3:INDEX($E$3:$P$3,$U4),$B4*10/$U4,$C4*10/$U4,)))
    Dear Tim,
    Just wondering how you determine your mean and std dev value in your "norm" sheet in your file?
    Appreciate you can share with me.
    TQ

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    ulala2 Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Registered User
    Join Date
    11-19-2019
    Location
    Turkey
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Thanks but i got a problem. What will happen if some activity starts in 15/08/2016 ends in 02/01/2017. how to write periods?

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  14. #14
    Registered User
    Join Date
    11-19-2019
    Location
    Turkey
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Hello Shg, very good document. But i got a problem;

    I got tons of budget activities with Start and finish dates.

    For example; Start Date: 15/01/2020 - End Date: 02/04/2020. And the cost is 10.000 $. How can i distribute values using "Nasa's Beta Cost Distribition" Because periods only representing integers. if i write dates in there, program giving false calculations. And if i give january to 1 and April to 4, then whole calculation include april as 30 full days. But in april, only 2 days are working.

    Thanks for help.
    Last edited by Zake; 11-19-2019 at 07:24 AM.

  15. #15
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Cost distribution across months , Normal & non-normal (skewed) way !

    Administrative Note:

    Welcome to the forum.

    Sorry, but your post does not comply with Rule #6 of our Forum RULES:

    Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction, then send a private message to them asking for clarification.

    All Participants:

    Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 07-24-2015, 03:40 PM
  2. Skewed Normal Distribution for Monte Carlo Simulations
    By VooDoo33 in forum Excel General
    Replies: 6
    Last Post: 04-29-2015, 02:44 AM
  3. Normal Distribution
    By dalai in forum Excel General
    Replies: 1
    Last Post: 08-23-2014, 05:58 PM
  4. Skewed Normal Distribution Function
    By Bilko in forum Excel General
    Replies: 2
    Last Post: 10-26-2011, 03:01 PM
  5. Replies: 5
    Last Post: 01-31-2011, 12:21 AM
  6. [SOLVED] NORMAL DISTRIBUTION
    By FLKULCHAR in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-27-2005, 06:05 PM
  7. Normal Distribution?
    By Anthony Slater in forum Excel General
    Replies: 3
    Last Post: 03-08-2005, 04:06 PM

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