Excel Value Stream Map
Value Stream Maps graphically represent process flow as it is actually performed.
VSM is typically built following a visit to Gemba (where things are really happening) in order to depict what is really going on (rather than the theoretical process described in standards and procedures).
This approach and VSM concepts are excellently described in "Learning to See" by Mike Rother
To follow the value stream you can follow a product along the manufacturing line or a customer order along the administrative value stream like in https://hbr.org/2004/07/staple-yourself-to-an-order
Horizontal Vs Vertical FlowValue Stream Maps are typically designed to flow horizontally left to right. When a process is complex it becomes difficult to view the whole process: it may require several pages and this makes it difficult to see the total picture.
Making the VSM vertically allows us to visualize complex processes in a single page.
Post-its on Brown PaperAnother common practice is to map the process by sticking post-its on a wall or large wrapping paper.
A dedicated room is normally used called Obeya or War Room.
This method has the advantage of simplicity:
- all participants write post-its in parallel and stick them on the right sequence on the wall.
- Sequence can easily be changed just by moving post-its
- Swim lanes can be drawn to specify who is doing what
Some Limitations of this Approach
- The Obeya room used to build the VSM is blocked and can't be used for other meetings or activities
- It becomes impractical to save all the work done to continue working on it later on.
- All the hard work done during several hours often ends up in a long roll of brown paper with pos-its stored in a cupboard which no one looks at again.
- It is also difficult to communicate to others: photos are difficult to read.
- Some processes involve several remote locations so a physical Obeya is not practical
- If you ask other people, not present in the VSM exercise, to give their input they can not modify the work you have done.
VSM directly built in a spreadsheet in the cloud (Google Drive or One Drive)Another alternative to this post-it approach is to build the VSM with the help of PCs, tablets or smartphones.
Line operators are quicker and more skilled at typing into their smartphones (see how they type WhatsApps) than hand writing post-its (specially if someone else has to read them). They can easily type while they discuss the VSM with the team.
The first step is to go to Gemba: the team has a good look at what is going on and then they gather in a room where the VSM being built is projected on the wall (large screen if possible). All participants write the process step descriptions with their portables or smartphones into the spreadsheet in Google Drive that is visible on the wall. Typing into the spreadsheet can also be done with voice using the smartphone (that's how I do it since I am no good at typing) .
The VSM can also be built directly in the cloud while visiting the line with the smartphones of the participants.
In this group exercise it is easy to make changes in the sequence, insert new steps, etc. At the end of this process we have a workable VSM agreed to by all participants which represents the current state as seen in Gemba.
After the current VSM is concluded we give write access to all process participants including those who were not present in its elaboration, in this way they can make modifications to make sure the VSM reflects what is really going on in Gemba. We might find out that we have more than one process, for instance the night shift is doing it differently. Google Drive keeps track of who has made what modifications.
We also give read only access to the VSM in the cloud to other people and plot an A1 size copy to stick on the department or line wall for everyone to see.
We keep backup copies of the different stages in the modifications to make sure we can go back to a previous version.
I find it very useful to indicate who does what in the VSM instead of making another separate document. In order to do this we include participant swim lanes on the left which are easily filled while building the VSM.
In the example above you can notice that the switchboard participates in the process on several occasions.
Complex Value StreamsMore complex VSMs with feedback loops can be done with Excel such as:
Download this Excel simulator VSMandSimulation1.xlsm from OneDrive folder: Polyhedrika
Share your VSM in the Cloud with Microsoft's OnedriveIn a complex process as the one above Google Drive is not able to manage the graphic flow chart so it is best to use Microsoft's OneDrive:
In this case it will even be possible to run the simulator we will see later in the cloud.
Process Times Collection in GembaAfter we have agreed on the VSM flow (who does what and when) we need to go back to Gemba to collect process times and WIP data for each process step. If we use the Excel approach we can access the VSM we have just produced with our smartphone or tablet and directly enter the numbers in our spreadsheet.
It is easy to share this work among all participants each taking care of a few process steps.
They can all write simultaneously into our VSM in the cloud.
Excel VSM Advantages:
- The team involved in the VSM exercise can be located in remote locations and they can all share a spreadsheet in the cloud communicating via Zoom, Meet, Skype, etc.
- To insure your VSM represents the current reality (hidden factory) rather than a theoretical process flow you need to validate your VSM with all other participants who were not in the room: line operators, suppliers, maintenance, etc. You can not start improving until you are certain of what the current reality is. This is easily done by giving them access to the VSM in the cloud.
- All the work done is not lost at the end of the meeting and it can be updated remotely at a later stage by all process participants
- You can easily keep track of the different versions of the VSM as you implement your improvements and you can even go back to a previous version if something goes wrong
- You can go to Gemba with your VSM on your tablet or smartphone and directly enter process times, WIP, etc. on your VSM in the cloud.
- Excel allows the addition of the process parameters for each process step by adding as many columns as required on the right:
- Process time
- Setup time
- Cycle time
- Work-In-Process waiting
Complex Process Data CollectionAfter we all agree with the Value Stream Map we need to collect the data. For a simple process, if no data is available we can manually collect the data in the line. This will give us a rough estimate of process times of each step.
Process times may also have significant variation.
Ideally we must define data collection points along the process and collect data automatically if possible. If data needs to be collected manually we can use a bar code scanner to read the product serial number on each point and the current timestamp can be automatically collected in the spreadsheet.
Process Data AnalysisWe can analyse these collected data with pivot graphs:
From this analysis we can enter average inter arrival time in cell K3
VSM with Feedback Loops and Alternative RoutesComplex processes require often feedback loops: repair failing items and test them again. These loops are an essential part of the VSM because they often become the bottleneck for the total process.
In this Repair process VSM the average time it takes to receive a product is 2 min and its standard deviation 0.5 min.
Throughput in the Different Process StepsSince not all products flow through all steps we need to know the proportion of the throughput entering the line which is flowing through each step. This is reflected in column O
- All products entering the line flow through the first 3 steps (100% in column O). In step 4 (line 6) only the products which are not under warranty are processed: 80%.
- In line 7 only products to be returned to the customer without repair are processed: 5%.
- In the repair step (line 10) 100% of products need to be repaired but some products more than once: those failing Functional or burn-in tests. 119% of products need to be processed.
Line Staffing Calculation
We calculate the total staff required in the line in cell M1 by adding all the yellow cells in column M. This cell value is what we want to minimize as long as we meet the requirements that all cycle times P4:P13 are below the inter arrival time in K3.
In cells D1 to H1 we obtain the compound for each process participant (some perform several operations) obtained from the column M.
If Receive and Coordinator can be combined we may have to round up the sum of the two from the 1.6 requirement to 2.0
Invoicing we round up from 1.8 to 2. Test from 2.9 to 3 and repair from 7.4 to 8.
This manual rounding will lead us to this situation:
This increase in the staffing has an effect on lowering the cycle of the steps affected below the takt time of 5 minutes.
Looking at the % Throughput column we notice that some steps only process 4% of the products to be repaired while test and repair have to process 123%. The reason for this is that only 80% pass the test OK the first time. This means 20% have to be tested a second time. Same with burn-in, where 10% fail and have to be retested.
Work In Process and Lead TimeWhen we visit Gemba to collect process times we can collect also the number of products waiting to be processed before each workstation this is WIP which is in column Q of our chart. Q1 has the total WIP in the line.
Every product arriving to a workstation will have to wait in the queue until it is its turn to be processed. Column T has the lead times for each product to be processed on each workstation taking into account the length of the queue. This is calculated with Little's law:
Just now the total average lead time to repair a product is 19 hours (19:00) as shown in T1. Out of these hours we are only adding value for 66 minutes 01:06 (total process time), as shown in K1. The rest of the time is waiting time along the different workstations which is non-value-add
The difference Lead Time - Process time = 13:00 - 01:06 = 11:54 is the Non Value Add Time
The ratio VAT/ NON VAT is therefore 01:06/11:54 = 9.2 %
We must bear in mind that the WIP data we have collected from Gemba has typically a lot of variation so this ratio we have calculated may vary widely depending on the moment we visit the line.
This is why we need to collect variation data and use simulation to get a more realistic picture.