In this video of #engineeringmanagementacademy #excel is used to Implement #PERT by #drmehrdadarashpour
❎ Excel workbook to follow along: bit.ly/Excel_PERT
▶ RELATED VIDEOS: • Program Evaluation and...
⌛ TIMESTAMPS
0:00 - Introduction to Program Evaluation and Review Technic (PERT)
0:21 - “STANDARDIZE” & “NORM.S.DIST” Functions in Excel
0:49 - Step 1 (Calculate Activity Expected Times (Te) & Total Expected Time (TE))
1:49 - Step 2 (Calculate Standard Deviation of Activities & the Project)
3:03 - Step 3 (Scenario Analysis to Find Probability of Project Completion Times, which are longer or shorter than the total expected time of our project)
4:38 - Step 4 (Scenario Analysis to Find Project Completion times for Desired Confidence intervals)
5:35 - P90, P95, P99 & Final Inferences / Interpretations
✍ Request Next Tutorial Video: forms.gle/Frz9U9imCouofdoD8
In a previous video, we discussed how to calculate Z-Score & associated probabilities using the table of standard normal distribution ( • Program Evaluation and... ). Now, we want to implement PERT in Excel. We can use Excel’s “STANDARDIZE” function to find Z-scores & “NORM.S.DIST” function to find Probability of Project Completion at Desired Points in Time. You can follow along by getting the Excel workbook via the provided link. bit.ly/Excel_PERT
Let’s start with a small project with eight activities for which dependencies or predecessors are specified. For each activity, optimistic, most likely & pessimistic times have been provided. We can follow only 4 simple steps to implement PERT in Excel.
Step 1 is to Calculate Activity Expected Times (Te) & Total expected time for our Project (TE)
We start with “Activity A” to find its expected time or Te. We should then go ahead and draw the CPM network based on the precedence logic. For all activities, the expected time or Te will be the duration. Forward pass is used to find the project duration. For this project Total Expected time or mean completion time is equal to 70 days. Then, backward pass identifies the chain of critical activities, which are ABFGH. This concludes the first step in implementing PERT in Excel.
Step 2 is to Calculate Standard Deviation of Activities or σe & Standard Deviation of the Project
We start with “Activity A” to find its standard deviation, which shows how spread is the duration range around its most likely value. We should then go ahead and find the standard deviation of the project or σ, which is calculated using standard deviation of critical activities, which are ABFGH in this project. The standard deviation of 4.773 indicates the amount of variability or spread, around the mean completion time of 70 days in our project. This concludes the 2nd step.
Step 3 is Scenario Analysis to Find Probability of Project Completion Times, which are longer or shorter than the total expected time of our project
So far, we know that our project has a mean completion time of 70 days, but we may want to know the chance of completing the project earlier (for example, by day 69) or later (for example, by day 75). We calculate the Z-Score for each scenario, which is a normalized value that subtracts the mean and divides by the standard deviation. Alternatively, we can calculate the Z-Score using “STANDARDIZE” function of Excel, which needs 3 inputs of project scheduled date or Ts, total expected time or TE and project standard deviation or σ). We should note that a negative Z-Score indicates a probability of less than 50%, which is the case for finishing the project sooner than the total expected time of 70 days. Associated probabilities for each scenario are calculated using the “NORM.S.DIST” function of Excel, which accepts the Z-Score & a binary value of “true” to consider the cumulative distribution function or CDF. Understandably, the chance of completing the project by day 75 is more than 50%. This concludes the third step in implementing PERT in Excel.
Step 4 is Scenario Analysis to Find Project Completion times for Desired Confidence intervals
Now we can consider various confidence levels such as 90% & find the associated project completion time based on Z-score calculations. For a high confidence level of 90%, we will obviously have a longer duration than the total expected time of 70 days. We can calculate the Z-Score using “NORM.S.INV” function of Excel, which needs the required confidence level as its only input. Then, Using our Z-statistics formula, we can calculate project time scheduled or Ts, which is associated with 90% confidence. The Value of 76.1 days corresponds to 90% confidence here. The formulas are then duplicated for other scenarios of P95, P99 & P99.9 to find associated project durations. We can appreciate the increase in completion times when a higher confidence is desired. This concludes the last step in implementing PERT in Microsoft Excel.
You can access the Excel workbook via the provided link. bit.ly/Excel_PERT
Негізгі бет PERT by EXCEL -4 Steps Program Evaluation and Review Technique Project Management Z Score Statistics
Пікірлер: 2