Speaker 1:	Thank you very much for attending this session. Today, I'm going to talk about probabilistic, critical paths or if you like critical chain computation. We learned CPM, which is purely deterministic model. We also learned PET, which has some probabilistic nature. But in this talk, I'm going to explain simulation in project control. The problem remains the same as it was. Still, we have a network. Technical precedence remains the same, no change. For example, A is still precedence for C and D. Both D and E are precedents for F, so there is no change in technical precedent. The change is in duration of activity. For example, if in a deterministic model, the duration of an activity is two days. Here, we may say the average is two days and standard deviation is say, 0.5 days. Or we may say the average is four, but it is between two and six days. Different probability distribution may replace that single number which we were using as duration of each activity. Usually, year long, triangular, these two distributions are frequently used as a representative of activity time. We can also use normal, but we should restrict the lower bound to something and especially not going below zero because we don't have an activity with a negative duration. We may also assume exponential. All of these are possible among other probility distributions. But the key idea is a determinist number a single number is replaced by a distribution. Let's make this change. In a simplest way and simplest way is to assume the activities have uniform distribution. They have a lower and upper value, and the random variable uniformly can move between these two upper and lower bounds. Let's make it even simpler than that. Let's assume activities have discrete uniform distribution. For example, if in a deterministic model, we say the activity time is six, now we may say it is between three and nine. Also we may say it's discrete. It is not continuous. It could be 3, 4, 5, 6, 7, 8, 9, with an average of six. If you make these simplifications, then implementation in Excel becomes extremely easy because Excel has a specific function which can give us random numbers between two, upper and lower limits, and that is rand between. Activity A has uniform distribution between two and six. I come here equal to rand between lower, upper. Give me an integer number between two and six. Because I'm going to copy it down, then I should lock those two rows here, I'm talking about Row 2, I like it and Row 3, I like it. Now I can copy it down. Because I have not locked A, I can copy it to the right to and create random durations for other activities. We just use a function rand between A and B, which gave us a integer number between A and B, Enter. Then I copy to the right. Now here, I'm generating a random number between one and three, 1, 2, 3. Here, I'm generating a random number between four and eight 4, 5, 6, 7, 8. Then I go here, and then I copy this down. I generated 1,000 random variables for each activity. Then I go through my network and I identify potential passes from the starting point to the ending point. I will let you discuss an approach how to find it out how many passes are in a network. For the time being, it is easy, because we look into it and we find this three, and we cannot find anything else. We have AC pass, ADF pass, and we have BEF pass, three passes, AC, ADF, BF. Then I come here. This is equal to A+C. Copy to the right. This is equal to A+D+F, copy to the right. This is equal to B+E+F. I created the lens of those three passes. Out of those three passes, AC and ADF are critical in this situation because critical pass or critical chain is the chain which takes Time more than any other chain. Now, click, and I generated 1,000 possibilities. Now I come here, equal to max, because I know the critic call pass takes more time than any other path, and then click. For example, here, I have one critic call pass, one critic call pass, two critical pass. Conditional formatting, highlight, the simplest highlight, equal to if this is equal to this, and then I should remove the dollar in front of five because I'm going to copy it down, painted, and then format paint and paint this. Now I know for example, in this situation, all three passes because they have probabilistic nature, all three passes were critical. I have created critical pass. Now I want to sort them and create a S shaped curve. Also, I like to compute basic descriptive statistics. I come here equal to average numbers, and I allocate a four, Enter. Let me delete these two columns, not to make you confused. I have average because I want to later put it into my graph, I also round it 21, Enter. The standard deviation, I just copy this down and then replace average with standard deviation of sample because you only have 1,000 observations. I did it on the top one, switch it back. Coefficient variation is standard deviation, divided by average, copy average, paste average, make it. We really don't need round here. Mean, copy it down. Max, copy it down. Median, copy it down. Count. I have descriptive statistics. Now I know that my critical pass may take as little as six days or as long as 16 days. Median is 12 days, average is 11.6 days, for 1,000 simulated instances that I generate. Now, I like to create a shape curve to tell me how I go from minimum to maximum under what probabilities. Obviously, I don't go like a linear because usually due to central limit here, when I incorporate several random variables, the final shape should look like a normal curve. Therefore, I expect such a shape. I come here equal to rows. I go to the cell column, and then I lock the first one. That is from absolute L5 to relative L5. In the first cell, it is one. If I copy it down, it goes to 1,000. Now if I come here, equal to small. Find a small of this column and lock it, rows from this one to itself, make the first one absolute. Now I find Small 1, I find Small 2, I find Small 3, and I sort it from small to large. Click, and because I have hidden it here, it goes from 8-16. Otherwise, if I unhide it, it goes to 8, 9, 10, 11, 12, and so on and so forth. Here I want to compute probabilities because I have 1,000 observations. They are the first one, the smallest one, the thousand one is the largest one. If I go here and divide this by count and lock it a four, that would give me probabilities. Click. Now what I can do, I can copy this too. Insert scatter chart, I click on this. A graph comes out, very strange, but that is because select data hidden and empty cells, show data in hidden rows, so this is my curve. Here, I have written some functions. You may look into them. It is just to create a format. I click on this equal to this. Then it will tell me what is the average and what is the coefficient of variations. But my coefficient of variations has a lot of decimal points. I go here to coefficient of variations, and I round 22. Now, it tells me the average is 1.8. I have clicked on wrong numbers. The average is 11.5 and coefficient of variations is 17. I think 17 here is 20 because if you like, Control 1, you can change this color to a better color, and you can increase this, for example to 2.5. This is your curve. You can also click on it, go here and make it darker. You can also make the phones to what I like. That is our graph. Let me tell you how I made this one. I said equal to average, and this one and coefficient of variations and this one, Enter. That appeared over there. But the two numbers are a little bit attached to each other. I'll go here and I say, and quotation, Space, and Space, quotation, and now it puts an and between those two values. Or if you like, you can just type and here. If it confuses you, you can type. Because I don't want that middle and to be confused with the and in the right hand side and in the left hand side. That is the curve. The next thing that we can do is to compute probability of each pass to be on the critical pass, equal to if this is equal to this and a lot, J, but I leave five as it is. If the time of that activity is equal to the maximum, then put one, otherwise, put nothing and then copy to the right. Now AC is critical. Here I have it. Copy it down. Here, ADF is critical. I have one. I copy this all the way down and then equal to sum because whenever it is critical pass, I have put one. That's it. This one, 357 times was critical. This one, 703 times and this one, 117. The summation of the three is greater than 1,000, which is the total number of simulation instances because as we saw in some instances, we have two, and even in some instances, we have three critical passes. It was more than that. Now if I divide what I have got here by count and lock it and copy it to the right these are probabilities of each pass to be on critical pass. Therefore, I can do this. Insert, chart, I insert this. Right click select data, horizontal, edit, and go and replace these three by 1, 2, 3. Now they are changed. Make sure that the color is good dark, and the font is what I like, and then I can go here. Click on this one, and, for example, make it this color. I can also go here, and this gap, I may reduce it to say 25%. I may click on this equal, and then click on this, which shows me the percentage that each pass is critical. We created a graph to show the distribution of the project, and we create a graph to show the dis solution of critical pass. Let me show you a couple of simple tricks. First of all, I really did not need to use this small function. I used it just to have a practice for you. Excel dynamic arrays have given us a lot of capabilities. Let's use one here. Equal to sort, and I then mark all this column. Enter, it is sorted for. We could have used small function and we have used dynamic arrays. Let me give you a second, which is, again, based on the capabilities that dynamic arrays bring to stage. Let me erase this side. I'll go here. I tap equal to find find what? Find A, B, C, D, and F, and I lock it because I'm going to copy this to the right and to bottom. Find this. Come up, here. I also lock this column F because I'm going to copy it down, but since I want to benefit from it when I copy it to the right, I don't lock G. I did that. Let's see what do we get? We get one and two. I go here, and I stated is number. If what you see is number, if this is correct, we will have true. Otherwise, we will have false, true, true, false, false, false. If this number is true, come. Put the corresponding value which you see here, and I should lock A, and I should lock F because I am going to copy it to the right. I don't lock five because I want to copy it down. If that is correct, put the time, and if it is not correct, put zero. Is number if you found a number, put the time associated with it. If you didn't find, that means that activity is not inside that path. Therefore, just put zero enter. It puts two, which is time of this activity, five, which is time of this activity, and the rest are not there. Then I go here again and I say, add that two to that five. Now, I can easily copy this to the right and copy it to the bottom. A little bit more difficult Excel functions, but easier, easier to automate. I really don't need to go everywhere and enter the time of the corresponding activities. Now, again, I can come here, the quarter sort. Thank you very much for your time. In another video, I'll go into a little bit more complication in these computations. But we are fine as long as understanding the basic concept is concerned, we got the idea. Oh, one more thing. This was our graph. We used discrete uniform distribution. Now let's go one step further. instead of discrete uniform distribution which is generated by rand between function in Excel, let's do it for continuous uniform distribution. Continuous uniform distribution is between A and Excel can generate a function called Rand, which is 0-1. Now you have a number. You have a random number 0-1, and you want to take it from zero and one and drop it into A and V. How you can do that? Think about it a little bit. I have something which is called rand. Rand is something 0-1. I want to take it to somewhere between A and B. I want to take the range of zero and one, to range of A and B. This is quite simple. I take Rand, and multiply it by B-A. If rand is zero, the result is zero. If rand is 1, it is one multiplied by B minus A, is B minus A. But I don't want it from zero to B minus A. I want it from A to B. How can I go from zero to B minus A to A to B. I just add A here plus A, and I add A here. What does it mean? I have a function B-A*Rand. I also add a to eight. Now I have RN at minimum, it is zero. At maximum, it is one. If it is zero, this is zero, zero times something is zero, then it will be A. If it is one, it is one times B minus A is B minus A, plus A is B. Therefore, I can take rand from D, range of zero and one, to the range that I'm interested in, which is A to B. Let's do it. Instead of using Rand between, I come here and I say equal of 2A, which is two for this activity, and I log two of eight plus upper bound for this activity, which is six, and I lock three or minus lower bound, which is two, and I lock this part. Multiplied by Rand, Enter. Now, this number is 4.6. It is between 2-6, but it is not integer. It is a real number. I can copy to the right. Copy. Look at this curve here when I do click lick. Click click. Now, the shape is very different. The probabilities are here and the graph is here, or we're updated. Let me go one step further. This is the same problem, but I have created three types for you to start from very simple functions and gradually make it a little complicated. Up to now, we have computed the length of the project, the probability of the duration of the project, the probability of each pass to be on the critical pass or critical chain. Now we want to compute the probability of each activity to be on critical path. I have A, B, C, D, E, F activities here. I can go here and I can use very simple function. I say, A is critical path if AC or ADF are critical path. This one would be equal to max of, these two. The first two pass. B is on critical path if the third pass is critical. C, if the first pass is critical, D, if the last pass is critical and F max, if the second or third pass is critical. So here we have ADF are critical. Then I can copy this down. Then I can go here sum these numbers, those times that I have observed one and then divide it by count. And then I can copy it to right, and then I can create this graph for activities that are critical, probabilities of critical activities. But we can do better. Take walk to Find. Find what? Find A, and lock 4, because I'm going to copy to the bottom. I lock 4, because also I am going to copy it to the right, I don't lock t. Find T in these three and locked. Is there A in any of those three? Yes, it is in the first one, and it is the second one, but it is not in the third one. Is it number? True, true, false. Instead of is number, we can also use Es error, but then we should have played oppositely. Both of them can handle the job, true, yes, true and false. If Es number is true, then put what you see here. Is it on critical path or it's not? I should lock P, and I should lock R because when I copy it to the right, I want P&R remain as they are. But when I'm copying down, I want five to six, seven, eight, and nine. That's it. If it is number, if you find it over there, if it is in that pass, write what is below it, which tells us whether that is critical pass or not because it may find A in one pass, but that pass may not be critical. We have this. If you find it, write what you see. Is there zero or one, critical or not critical? If you didn't find it, just simply put zero. We found A. We found A in the first one and in the second one. But the first one was not critical while the second one was critical, and that is why we see this one here, but we see nothing here. Now we have this. What is important for us is to see if there is a one or not. Max, so we write the max, and we place the max over there. In this situation, A is not on a critical path because these two are not critical paths and critical pass is here, and A is not there. If I copy to the right, D and F and A are critical pass, and critical pass is here. ADF. Now we can copy this down much less time consuming than the previous one. That is the last concept that I also like to include it and I want to quickly draw a histogram. Let me go to the next page, which is the continuous version. I have minimum. I have maximum. I can compute range, max minus mean. Let's make the mean mean adjusted a quarter. It of this one, no matter what it is. Max adjusted equal to round of, zero, so it's 7 and 16. Type, equal to six. I type equal to seven. Now it became 7+1=8, copy this down. 16 or 17. Count if, what you observe here these times and lock it, is less than and this, that means if there are less than seven and then copy it down, we should end up with 1,000. Equal to seven that is cumulative. Now we want to make it for each bean. This one minus this one. Copy it down. And equal to divide it by count to compute frequency count of these numbers and lock it. On this page, I didn't have count over there. Then I copy it down. Then I can go here. I can insert chart. This is a chart. I can also go here and type equal to average of eight and seven or whatever it is, and copy it down select data, edit, and then I can place these over there. I can go here, Ctrl 1. That is the histogram for the project duration. Thank you very much for attending this session. I hope I have been able to deliver some substance, and I hope you spend time a little bit on this topic, learn it and be able to implement it in different situations. See you in my next lecture.