14 point DCMA Review

Oliver,

John's formula demonstrates the most efficient way to count FS relationships from predecessor lists - i.e. those that are not explicitly defined as one of the other three types. Unfortunately, the metric itself (#4 of the DCMA 14-Point Schedule Assessment) quantifies the number of relationships, not the number of tasks, in both the numerator and the denominator.  Although many tasks in a typical project have only one predecessor, some tasks have many predecessors, each of which must be categorized and counted.  Parsing the predecessor strings using custom field formulas is impractical in this general case.  In addition, the metric's exclusion of many tasks and associated relationships further complicates the calculations.

As a rule, performing and summarizing the entire 14-point assessement - particularly the trigger-value percentage calculations - is not well suited to the restrictive task-table structure in Project.  Exporting the data and performing all the calculations in Excel is pretty straightforward as a do-it-yourself solution for those who like the DCMA metrics (not me.)  There are also any number of 3rd-party tools that make pretty-colored dashboards based on the DCMA criteria.

To address Metric #4 solely within Project, then I'd suggest the following:

1. Use vba to populate two custom number fields: one counting all relationships and the other counting FS relationships.  (I've attached a simple code block for Number11 and Number12 below. The code excludes milestones and completed tasks but does not exclude hammocks/LOEs/SVTs, which are typically identified by special flags or naming conventions.)  Set these two fields to rollup sums to the summary tasks.
2. Add a third custom number field with a formula to compute the ratio between the first two fields.  Set the summary tasks on this field to use the formula.  You could add a text field to hold a formatted version of this number if the "%" is important to your audience.
3. At the end of all this, the ONLY number of any real consequence is the third number field in the Project Summary task.

Sub CountDCMARelationships()

'This code adds up total relationships (Number11) and total FS relationships (Number12)

'for incomplete, non-summary, non-milestone tasks, as defined by DCMA Metric #4

Dim t As Task

Dim d As TaskDependency

Dim CountAll As Integer

Dim CountFS As Integer

For Each t In ActiveProject.Tasks

    If Not t Is Nothing Then 'to prevent errors on blank lines

        If Not t.Summary Then

            CountAll = 0

            CountFS = 0

            If Not t.Milestone And Not t.Duration = 0 _

                  And t.ActualFinish = "NA" Then

                For Each d In t.TaskDependencies

                     'Check predecessors only

                     If d.To = t Then

                         CountAll = CountAll + 1

                         If d.Type = pjFinishToStart Then CountFS = CountFS + 1

                     End If

                Next d

            End If

            t.Number11 = CountAll

            t.Number12 = CountFS

        End If

    End If

Next t

End Sub

Oliver,

Okay, I agree with the concept. One hundred percent of the tasks in the file should be part of the plan's network logic (i.e. no independent tasks), and in the back of my mind, (lotta cobwebs back there), I do kinda remember something about a 90% metric.

You do NOT need to look at both predecessors and successors to calculate the metric. Tasks with a successor only will automatically fall into the "no FS relationship" group.

Try this formula in a custom number field and use rollup "sum" for Summary lines.

Number1=IIf([Predecessors]>"" And instr(1,[Predecessors],"ss")=0 And instr(1,[Predecessors],"ff")=0 And instr(1,[Predecessors],"sf")=0,1,0)

Then from the total number of tasks in the file, subtract any tasks that start on the Project Start Date (PSD), perhaps sum those up in another custom Number field. The metric will then be

Project Summary Task Number1 field / (Total file task count - tasks starting on PSD).

Here's what it might look like with a super simple file.

Does that get you there?

John