none
Calculating the Forecased % Complete RRS feed

  • Question

  • Hello,  I am looking to calculate the forecasted % planned to calculate what the planned % is at any future time in the project just by changing the status date.  I am using Project 2010.

    Does anyone know how to do this?

    Thank you in advance for your assistance.

    William

    Saturday, July 14, 2018 10:43 PM

Answers

  • Wiliam,

    This type of metric has been asked and addressed many times over the years in this forum. The approach to get a "planned % complete" value depends on a basic definition, should the "planned" value be based on the current schedule or on the baseline? If it's equated to the methodology used in earned value "planned" values should be based on baseline data.

    As far as the steps you show I'm not sure where you found them but they are way more complex than necessary. As noted, all time based data in Project is stored in minutes and since the desired end result is a percentage, there is no advantage in converting date differences or durations into days.

    Given the assertion that a planned value should be based on baseline data, the following formula gives a planned percent complete"

    Text1=IIf([Status Date]<[Baseline Start],"0%",IIf([Status Date]>[Baseline Finish],"100%",ProjDateDiff([Baseline Start],[Status Date])/[Baseline Duration]*100 & "%"))

    Hope this helps.

    John


    • Edited by John - Project Sunday, July 15, 2018 6:43 PM spelling/grammar
    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Sunday, July 15, 2018 4:23 PM
  • I would probably approach this by using the update project tool (Project, Update Project), selecting the date as required and recording the % complete. Repeat the process for each reporting period.

    You can save a copy each time for traceability.

    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Monday, July 16, 2018 9:38 AM
  • William,

    Percent complete at summary level is a weighted average. The formula Project uses is:

    (sum of subtask actual durations)/(sum of subtask durations) x 100%

    However in your case you want to calculate an expected value and you can't use a weighted average approach with custom field formulas since a custom field formula can only operate on data for a single task line (i.e. it can't sum up a weighted values from other task lines)

    You can however go with a simple average for summary lines if you use a custom number field (e.g. Number1) for your expected value formula and select "average first sublevel" as the rollup option for Calculation of task and group summary rows in the Custom Fields window. Note, you will not be able to show the "%" symbol directly in the Number field but you could translate that number field to another custom Text field. It all depends on how complex you wanna get.

    Again, if we answered your question, please consider marking one of our responses as the answer. Note, that will not prevent further questions or discussion, it only gives credit to those of us who are providing the help.

    Hope this helps.

    John

    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Monday, August 6, 2018 5:59 PM

All replies

  • I am do this but get a #ERROR at step 2

    MS Project Planned % Complete

    In MS Project there is no Planned % Complete Column to show the forecast. The following steps will give us the %Planned for entire project.

    STEP 1:

    MS Project stores the duration in minutes, in order to use duration in days in our calculation we have to create column based on duration in days.

    Right click any column and select insert column. Then select field name as Number1.

    Then right click Number1 column and select Custom Field. On the custom attributes click on the radio button “Formula” and then type copy paste the formula as below and press OK.

    Val(ProjDurConv(Duration,pjDays))

    In the next section click on “Use formula” radio button and click OK.    

     STEP 2:

    Based on the start date of the project and the status date, calculate the days elapsed. This is required to calculate the percentage.

    Right click any column and select insert column. Then select field name as Number2.

    Then right click Number2 column and select Custom Field. On the custom attributes click on the radio button “Formula” and then type copy paste the formula as below and press OK.

    IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff("d",[Baseline Start],[Status Date])<=0,0,ProjDateDiff(Start,[Status Date])/480))

    In the next section click on “Use formula” radio button and click OK.

     

    STEP 3:

    To get the planned value right click any column and select insert column. Then select field name as Number3.

    Then right click Number3 column and select Custom Field. On the custom attributes click on the radio button “Formula” and then type copy paste the formula as below and press OK.

    Number2/Number1

    In the next section click on “Use formula” radio button and click OK.

     

    STEP 04:

    To display the percentage complete field as text with a % sign, create a new text field column and in the formula format the Number3 field.

    Right click any column and select insert column. Then select field name as Text2.

    Format(Number3,"0%")

    In the next section click on “Use formula” radio button and rename the Text 2 by %Planned.

    Hide Columns Number1 to Number3


    Saturday, July 14, 2018 10:50 PM
  • Wiliam,

    This type of metric has been asked and addressed many times over the years in this forum. The approach to get a "planned % complete" value depends on a basic definition, should the "planned" value be based on the current schedule or on the baseline? If it's equated to the methodology used in earned value "planned" values should be based on baseline data.

    As far as the steps you show I'm not sure where you found them but they are way more complex than necessary. As noted, all time based data in Project is stored in minutes and since the desired end result is a percentage, there is no advantage in converting date differences or durations into days.

    Given the assertion that a planned value should be based on baseline data, the following formula gives a planned percent complete"

    Text1=IIf([Status Date]<[Baseline Start],"0%",IIf([Status Date]>[Baseline Finish],"100%",ProjDateDiff([Baseline Start],[Status Date])/[Baseline Duration]*100 & "%"))

    Hope this helps.

    John


    • Edited by John - Project Sunday, July 15, 2018 6:43 PM spelling/grammar
    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Sunday, July 15, 2018 4:23 PM
  • I would probably approach this by using the update project tool (Project, Update Project), selecting the date as required and recording the % complete. Repeat the process for each reporting period.

    You can save a copy each time for traceability.

    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Monday, July 16, 2018 9:38 AM
  • ok sounds great.  thank you for your feedback as I am a new MS Project user.  I will try this right away.

    thanks again.

    Sunday, July 29, 2018 3:52 PM
  • William MC D,

    I'm not sure who's suggestion your are responding to but you're welcome and thanks for the feedback. If your question was answered, please consider marking the appropriate response as the answer.

    John

    Sunday, July 29, 2018 8:13 PM
  • Hi John,

    Your approach seems to work here so thank you very much.  Do you also know how to get the roll up % as well?  this formula works for individual lowest level sub-tasks but does not calculate any higher levels.

    thanks,

    William

    Monday, August 6, 2018 4:51 PM
  • William,

    Percent complete at summary level is a weighted average. The formula Project uses is:

    (sum of subtask actual durations)/(sum of subtask durations) x 100%

    However in your case you want to calculate an expected value and you can't use a weighted average approach with custom field formulas since a custom field formula can only operate on data for a single task line (i.e. it can't sum up a weighted values from other task lines)

    You can however go with a simple average for summary lines if you use a custom number field (e.g. Number1) for your expected value formula and select "average first sublevel" as the rollup option for Calculation of task and group summary rows in the Custom Fields window. Note, you will not be able to show the "%" symbol directly in the Number field but you could translate that number field to another custom Text field. It all depends on how complex you wanna get.

    Again, if we answered your question, please consider marking one of our responses as the answer. Note, that will not prevent further questions or discussion, it only gives credit to those of us who are providing the help.

    Hope this helps.

    John

    • Marked as answer by William MC D Monday, August 6, 2018 6:31 PM
    Monday, August 6, 2018 5:59 PM
  • Hi John, thank you again for the information.

    I will try what you have suggested.

    Cheers,

    William

    Monday, August 6, 2018 6:31 PM
  • William,

    You're welcome and thanks for the feedback.

    John

    Monday, August 6, 2018 7:53 PM
  • Hi, I have been working with above formulas, but i am not getting planned % completion at header level, its showing only in individual talk level. can you help out solving this.
    Tuesday, October 23, 2018 11:04 AM
  • Update the status date, also if duration is Zero ERROR text will show.

    Thanks 

    Nithin

    Tuesday, October 23, 2018 11:06 AM
  • Hi Wiliam,

    Above Formula helps but in header task its not showing any percentage completion only in talk level. Because of this its not possible to know what is overall status of project(where on track or off track against % work completed). Hope you can help.

    Thanks,

    Nithin B

    Tuesday, October 23, 2018 11:10 AM
  • Hi John,

    Can you please elaborate, above explanation. Still not able to sum up % completion at header level.

    Thanks,

    Nithin B

    Tuesday, October 23, 2018 11:50 AM