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 Flow

Value 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 Paper

Another 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.


You can draw your VSM directly using Google Spreadsheets in Google Drive and give write access to your document to all participants in the team and it will take shorter to build the VSM than if you have to write and stick the post-its. You can even have team members participating from remote locations.

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.
You can use a Virtual Obeya Room to house the VSM and use, as required, a room with a projector as physical Obeya during the initial VSM construction.

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 Streams

More 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 Onedrive

In 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 Gemba

After 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:

  1. 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.
  2. 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.
  3. 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
  4. 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
  5. 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.
  6. 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 Collection

After 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.

Collecting WIP data manually may not be very useful since the values may vary widely depending on the moment we visit the line.

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 Analysis

We can analyse these collected data with pivot graphs:

We enter average process times in column K and standard deviations in column L
We enter the % of YES data in column N:

Average inter arrival cycle is 5 minutes therefore this is the takt required for our complete repair line.


Looking at the inter arrival times histogram we notice they follow an exponential distribution, something quite typical while operation times are close to normal distributions.

From this analysis we can enter average inter arrival time in cell K3

Average measured repair cycle of 5:31 is a bit larger than the target of 5:02 which is the takt time defined by the average inter arrival time

VSM with Feedback Loops and Alternative Routes

Complex 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.


When the process doesn’t have a single flow but there are several branches you need to estimate the proportion of items that will flow along each branch and this you can do by collecting data along a period of time.

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 Steps

Since 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 are going to calculate the staffing required (Full Time Equivalent) for each process step in M4 to M13 using Solver.
In order to balance the line we need the right staffing on each step to insure enough capacity to handle the product arrivals. This means that Cycle time  ≤  Inter arrival time (takt).

Cycle time = (Average time)  x  (% Throughput )  /  (Staffing)

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.


We are adding the constraint M4:M13 ≥ 0.1. The proposed staffing by Solver in the yellow cells M4:M13 is: 

Which gives a total staff of 13.7 (M1). This is a theoretical minimum which assumes all staff has all skills, which is not the case.

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:


Where the total staff has increased from the theoretical 13.7 to 15 after considering the different skills required in the different steps.

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 Time

When 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: 
Lead Time  = WIP  x  Throughput

T1 has the average total lead time for one product from the time it is received from the customer until it is returned repaired.Value-Add-Time Vs Non-Value-Add-TimeColumn K has the process times for each operation. K1 has the total average process time for a product taking into account that not all products go through every process step as shown in column O.

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.

Convert your VSM into a Simulator

We will add some additional columns to our VSM converting it into a simulator in our next article: How to convert your VSM into a simulator to experience variation


Post a Comment

Popular posts from this blog

Excel VSM Simulator

Six Sigma Virtual Catapult