Waterfall Chart

February 2, 2011

Waterfall Chart Defined

The waterfall chart is a useful graphing tool used by the financial community, business analyst community or anyone interested in graphically displaying quantitative variances for a single variable. I have also heard the Term “Bridge” used to reference the waterfall chart. The term “Bridge” is often used in the financial community.

Possible variables used in a waterfall chart may include:

  • Sales
  • Hours
  • EBIT
  • Inventory
  • Etc.

Waterfall Chart Creation

The waterfall chart is nothing more than a combined column chart with certain columns hid to help the viewer digest the information. The set-up of the data and understanding how this floating column chart works is a straight forward exercise once you understand the technique. Some of the formulas used in automating this chart are moderately complicated, but the concept is sound. A down-loadable example can be grabbed here for your own use, “Download Waterfall Chart.”

Example Data and Corresponding Chart

In this example I will use sales as our variable, though in this example I would hate to be this sales manager.

Description Amount
Sales Goal $10,000
Lost Sales ($1,000)
New Product $550
Lost Sales Channel ($11,000)
New Sales Channel $6,000
Specials $1,000
Recall ($1,000)
Misc. Adj. ($4,000)
New Big Box Deal $7,000

I developed this example to show how the large swings would appear in the graph. Notice how easy it is to visually view these variable lined up now. The graph provides a richer visualization for the user to discern what happened during the year. Instead of just knowing that you achieved or missed your goal is not enough, you need to look at the granularity to determine what actions you can take now or plan on taking in the future to remedy any shortfalls, and in this case there are a lot.

If you Google Waterfall chart you will find many variations of this chart in excel, some examples may be easier to understand so explore and report back on your findings. Also, a great article on how to use Tableau, (a tool I use regularly), to generate the waterfall chart can be found here “link to Tableau Waterfall Chart.”


Process Model With Downloadable Examples

January 29, 2011

Process Model Definitions Part 3 of a 3 part series

  1. Part 1 Defines a Business Process Model
  2. Part 2 Provide Process Definitions
  3. Part 3 Walk through of a Complete Process with Down Loadable Examples

As mentioned in the previous posts, this is the final series in process model definitions; this post will tie the three part series together and provide examples you can download.

A completed process should contain:

  • A goal “milestone(s)”
  • Process flow, some type of ordered steps
  • Inputs
  • Outputs
  • Roles/owners “see previous post on RACI”, Responsible, Accountable, Consult, Inform.”
  • Metrics
  • Referenced documents or tools

In addition you should develop the process so it is easy to understand for any user and contain institutional knowledge specific to your company. The nomenclature you use in your process is important also. These examples represent a small sampling of the nomenclature available to model processes; try and use a set that is tailored to your company needs or one that may be specified by your company.

These process samples are examples only; there would be many additional steps with more detail and flow than what is presented here (this is just set up to show how a process flow may look.)

The first document is the process flow itself. The image below shows an example of cycle counting equipment. You can download this Visio sample here “Process Visio Example.”

Process Example

The second document to consider is the process matrix. The matrix contains information at the task level and defines:

  • Task Step
  • Task Description
  • Timing
  • SOP
  • Input
  • Output
  • Software “if used”
  • Misc. Notes

Again, this document was generated in Visio and the file can be downloaded here “Process Visio Matrix Example.”

Process Matrix Example

As mentioned in a previous post, the RACI diagram is useful in identifying roles within your process. An example of the RACI diagram can be downloaded here “RACI Excel Example.”

Building off this process example, an SOP can also be developed. An example of a write-up can be downloaded here “Write-Up Word Example.”

And finally, when you roll out a new process it must be agreed upon by the major process shareholders. This document should briefly describe the process, present a way to measure the effectiveness of the process through metrics, and present some type of sign-off document. An example of a sign-off document can be downloaded here “Sign-Off Document Power Point Example.”

If you have additional thoughts on this subject or would like to share your examples please let me know.


What Is A Process Model?

April 10, 2009

Part 1 of a 3 part series

  1. Part 1 Defines a Business Process Model
  2. Part 2 Provide Process Definitions
  3. Part 3 Walk through of a Complete Process with Down Loadable Examples

An enterprise, or business-level, process model is a tool that clearly identifies the processes that the entity engages in during the value creation process. It helps communicate to individuals what their role is with respect to the overall organization. It clearly defines, at an individual level, the tasks to be accomplished.

But what is a business process?

An enterprise, or business-level, process model is a tool that clearly identifies the processes that the entity engages in during the value creation process. It helps communicate to individuals what their role is with respect to the overall organization. It clearly defines, at an individual level, the tasks to be accomplished. Thomas Davenport Process Innovation. 1993

A process, then, is a specific ordering of work activities across time and place, with a beginning, an end and clearly identified inputs and outputs. These processes almost always cut across the functional boundaries that exist within a company.

Process Model Entities

  1. Core Process
  2. Sub Process
  3. Activities
  4. Tasks
Process Model Entities

Process Model Entities

The next step in defining a process model is to then define the Model Hierarchy.  The hierarchy is different from company to company.

Business process can be specified in terms of level of detail.  Each level is typically made-up of 3 to 12 definable work groupings.  As defined earlier with the process levels, a generic process model hierarchy along with process levels is shown below.

Process Model Hierarchy

Process Model Hierarchy

Visit back here next week to discover straight forward process definitions followed up with business process examples.


Who Holds The “R”, The RACI Chart

March 26, 2009

Who Holds The R, Exploring the RACI model

The RACI model is a powerful tool utilized to define roles and responsibilities. In general, you match up roles and responsibilities with processes. The tool is extremely useful when rolling out a new change management program, or just uncovering the processes that make your organization function and identifying the participation in those processes. First let’s discuss what RACI stands for:

RACI Legend

RACI Legend

R–Responsible-The buck stops here. Whoever is responsible needs to make sure that the process works as planned. The R owns the process/problem or project.

A–Accountable-This is the person who is delegated the task of completing the Activity. They support the person who owns the R.

C–Consult-This person usually have in depth knowledge of the process in question and all major decisions need signed off by this individual.

I–Inform-The people who need informed of activity taken, but not necessarily consulted.

Each individual who is involved in a process is either identified as an R, A, C, or I. No two individuals in one process should both have the R.

RACI Chart

RACI Chart

Typical steps in designing a RACI process:

  1. Identify the process in question completely. Start from a top down approach viewing the large picture and map out the process in enough detail to support your requirements. You may only need the top level processes, but in order to define or accomplish the task at hand defining the sub processes, tasks, or steps may be required. Each program is unique. List the process steps horizontally down the left hand side.
  2. Identify the roles that will be impacted by the program/process, and those that will implement the program/process change and list these vertically across the top.
  3. Each role will have an owner. You can list the person here if you want, but in order to make the process timeless you should list the role and assign the role to an individual.
  4. No identify who has the R first and then complete the ownership of A, C, and I. Remember, no two people holds the R for a single process. The buck needs to stop somewhere….This is where the phrase who hold R comes from.
  5. Review any gaps in the process, make sure only one R is in each process, make sure each process has an owner. Sometimes when you have arguments of who holds the R for any given process, the resolution usually resides in looking into the process further, maybe you need to explore the sub-processes, the roles, or the tasks associated with the process. Once you get to a point that only one person holds the R you have dove deep enough into the process.
  6. One exception to multiple R’s in one process may entail the difference between who is responsible for the Initiative, and who is responsible for the Process. This at first seems like the difference between the Responsible and the accountable party here, but with the correct boundaries set up this can be a powerful concept especially when considering the interaction of a Program Management Office PMO with a large organization. I have included this as an example in the RACI chart view. Be careful with this concept!

An example of a RACI chart for the top view of a sales process is shown to help visualize the RACI diagram. Note how colors are used in the matrix, this makes it easier for others to quickly understand the relationship of the process. An excel template you can use to start you next project, or process improvement initiative is attached here.

One very important step in any project or improvement initiative is the understanding and mapping of the underlying process that support the program being implemented or enhanced. Check back soon for an installment of defining and implementing a process map.


How to squeeze your Working Capital (GMROWC)

March 22, 2009

Gross Margin Return On Working capital is a great measurement used by companies to explore their inventory investment and the return on that investment in relationship to their Cash Conversion Cycle. Using GMROWC you will be able to determine the contribution each product, product mix, or division adds to the bottom line of your company; this analysis will depend on the granularity of your available data. You can also use GMROWC to help you obtain better terms with your suppliers so they understand the contribution they bring to the overall value stream. You will need the following measurements for this calculation:

Required Information

    Gross Margin Return On Working Capital, GMROWC

  • Product Sales for Month Annualized
  • Recorded Cost of Product for Month Annualized
  • Accounts Payable
  • Accounts Receivable
  • Inventory Value

See figure 1 for a visual depiction of the GMROWC. Click Here For Calculator

The calculations for determine your GMROWC on the surface may appear simple, but if you start exploring your GMROWC from a top down analysis it can start to get complicated. For instance, if you initially want to know the total GMROWC for your entire inventory portfolio, this is straight forward, but if you start exploring the GMROWC by product group, by product, or by vendor you need to make sure you data is granular enough for these calculations.


Gross Margin Return On Working Capital, GMROWC

GMROWC Calculator "Click Here"

To gain an understanding of the power of this simple measurement I have created a snapshot of a distribution company which appears to be in distress. Let’s explore the GMROWC for a distribution company with the following characteristics:

  • Current Accounts Receivable=$17M
  • Yearly Sales=$80M
  • Current Inventory Value=$26M
  • Yearly Cost of Goods Sold COGS=$58M
  • Inventory Accounts Payable=$5.5M

Product GM%; ( Sales-Cost)/Sales, ($80M-$58M)/$80M)=28%

DSO=AR/(Year Sales/365), $17M/($80M/365)=78 Days

DIO=Inventory/(Year COGS/365), $26M/($58M/365)= 164 Days

DPO=Inventory AP/(Year COGS/365), $5.5M/($58M/365)=35 Days

Calculate Cash Conversion Cycle DSO+DIO-DPO=207 Days

Calculate Working Capital Turnover 365/207=1.77 Turnover

Calculate GMROWC=1.77 X GM%=49

A sampling of the observations you can make from these calculations are as follows:

  • This Distribution company is only making a 28% product Gross Margin, the marketplace must be competitive or the purchasing department is not cohesively tied together to take advantage of it’s buying power and not negotiated rates properly. A lot of action items can come out of this observation.
  • The days in inventory is too high at 164, their could be a lot of dead inventory, or the company could be holding too much inventory. This area is rich for analysis.
  • The DPO=35, the company may be paying it’s vendors too quickly or a couple vendors may be pulling this figure down. Look into obtaining better terms from the vendor.
  • With your receivables figure high at 78, collections may need a new strategy to allow for quicker turnaround on payments from customers.
  • Overall the cash conversion cycle is 207; working capital turnover is therefore 1.77. This figure needs reduced and by exploring the DPO, DSO, and DIO in more detail a plan will materialize to lower this figure.
  • The GMROWC is only 49, by pulling the different levers available to this distribution company from just a sampling of the available resolutions mentioned above the GMROWC metric can be tracked and improved upon.

This hypothetical company profile shows the power behind tracking a reliable metric and then making plans to attack areas that can be improved upon. The GMROWC should be explored from many angles, a few of which are:

  • By Division
  • By Warehouse
  • By Product
  • By Product Group
  • By Vendor
  • By Purchasing Manager

Free Tools

Explore these free tools which hopefully will make your life a little easier.