Sec L Attachment 14 - DCMA 14 Point SHA Macro.pdf

14PA Macro Files, Installation and Usage Guide

DEFENSE CONTRACT MANAGEMENT AGENCY

Earned Value Management Center 6350 Walker Lane

Alexandria, VA 22310

Document Control ID: EVC-110_Rev1 Vista/Win7

• The following slides show how to:

– Install the MS Project-based macro on your computer in either a MS Project 2003 or 2007 environment

– Use the macro to perform the 14 Point Assessment

• What’s the value?

– After gaining an important understanding of how the 14 Point Assessment is performed “manually”, the macro can calculate 12 of the 14 metrics in a matter of minutes with complete accuracy

– The macro can automatically generate a MS PowerPoint chart that displays the results of the 14 Point Assessment

– The macro can automatically fill in the “Metrics Colors” tab of the Excel Analysis Template that was used during this training

MS Project Macro

Using the MS Project macro saves time and ensures accuracy!

MS Project Macro Installation

• For WindowsXP, Vista or Windows 7, download this file:

or email: Erik.Berg@dcma.mil for the latest files mailto:Erik.Berg@dcma.mil�

Macro Installation Cont’d

• Second, open the installation instructions below:

Macro Instructions – Step 1

OPEN THE SCHEDULE FILE YOU WISH TO ANALYZE

FOR THIS EXAMPLE, THE SAME ACME SCHEDULE FILE WILL BE

ANALYZED USING THE MACRO

NOTE: WHEN USING THE MACRO, IT IS NOT NECESSARY TO

CREATE THE 14 POINT ASSESSMENT “TABLE” AND “VIEW” IN

ORDER FOR THE MACRO TO RUN PROPERLY

THE FOLLOWING INSTRUCTIONS ARE THE SAME FOR BOTH MS

PROJECT 2003 AND 2007

Macro Instructions – Step 2

CLICK ON “File” IN THE MENU BAR

CLICK “Save As…”

IT IS A GOOD IDEA TO SAVE THE FILE AS A DIFFERENT NAME

PRIOR TO RUNNING THE MACRO (OR WHENEVER DOING ANY

SORT OF ASSESSMENT)

THE PURPOSE IS TO PRESERVE THE SCHEDULE FILE IN ITS

ORIGINAL FORM

Macro Instructions – Step 3

SAVE TO “My Documents” OR

SOME OTHER LOCATION

RENAME THE FILE

SIMILAR TO AS SHOWN

CLICK “Save”

Macro Instructions – Step 4

TO BEGIN THE PROCESS OF

RUNNING THE MACRO, CLICK ON

THE BUTTON CALLED

“RunScheduleMetrics”

NOTE: IF YOU DO NOT SEE THIS BUTTON, THERE IS A PROBLEM

WITH YOUR INSTALLATION – REPEAT THE INSTALLATION

STEPS. IF THE PROBLEM STILL EXISTS, CONTACT

ERIK.BERG@DCMA.MIL FOR FURTHER ASSISTANCE

ERIK BERG IS NOT THE AUTHOR OF THIS MACRO, BUT IS THE

POC FOR INQUIRIES

mailto:ERIK.BERG@DCMA.MIL�

Macro Instructions – Step 5

AFTER CLICKING THE BUTTON, YOU WILL SEE THIS POP UP

WINDOW. THIS IS THE INPUT SCREEN FOR THE MACRO. ONCE YOU

PROVIDE THE NEEDED INFORMATION AND MAKE THE APPROPRIATE

SELECTIONS, THE MACRO WILL COMPLETE THE ASSESSMENT

Macro Instructions – Step 6

NOTICE THE ADVISORIES AT THE

TOP OF THE POP UP WINDOW

BY DEFAULT, THE “Text6” FIELD

WILL APPEAR HERE – PRIOR TO

MAKING A SELECTION, YOU NEED

TO DETERMINE WHICH “Text” FIELD

IS BEING USED BY THE

CONTRACTOR FOR STORING THE

EV METHOD INFORMATION

Macro Instructions – Step 7

CLICK THE DOWN ARROW TO

SELECT THE PROPER “Text” FIELD

THAT HAS THE EV METHOD

FOR THE ACME SCHEDULE, THE EV

METHOD IS CONTAINED IN THE

“Text6” FIELD

Macro Instructions – Step 8

SELECT FROM THE LIST OR TYPE IN MANUALLY (IF NOT

AVAILABLE IN THE LIST) HOW THE CONTRACTOR IDENTIFIES

LEVEL OF EFFORT TASKS IN THE “Text” FIELD CHOSEN

FOR THE ACME SCHEDULE, LOE

TASKS ARE IDENTIFIED AS “LOE”

IN THE “Text6” FIELD

Macro Instructions – Step 9

IF THE CONTRACTOR IS USING A

“ROLLING WAVE” FOR DETAIL

PLANNING THE SCHEDULE, THIS

CHECK BOX MUST BE SELECTED

FILTERING PLANING PACKAGES IS ONLY FOR THE HIGH DURATION

METRIC (#8). PLANNING PACKAGES, BY DEFINITION, ARE LONGER IN

DURATION AND SHOULD THEREFORE BE EXCLUDED FROM THE

HIGH DURATION METRIC

AS A SIDE NOTE, OTHER METRICS SUCH AS LOGIC, LEADS, LAGS,

FLOAT, ETC ALL APPLY TO PLANNING PACKAGES – THE DURATION

METRIC IS THE ONLY EXCEPTION

Macro Instructions – Step 10

SELECT THIS CHECK BOX

THEN ASK THE PROGRAM SCHEDULER

THROUGH WHICH CALENDAR DATE HAS

THE SCHEDULE BEEN DETAIL PLANNED

(I.E. THE “CUT OFF” DATE)

ALSO, CHECK THE CONTRACTOR’S EVM SYSTEM DESCRIPTION

DOCUMENT FOR ROLLING WAVE GUIDANCE

A SIDE BENEFIT TO USING THIS FEATURE IS THAT THE MACRO

RESULTS WILL SHOW YOU ANY TASKS THAT ARE IDENTIFIED AS

PLANNING PACKAGES THAT ARE INSIDE THE DETAIL PLANNED

PERIOD (THIS IS A PROBLEM)

Macro Instructions – Step 11

NEXT, THE “CUT OFF” DATE SHOULD BE

SELECTED (FOR WHICH ALL TASKS WITH

BASELINE START DATES ON OR BEFORE

THIS DATE MUST BE DETAIL PLANNED)

CLICK “Okay”

Macro Instructions – Step 12

THE PRIMARY ISSUE WITH USING THE ALTERNATIVE METHOD

ABOVE IS THAT THE PLANNING PACKAGE IDENTIFIER WILL

SOMETIMES BE INACCURATE AND UNRELIABLE – IT IS BETTER TO

USE THE “CUT OFF” DATE METHOD FROM THE PREVIOUS STEPS

AS AN ALTERNATIVE TO THE

PREFERRED METHOD OF SELECTING A

“CUT OFF” DATE, THE MACRO GIVES

YOU THE OPTION OF SPECIFYING THE

FIELD THAT DESIGNATES THE PLANNING

PACKAGES

Macro Instructions – Step 13

IN THIS EXAMPLE, THE ACME FILE IS

DETAIL PLANNED THRU THE END OF THE

CONTRACT – THEREFORE, DE-SELECT

THE CHECK BOX TO CONTINUE

SINCE THE ACME FILE IS DETAILED PLANNED THRU THE END OF

THE CONTRACT, THERE ARE NO PLANNING PACKAGES AND

THEREFORE NO NEED TO UTILIZE THIS MACRO FEATURE

Macro Instructions – Step 14

THE MACRO GIVES YOU THE OPTION OF

TAILORING THE THRESHOLDS FOR THE

HIGH FLOAT AND HIGH DURATION

METRICS

THE THRESHOLDS SHOULD REMAIN AS “44” WHEN PERFORMING A

STANDARD 14 POINT ASSESSMENT – HOWEVER, TAILORING THE

THRESHOLDS MAY BE USEFUL WHEN “DIGGING DEEPER” IN YOUR

ANALYSIS (I.E. FINDING THE “LOW HANGING FRUIT”, > 200, ETC)

Macro Instructions – Step 15

CHECK THE “Log Failed Tasks in Excel”

OPTION IF YOU WOULD LIKE THE MACRO TO

CREATE AN EXCEL FILE THAT LISTS THE

POSSIBLE ISSUES FOUND FOR EACH

METRIC – THIS IS HIGHLY RECOMMENDED

Macro Instructions – Step 16

CHECK THE “Log Total, Completed and Baseline Tasks” OPTION IF YOU WOULD LIKE THE MACRO

TO CREATE ADDITIONAL TABS WITHIN THE

EXCEL FILE FOR THESE ITEMS OF INTEREST

Macro Instructions – Step 17

NOTICE THE FOLDER LOCATION WHERE THE

EXCEL LOG FILE WILL BE CREATED – THIS IS THE

SAME LOCATION OF THE SCHEDULE FILE BEING

ANALYZED

Macro Instructions – Step 18

CLICK THE “OK” BUTTON TO

BEGIN THE MACRO ASSESSMENT

Macro Instructions – Step 19

CLICK THE “OK” BUTTON

TO CONTINUE

THE BELOW DIALOGUE BOX INFORMS YOU THAT THE EXCEL LOG FILE

WAS CREATED AT THE SPECIFIED LOCATION

Macro Instructions – Step 20

THE BELOW DIALOGUE BOX WILL APPEAR SHOWING YOU THE

PROGRESS OF THE MACRO

Macro Instructions – Step 21

WHEN THE MACRO HAS COMPLETED THE ASSESSMENT, THE RESULTS

SCREEN WILL APPEAR AS SHOWN

Macro Instructions – Step 22

THIS SECTION GIVES THE FILE NAME

AND STATUS DATE OF THE SCHEDULE

AT PRESENT, THE MACRO IS

UNABLE TO AUTOMATICALLY

PERFORM METRICS #12 AND #13

THIS SECTION PRESENTS THE

RESULTS OF THE ASSESSMENT IN A

“QUICK LOOK” FORMAT

Macro Instructions – Step 23

NOTICE HOW THE RESULTS MATCH

THE “MANUAL METHOD” PERFECTLY

TO CREATE A MS POWERPOINT CHART

OF THE RESULTS, CLICK THIS BUTTON

Macro Instructions – Step 24

TO CONTINUE

THE BELOW DIALOGUE BOX INFORMS YOU THAT THE POWERPOINT

FILE WAS CREATED AT THE SPECIFIED LOCATION

NOTICE THAT THE LOCATION IS THE SAME AS THE LOCATION OF THE

SCHEDULE FILE AND THE EXCEL LOG FILE

Macro Instructions – Step 25

TO OPEN THE EXCEL LOG FILE AND

THE POWERPOINT CHART, GO TO

THE SPECIFIED LOCATION FROM THE

PREVIOUS STEPS (I.E. “My

Documents”, ETC)

Macro Instructions – Step 26

NOTICE THE EXCEL LOG FILE AND

POWERPOINT CHART THAT WERE

CREATED BY THE MACRO

Macro Instructions – Step 27

FIRST, DOUBLE CLICK THE EXCEL LOG

FILE TO OPEN IT

Macro Instructions – Step 28

AT A MINIMUM, EACH TAB WITHIN THE EXCEL

LOG FILE WILL CONTAIN THE “Unique ID” AND

“Task Name” FIELDS WHICH ENABLE

TRACEABILITY BACK TO THE SCHEDULE FILE

THERE ARE NUMEROUS TABS AT THE

BOTTOM OF THE EXCEL LOG FILE – EACH

ONE CORRESPONDS TO A 14 POINT

ASSESSMENT METRIC OR STATISTIC

Macro Instructions – Step 29

IN THIS EXAMPLE FOR THE

“Missed Tasks” METRIC

TAB, IT DISPLAYS ALL OF

THE TASKS THAT HAVE

FINISHED LATE OR ARE

EXPECTED TO FINISH LATE

(FOR THOSE TASKS WITH

BASELINE FINISH DATES

ON OR BEFORE THE

STATUS DATE)

Macro Instructions – Step 30

AT THIS POINT, YOU SHOULD USE THE EXCEL LOG FILE TO

PERFORM A MORE DETAILED ASSESSMENT. FOR EXAMPLE,

YOU MAY WANT TO UTILIZE EXCEL’S AUTOFILTER FEATURE TO

DO ADDITIONAL RESEARCH (I.E. NUMBER OF TASKS WITH

TOTAL FLOAT GREATER THAN 200 DAYS, ETC)

Macro Instructions – Step 31

NEXT, DOUBLE CLICK THE

POWERPOINT FILE TO VIEW THE 14

POINT ASSESSMENT MACRO RESULTS

Macro Instructions – Step 32

THE RESULTS DISPLAYED IN THIS CHART MATCH THE “QUICK LOOK”

RESULTS FROM THE MACRO IN MS PROJECT

Macro Instructions – Step 33

FOR THE CRITICAL PATH TEST AND

CPLI, THE RESULTS CAN BE INPUT

MANUALLY INTO THIS CHART BY

SIMPLY EDITING THE CELLS

Macro Instructions – Step 34

FOR THE ACME SCHEDULE, THE

RESULTS WERE MANUALLY INPUT

AS SHOWN

TO PASTE THE RESULTS INTO

THE EXCEL ANALYSIS

TEMPLATE, CLICK THIS BUTTON

Macro Instructions – Step 35

AFTER RE-RUNNING THE MACRO, YOU CAN SELECT THE OPTION TO

“PASTE RESULTS INTO EXCEL” IF YOU WISH TO PASTE THE MACRO

RESULTS INTO THE EXCEL ANALYSIS TEMPLATE

FIND THE LOCATION OF THE

EXCEL ANALYSIS TEMPLATE

“Schedule Analysis V1.xlsx”

Macro Instructions – Step 36

CLICK ON THE FILE

CLICK “Open” AND

THE MACRO WILL

PASTE THE DATA

INTO THE FILE

Macro Instructions – Step 37

TO CONTINUE

THE BELOW DIALOGUE BOX INFORMS YOU THAT THE MACRO

ASSESSMENT RESULTS WERE SUCCESSFULLY PASTED INTO THE FILE

Macro Instructions – Step 38

GO TO THE FOLDER LOCATION WHERE THE EXCEL ANALYSIS

TEMPLATE IS LOCATED AND OPEN THE FILE

NOTICE HOW THE MACRO

AUTOMATICALLY PASTED THE

ASSESSMENT RESULTS DIRECTLY

INTO THE FIRST AVAILABLE ROW

Macro Demo Complete

• The preceding slides showed you how to install and operate the macro

• If you have questions, suggestions, or need assistance – please contact:

Erik.Berg@dcma.mil mailto:Erik.Berg@dcma.mil�

Troubleshooting

• A common issue encountered while installing the macro is that the “RunScheduleMetrics” button (from Step 4) does not show after installation is complete

• If you are having this issue, follow the steps in the following slides to enable the “RunScheduleMetrics” button on your screen

1. Right Click Near the Toolbars and Select Customize.

2. Click New and enter a name (e.g. “Metrics Macro”) for the new toolbar, Click OK.

3. A new Toolbar appears.

4. Click the middle tab, Commands, then scroll down to All Macros.

5. Highlight RunScheduleMetrics.

6. Drag the highlighted command to the new toolbar.

7. Drag the new toolbar to the toolbar area at the top of the Project Window.