none
VBA Help with Summary Tasks RRS feed

  • Question

  • It does't look like MSP allows you to manually enter your Actual Costs at a Summary Level.  Is this in fact the case?  I am not aware of many project managers who capture Actual Cost at the Task level - most of the time you are capturing Actual Costs a higher rollup/summary level.

    Does anyone know how I could create a custom field that would let me enter a manual entry and if nothing gets entered and the number is 0, do a sum of all the children tasks below that summary?  I assume I could do this with VBA, but am unsure how.

    Thursday, April 14, 2011 5:21 PM

Answers

  • This solution requires VBA, not a formula.  There is either a formula, or there is not ... can't dynamically switch.

    I challenge your EV method.  EV is not a function of Actual Cost. EV is a measure of value, independent of the cost it took to create the value.  Just because Joe charged 3 hours does not mean you got 3 hours of value from him.  It is a very very rare circumstance the EV is a function of hours or costs incurred, even in Level of Effort.  Example, you bid Joe at 8 hours to "paint this room" ... If he finishes the room in 3 hours, you do not care ... he earned 8 hours of value (the job is complete). I he painted 1 wall in 3 hours you are behind schedule and may over run.  In neither case is EV a function of the time charge of 3 hours.

    I think you need to do two things.  First, use the Physical%Complete method of EV, this is not the default method but is easily changed.  If you need instructions post back.

    Second, you need to set project to calculate actual costs manually, not the default but easily configured.  Now, if you want to "Hand Jam" the Actual Costs into the Actual Cost column feel free to do so.  I do agree that management rarely has visibility to costs so descretely and it cannot be entered on summary tasks... but the Custom Cost fields can be configured to allow manual entry.

    We have a separate task for Actual Costs for each charge line.  We then create a custom text field (Text1) that contains the appropriate charge item (like a job number, etc.)  I enter the AC on the "Actual Cost - Acct 1234" line.  Since many tasks in the file have a "Text1" value equal to Acct1234, (including the line where I entered the actual cost) I can then create a "Group By" and the BCWS, ACWP, BCWP all sum up nicely by Acct1234.  (Plus all the other accounts as needed).

    To learn more about Physical%Complete, please read my blog www.msprojectblog.com and search for the white papers on "What % Complete Should I be" .... it should be the first two or three papers on the "Pojrect Tips" page.

    If you insist on an Actual Cost formula to calculate EV, please post back your specific criteria and lets see what the "real situation" is, not the hypotheticals posted.  A real example will assist greatly.

     

     

     


    If you feel this post answered the question, please vote for it. I am also available here:
    msprojectblog.com
    Thursday, April 14, 2011 9:06 PM
    Moderator
  • Jim has already explained the things excellently, so if still you want a macro for your calculations please post it on MS Project Customization thread : http://social.microsoft.com/Forums/en-US/project2010custprog/threads

     


    Sapna S
    Friday, April 15, 2011 5:49 AM
    Moderator

All replies

  • Hello cwilkins59,

    Actual Cost is calculated based on the % Complete when cost based resources are assigned to a task, hence it is not editable but calculated. If you want to add any individual tasks specific costs add Fixed Cost Column, it gives you flexibility to add other fixed costs

    But remember Fixed Costs don't roll-up to summary, but all the costs do roll up and get summed up in Total Cost field. Apply Cost table and you can see how everything gets calculated.

    I think combination of Fixed costs and Total Cost fields could help you achieve what you are looking for. Still if you think it doesn't resolve the purpose, you can insert Cost 1, enter your costs per task and then go and customise it by saying that Summary task pulls SUM from the drop down for Summary calculations. But remember this Custom Cost won't be considered in defualt cost calculations.


    Sapna S
    Thursday, April 14, 2011 6:44 PM
    Moderator
  • I am trying to create custom Earned Value fields since MSP doesn't do exactly what we would like.  I will base the EV Calculations on the custom Actual Costs that I enter.  Is there VBA that will look at a summary task and if that summary task is 0, the it sum's all of the children to that summary.  On another note, does MSP keep history of EV?
    Thursday, April 14, 2011 6:52 PM
  • EV is an internally calculated field based on the Baseline Costs and % Complete (hence ACWP) that can't be calculated based on macros, as for sum of Summary costs I already told you to SUM up the Cost 1 field, no history for EV is kept by defualt, but you can keep it using Custom fields. And if you base your calcualtions on your Formula based costs and EV, that won't be included in MSP's EV the only option would be your own Costs and your own Formula based calculations that may or may not match with MSP's EV. Custom Costs can't be included in EV to over ride its calculation as far as I know.
    Sapna S
    Thursday, April 14, 2011 7:01 PM
    Moderator
  • I understand how the EV fields work.  I am simply creating my own that will perform the calculations that I define on the Custom fields in the formula.  I understand that you can have a custom field Sum at the summary level automatically, but I want to be able to enter a number there and if I dont enter a number, then I want the summary to sum the children.

    Thursday, April 14, 2011 7:05 PM
  • Adding a number at summary is possible if you say Summary Calculation as None -it keeps it in editable format, now you can have in 1 Cost field only in 1 format either None or Sum. Please let me know if I m not understanding the question appropriately.
    Sapna S
    Thursday, April 14, 2011 7:09 PM
    Moderator
  • Ok, lets try it this way.  Do not think about the summary calculation field when you create a custom field.  I basically want it to do this

    If(Expression, True, False)

    If(Summary Task = 0,Summary Task  = Sum all children, keep it what it is)

    Thursday, April 14, 2011 7:43 PM
  • This solution requires VBA, not a formula.  There is either a formula, or there is not ... can't dynamically switch.

    I challenge your EV method.  EV is not a function of Actual Cost. EV is a measure of value, independent of the cost it took to create the value.  Just because Joe charged 3 hours does not mean you got 3 hours of value from him.  It is a very very rare circumstance the EV is a function of hours or costs incurred, even in Level of Effort.  Example, you bid Joe at 8 hours to "paint this room" ... If he finishes the room in 3 hours, you do not care ... he earned 8 hours of value (the job is complete). I he painted 1 wall in 3 hours you are behind schedule and may over run.  In neither case is EV a function of the time charge of 3 hours.

    I think you need to do two things.  First, use the Physical%Complete method of EV, this is not the default method but is easily changed.  If you need instructions post back.

    Second, you need to set project to calculate actual costs manually, not the default but easily configured.  Now, if you want to "Hand Jam" the Actual Costs into the Actual Cost column feel free to do so.  I do agree that management rarely has visibility to costs so descretely and it cannot be entered on summary tasks... but the Custom Cost fields can be configured to allow manual entry.

    We have a separate task for Actual Costs for each charge line.  We then create a custom text field (Text1) that contains the appropriate charge item (like a job number, etc.)  I enter the AC on the "Actual Cost - Acct 1234" line.  Since many tasks in the file have a "Text1" value equal to Acct1234, (including the line where I entered the actual cost) I can then create a "Group By" and the BCWS, ACWP, BCWP all sum up nicely by Acct1234.  (Plus all the other accounts as needed).

    To learn more about Physical%Complete, please read my blog www.msprojectblog.com and search for the white papers on "What % Complete Should I be" .... it should be the first two or three papers on the "Pojrect Tips" page.

    If you insist on an Actual Cost formula to calculate EV, please post back your specific criteria and lets see what the "real situation" is, not the hypotheticals posted.  A real example will assist greatly.

     

     

     


    If you feel this post answered the question, please vote for it. I am also available here:
    msprojectblog.com
    Thursday, April 14, 2011 9:06 PM
    Moderator
  • Jim has already explained the things excellently, so if still you want a macro for your calculations please post it on MS Project Customization thread : http://social.microsoft.com/Forums/en-US/project2010custprog/threads

     


    Sapna S
    Friday, April 15, 2011 5:49 AM
    Moderator
  • I think I have caused a lot of confusion on this discussion and I apoligize for that!  I, in no way, was trying to calculate EV using a Actual Cost formula.  We are doing some custom things and all I really wanted to know was if there was a way create the VBA code that did what I showed up above with the If statement.
    Friday, April 15, 2011 12:42 PM
  • No Probs, you can post the query on Customization thread, the link I shared in last post.
    Sapna S
    Friday, April 15, 2011 12:45 PM
    Moderator