Need Help with statistical forecasting formulas.

Posted on 2015-02-21
Last Modified: 2015-10-05
The attached workbook has one worksheet, "Forecast" it uses numerous statistical formula to produce various bell curves.

I am trying to figure out how to:

A) Change the starting point from 0 to a given number. Thereby allowing it to be truly used to forecast. with the total still being the same.
B) Change the number of periods from 41 to 20 or even to 80 as required. Also allowing for forecasting to final completion.

I don't even know where to start to figure this out. Any ideas, or even a solution would be appreciated. Maybe it can be done in formulas, maybe it needs VBA, not really sure yet.
Question by:DougDodge
  • 5
  • 4
LVL 29

Expert Comment

ID: 40624476
Where would your 0 change ? in C10 ?

and 41 to 20 to 80 where are you referring this in Columns B,C,D

Can you please just brief how these works ?


Author Comment

ID: 40624505

Cell B10 would be the natural place to have the start variable. Instead of 0, it could be 40%. A project will progress along until it finds itself so far behind plan that it needs to reforecast. But that being said, it would still have actual work completed to date. So that percent complete would the start of the curve.

If I place 40 in Cell B10, the spike is only for that period, then drops drastically instead of running cumulative from that point.

From that point, the remaining periods (Rows 10 to 50) could change. There may only be 20 periods left to complete the project.

From my way of thinking, the first parameter or variable needed to be set would be the remaining periods (Rows) to complete the work. Then a distribution of the value of work to go over those periods

In the case of the original file, the variance from row to row (Row 10 through 50) in column B is 2.5%

The profile generated uses the parameters of the cells G4:AG7

The different Columns of that series are used dependent on the resource being used and the work type being performed.

Curve 1 is a normal Bell Curve. (Cell B9)
Curve 4 is a back loaded Bell Curve, typical of finishing carpentry etc.
Curve 8 is a sloping Curve, typical of security on the project. Even distribution.
Curve 12 is a front loaded Bell Curve, typical of a framing crew.

As you can see, they are all still based on the 2.5% variance from B10:B50

Hope this helps.....

LVL 29

Expert Comment

ID: 40624576
So from what I first see is we have 2 variables the rows or the period as you call them and the variance from row to row which is this 2.5%

Are those 2 variables set by you or they are a result of some calculation ?

If set by you then can we say that we put them in Cell B3 and B4
Like B3 = rows = 50 here
B4 = Variance = 2.5%

Can we do this as a start this way if we build a VBA then we can count on those 2 cells to draw the rest.

That is my first quick conclusion if you find also and other variable please indicated to me and advise where to put it and what does it affect.

Once we set all the variables then we can built the model.


Author Comment

ID: 40624862
The 2.5% is just a formula dividing a fictitious project budget of 100 by 40 periods. they could be anything, 57,436 for instance instead of 100

Sure, the variables can be set there.....

A project will have a timeline, it may have 32 periods to finish. (Periods could be days, weeks, or months) irrelevant right now.
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 29

Expert Comment

ID: 40625465
ok let me understand this:

The 100 I see is in cell C50 which is a result of a formula and the formula points to the 4 rows (row 4 to 7) I do not see where you set this project to be 100 or 57,436 as you said or any other value. As if we need to deduct the 2.5 from the project we need to see where the project value is as we understand also that the 50 is variable also

Can you please clarify this?

In 2 words I need to know what are the variables in this project and what is the dependent from these variables.

In my mind it is
1) the project value (can't see where it is as value not as formula)
2) the number of rows 50 or less or more
3) I had said the 2.5% but you said it is derived from Project/number of rows which is fine it is no more a variable but a dependent.

Do we have more variables ???


Author Comment

ID: 40625678
Let's just say for example a project has a budget of 4,500 man-hours, 3 people on it, working 10 hours a day, That would mean 30 man-hours a day on the project.

25% of the way through the project they have spent 700 man-hours. They should have earned 1,125 man-hours. That is a shortfall of 425 man-hours. At this point we would reforecast the project and try to hold the completion date.

The project has spent 37.5 days, leaving a remaining 112.5 days to finish.

If you follow so far, the means we have variables of:
1) Total budget (4,500) man-hours
2) Original duration of 150 days.
3) Remaining budget of (4,500-700=3,800) man-hours
4) Remaining duration of (150-(150*0.75)=112.5) days (or periods if you want to call them that.)

Simple math tells us that (700 / 4,500 = 15.5% complete, NOT 25%)

So at the 25% period, B20, we would need to inject 15.5 and prorate the rest of the values to reach 100% in 112.5 periods.

Simply inserting 15.5% in B20, spikes the value for that period, and then fall back to a normal curve again. Which is the wrong answer.

If we change the 2.5% values for B21 through to B50, then the value hits 100%

To your questions......

1) You are correct, the project budget does not show here. It is a matter of applying the budget / periods to come to an even spread for the project. In the case of this worksheet it is 100 / 40 periods = 2.5% per period.
2) The number of rows will change depending on the status of the project and exactly when a reforecast is called for..... It may be at 75% of the way through the project. (A project of 600 periods and 75% complete would mean 150 periods remaining, or to go yet.) So 150 rows would be needed.
3) Your assumption regarding Q3 is correct, it is not a variable at all.

Hope this helps.
LVL 29

Expert Comment

ID: 40626080
re this comment:

Simply inserting 15.5% in B20, spikes the value for that period, and then fall back to a normal curve again. Which is the wrong answer.

then if I follow your logic then if at 25% (at a rate of 2.5%) you get to 100 in 40 periods (row 50 starting at row 10) then if at 25% we want to change that to 15.5% and keep the remaining periods the same meaning a remaining period of (row 50 - row 20 = 30 periods) in my understanding we now would have a new increment which is calculated as follows:
100-15.5 = 84.5 /30 = 2.82

this means the VBA should change the figures from B21 till B50 at an increase of 2.82% per row

Is the reasoning correct ? and that is what you want to achieve ?
to summarize my understanding:

You have the layout of 0 to 100 with a certain increase per row if you plug a figure at any row in Column B you want the macro to recalculate the variance basis the project will remain at the same rows of 40.

If my reasoning is correct then please look at the attached file I made the increase in Col A then I modified Col B to be a cumulative from Col A then I inputted a new formula in A20 taking care of the new 15.5% and changed manually the value of B20 to be 15.5%. Please look at the results are they what they supposed to be ?? This is just to understand the mechanics and the results so I could develop the VBA.


Author Comment

ID: 40626673
Almost there......

Everything seems right, but we still need to consider the variable of number of rows..... The project may give an extension to reduce overtime, or may compress it to come on line quicker. The default would be the 30 rows we have, but as the VBA does its calculations it may have need for more or less rows than 30.....

Yes, you are correct in your assumptions regarding the recalc of the variance from 2.5% to 2.82%
LVL 29

Accepted Solution

gowflow earned 500 total points
ID: 40626690
ok before we go further did you see the implications on the graph ? at the 25% there is a drop then back up is this normal are the figures correct ?


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now