Using Excel to Schedule Project Tasks

Using Excel to Schedule Project Tasks
Page content

Introduction

By using a project planning form or setting up some simple formulas, project managers can make use of Microsoft Excel as an effective project management tool to help in project planning and scheduling. For example, the first column, Column A, of a spreadsheet used for listing project management tasks can indicate if the task must be completed before the next task can begin.

Tasks on the critical path consist of back-to-back activities without any time in between. Entering “CP” in this column indicates the task fits this criteria for being on the critical path. Later, if the project manager needs to shorten a schedule, she knows where to look for critical tasks.

Project task names can be listed in the next column, Column B, and then in subsequent columns, the project manager can enter details about durations, costs, dependencies, resources and due dates. For example, Column C might contain information about how long it takes to complete the task in days. Another column might indicate if the task depends on another. For example, components must be built before the testing of them can begin.

Determine How Long a Project Takes to Complete

At the end of the column containing information about how long it takes to complete each task, the project manager can enter a simple Excel formula that adds up all of the values. For example, if the tasks are listed in rows 2 through 50 of Column C, the Excel formula would be “=SUM(C2:C50)” and provide the project manager with a project total. By manipulating the values entered, a project manager can quickly see and analyze the impact. Project managers can adjust the total time allowed for the project by adding or subtracting days to specific tasks in the project schedule.

Determine the Impact of Crashing Tasks

Entering revised values indicating how long tasks might take if the schedule gets shortened allows the project manager to generate a new total duration. The project manager use this information to communicate to stakeholders that the project can be delivered earlier than previously planned. For example, by entering reduced durations in Column D, the project manager can use a simple formula to add up the values and obtain a revised total. For example, if the tasks are listed in rows 2 through 50 of Column D, the Excel formula would be “=SUM(D2:D50)” and provide the project manager with a new project total. Project managers need to proceed carefully, since crashing a task may make it occur on the critical path, which makes it required before the next step can occur.

Determine the Cost Benefits of Crashing Tasks

By entering the dollar value associated with the original duration and the revised duration, the project manager can indicate how much the project tasks would cost if the schedule was shortened. Creating a monetary value for the crash reduction often creates a compelling case for shortening the time allowed to complete the task, assuming it can be done without sacrificing quality significantly. The project manager can determine the difference between these two values using a simple Excel formula. The project manager can calculate this dollar value as the difference between the two cost values. For example, if the original cost is in Column E and the revised cost is in Column F, the project manager would enter the formula “=E2-F2” in Column G to determine the crash reduction cost value for the first project task. Then, she could calculate the sum of all values in Column G to determine the total projected project cost adjustments.

Conclusion

Project managers can use Microsoft Excel, a tool many people are familiar with, to make effective project management decisions regarding scheduling. By examining the tasks and identifying the cost benefits associated with reducing the time permitted to complete the work, the project manager can schedule the project work with confidence. She needs to be sure the tasks on the critical path actually shorten the overall project schedule by shortening their duration. Ideally, she should look for the largest reduction that impacts the least number of tasks. Using Microsoft Excel functions allows her to generate and examine these values quickly and easily.

References and Image Credit