How to create an Excel based process mapping tool (analyze your processes + more)

I was recently engaged in a business improvement project with some colleagues and we were ruminating on how best to map the process.

Now as you readers know, there are some great tools & techniques out there for process mapping, from swim lane mapping to value stream mapping together with countless software tools you can use.

My colleague and I were discussing when it comes to process analysis what data to capture and why it’s best to capture the type of process step (for example “manufacturing operation” or “inspection”) which can then assist you in identifying “value add” and also in determining improvement opportunities.

For us here, great process mapping is crucial and is one of the 5 key problem-solving tools in business and while we’ve all heard it before the mantra of if you don’t know your processes you can’t manage them is as true as ever.

When it comes to process mapping there is some great advice out there and I’ll give a shout out to my friends over at Triaster for this great piece Techniques to create process maps

The article raises an interesting point that each technique has it’s pro’s and cons so it’s important to select something that’s going to be beneficial.

I’ve previously published a simple process mapping/process analysis tool, and I’ve decided to give it a bit of a tweak/upgrade and I’ve updated the previous template (here) to include a few new columns which you’ll hopefully find useful.

This article covers some updates to that template – if your confident in process mapping (and understand the benefits and pitfalls you can skip to the how-to-build-a-process-mapping-tool-in-excel bit) If you’re interested in the background of process mapping – read on!!

Why process map in the first place?

Process Mapping is the method of utilizing flowcharts to describe a process. Process maps vary on the level of detail they provide from those that display a top-level detail to those that map the activity at a detailed level.

When you’re embarking out on mapping your processes you need to have an objective which helps you understand the detail required and the best method to be used. Some of those objectives could be:

* Gaining a basic understanding of the process
* Identifying what value is added by the process
* Identifying areas for improvement
* Identifying the participants in the process.

I’m usually interested in process analysis (with a view to establishing areas of improvement opportunity) so I like to gather data around the process more so than just merely being interested in the process steps.

Whatever method you utilize for analyzing your processes there are at least 5 key things you should focus on:

1/ Ease of use (complexity usually causes confusion!)
2/ Something you can analyze (i.e. capture waiting time helps discussion on waste.)
3/Something you can present out / use as a communication tool
4/ Something that helps you identify opportunity (link out to 5 wastes)
5/ Something that helps you reduce risk

Indeed if you want to follow this up there’s a great 10 commandments list for process mapping over at https://www.processexcellencenetwork.com/business-process-management-bpm/articles/the-10-commandments-of-process-mapping)

For me, Process mapping is part of business 101. You simply can’t expect to run a successful business if you don’t understand your processes.

My new template.

First of all, I’ve created my template using Excel. Whilst I’m aware that there are some brilliant software packages for mapping processes not everyone has access to these (and not having process mapping software shouldn’t restrict you to not mapping your processes!). Most of us have access to some basic spreadsheet packages and these can be used very effectively to capture processes.

Here’s a screenshot of what I’ve produced.

As you can see, this template uses a simple table to capture the process and some detailed information that is designed to help you analyze the process and assist with improving it.

I like this method of capturing the process in a tabular format because it’s simple and easily understood. I can’t impress on you enough that whatever method you use simplicity is king!

If you’re looking to reproduce the template, the good news is that it’s a fairly simple table and is designed to help capture as much information as possible about each process step.

We use a column for each data that we collect and the user then populates process steps on each unique row. Let’s look at what we use the columns for and what data we collect

1/ Process step # – this is simply the process step number

2/ Description – this is used to describe the process step

3/ Owner – this is the person that executes that step of the process

4/ Team/Operator – this is the functional group responsible for the process step (i.e. Operations, Stores)

5/ Process time – the time taken to undertake the process step

6/ The next section indicates the type of activity undertaken

a) Operation
b) Inspection
c) Transport
d) Delay
e) Storage
The above is what I’ve used in the example but this can be tuned to your business. It adds an extra level of granularity to the analysis and highlights process steps that add value and those that don’t

7/ Data/Paper – Indicates whether the activity generates data or documentation (this can be used for further analysis

8/ Value added – this allows the person capturing the process to describe if this step adds value to the end product

9/ Batching (yes/no) – Is the process step producing an individual product or a batch of products

10/ Monitored/Metric – is the process step monitored via a metric?

11/ What is the target KPI for this process step (where applicable)

12/ Notes: further notes/commentary relating to the process step.

Now as you can see from the above, this process capture tool has been defined to capture *lots* of data! Some of this may or may not be applicable to your business so please feel free to tune it accordingly.

Once the table is populated with information regarding the process you can use the information generated to help in analyzing/improving it.

A quick note that this tool currently assumes a sequential process that follows a step-by-step approach.

I always recommend process mapping as part of a team (and usually try and do this where the process happens!!). Once I’ve got my process mapped I then like to use the team environment to ask;

* Is the process we’ve mapped being run as it should?

* Does the team agree that we’ve mapped the process accurately?

* Will the new process map be followed?

* Have we included anything we shouldn’t have

* Have we missed anything?

I see far too many companies process mapping without going to the process itself. They sit in an office and describe the process without being overly familiar with it. Whilst this does produce a process map it’s unlikely to offer up the benefits you could obtain from actually visiting and walking the process. Doing this produces a process map that’s real and representative, not just something that looks pretty!

And one final thing. I know there is a tendency to think you know best and that you can process map in isolation. But mapping processes is usually a team game. No-one knows the process better than the operators and this is especially true of finding opportunities. The people that put up with the pain of the process day in day out are usually the first to come up with suggestions (even if those ideas are parochial and are at the detriment of adjoining activities!).

So that’s it for now – if you have some feedback we’d love to hear your comments below.