western winter workshop implementing project management tools february 9, 2002
TRANSCRIPT
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
