From the course: Excel: Business Process Analysis

Determine time for a unit to move through a process

From the course: Excel: Business Process Analysis

Start my 1-month free trial Buy for my team

Determine time for a unit to move through a process

“

- [Instructor] One of the most basic questions you can ask about a process is how long it takes a unit, whether that's a customer or product, to move through the entire process. In this movie, I will show you how to calculate the total time and how often a new unit arrives at the end of the process. My sample file is the Process Time workbook, and you can find it in the chapter one folder in the exercise files collection. I have described a process here that is made up of six different resources. So resources one through six are displayed in row three. And the time it takes for a unit, and again that could be a customer or it could be a product, to move through each of those resources is displayed in minutes in row four. When you determine how long it takes a unit to move through the entire process, you need to distinguish between the first unit and then each additional unit. The first unit is pretty straightforward. You just need to add up all the times in the process. So if I click in cell B7, which, here, is already selected and type equal, I can take the sum, S-U-M followed by a left parenthesis of all the times in the process. And that is B4 through G4. Then type a right parenthesis and Enter, and I see that it will take the first unit 31 minutes to move through the entire process. I can verify that by hand if I want to. Resources one and five, or, excuse me, one and six are five minutes each so that's 10. Resources three and four are six and four, so that's another 10 for 20 total. Resource two and resource five are three and eight for 11. So that verifies the result of 31. Now I need to ask how long it takes each additional unit to come out of the process. And it is very tempting to look at the last resource, resource six in cell G4 and say, oh, I'll get a new unit every five minutes. But that's actually not the case. When resource six receives a unit from resource five, it can finish the processing in five minutes, however, after resource five receives a unit from resource four, it will take eight minutes to process. So that means that resource six will be waiting for three minutes while resource five does its work. Resource five has the longest processing time of any of the resources, so that is the controlling factor. And you can identify that using the Max function. So if I click in cell B8 and then type Equal followed by Max, M-A-X, and a left parenthesis and select cells B4 through G4, the same ones I did for the sum earlier, close out the parenthesis and press Enter and I see that we will turn out a new unit every eight minutes. And again, that's because the resource with the longest time controls how often a new unit rolls off of the production line.

Contents