none
Custom EV Calculations RRS feed

  • Question

  • Hi! I'm working on a construction project which would require some custom fields. I'm trying to create custom fields for the following calculations at Task level:

    Actual Percent of Completion = [Actual Cost] / Total Project Cost

    Target Percent of Completion = BCWS / Total Project Cost

    wherein [Actual Cost] is an inputted amount (based on cost incurred during that particular reporting period). this would preferably be computed from resource sheet but I would save that as a separate question so it will just be an inputted value for now.

    It may be basic (?) but I can't seem to find a formula to calculate for Total Project Cost. 

    Thanks in advance

    Sunday, July 2, 2017 6:25 PM

Answers

  • John,

    There is no separate field for total project cost but that value is shown in the Cost field of the Project Summary Task. So perhaps all you need to do is to display the Project Summary Task. Then you could use your custom field formulas but note that custom field formulas can only operate on data for each row.

    If you want to calculate your custom values for each task row, then you will need to use VBA. This simplified code will implement your two custom fields.

    Sub CustomPerCmplt()
    Dim t As Task
    Dim TCost As Single
    TCost = ActiveProject.ProjectSummaryTask.Cost
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Text1 = Format(CStr(t.ActualCost / TCost * 100), "#.00") & " %"
            t.Text2 = Format(CStr(t.BCWS / TCost * 100), "#.00") & " %"
        End If
    Next t
    End Sub

    John



    Monday, July 10, 2017 1:22 PM

All replies

  • You should re-name your "Actual Percent of Completion" to "% Cost Complete". MSP knows about duration, work and cost. There is already built in fields for % complete and % work complete and these are both actual/total, duration and work respectively) and total = actual + remaining. There is no equivalent built in field for "% Cost Complete" but it is easy to make one as you are doing. Actual is not the actual cost (or duration or work) incurred during the period, but rather is the cumulative actual from the start of the project

    Any help?

    Monday, July 3, 2017 12:26 AM
  • sounds a lot like earned value and it is built in project.  Right click in the corner of the gantt view and change the table to zEarned Value.  Right clicking shows a few tables.  Click on More tables to get to the earn value, earn value cost indicators and earned value schedule indicators.   Then read up on how earned value is managed in project

    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Monday, July 3, 2017 1:47 AM
    Moderator
  • Hi! Thanks for the response! Took some time for me to get familiarized with the built-in EV calculations in MSP.

    Anyhow, my question really is -- what is the field to use for "Total Project Cost"?

    If I can get the Total Project Cost, i can use it for my customized % Cost Complete formula

    Thanks in advance!

    Monday, July 10, 2017 3:39 AM
  • John,

    There is no separate field for total project cost but that value is shown in the Cost field of the Project Summary Task. So perhaps all you need to do is to display the Project Summary Task. Then you could use your custom field formulas but note that custom field formulas can only operate on data for each row.

    If you want to calculate your custom values for each task row, then you will need to use VBA. This simplified code will implement your two custom fields.

    Sub CustomPerCmplt()
    Dim t As Task
    Dim TCost As Single
    TCost = ActiveProject.ProjectSummaryTask.Cost
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Text1 = Format(CStr(t.ActualCost / TCost * 100), "#.00") & " %"
            t.Text2 = Format(CStr(t.BCWS / TCost * 100), "#.00") & " %"
        End If
    Next t
    End Sub

    John



    Monday, July 10, 2017 1:22 PM
  • I had that hunch that custom field formulas are only for a specific row. Thanks for confirming that. 

    I will start to play around with VBA. Looks like the value i need is taken from ActiveProject.ProjectSummaryTask.Cost. Thanks for providing the sample code!

    Thursday, July 13, 2017 6:44 AM
  • John, before you go down whatever track you are about to go down, pause for a minute.

    The "total project cost" is already in the cost field for the project summary task. It will be made up of two parts, actual and remaining.

    From your original post, It would appear that you want to calculate the actual cost of a single task (or every task) as a fraction of the project total cost.

    As you see, however, a formula in a custom field can only work on or with the built in and custom field values in each row, and the cost on the project summary row is nort accessible by a formula in another row.

    Now I am not saying don't use the VBA, especially since John has kindly provided it, but wouldn't it be just as easy to just copy the cost of the project summary and paste it into a spare cost field and the fill it down to all task rows?

    Thursday, July 13, 2017 7:54 AM
  • Hi Trevor,

    Your response is very much appreciated. I guess the intention is to try to automate the process as much as possible. We are expecting several hundred tasks (e.g., 500+ housing units per construction project) and it may be a bit cumbersome for the construction engineer to keep on doing copy+paste for each additional unit (housing units may come in several batches). 

    Monday, July 17, 2017 3:14 AM