Spread project value evenly accross project duration

[Anonymous]

Essentially i have a table similar to below:

Project	Start	End	Value	Revenue Shape
ABC	01/02/2020	01/04/2022	$100,000,000.00	Flat
ZYX	01/01/2018	01/02/2029	$310,000,000.00	Flat
MNO	01/06/2021	01/07/2024	$75,000,000.00	S-Curve
TUV	01/10/2019	01/02/2025	$150,000,000.00	S-Curve

Flat - Evenly distributed revenue per month

S-Curve - Variable, but essentially a normal distribution of revenue across duration.

I need to create a monthly revenue line chart from the above data.

My first assignment is to assume all the "Revenue Shape" is flat, and to evenly distribute all revenue on a line chart.

Second assignment is to figure out a standard "S-Curve" or normal distribution for forecasting purposes, and to implement this where the "Revenue Shape" equals S-Curve.

I cannot seem to get anything to work, any advice would be appreciated.

[Ashish_Mathur]

Hi,

I can help you get the monthly revenue projection where the revenue shape is flat of all projects.  Would you be OK with that starting point? 

[Anonymous]

Hi Ashish,

That would be great!

Thanks

[Ashish_Mathur]

If the start and end dates are not the first of the month, then should the revenue of that month be recognised on proportionate basis or should the revneue still be recognised for the full month?

[Anonymous]

Proportionate would be the best approach

Thanks, 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  I changed the start and end dates to check my result.

Hope this helps.

[mxdsa]

Ashish,

Thank you for sharing this example. It was exactly what I was looking for.

Caleb

[Ashish_Mathur]

You are welcome.

[lbudack]

@Ashish_Mathur Hi, Ashish! This solution proved very useful for something I'm working on, but occasionally I'm getting duplicate rows. Instead of revenue, I'm spreading labor forecasts through projects with multiple phases & dates so that may be why. I can't find where that's happening in the calculations. Any ideas or do I need to put my data here? 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file, explain the question and show the expected result.

[lbudack]

@Ashish_Mathur Sorry for the delay - it took me a while to format everything and get it out of my test files! Here's the link to the test file. One table is the current state, and the other is the desired state. What I am aiming to do is take phases of a project with different start and end dates and evenly spread out the projected labor across resources. 

https://schmidtassociates-my.sharepoint.com/:u:/g/personal/lbudack_schmidt-arch_com/EUswp4Rquf1Cm7xg... 

[Ashish_Mathur]

Hi,

I cannot understand your requirement at all.

[lbendlin]

Is the end date (month) inclusive or exclusive?

For the normal distribution - what is the value for the first and last month?

[Anonymous]

It would be inclusive, they are usually not the first of the month, but that's just what i put in the table above.

There is literally no defined values to start or end, I just know that some contract types would more likely have an "S-Curve" revenue shape, but I would like to have an "outlook" of revenue.
Would really just need to be some basic standard we apply like over 12 months it could be:

Jan	2%
Feb	5%
Mar	7%
Apr	10%
May	12%
Jun	14%
Jul	14%
Aug	12%
Sep	10%
Oct	7%
Nov	5%
Dec	2%

[lbendlin]

So the initial value would be 0.02 .

But your sample table only has 12 values. You said inclusive, which would mean 13 months for your examples. Sooo - which one is it?

[Anonymous]

The amount of months would be variable, i just gave an example of the distribution over 12.
Month count could be 6, 12, 13, 48, 49 etc. etc.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.