How to Create a PERT/CPM in Excel
A PERT/CPM is a project diagram, much like a flow chart, used to identify various stages in a project. This technique combines a Program Evaluation and Review Technique diagram, and a Critical Path Method diagram. A PERT diagram is used to illustrate worst-case, best-case and most likely occurrences from a project start to finish. It can also illustrate the probability of a project being completed on a specific date. A CPM diagram, as its name implies, shows the path, or steps, through the project. You can use Microsoft Excel to create a PERT/CPM diagram using the text boxes and drawing tools much like those produced using project management software.
Creating Project Steps
Launch Excel and create a new worksheet. Click the “Insert” tab, then select “Text Box.” Each text box will represent a stage in the project.
Position the cursor on the left side of the document, then hold down the mouse button and drag it diagonally to create a text box.
Click inside the text box and type the text to identify this as the project start, such as “Project Inception,” and a date if one is available. Highlight the text, then format the text as desired by clicking the “Home” tab and using the font options in the Font group.
Click the border of the text box to select it, press “Ctrl-C” to copy it, then “Ctrl-V” to paste a copy into the document. Click on the border of this text box and drag it to the left of the first box. Highlight the text and type the details of the second step of the project’s best-case scenario.
Press “Ctrl-V” to paste another text box. Change the text for the third step and drag it to the left of the second box. Continue adding boxes until you reach the project completion.
Consider additional steps that may be required for your project. For example, if you were designing a Web site for a client, changes in the banner or structural changes requested by the client could represent additional steps, as could finding a new Web host or purchasing a new domain name.
Paste another text box onto the document by pressing “Ctrl-V” on the keyboard. Label the box as one of the additional steps in the process and place it below the two stages it falls between. Add additional steps that may be required in the project by adding new text boxes.
Identify the most likely chain of events in the diagram. Click each text box in this chain while holding down the “Ctrl” key to select them. Click the “Format” tab, select “Drawing Tools,” then click “Shape Outline.” Select a color, such as blue, for the borders of these text boxes to identify them as the most likely scenario. Change the text box borders to red for any steps that may interfere with the completion of the project
Add anticipated completion dates to each step by typing them in each text box. Each text box can have three dates. Highlight the dates that follow the most likely chain of events and change the font color to blue. Use black for the best-case scenario completion dates and red for the worst-case scenario dates.
Connecting Text Boxes
Click the “Insert” tab, then click the “Shapes” button. The Shapes gallery opens. Select one of the line shapes that has an arrow. Use a straight line for text boxes that are directly beside each other horizontally. Use a bent line for text boxes that are above or below one another.
Click the border of the first text box where you want the line to begin, then drag the line to the border of the second text box where you want the line to end.
Repeat Steps 1 and 2 to connect all of the text boxes to illustrate the path of events from one object to the next.