none
Formula for % Baseline RRS feed

  • Question

  • Hi,

    I'm new in Programme Management Office department, so I'm still abit lost in tracking correlated projects.

    In my master programme schedule, I've included 3 data categories:

    1) Start Date, Finish Date, Duration

    2) Actual Start Date, Actual Finish Date, Actual Duration

    3) Baseline Start Date, Baseline Finish Date, Baseline Duration

    and I've added 2 colums for % calculation:

    1) % Actual Duration Complete (Default name is "% Complete")

    2) % Baseline Complete (As of the status date what is the % the task should be at)

    So in comparing the % Actual Complete and % Baseline Complete, the formula I'm using for % Baseline Complete seems to be giving me different figure compare to the % Actual Complete even if the actual start date is exactly the same as was planned in the baseline. The % value in the summary level is even worse, a difference up to 20%.

    Appreciate if someone can help me out or guide me through this.

    The formula i use:

    IIf([Current Date]>[Baseline Finish],"100",IIf([Current Date]<[Baseline Start],"0",ProjDateDiff([Baseline Start],[Current Date])/[Baseline Duration]*100))

     

     

    Friday, November 11, 2011 6:00 AM

Answers

  • Hi,

    Project works aout every date by the minute.

    If you do not specify time of day:

    Finish dates are set to End of working time for that day

    Start dates are set to Start of working time for that day

    Current date is set to start of working ime for that day.

    So if Baseline Start is yesterday, difference with Curent date is 1 day.

    Hope this helps,

    Friday, November 18, 2011 8:25 AM
    Moderator

All replies

  • Sazlee69,

    While it might be useful to you to work through debugging this formula, more effective might be to point to you the concept of Earned Value  Analyis which is built in to Project.   I suspect you are thinking Project is like Excel; whereas there is a tremendous amount of standard computational capability into the product.  You don't need--nor do you really want--to do your own progress analysis.  

    As a starter, look in Help for "Earned Value Analysis". Supplement that with some articles I'm sure you can find on the Internet and there are scores of books written on the topic including in many of the books which focus on Microsoft Project.


    --rms www.rmschneider.com
    Friday, November 11, 2011 7:23 AM
  • Hi,

    There is absolutely nothing wrong with your formula.

    What gives a problem is your perception of how %complete is calculated. It is:

    100% Actual Duration/Duration

    Wherein Actual duration is something entered by you and NOT calcullated by Project based on current date. How do you enter actuals?

    When you enter actual duration, % complete is calculated as shown disregarding current date

    When you enter % Complete, it is accepted disregarding current date.

    Only when you neter "Mark on Track" will Project use current date for calculation - but even then you may have a one day difference since mark on track will mark work on current date as done whereas your formula assumes it isn't.

    Finally, it's not because you CAN do earned value analysis in Project that you SHOULD do it; it requires quite some learning, you have to enter cost data for resources etc. 

    Greetings,

    Friday, November 11, 2011 8:12 AM
    Moderator
  • Hi,

    Other source of difference between "Mark on Track" and your formula is task splits (which affect the actual duration but not your formula).

    In the the summary level your results become worse because the progress for summary task (assuming you mark everythin "On Track") is not (Today-Start)/Duration. It's [SUM of Sub-Tasks Actual Duration]/[SUM of Sub-Tasks Duration].

    For example, if Last week you worked on 4 week-long tasks and next week you will work on 1 week-long task, your formula would expect 50% progress (a week passed, a week remains) but you'll see an 80% progress (4 weeks of sub tasks actual duration out of 5 weeks of sub-tasks duration)

    Regards,

    Sunday, November 13, 2011 11:39 AM
  • Appreciate if you guys can let me know why is the % for both column (in red) seems abit unreal. I'm providing 3 scenarios with 3 different start dates: Parcel A, Parcel B, Parcel C. Let's say the current date / status date is 15/11/2011.

    Parcel A: Actual start date the baseline start date

    1) % Actual Duration Complete, it should be 50%.. (4days/8days * 100 = 50%)

    2) % Scheduled Completion (Baseline), it should be 87.5%.. (7days/8days * 100 = 87.5%)

     

    Parcel B: Actual start date the same as baseline start date

    1) % Actual Duration Complete, it should be 75% (6days/8days * 100 = 75%)

    2) Since the actual start date and baseline start date is the same, i supposed the % Scheduled Completion should be the same as well

     

    Parcel C: Actual start date earlier than baseline start date

    1) % Actual Duration Complete, it should be around 66% as of now since the task started 2 days earlier

    2) % Scheduled Completion, acceptable to be at 0% considering the task should only begin tomorrow


    Thank you

    Tuesday, November 15, 2011 2:46 AM
  • If you look at this one, the Actual Start Dates for both Parcel X & Y are the same with the Scheduled Baseline Start Date. Meaning both task is progressing as what was planned before.

    The current date is also 15/11/2011. The Actual Duration Days seems a little bit off if i do my own calculation, and also % Actual Duration Comple and % Scheduled Completion are not the same. Isn't it supposed to be the same? considering everything is on schedule.

    If i take the summary % of Project ABC, it shows that as of now we are supposed to be at 55% where the actual is saying we are behind schedule with only 47% which actually not true because we started the same day as the planned date.

    Tuesday, November 15, 2011 3:01 AM
  • Looks like I am late to join the party.  See if my white paper on "What %Complete Should I be" gives you any additional information.  It is at http://www.msprojectblog.com and select the MS Project Tips link.  It is the first couple of links on the page that appears.

     

    Hope that helps

     

    Jim


    If you feel this post answered the question, please vote for it. I am also available here:
    msprojectblog.com
    Tuesday, November 15, 2011 6:04 AM
    Moderator
  • Hi,

    How did you calculate (or have Project calculate) Actual Duration? When you say it is a bit off, off what? And since your formula and the formula for %Complete are ESSENTIALLY different why would the result be the same?

    Remember %Complete=Actual Duration/Duration DISREGARDING current date whereas your %Scheduled completion uses Current date in the formula. Just for testing, if you set Actual Duration on Parcel X to 0, %Complete becomes zero but %Scheduled is still 71%.

    Your assumption is clearly that when you start on the planned date, progress is also in line with plan. Again, Actual Duration is not automatically linked to current date (see my first post).

    Does this help?

    Tuesday, November 15, 2011 8:55 AM
    Moderator
  • I'm uploading it again for the missing attachment
    Wednesday, November 16, 2011 3:14 AM
  • Hi,

    Very nice, and all the figures are perfectly in line with what we discussed. So I suppose your problem is solved?

    Greetings,

    Wednesday, November 16, 2011 9:19 AM
    Moderator
  • But the % Complete doesnt seem to give a good comparison the the % Scheduled Baseline Completion. I have no idea whethere the default % Completion is going to provide me any good data or not.

     

    Formula for % Scheduled Baseline Completion (to date):

    IIf([Current Date]>[Baseline Finish],"100",IIf([Current Date]<[Baseline Start],"0",ProjDateDiff([Baseline Start],[Current Date])/[Baseline Duration]*100))

    Formula for % Actual Completion (to date):

    IIf([Current Date]>[Finish],"100",IIf([Current Date]<[Start],"0",ProjDateDiff([Actual Start],[Current Date])/[Duration]*100))

     

    Can i use the second formula to compare with % Scheduled Baseline? Both takes the current date into calculation. The first formula will give me % that I should be at today based on the baseline. And the second formula will give me % that I should be at today based on when my actual start date is. In this case i can compare the % that the project should be VS the % that we are.

    My another objectives is to eleminate the need of Project Managers to update PMO on % completion. Based on my experience, most of them is only a representative from each division and being considered as a PM for their own departments task. Therefore PMO will be integrating all projects info from each PM and consolidate into a program report. If the PM provides inaccurate % complete (normally based on their intuition they just plug in the number), the programme report after consolidation seems abit rubbish.

    Therefore my plan is after all the PM have submitted their first schedule plan and they been approved and set as baselines, on monthly basis they can just update this information:

    1) Actual start date & Actual end date

    2) Estimate end date (if the baseline date doesnt seem possible to them in the event if they started any tasks later than the baseline start date. For example if the baseline start date for a particular task is 1/1/11 and take 10 days, the team started only 2 days after the scheduled date and need the same number of days which is 10 days, they will update that the estimation finish date will be on 12/1/11 instead of 10/1/11 baseline start date.

    ** Baseline start date & Baseline finish date is fixed and visible to them in their PMO update form

    ** % Actual Completion (to date) will be updated by the PMO after they submit the information above and will be provided to them later on what is their % at as of today.

     

    Does this way make sense to you guys?

    Thursday, November 17, 2011 6:34 AM
  • It seems that % Complete is not very meaningful in your organization. It's more of a source of confusion. Consuder to report status by means other than % complete. 

    Just build your reports around end-dates. PMs will communucate Finish vs. Baseline Finish (or Finish Variance) and the PMO will assemble their program reports by reporting end-dates as well.

     

    Barak

    • Proposed as answer by Rob Schneider Thursday, November 17, 2011 7:05 AM
    Thursday, November 17, 2011 7:00 AM
  • Hi,

    I have two remarks, not on the formulae itself, and they will definitely provide some indication on progress but

    - Considering a task is 100% Complete only because its planned finish date is in the past is very very optimistic to me!!

    - Technically I do not understand when you say (quote) ** % Actual Completion (to date) will be updated by the PMO (end of quote); since % Actual Completon todate is calculated by a formula you can no longer "update" it as such.

    Greetings,

     

    Thursday, November 17, 2011 9:23 AM
    Moderator
  • "Considering a task is 100% Complete only because its planned finish date is in the past is very very optimistic to me!!"

    If the project managers are giving their own % of completion without any basis i believe that will be more worse. I understand that the 100% complete is a planned finish date, but if they do no give any new estimation of completion date indirectly it means their end dates are and still the same. The problem actually lies in how they declare their % completion, in this case to minimize inaccuracy of information, i believe it's better to dictate the % completion by taking into account the information given by them such as actual start date, actual finish, estimate end date, etc.

    "Technically I do not understand when you say (quote) ** % Actual Completion (to date) will be updated by the PMO (end of quote); since % Actual Completon todate is calculated by a formula you can no longer "update" it as such."

    Yah correct, % Actual Completion (to date) is calculated by a formual. But what i meant is instead of the Project Managers updating the % on their own in their own Microsoft Project, the PMO will be updating and provide them later with the updated schedule. The Project Managers should only provide the information such as Actual Start Date, Actual Finish Date, Estimate Finish Date (if they need more days or if they can finish earlier). In this way, there will be some sort of governance to the cohesive programme schedule. If let's say they update the % actual completion on their own, still they are getting the same % compare to if PMO updating it. As far as i concern, there's only one way to calculate % Duration Complete.

    I've thought about providing the programme committee and board with either:

    1) % physical complete

    2) % work complete

    However % physical complete need to include cost and resource for each task into the calculation. Where % work complete on the other hand requires detailed information up to hours and man hours. Both seems impossible if you are managing bigger projects that consists of 8-10 correlated projects.

    Friday, November 18, 2011 1:57 AM
  • 1) IIf([Current Date]>[Baseline Finish],"100",IIf([Current Date]<[Baseline Start],"0",ProjDateDiff([Baseline Start],[Current Date])/[Baseline Duration]*100))

    2) IIf([Current Date]>[Finish],"100",IIf([Current Date]<[Start],"0",ProjDateDiff([Actual Start],[Current Date])/[Duration]*100))

     

    For some reason i think both of this formulas, they kind of short 1 day. I've done some testing with the dates, the results seems to be short of 1 day thus the % completion also is actually not accurate..

    Anyone can confirm this?

    Friday, November 18, 2011 2:27 AM
  • Hi,

    Project works aout every date by the minute.

    If you do not specify time of day:

    Finish dates are set to End of working time for that day

    Start dates are set to Start of working time for that day

    Current date is set to start of working ime for that day.

    So if Baseline Start is yesterday, difference with Curent date is 1 day.

    Hope this helps,

    Friday, November 18, 2011 8:25 AM
    Moderator