Determine inventory waiting time - Microsoft Excel Tutorial

- [Instructor] Once a station completes its part of a process, its output becomes inventory for the next step in the process. In this movie, I'll show you how to calculate inventory waiting time and add that information to your value stream map. I'll start in Excel using the 02_04 Inventory workbook. You can find that in the Exercise Files folder. I have information for my three stations, that is, attaching the limbs, the head, and also the backplate. And I've changed from a takt time to daily demand. The weekly demand, as we've seen in other movies, was 600 units. If you work five days a week, then your daily demand is 120. Now we can use our average inventory levels, that is, the number of units on hand in inventory for each of our three stations, to calculate the number of days of inventory that we have available. So I'll go down to cell C5 and click, and all we need to do is divide the average inventory by the daily demand, and that will give us our days of inventory. So in cell C5, I'll type equal, and then C4 divided by C1. And I want to copy this formula to other cells, so I will press F4 to make the C1 reference absolute. Enter, and we see we get 40 days of inventory. 4800 divided by 120 is 40. Now I can copy that formula from C5. So I'll click cell C5, press Ctrl + C, go to F5, press Ctrl + V. We have 30 days, 3600 divided by 120. And we'll do the same thing for cell I5 for the back station. So I'll click cell I5, Ctrl + V, and we get 35. And I'll press Escape to get out of cut/copy mode. So those are the number of days of inventory that we have on average for each of these three resources. Now we can switch over to the value stream map and add that information to it. Here is our value stream map with all the information that we've added before, and now all we need to do is add our three inventory elements. So over on the left, you'll see we have 4800 units, daily demand of 120, and an average inventory of 40 days. Then we can do the same thing for inventory going to the head station, about 30 days. And for the back, we have 4200 units, daily demand of 120, and that means on average, we will have 35 days of inventory. And with that information in place, we can continue with our analysis.