Microsoft® Office
®
Excel 2007
for Project Managers
Kim Heldman
William Heldman
Wiley Publishing, Inc.
Microsoft® Office
®
Excel 2007
for Project Managers
Microsoft® Office
®
Excel 2007
for Project Managers
Kim Heldman
William Heldman
Wiley Publishing, Inc.
Acquisitions Editor: Maureen Adams
Development Editors: Maureen Adams and Tom Cirtin
Technical Editor: Vanessa L. Williams
Production Editor: Sarah Groff-Palermo
Copy Editor: Judy Flynn
Production Manager: Tim Tate
Vice President and Executive Group Publisher: Richard Swadley
Vice President and Executive Publisher: Joseph B. Wikert
Vice President and Publisher: Neil Edde
Book Designer: Judy Fung
Compositor: Laurie Stewart, Happenstance Type-O-Rama
Proofreader: Ian Golder
Indexer: Ted Laux
Anniversary Logo Design: Richard Pacifico
Cover Designer: Archer Design
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-04717-9
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sec-
tions 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Pub-
lisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222
Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for per-
mission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianap-
olis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warran-
ties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all war-
ranties, including without limitation warranties of fitness for a particular purpose. No warranty may be
created or extended by sales or promotional materials. The advice and strategies contained herein may not
be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in
rendering legal, accounting, or other professional services. If professional assistance is required, the services
of a competent professional person should be sought. Neither the publisher nor the author shall be liable for
damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation
and/or a potential source of further information does not mean that the author or the publisher endorses the
information the organization or Website may provide or recommendations it may make. Further, readers
should be aware that Internet Websites listed in this work may have changed or disappeared between when
this work was written and when it is read.
For general information on our other products and services or to obtain technical support, please contact our
Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993, or fax (317)
572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be
available in electronic books.
Library of Congress Cataloging-in-Publication Data is available from the publisher.
TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John
Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without
written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States
and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc.,
is not associated with any product or vendor mentioned in this book.
10 9 8 7 6 5 4 3 2 1
Acknowledgments
We’d like to thank all the people who helped make this book possible. Writing a book meets
the definition of a project, and as with most projects, it takes the dedication and hard work
of many team members to bring it to a successful conclusion.
Thank you to Maureen Adams, our acquisitions editor, for suggesting this book and for
asking us to write it. It’s always a delight to work with her. We’ll miss her and we wish her well
in her new endeavors.
Thanks also to Vanessa Williams, our technical editor, for checking and rechecking the
Excel and MOSS references. Her suggestions were invaluable and helped make some of our
examples even better.
Thanks to Sarah Groff-Palermo and Judy Flynn, our production editor and copyeditor,
respectively, who are experts at quality assurance! We appreciate their thoroughness and eye
for detail.
There are many others behind the scenes at Sybex who also worked hard to make this
book the best product it could be. Thanks to Laurie Stewart and Ian Golder. We also want
to thank the book distributors and merchants for getting our books on the shelves and into
your hands.
Another big thanks goes to all of the instructors and consultants out there who’ve used Kim’s
other project management books for classroom and corporate instruction—Terri Wagner and
Claudia Baca in particular.
Most of all, thanks to you, our readers, for buying this book. We hope you find it helpful
for managing your next project.
About the Authors
Kim Heldman, the chief information officer for the Colorado Department of Natural Resources,
has more than 16 years of project management experience in the information technology field.
She’s managed small, medium, and large projects over the course of her career and shares her
breadth of experience and knowledge in her books through examples, stories, and tips.
Kim is the best-selling author of several other project management books, including PMP
Project Management Professional Study Guide, Third Edition (Sybex, 2005); Project Manager’s
Spotlight on Risk Management (Sybex, 2005); and Project Management JumpStart (Sybex,
2005). You can learn more about Kim at her website: KimHeldman.com.
Bill Heldman is a computer technology instructor at a Career and Technical Education
(CTE) high school in Lakewood, Colorado, where he teaches 11th- and 12th-graders on a
variety of topics, including programming (application and game), networks, A+, project
management, security, databases, and TCP/IP. Bill has 20 years of experience in the computer
technology field, starting with mainframe computing and working through programming,
networks, and enterprise application software. He has worked as a technician, supervisor,
and mid-manager in both public and private-sector information technology organizations.
Bill has written numerous certification study guides for Sybex. He is also a frequent con-
tributor to Microsoft Certified Professional (MCP) Magazine and its cousin, Redmond Mag-
azine, as well as Windows IT Pro magazine. You can learn more about Bill at his website:
BillHeldman.com. You can view his class outline along with other academic information
at www.ctfp.org.
Contents at a Glance
Introduction xiii
Chapter 1 Establishing Project Management Fundamentals 1
Chapter 2 Establishing Excel and Office 2007 SharePoint
Server Fundamentals 23
Chapter 3 Initiating the Project 49
Chapter 4 Determining Project Requirements 81
Chapter 5 Planning and Acquiring Resources 119
Chapter 6 Assessing and Tracking Risk 149
Chapter 7 Quality Management 177
Chapter 8 Constructing the Project Schedule and Budget 211
Chapter 9 Establishing Change Control Processes 245
Chapter 10 Controlling Project Outcomes and Archiving
Documents 275
Appendix A Excel Function Junction 303
Index 335
Contents
Introduction xiii
Chapter 1 Establishing Project Management Fundamentals 1
Project Management Institute 2
What Is a Project? 3
Projects versus Ongoing Operations 3
How Projects Come About 5
Overview of the Project Process Groups 6
Key Project Management Skills 10
Project Management Maturity 11
Leadership Skills 12
Communicating Successfully 13
Negotiating and Problem-Solving Skills 16
General Management Skills 17
Organizing Time and Information 19
Professional Responsibility 22
Chapter 2 Establishing Excel and Office 2007 SharePoint
Server Fundamentals 23
Using Excel and SharePoint to Manage Projects 24
How Excel 2007 and MOSS Support Project
Management Processes 29
Excel Is a One-Stop Environment For Project Documents 32
SharePoint Services 32
Office 2007 33
Excel 2007 and Office SharePoint Server 2007 34
Creating a Document Repository 37
Setting Up MOSS Page Forms 40
Embedded MOSS Form Links 42
Populating MOSS with Documentation 46
Notifying Users of Document Availability 46
Chapter 3 Initiating the Project 49
Establishing a Project Initiation Process 50
Components of the Initiating Process 51
The Elements of a Project Request Form 51
Establishing the Business Justification 54
Creating the Project Request Form using Excel 56
Publishing the Project Request Form 57
Contents ix
Project Selection Criteria 65
Creating a Project Request Tracking Log 71
Identifying Stakeholders 72
The Role of the Project Sponsor 72
Key Stakeholders 73
Documenting Key Stakeholders 74
Defining Project Goals and Creating the Project Charter 75
Principles of Goal Setting 75
Project Charter Elements 76
Obtaining Approval 79
Chapter 4 Determining Project Requirements 81
Creating the Project Scope Statement 82
Essential Elements of the Project Scope Statement 84
Determining Deliverables and Acceptance Criteria 84
Documenting Requirements 87
Remaining Scope Statement Elements 94
Creating the Communication Plan 100
Determining Communication Needs 101
Improving Project Communication with SharePoint
and the MOSS 102
Reporting and Tracking Project Progress 104
Status Reporting 104
Creating Reporting Templates with MS Excel 105
Action Item Log 109
Issues Log 115
Chapter 5 Planning and Acquiring Resources 119
Establishing the Project Team 120
Team Member Recruitment 120
Stages of Team Development 122
Performing a Skills Assessment 124
Documenting Roles and Responsibilities 126
Creating a Project Organizational Chart 128
Motivating Teams 131
Adding Team Members to MOSS 135
Contact List Update 140
Procuring Materials, Supplies, and Equipment 142
Creating a Materials List 143
Make-or-Buy Analysis 144
Soliciting Bids and Proposals 145
Selecting a Winner 145
Managing Vendors 147
x Contents
Chapter 6 Assessing and Tracking Risk 149
Identifying Risks 150
Identification Techniques 150
Common Project Risks 152
Creating a Risk Checklist 154
Performing Risk Assessment 155
Risk Tolerance 155
Risk Probability and Impact 156
Documenting the Risk Register 160
Creating a Risk Register with MS Excel 161
Responding to Risk Events 171
Risk Response Techniques 172
Documenting Risk Response Results 174
Updating the Risk Register 174
Closing Out Risks 175
Chapter 7 Quality Management 177
Quality Management Plan 178
Cost of Quality 180
Costs Associated with Cost of Quality 180
Cost of Quality Theories 181
Determining Quality Metrics 181
Benchmarking 181
Cost-Benefit Analysis 182
Affinity Diagrams 182
Pareto Charts 182
Scatter Diagram 190
Flowcharts 191
Control Chart 195
Inspection 196
Controlling Quality 197
Security and Formatting Elements of MOSS 197
Information Rights Management (IRM) and Office 2007 204
Digital Signatures 206
Chapter 8 Constructing the Project Schedule and Budget 211
Creating the Work Breakdown Structure (WBS) 212
Constructing WBS Levels 212
Work Package Level 213
Defining Resources for Work Packages 214
Estimating Project Tasks 215
Expert Judgment 215
Top-Down and Bottom-Up Estimating 215
Contents xi
Parametric Estimating 215
Three-Point Estimates 216
Sequencing Tasks 218
Determining the Critical Path 220
Calculating the Forward Pass 220
Calculating the Backward Pass 221
Calculating the Critical Path 221
Crashing the Schedule 222
Determining Resource Availability 223
Documenting the Project Schedule 227
Milestone Chart 228
Project Calendar 228
Network Diagram 229
Gantt Charts 230
Creating the Project Budget 231
Budgeting Inputs 232
Building Project Budgets 233
Elements of a Project Budget 233
Estimating Budget Items 235
Determining and Monitoring Performance Measures 237
Cost and Schedule Variance 238
Performance Indexes 238
Chapter 9 Establishing Change Control Processes 245
When Change Occurs 246
Reasons for Change 247
Dealing with Change 248
Creating a Change Control Process 248
Level of Authority 249
Emergency Requests 249
Escalation Process 250
Creating Change Control Forms 250
Change Request Form 250
Change Request Log 251
Converting Tables to Ranges 253
Reporting and Printing Change Control Data 254
Assessing the Impacts of Change 263
Establishing a Change Control Board 265
CCB Procedures 265
Project Manager’s Role on the CCB 266
Independent Verification and Validation 266
Windows SharePoint Services Templates 267
Accessing Templates in MOSS 269
Subsite Creation 271
xii Contents
Chapter 10 Controlling Project Outcomes and
Archiving Documents 275
Performing the Work of the Project 276
Monitoring and Controlling the Work of the Project 277
Schedule and Budget Monitoring 278
Monitoring Risk 278
Procurement Monitoring 279
Monitoring Vendors and Contracts 280
Change Request Monitoring 281
Team Member Monitoring 281
Taking Corrective Action 282
Schedule Actions 283
Budget Actions 288
Personnel Actions 288
Contract Actions 290
Signs of Project Trouble 291
Accepting Project Deliverables 292
Notifying Stakeholders of Project Acceptance 293
Closing Out the Contract 294
Documenting Lessons Learned 294
Releasing Team Members 297
Archiving Project Documents 297
Backing Up the MOSS Server 298
Celebrate 301
Appendix A Excel Function Junction 303
Leveraging Excel Functions Using Excel Services 304
Calculating Various Workbook Elements and Publishing
to MOSS 308
Using Excel Functions to Enhance Project Management
Productivity 317
Function Groups 318
Functions for the Project Manager 319
VBA, Macros, and Other Ways to Automate Excel 324
Consolidating Sheets 330
Cubes/Pivot Tables 332
Index 335
Introduction
We have written this book for those of you who have some experience in project management and
are looking for a quick and efficient way to manage your projects. When combined, Excel 2007
and Microsoft Office SharePoint Server (MOSS)—two components of the Office 2007 initiative
that Microsoft has developed—are great for managing all phases of a project, creating templates,
collaborating on planning processes, tracking project progress, and sharing information with all
interested parties.
The project management field has grown exponentially over the last decade. Run a query on
your favorite job-hunting site and you’ll see that project management experience is a require-
ment (or at a minimum, a desired skill set) for tens of thousands of job postings. In addition,
many of you have upwards of half a dozen to a dozen independent projects running at the same
time. This book will show you how to organize the management of those projects using tem-
plates we’ve built for Excel 2007 and how to take advantage of the power of SharePoint to com-
municate and share that information with team members and stakeholders.
If you find that this topic interests you and project management seems like a career worth
pursuing, we strongly recommend that you consider obtaining your Project Management
Professional (PMP) certification through the Project Management Institute (PMI). PMI is
the de facto standard in project management methodologies. You will find that many orga-
nizations now require a PMP certification for positions related to project management.
This book is based on the project management guidelines recommended by PMI, and
many of the terms, concepts, and processes you’ll read about in this book are based on PMI’s
publication, A Guide to the Project Management Body of Knowledge (PMBOK Guide),
Third Edition.
For a more detailed exploration of the PMP certification, pick up a copy of
PMP Project Management Professional Study Guide, Third Edition, by Kim
Heldman (Sybex, 2005).
Whether you choose to pursue certification or not, solid project management practices are
required to achieve success on your projects. Using the processes, templates, and communica-
tion methods we’ve outlined in this book will help you achieve that success.
Who Should Read This Book
This book was written for those of you who have some understanding of project management
but would like to further that understanding and apply some solid principles to your next
project. It’s for those of you who manage the day-to-day projects that keep your organization
running. Excel 2007 is a great tool for managing those types of projects. This doesn’t mean
you’ll have to implement a rigorous discipline that will take as long to set up and administer
as it will to complete the project itself. Project management really boils down to a handful of
basic principles that can be scaled to meet the complexity of each project. Excel 2007 and
xiv Introduction
SharePoint can help you set up those processes and this book will show you how. You’ll find
the templates and checklists included in this book immediately applicable to your next project.
Your knowledge and practice of the principles outlined in this book will help assure employ-
ers that you understand how to bring a project to a successful closure. If you’re interested in
managing projects using a proven approach that’s efficient, easy to use, and not excessively
burdensome, this book is for you.
What This Book Covers
This book walks you through a project life cycle from beginning to end and shows you step-by-
step how to set up templates to manage the process and how to share project information using
SharePoint. We’ve included many useful examples, tips, and hints that will help you solve com-
mon project management dilemmas. Here’s a high-level overview of what this book entails:
Chapters 1 and 2 These chapters lay the foundation of project management, Excel 2007,
and SharePoint Server Fundamentals and delve into definitions, project life cycles, and the
skills all good project managers need for success.
Chapters 3 and 4 This section deals with the Initiating and Planning phases of the project.
Here you’ll find templates for initiating projects and documenting the scope of the project, and
we’ll discuss how to set project goals and document the requirements. We’ll also walk through
how to publish these documents to the SharePoint server.
Chapters 5 and 6 These chapters walk you through acquiring resources, building strong
teams, managing contracts, and identifying and planning for risks. There are a host of tem-
plates for you to use and or modify for projects.
Chapters 7 and 8 These chapters discuss the quality management processes, breaking down the
work of the project into manageable components, and creating the project schedule. You’ll also
determine schedule and budget estimates and create the project budget, all using Excel 2007.
Chapters 9 and 10 A large part of the planning work is done. In these chapters, we’ll dis-
cuss procedures for managing changes to the project, assessing change impacts, monitoring
the performance of the project, taking corrective action, accepting the final project, and doc-
umenting lessons learned. We’ll also show you how to publish and archive this information
on SharePoint.
Appendix The Appendix covers the more esoteric elements of Excel 2007 such as pivot tables,
publishing to MOSS, automating Excel, and Excel functions. While some of these features may
have been prevalent in previous versions of Excel, they have been updated for Excel 2007. And,
of course, if you’ve never ventured into these advanced areas, the Appendix walks you through
so that you have the ability to utilize these tremendously helpful features in your project man-
agement efforts.
Introduction xv
Making the Most of This Book
At the beginning of each chapter, you’ll find an introduction that highlights all the topics cov-
ered in the chapter. In addition, some special elements highlight important information:
Notes provide extra information and references to related information.
Tips are insights that help you perform tasks more easily and effectively.
Appendix A discusses many of the built-in functions Excel 2007 contains that are useful
in everyday project management.
All the templates you’ll encounter throughout the book can be downloaded from
www.sybex.com/go/excelpm.
Chapter Establishing Project
Management
1 Fundamentals
Copyright by webtailieu.net