Calculate process utilization - Microsoft Excel Tutorial

- [Instructor] Each station within a process is called a resource. When you create your value stream map, you need to determine how close to capacity each resource is running. This calculation, usually expressed as a percentage, is the resources utilization. In this movie, I will show you how to calculate utilization. The sample file I work from is an Excel workbook called 02_03 Utilization, and you can find that in the Exercise Files folder. I have all the information from our previous movies. Those are the takt time and also the process time, number of full-time equivalent workers, and the resulting cycle time. To calculate utilization, I need to divide the cycle time by the takt time. So for our limbs, we have a cycle time you see of 110. That's in cell C6. So I want to divide cycle time from C6 by the value in C1. So I'll click in cell C7, type an equal sign, and I'll type C6, which is cycle time divided by C1, the takt time. I'll copy those formula to other cells, so I'll press F4 to create an absolute reference for cell C1, Enter, and I get a utilization of 45.8%. The cycle time, that is the amount of time it takes on average to add limbs to one of our count robots is 45.8% of the takt time, the total time allowed. Now I'll click cell C7, and press Ctrl+C to copy the formula and I'll go over to cell F7, press Ctrl+V, and we get 87.5%. Do the same thing for cell I7 to attach the back plate, Ctrl+V, and we get 50%. And these values make sense. The cycle time of 120 is exactly one half of the takt time of 240, so the utilization of 50% is what we expect. Now the next question is how do we interpret these values. Well, we see that we have some room to spare for limbs and the back; however, with the head, we see that with the utilization of 87.5%, we're getting dangerously close to the takt time. If we feel we're running a little too close to the margin and perhaps in danger of exceeding the takt time, then we could add another, say, quarter FTE, that is a person working quarter-time on this project, to bring the percentage down. One thing we do know is that with the head as our bottleneck that is, the most constrained resource, we don't have a lot of room to grow unless we make some changes. All right, those are our values. Now let's switch back over to the value stream map in PowerPoint and add them in. So we're back in the value stream map that we've been creating so far. I've got all of my other information. Now I just need to add the utilization percentage for each of my three resources, the three stations. Utilization for attaching limbs of 45.8%, for the head of 87.5%, and for the back of 50%. One thing to add before I go is that you don't need to add the utilization percentages together. What you assume is that the work is done in parallel, so you're just worried about keeping each station under the takt time.