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

Share

Process Model Definitions

April 14, 2009

Process Model Definitions Part 2 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

A process model ties together processes in a similar class. Models can be defined at many levels:

  • Corporate (Core Processes)
  • Business Teams (Sub Processes)
  • Natural Work Teams (Activities)
  • Individuals (Tasks)

Each process can have many or few steps depending on the nature.  In general though each levels is typically made up of 3 to 12 definable work groupings.  A generic corporate process to help you on the way is shown below.

Corporate Process

Corporate Process

The processes can be further defined with:

  1. Overall Objectives
  2. High Level Inputs
  3. High Level Outputs
  4. Technology Enabler
  5. Process Owner
  6. Timing
  7. Process Metrics (if you can’t measure it look at it a different way)
  8. Additional items may also include RACI ownerships and GAPS.  See my previous posts on RACI

A Corporate Process may then look like this:

Corporate Process Define

Corporate Process Define

What is inherently missing from this view thought is 7.0 supporting the value chain.  A typical sub-process definition with a value chain input is shown below:

Sub Process

Sub Process

Value Chain Process

Value Chain Process

Visit back here next week and I will lead you through a complete process definition through the task level with down loadable examples.


Share

Show Me the Money, How to Find it

March 29, 2009

Show me the money, but where do you look?

If you are looking for areas to reduce cost, then analyze your data and find your own gold.  In order to keep focus, follow these simple dimensioning techniques and then dive into the details.

Dimension By

  • Relevance
  • Size of Benefit
  • Potential Penetration
  • Ease of Capture
  • Sustainability

When you add a dimension to your analysis either through a data warehouse, or through simple thought experiments you can quickly pinpoint the quick hits to that of the slow brew money makers.

When you look at the relevance you are exploring weather the opportunity ties to a key value driver, and when you look at the size of benefit you need to tie in the estimated saving potential time frame.  Likewise, will the potential penetration get you more than 50% of the value.  Remember, your resources are scarce so pay particular attention to the ease of capture dimension.  Lastly, will the cost saving initiative be sustainable or will it only be a quick hit that will quickly dissipate over time.

A visual analysis of this technique is shown below.  Hopefully some of this information will lead you through a  thought provoking analysis that will help you find your money quicker through a cost saving portfolio of projects.

Additional filters to apply to the analysis will be:

  • Your current organizational skills
  • Your current knowledge
  • Your prior initiatives and how closely they dovetail with your short list of opportunities

Enjoy and remember, Show me the money!

Share

Intelligent Information

March 12, 2009

Look beyond your own internal data when conducting detailed Analysis or using a Business Intelligence dashboard. Many companies too narrowly focus on internalizing their analysis and often do not include external forces which impact their operations. Examples of external forces to aligning your information with are:

  • Competitor products
  • Supplier influences
  • Pricing indexes
  • Substitute products

Business Intelligence dashboards were once considered a luxury and only available to companies that had operating budgets to support large investments. Intelligence and Analysis are no longer luxuries, but necessities required to help you understand how your internal operations compare to and are affected by external forces and allow you to add information as a strategic weapon to your arsenal. New technologies for BI and Analysis and are now within reach of anyone for a fraction of the investment that was once required. Data and information are exploding at a neck braking pace and if used properly can add a strategic weapon to your arsenal.

What information do you need – There are many straight forward steps to properly set up a BI and Analysis process for your company. First you need to determine what information you need and then explore possible sources of that information. Ask yourself, what I expect to achieve through this endeavor. You also need to determine the interrelation between your data that will allow for efficient and straight forward analysis. The interrelationship between data can be achieved by a process known as dimensioning your data sources. For instance, your product part numbers would be in your sales files, and invoice files; you can link your product codes up to product classes and then relate your product classes to your sales budgets to easily explore relationships within you systems.

How will you explore your information – What tools will you use to explore your data and act upon it? How will you disseminate your findings so the proper people can quickly act upon it? In the beginning keep it straight forward and do not over complicate the process of dissemination.

Where will the information come from and go – You must make sure the data is cleansed and will provide you with intelligent information you require. Then create a repository for you information, this could be as simple as an excel spreadsheet, a small access database, or even as complicated as a large SQL database. Start small to achieve quick home runs and then build out your strategy, this will allow you to not only start with a modes investment of time and money and then build upon your success through time. Do not start with a massive project that tries to link everything together, but begin with a strategy with the end in mind. Large complicated projects have a higher failure rate than a more focused approach.

When and how often will you update your information – Information can become stagnant; make sure a process is set up to allow for easy updates to your data. Keep the end in mind, but continuously revisit your BI and analysis strategy to ensure the processes and stake holders are properly aligned.

There have been many books written around BI, Data Warehousing, and Analysis. If you combine a solid product such as Tableau, with the visual presentation skills defined by Stephen Few, and a solid warehousing process developed by Ralph Kimball the end result will be nothing short of success. These steps shown above are just a sampling of the efforts you must go through, but with a solid understanding of the 4 elements defined above you will be able to define your own BI and analysis strategy. If you need help or require additional information please contact me.

Share

Free Tools

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