Search code examples
excelvbaexcel-formulanormal-distribution

Front Loaded and Back Loaded | Normal Distribution Column Chart and S Curves in Excel

Most of us may be aware of normal distribution curves however those who are new to front-loaded and back-loaded normal distribution, I would like to provide the background and then would proceed on stating my problem.

Front-Loaded Distribution: As demonstrated below, it have a rapid start. For e.g. in a project when more resources assumed to be consumed early in the project, cost/hours is distributed aggressively at the start of project. Front-Loaded Distribution / S Curve

Back-Loaded Distribution: Contrary to Front-Loaded distribution, it start out with a lower slope and increasingly steep towards the end of the project. For e.g. when most resources assumed to be consumed late in the project. Rear Load Distribution S Curve

In the above charts, green line is S-Curve which represents cumulative distribution (utilization of resources over the proposed time) and the blue Columns represents the isolated distribution of resources (Cost/Hours) in that period.

For reference, I am providing the Bell Curve / standard normal distribution (when Mean=Median) chart (below) and the associated formula to begin with. Normal Distribution S Curve

Problem Statement: I was able to generate the normal distribution curve (See below with formulae) however I am unable to find a solution for Front loaded or Back Loaded curves.

How to bring the skewness to the right (front-loaded / positively skewed distribution which means mean is greater than median) and left skewed (back-loaded / negatively skewed distribution which means mean is less than median) in a normal distribution?

Gaussian Bell Curve with Excel Formula

Formula Explaned:

Cell B8 denotes arbitrarily chosen standard deviation. It affects the kurtosis of normal distribution. In the above screenshot, I am choosing the range of the normal distribution to be from -3SD to 3SD.

Cell B9 to B18 denotes the even distribution of Z-Score using the formula:

=B8-((2*$B$8)/Period)

Cell C9 to C18 denotes the normal distribution on the basis of Z Score and the Amount using the formula:

=(NORMSDIST(B9)-NORMSDIST(B8))*Amount/(1-2*NORMSDIST($B$8))

Update: Following one of the link in comment, I closest got to the below situation. The issue is highlighted in Yellow pattern as due to the usage of volatile Rand() function the charts are not smooth as they should be. As my given formula above do not create ZigZag pattern, I am sure we can have skewed normal distribution and smooth too ! ZigZag Columns Issue in Normal Distribution

Note:

  1. I am using Excel 2016, so I welcome if any newly introduced formula can solve my problem. Also, I am not hesitant to use UDFs.

  2. The numbers of front-load and back-load distribution are notional. They could vary. I am only interested in shape of resulting chart.

Kindly help !

Solution

  • You can generate the curve using below methods and can use the numbers generated by them for your requirement.

    enter image description here

    With formulae

    enter image description here

    The curve

    enter image description here

    Notes:

    1. If you want to change the bins you have to drag the cells down or up in order to complete the series
    2. If you want to change the total cost, you can change the multiplier
    3. If you want to change the tilting of the curve you can change the divider in column C which is currently set to 2, if it is -2 the tilt will change direction, you can experiment with different numbers, the direction depends upon either it is less than zero or greater than zero

    For copy past

    =A2+180/($G$3-1)
=RADIANS(A2)
=$G$4*SIN(B2 + SIN(B2)/2)