This is a guest post by Joe Smith, a project manager in Austin, Texas. Thanks for sharing this cool technique, Joe!

Four columns of data are required to make meaningful Gantt Charts in Excel. They are the Task, Start (dates), Work (represents work performed), and Finish (desired finish date). Work Performed is calculated by taking the number of days between Start and Finish, multiplying it by the Percent Complete, and adding it back to the Start.

Langoliers

If we add a fifth column , “Today” (“=Now()”), we will be able to add a Trend Line. This will allow us to visually determine if each task is on target, behind schedule, or ahead of schedule. This approach is a little bit pessimistic because the trend line will be recalculated each time you open the workbook, but progress is generally only recorded once or twice a week.

A sixth column, Forecast Finish, is used to calculate how much longer it may take to finish a task that is behind schedule. The Forecast Finish is calculated by adding the amount of work remaining to the Scheduled Finish, or to the current date, whichever is greater. It is an optimistic approach because it assumes that the task can be finished in the same amount of time that is work remaining. However, because it uses the current date, the value is recalculated each time you open the workbook.

I refer to the fifth and sixth columns as Langoliers. The Langoliers are characters in a Stephen King novella. “Langoliers are the timekeepers of eternity; their purpose is to clean up what is left of the past by eating it.” This is the reason that work cannot be completed in the past.

Case Study

In January, I accepted a position as Project Manager for a 4 phase project. Phase 1 is network infrastructure installation, Phases 2 is connecting legacy systems (islands of automation) to the new network, Phase 3 is upgrading and implementing new software, and Phase 4 is virtualization of as much hardware as possible. Phase 1 had started 3 months before I joined the project. It is/was a $2 million project, with a 2 year schedule.

Task List

Fortunately, the project is based on quotes from hardware vendors and service providers.  I created a list of each quoted item, aggregated to recognizable components (ie: server components, like memory and mother boards and power supplies, became “Server 1”). I keep traceability to the Quote, PO, and Invoice. Then to most items, I added the “labor” tasks (procure, install, configure, test, qualify). For most start dates, it was when the Purchase Order (PO) for the item was issued to the vendor. I met with the resources (individually) to collect the percents complete, and where the scope had changed. On items that were not complete, I also requested the level of effort in days (LOE) and the amount of time required for the tasks. (I needed these to calculate the Float). Armed with this information, I could provide very accurate Gantt Charts.

Gantt Charting in Excel

I chose to work in Excel, because of the level of control it allows over formulae, calculations, and Gantt Charting. In the Task List, I track Deliverables and “Labor”.  For each item in the task list, I am tracking Scheduled Start, Work Complete, Scheduled Finish, Forecast Finish, Level of Effort, Percent Complete, Actual Finish, Vendor Quote, Purchase Order, Invoice, and Resources. On the Gantt Chart, I am displaying the Task, Scheduled Start, Work Complete, Scheduled Finish, Forecast Finish, and the Current Date.

Score Cards

Every month, the PM’s are required to report their project health on a corporate scorecard. The projects are represented at the “ten thousand foot” level. The key performance indicators (KPIs) are Scope, Time, and Budget. “Green” means the project has less than 10% growth in any of the KPI’s. “Yellow” means between 10% and 15% growth in any of those areas, and “Red” means greater than 15% growth in any area. A project can go from Red to Green if the KPI(s) is adjusted and formally accepted/approved, or if a miracle has brought it back into the green, but it must be reported as “Red” or “Yellow” for the month that situation occurred. The Scorecards are reported the first week of each month, for the previous month.

Resource Communications

Resources are the heart and soul of any project, so I work very hard to keep them happy.  On this project, my resources wanted to see the whole project each time they need to enter percent complete, but they wanted the in-progress tasks to be highlighted in some manner, so that they would know which ones to focus on.

I developed a macro to highlight and “un-highlight” the tasks as they come due and/or are completed.

The resources only need to update the pink cells, and the green highlighting makes it easy to track across the page.

My goal with this article is to share my experience in the hopes that it will help others benefit from my experience and methodologies. To download a sample, click the icon, below. I welcome your questions, comments, and suggestions.

Gantt Chart in Excel Sample

1 file(s)   80.13 KB

Download