Value stream analysis is one of the easiest methods to analyze a process and identify areas that can be improved. Even someone inexperienced can find the low hanging fruit. But what do you do when you want to conduct a value stream analysis and the boss tells you that he is not going to buy you dedicated software and pay to train you because then he would have to do the same for every Belt in the organization and he cannot afford it?

The answer is simple if you have any background in using a spreadsheet. While you cannot perform some of the additional functions that can be achieved by the software packages created just for this purpose, you will be able to develop a value stream map (VSM) that will achieve what you are trying to accomplish.

The process requires three worksheets but all are fairly simple and once the templates are set up, you can record future VSMs with little setup work. The first worksheet indicates the actual steps in the process. Start with the current process and use two boxes per step. The top box indicates who performs the step and uses color coding. The box also is numbered to indicate what step is being performed. The box below indicates what is actually being performed. Below the current state, repeat the identical steps to follow the future state.

This has two advantages over the specialized software. First, it shows the current state and the future state next to each other for quick comparison. Even the unsophisticated used can determine easily how many steps have been eliminated. Second, this system forces a linear view. Often VSMs are created from flow charts which show several steps in parallel and a great number of steps vertically. A good VSM is totally horizontal. This method forces steps to be considered in linear way. While you are free to add another row to show two process streams, you will be discouraged from having multiple process streams unless you truly go through the mental thought process to create them.

The next worksheet is where data is entered. You can set up the data in any of several formats. One way is to actually have two worksheets side-by-side, one for current state and one for future state. This method is easier to work with since you enter the data for one and then for the other independently. The other method is to enter the process step on each line and continue the data out with all calculations. There seems to be an advantage in creating two smaller worksheets but formatting is all a matter of preference.

Table 1: Second Worksheet – Enter the Data
Current State
Step	Task	Cycle Time		Touch Time		People
		Minimum	Maximum	Minimum	Maximum	Minimum	Maximum
1	Problem discovered	16	32	8	8	5	10
2	Field investigates problem	24	40	2	12	2	3
3	Modification required decision point	1	16	1	2	2	4
4	Assign number and prepare modification file	1	4	1	1	1	1
5	Define statement of work	16	40	4	16	22	3
6	Project manager enters into project tracking system	1	4	1	1	1	1
7	Modification review board	40	80	8	16	6	6
8	Design support for change order	0	160	0	80	5	10
9	Allocate funding	8	40	2	4	5	7
10	Funding project	8	40	2	4	5	7
11	Notify change customer	8	16	1	1	3	3
12	Prepare necessary documents	0	80	0	24	0	5
13	Funds available	1	8	.25	1	2	3
14	Funds request	0	16	0	8	4	4
15	Funds approved	0	0	0	0	0	0
16	Budget amended and approved	8	40	1	2	2	2
17	Request for proposals	8	16	1	4	2	2
18	Receive contractor proposals	80	360	1	1	2	2
19	Prepare pre-negotiation objectives	0	40	0	8	1	1
20	Legal review of changes greater than $100,000	24	40	1	2	2	4
Variables limited due to formatting constraints
Future State
Step	Task	Cycle Time		Touch Time		People
		Minimum	Maximum	Minimum	Maximum	Minimum	Maximum
1	Problem discovered	16	32	8	8	5	10
2	Field investigates problem	24	40	2	12	2	3
3	Modification required decision point	1	16	1	2	2	4
11	Notify change customer	8	16	1	2	2	4
5	Define statement of work	16	40	4	16	2	3
4	Assign number and prepare modification file	1	4	1	1	1	1
6	Project manager enters into project tracking system	1	4	1	1	1	1
8	Design support for change order	0	160	0	80	5	10
12	Prepare necessary documents	0	80	0	24	0	5
13	Funds available	1	8	.25	1	2	3
14	Funds request	0	16	0	8	4	4
15	Funds approved (preliminary)	0	0	0	0	0	0
17	Request for proposals	8	16	1	4	2	2
18	Receive contractor proposals	80	360	1	1	2	2
19	Prepare pre-negotiation objectives	0	40	0	8	1	1
22	Negotiate with contractor	8	24	4	16	2	2
23	Reach settlement (bilateral)	8	40	4	8	2	2

 

Finally you will need to create a summary worksheet. Again this can be placed this on a separate worksheet or at the top or bottom of the data sheet. Wherever this section is placed, it should be easy to find because this where you perform your analysis. As is obvious, you can have as many variables as you desire. Adding variables is as easy as adding “minimum” and “maximum” columns for the current and future states. You can track further by time period or any other factor you think necessary.

Table 2: Third Worksheet – the Summary
	Current State	Future State	Improvement
Steps	25	17	32%
Cycle Time	76/338	60/212	21-37%
Touch Time	18/34	15/24	16-29%
Touch Time/Cycle Time	16.8%	27-47%
Number of People	65/82	40-52	36-38%
Approvals	44-46	29-31	32-34%
Rework	365%	185%	49%