Pin Me

Using Excel for PERT Analysis

written by: Ian Johnson • edited by: Michele McDonough • updated: 5/20/2011

Understanding the calculations within PERT analysis is an essential aspect of assigning values and time to these critical and non-critical paths. Excel, and other spreadsheet programs, can be a big help.

  • slide 1 of 4

    More Efficient Management

    Managing a project from inception to completion is definitely no easy task. Project management itself is fraught with numerous variables and uncertainties that can make it extremely difficult for companies to finish on time and within budget. Being successful requires someone who uses factual assertions, proven project management methodologies and who is able to multi-task the entire project from beginning to end.

    One of the most trusted approaches to project management is using a PERT (Project Evaluation & Review Technique) analysis. The most important tool within PERT is the use of PERT analysis Excel templates. You can find a sample of such a template at the following link: PERT Analysis Template

    PERT allows companies to better manage all the variables project managers encounter when managing a project. These projects could involve a capital expenditure on expanding the company’s infrastructure, building an entirely new facility, or a project installation for a customer. Regardless of the application, accounting for these variables will help the company complete their projects on time.

    Being able to finish a given project ahead of time means the company will benefit from a higher gross profit. Finishing on time means the project’s projected gross profit is attained. Encountering delays leads to finishing the project outside its allotted timeline. In this case, the costs add up while the gross profit declines. These additional costs erode the company’s gross profit and make it far more difficult to ensure the customer’s requirements are met. Therefore, the question remains, what is the PERT methodology and why is it such a useful tool for project management?

  • slide 2 of 4

    Who Came Up with the PERT Methodology?

    PERT was developed by the United States Navy when it designed the Polaris submarine. Its essential approach is to identify critical paths or variables that must be addressed and dealt with in order to ensure the project is completed on time and without any adverse delays that raise costs. In making this work, the Navy applied a value to three distinguishable variables concerning the time required to finish a given task within the project’s timeline. These variables are summarized below.

    When one thinks of a given task, these are the most likely outcomes. It’s these three possible outcomes that are used within PERT to deduce the most realistic time needed to finish a given task. This information is essential in planning all the activities of the project and identifying those critical operations that will either allow the project to be completed on time and within budget, or force the project to go beyond its scheduled finish date. While project management isn’t an exact science, and errors do occur, there is a value to the PERT method of isolating those critical tasks as part of the project’s timeline. However, how do these three time variables play into the PERT methodology? Well, it involves adopting the PERT calculation and using these calculations within PERT analysis Excel templates. This involves assigning a value to these variables under the following calculation.

    • Best case scenario for task completion =A
    • Most likely scenario for task completion =B
    • Worst case scenario for task completion =C

    PERT calculation = {1(A) +4(B) + 1(C)} / 6

  • slide 3 of 4

    How is PERT Used in Project Management?

    Let’s assume that a company has a project that includes a number of different tasks. For each of these tasks the company will apply a value to the best case scenario (A), most likely scenario (B), and the worst case scenario (C). These values will then be established by multiplying the best case scenario (A) by a factor of 1, most likely scenario (B) by a factor of 4, and the worst case scenario (C) by a factor of 1. These values will then be added up and the total will be divided by 6, which is commonly referred to as a “Beta" value. This will provide the most realistic estimate on the time needed to complete this particular task. Therefore, if a company was building a foundation as part of a capital expenditure on a new facility, they may apply the following values to the following scenarios.

    • Best case scenario for foundation completion (A) = 2 weeks
    • Most likely scenario for foundation completion (B) = 4 weeks
    • Worst case scenario for foundation completion (C) = 9 weeks

    Taking the above values and putting them into the PERT calculation would provide the following answer.

    PERT Calculation: {1(2) +4(4) + 1(9)} / 6 = {27}/6 or 4½ weeks

  • slide 4 of 4

    Using PERT Analysis Excel Templates

    This is just one of many tasks the company must complete as part of the project. Additional tasks will have the same questions asked and the same calculation done. The company will then pinpoint those operations that are critical to finishing the project on time and the non-critical operations, or paths, of the project. Those tasks that are deemed non-critical paths will not be counted towards the overall timeline for project completion. All the timelines for each task will then be added up to provide an overall schedule. The company will then use this schedule to manage its expenditures on equipment and installation, as well as determine how much labor to employ and when. Successful projects mitigate costs by properly allocating resources at the right time.


    PERT is an established project management tool used by many companies who need better visualization on a project’s potential costs and timeline. Properly using PERT will allow companies to reduce expenditures and increase gross profit. Companies only get better with practice. Delays are commonplace on projects. It’s how the company best manages those delays that determines success or failure.

    The above is an example of what would be included in PERT analysis Excel templates. In this case, the company has identified its essential tasks. These tasks include the foundation, framing, electricity & plumbing, painting, offices and furniture installation. In each case the company has accounted for the best case scenario (A), the most likely scenario (B) and the worst case scenario (C). The calculations are used to identify the time for each task in weeks. The company has then identified the non-critical paths of their PERT project analysis. These non-critical paths are "electricity & plumbing" - these are deemed non-critical paths because their completion doesn't impact the entire project timeline. In essence, these tasks can be done in conjunction with others. When the company adds up the total time needed to finish the capital expenditure on a new building, it will remove these two variables by concentrating solely on those critical paths.