iSixSigma

Value Stream Mapping on a Budget (Using a Spreadsheet)

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.

Advertisement

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.

First Worksheet - Steps in Process

First Worksheet – Steps in Process

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.

Handpicked Content:   Resource Page: Value Stream Mapping

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

160

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

80

24

5

13

Funds available

1

8

.25

1

2

3

14

Funds request

16

8

4

4

15

Funds approved

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

40

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

160

80

5

10

12

Prepare necessary documents

80

24

5

13

Funds available

1

8

.25

1

2

3

14

Funds request

16

8

4

4

15

Funds approved (preliminary)

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

40

8

1

1

22

Negotiate with contractor

8

24

4

16

2

2

23

Reach settlement (bilateral)

8

40

4

8

2

2

Handpicked Content:   Organizational Process Flow: A River of Possibilities

 

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%

Comments 1

  1. R. Anderson

    Thanks, interesting article. Is the content of your “first worksheet” in the example correct as intended? Because the steps in the Future State look a whole lot different from the steps in the Current State – not really like a streamlined version of the process, as I expected after reading the text.

    0

Leave a Reply