western winter workshop implementing project management tools february 9, 2002

Western Winter Workshop

Implementing Project Management Tools

February 9, 2002

Managing Cost Data Using Expedition and P3

Mark Greenhalgh

3

Simple Goal

Put accounting data, actual $, into two software packages: Expedition Primavera Project Planner

4

Phases of Operation

1 Researching the Available Systems2 Collecting Accounting Data3 Preparing the Data4 Setting up the Trends in Expedition5 Moving a block of data into Expedition6 Importing data into Primavera

Schedule7 Reporting and Analysis

5

Software

Accounting Program, IFASMS Access, ver. 97, SR-2Launchboard for Windows, ver. 2.0Expedition, ver. 6.3Primavera P3, ver 3.0

6

The Challenge

Expedition is “Contract Control Software”

...and does not contain an accounting interface if you haven’t bought it,

…nor does it allow import to all contracts at the same time

…the task of entering actual costs to Trends is tediously slow

7

Phase 1 - Researching the Available Systems

Accounting program can export to a text file

Expedition and Primavera specifications Data Import ODBC Links

LaunchBoard can automate keystrokes between different Windows programs

8

Begin by Opening a Project in Expedition

9

Hundreds of Projects to Choose

10

Opening Project “00000” Trends Module

11

No Cost Data in the Actuals Expended Column

Importing Data into an Expedition Project

No Accounting Interface

12

Project “00000” Cost Worksheet - No Actuals

13

Importing Data into an Expedition Project

Your goal is to import data from accounting into your Expedition Projects database

No Accounting Interface is available

14

Importing Data - Help

From Expedition, choose Tools, Import (if you created a command for the Import application in the User Preferences dialog box). Otherwise, click Start, Programs, Primavera, Expedition Utilities, Import.

Expedition On-Line Help, Primavera Systems, Inc.

15

Importing to All Projects

16

Importing into Trends is not on the List

17

Importing to All Projects will not work for Costs

18

Expedition Tools Accounting Interface

19

Expedition Accounting Interface Not Available

20

Trends can contain Actual Expenditures

21

Expedition - Adding a new Trend #, A10400

22

Distribute

23

Cost Distribution

24

Select Cost Code

25

Updates Cost Worksheet

Cost is now linked from the Trends Module to the Cost Worksheet

The Cost Worksheet is recalculated and available for review or printed reports

This process needs to be repeated possibly thousands of times depending upon categories charged

26

Phase 2 - Collecting Accounting Data

Obtaining a month end closing data file from the accounting program

27

IFAS Actuals Incoming

28

Determine File Layout and Account Identifiers

29

Phase 3 - Preparing the Data

Importing the text file into an MS Access table - dealing with abnormalities

Re-computing correct Contract Numbers Some are missing or blank All are different from Contract

Numbers (or “Projects”) in Expedition Adding other costs, credits not in text fileSetting Up the ODBC Links to Expedition

30

ODBC links to Expedition Tables

31

Phase 4 - Setting up the Trends in Expedition

Create Query in MS Access as the source of the Trends to be created

Every Trend must be uniqueA new Trend is created for every

occurrence of the three account identifiers: Contract, Project, or General Ledger Object Code

32

MS Access Query - Design View

33

MS Access Query

34

LaunchBoard

Automating the process to add exactly 1,445 trends to Expedition

Create a Macro for the F3 key to perform the repetitive tasks

Put Trends in MS Access Query into Cost Code 80003-C0064 and others

35

LaunchBoard discovered at http://www.aacesf.com

36

LaunchBoard Opening Screen

37

LaunchBoard Edit Screen

38

Phase 5 - Moving a block of data into Expedition

Run two MS Access Queries 1 Display source of costs sorted by

Trend 2 Display Expedition target: “exp_ADJT”

table, “value” field, sorted by Trend Requires Matching the Trend # to the

three account identifiers: Contract, Project, or General Ledger Object Code

39

Copy and Paste 1445 fields

40

Phase 6 - Importing data into Primavera Schedule

Two alternatives ODBC Link using Pervasive Drivers can

modify the P3 file directly without opening Primavera

MS Access Query to prepare DBF file for import into P3

41

Phase 7 - Reporting and Analysis

Expedition Reports Online Cost Worksheet Printed Reports

Primavera Reports ETC Costs spread over future life of

each activity

42

Project “00000” Cost Worksheet - With Actuals

43

P3 Cost Loading report

Managing Cost Data Using Expedition and P3

Mark_Greenhalgh@RocketMail.com