Chapter Meeting

Frank Cox

Mapping the Use of Microsoft Applications to Project Knowledge Areas - This is your whole brain on Microsoft Excel and Project!

About the Program:

Copyright© Frank Cox, 2004

Too many project managers and facilitators who use Microsoft Access, Excel and Project fail to take full advantage of the products' rich interoperability features and function sets. Such users therefore miss opportunities to significantly improve project planning, execution, and control. Conversely, practitioners who are disciplined enough to make time to explore ways to move data Among Access, Excel, and Project will find the experience empowering.

To take full advantage of applications used in a project management system, practitioners should learn or, at minimum, should improve their ability to dynamically map information among databases, spreadsheets, and project scheduling engines. Learning to move data to the correct application to meet a specific purpose will result in more opportunities to measure and manage as thoroughly as some projects, portfolios, and programs require.

Having the skill to map and move data among applications can be leveraged to yield revealing information about dynamics that affect project performance.

Achieving data mobility skills - learning to port basic data types intact - can be accomplished fairly easily, incrementally, in an organized way. Text, dates or system equivalents, numbers including whole digits and decimals and percentages, and even calculated data may be moved. (Calculations may be moved as values or results, or as text containing the algorithm string.)

Project information stored in Access is difficult to holistically represent and present. Data moved from Access to Excel or to Project, on the other hand, can be structured for precisely accurate and valid analysis. Project data moved from Access to Excel may be charted many different ways for clear reporting. Data stored in Excel may be moved to Project and organized into any of the available views including task and resource usage views.

Very small to very large and complex efforts may also be supported with portable data. Project level portfolio data, for example, may be mapped from Access to Excel for highly flexible quantitative analysis, and may then be logically organized and mapped to Project, printed in a time-phased Gantt Chart view on a large (D size) plotter and shared in a concise, albeit poster size, document at a strategic level planning or decision-making meeting.

Moving data can also support uncertainty reduction in projects. Massive amounts of "buried" data in Access, for example, may be screened for mapping to Project, and only the pertinent data can be moved: project, phase, and task names; start, finish, and status dates, information needed to establish dependencies, resource assignments, costs, and other data that are vital to efficient project management.

Data in various types of Open Database Connectivity applications, "ODBC-compliant" source files, also may be queried from Excel using the largely unknown Microsoft Query add-in. Query is fairly powerful but it also includes a "wizard" that makes connecting to a data source and performing a query relatively simple. Project provides a similar capability through its built-in Organizer, which supports mapping. To "query" an Access file from Project, a user would select and open the Access source file. In the process the Organizer dialog box appears to enable mapping of data from Access to Project.

As will be covered in the presentation, the value of mapping data among Access, Excel, and Project is only limited to demand and the creativity of the practitioner.

Speaker Biography

Mr. Cox is a state certified project management specialist in the Missouri Department of Corrections, an organization of 12,000 employees that manage 90,000 offenders. He directly manages, facilitates, and advises on computer information system projects in the department's Information Systems Office.

From May 1974 to September 1994, Frank served in the Active Component of the U.S. Army. As an airborne infantryman in the mid to late 1970's, he participated in rapid deployments to sensitive hotspots overseas, in a period marked by increasing terrorism throughout Central and Southern Europe. In the 1980's as a uniformed journalist and photojournalist he reported on military activities and programs in Europe, the Mediterranean, and Central and South America. In 1990, Frank was selected as the military journalist of the year. As a Master Sergeant, he was an academy instructor. He completed his Army career as First Sergeant of the Department of Defense Information School.

Frank has two undergraduate degrees, both earned in the military, and a master of science degree in information and communication sciences, which he completed after retiring from the Army. From 1994 to 1997 he worked as a state university technology labs manager, as an adjunct assistant professor on a graduate faculty, and as an associate in an applied research institute. He has designed information and communication science curricula and has taught advanced courses and labs in organizational communication, local and wide area data networking, and telecommunications at the local and inter-exchange levels. As a researcher, he has consulted to the American Automobile Association on Frame Relay networking in Indiana, and to McDonald's Corporation on network security and disaster recovery.

In his current position Frank has managed the planning, scheduling, execution, and performance of human resource and finance system migration and integration projects, and he has advised on an interface project for a statewide profit center. He has also tracked a point-of-sale system rollout covering 25 adult institutions, and he has designed and applied a custom earned value management system to track and correct contractor performance. Frank analyzes and reports on state data center service item usage and cost, and also on a running portfolio of outsourced application development projects that average more than 900 hours and $100,000 per month.

After normal working hours during the last four years he also has helped advance the PMI Mid-Missouri Chapter by contributing as a founder, board member, and presenter.

Frank is married and has a daughter and two sons. He enjoys spending as much free time as possible with his family.