Tornado Charts in Excel 2007 Update

This is an update to my previous post on tornado charts.

As reader Shaun pointed out, the instructions were created under Excel 2003. Let’s refresh them to Excel 2007.

There is no need for any external add-ins for making good-looking tornado charts. And it takes only 5 steps. Let’s start with data in a table like this:

1. Select the data, excluding the Delta column
2. On the Insert ribbon, choose Bar. Pick Clustered Bar (first one in the 2-D section)
3. Right-click on the horizontal axis, choose Format Axis. On the bottom of the Axis Options pane look for Vertical Axis crosses:, choose the Axis value radio button, and type your base case value (7 in the example)
4. Without closing the window, choose the vertical axis of the chart. Again on Axis Options, check the box Categories in reverse order, and set the Axis Labels menu to Low
5. Without closing the window, click on any of the bars, which should bring the Series Options pane. Move the slider Series Overlap completely to the right (Overlapped).

That’s it! Now you can format the chart as you like. The final result should look something like this:

Final Tornado Chart

Keep reading to learn more, to watch a screencast, or download the Excel file

(more…)

Logistic model for the s-curve and project management cost estimation

I posted a while ago an article on Modeling market adoption with a logistic curve in Excel. It has been quite popular on this site, and even at some point was linked as a reference on Wikipedia. Shameless plug – if you find it valuable, please ask Wikipedia maintainers to keep the link.

Recently reader Mina asked how to use the Excel model in the context of project management. The particular question is: if the project duration changes from 18 to 48 months, what is the new spending curve? Fast and quick answer would be – it’s not applicable. This post will take the question for a spin, though.

First off, the PMBOK, or Project Management Body of Knowledge, tries to standardize and unify the terminology that PMPs use when managing projects. The term used to refer to the spending curve, along the life of a project is called “s-curve” in the PMBOK, just because in real life, projects tend to have low budgetary requirements in the early stages (you are framing the project, defining what will be done, etc), then a bulk of spending in the middle, and finally, low requirements again at the end. With that general pattern, the cumulative spending plan roughly looks as an s-curve.

In most cases, the easiest way to answer the question would be to look at your Gantt chart, estimate which are the tasks that will be delayed/extended, and regenerate an spending curve.

If you are not actually managing the project, but instead you are doing analysis on “what ifs” regarding projects where you don’t have the actual list of tasks, or you are doing portfolio management and you are forecasting across a portfolio of projects with different life spans and budgetary needs, you may be fine using a logistic model. Your needs may be different.

With that preamble, here we go. I’m going to use the estimate percentages of spending in Mina’s question

The process will be quite simple:

1. Fit the data to a logistic model
2. Apply the derived model to the new project duration
3. Infer the spending by month under #2

An Excel file to do this can be downloaded here. By downloading it, you are agreeing that any damages, consequential or incidental arising from using this file or the information in this post are your sole responsibility, and you explicitly releases me from any liability.

If you look at the table below, the first two columns are the data provided by Mina, the third column just adds up previous spending, then column D uses my simplified Excel logistic model =saturation/(1+sharpness^((hypergrowth+takeover/2-year)/takeover)) to forecast the cumulative spending, and column E is simply the square of the difference between C and D. The formula for E2 is =(C2-D2)^2. We’ll use the sum of minimum square of errors to fit the curve, to keep things easy. Other fitting techniques are OK too.

Excel Table showing the process

Then we use Excel Solver, to minimize E21, subject to the condition that $D$19=1, by changing saturation, hypergrowth, takeover, and sharpness (C21 to C24). Fill in the most realistic guesses you can find for these parameters before running Solver. Given the technique Solver uses, your guess of sharpness will be barely modified (if at all), so spend some time looking at your data and tweaking manually.

As you can see in the image below, this step fitted the cumulative spending curve to the logistic function.

Fitted s-curve logistic function to data

Column F in the table just infers the spending by month by subtracting the cumulative on each period to the cumulative on the previous one. As you can see below, even if the cumulative curve fitted more or less nicely, the inferred monthly spending may look surprisingly different to the original.

Monthly spending curves side by side

You’ve got to remember, all models are a simplification of reality, to extract the things that are important for the particular use of the model. As mentioned above, if you are doing “what if” analysis or portfolio management, this simplification may be acceptable and the differences are not a surprise to you.

Finally, we define two new constants newhypergrowth=hypergrowth*newlength/oldlength, and newtakeover=takeover*newlength/oldlength which simply allows us to use the s-curve over a longer time span. The new cumulative spending (across months 1-48 in the example) would be =saturation/(1+sharpness^((newhypergrowth+newtakeover/2-newyear)/newtakeover))

El Juego de la Cerveza – Systems Dynamics

One of the activities conducted during the System Dynamics class in Colombia was the Beer Game – El Juego de la Cerveza

It allows students to experience first-hand how structure of business organizations impact their behavior, to some extent irrespectively of how skilled their managers may be. In the game, a 3 tier distribution system delivers beer from a factory to consumers. Only the retailer knows the consumer demand. As information and goods propagates through the chain with delays, oscillation occurs.

Students play competitively, as a pool of money collected from them at the start of the game will be awarded to the winners. A very entertaining and active dynamic develops during the 4 hour exercise.

The Systems Dynamics Society offers kits for playing the beer game, that I recommend. They include everything you need to run the game, as well as videos of Professor Sterman running a session.

I have attended many of his sessions, both as student and observer – my employer sponsors the Beer Game during incoming MBA orientation at MIT. Professor Craig Kirkwood at Arizona State University also has very good materials and hints.

I decided to run with a low-budget version that uses beans, pieces of paper and boards printed on common plotter paper. Everything is in Spanish language. I believe publishing the materials here may be useful to colleagues in Latin America and other Spanish-speaking countries.

Here is the board for the game, and here are the slides

For editable versions of these files, please contact me directly.

Gearing up for summer classes in Colombia

I’m very excited about the invitation I received by Universidad de los Andes to talk about policy and decision making using a systems dynamics perspective.

I haven’t been back to Bogota since 1999, except a quick stop-over during our honeymoon.

This is also exciting as I will add more content to this website in preparation for the classes. Stay tuned.

Here are the links to the courses:

Escuela internacional de verano / Facultad de Ingeniería – Universidad de Los Andes

Definición de políticas y toma de decisiones en organizaciones globales – Una perspectiva de dinámica de sistemas
Juan Carlos Méndez
Global Portfolio Strategy Manager – General Motors
Junio 30 – Julio 11 (40 horas)

Escuela de Verano – Facultad de Ingeniería

Políticas y decisiones en organizaciones globales desde la dinámica de sistemas
Julio 01 al 11 de 2008
Invitados Internacionales:
Juan Carlos Méndez: Gerente de Desarrollo de Negocios para Asia Pacífico de la compañia General Motors, basado en Shangai, China.

System dynamics interpretation of the logistic and Bass models

I have received a number of comments regarding the Simplified Excel Model for market adoption published a few months ago. Reader Vince asked how to extend the math behind it to comprehend effects like cross-segment interactions.

There is no simple answer, and this post is an attempt to point readers to ways to think about what they want to model, as well as giving helpful resources for further study

In my opinion, one of the best approaches to understand market adoption is through system dynamics. One of the advantages of the methodology is that it allows you to conceptually link business effects and relationships to the equations. I touched on this issue on on a previous entry, and here I will try to explain further.

The logistic equation (shown below) is a commonly used way to model market adoption.

From a System Dynamics perspective, the logistic model can be explained looking at the following model (click for full size): The boxes, called “stocks” in SD terminology, represent an accumulated quantity over time. One way to think of stocks is a bathtub. The amount of water in the tub is the accumulation over time of how much water you added through the faucets, less how much water you let out through the drain.

On the model, there are two stocks: how many potential adopters are out there (left side) and how many adopters are (right side). The pipe that connects the boxes is called a “flow”, and it shows a valve, whose value represents how fast potential adopters turn into actual adopters (thus we call it Adoption Rate). Again, in the bath tub analogy, we can think of the value of the flow as how open or closed the faucet is.

Adoption rate depends on how big the population is (the larger the population, the larger the adoption rate), how much the adopters interact with potential adopters (creating the “word of mouth” benefits), etc.

As stocks are accumulations of whatever flows in minus what flows out, from a mathematical perspective, the value of a stock is calculated integrating over time the values of the net flow. On the logistic model, the arrow that links the stock and the adoption rate flow means that the flow changes proportionally to the stock – i.e. if I have more potential adopters, there are more possibilities for contagion, when a user talks favorably to a potential user about the product. The net result is an exponential behavior, which, after some mathematical reduction, is represented by the formula above.

If I want to explain a business audience some market adoption dynamic, it possible to do it talking in terms of stocks and flows (once the audience is comfortable with these terms). It’s almost a guaranteed failure if I try to explain it by using a mathematical formula with exponentials and integrals

The Bass model addresses one limitation of the simple logistic model, regarding how the system “gets started”: with no adopters, there is no chance for interactions, so there is no inflow to the adopters stock. It does it through the use of an external force, like advertising.

Below is a Systems Dynamics interpretation of the Bass model. As you can see, the only difference is that now the Adoption Rate is the addition of two elements, adoption rate from advertising and adoption rate from word of mouth. The latter is exactly the same as the AR in the logistic model.

Returning to Reader Vince’s specific question on how to extend the logistic or Bass models to comprehend effects like cross-segment interactions, I would frame it like this:

• Identify the most important cross-segment interactions – How much “cross-shopping” exists between the segments? (using data like second choice selection); are there characteristics of the upper segment that consumers will translate into the lower segment favorably/unfavorably? consumers replace their vehicles within segment or they try to go up segment? etc.
• Incorporate the key cross-segment interactions on the model – They will most likely affect the Adoption Rate. It also may be necessary to model another stock or stocks (Upper Segment Adopters and Lower Segment Adopters, for instance)
• Check sensitivity of cross-segment assumptions – Understand how different the results are when the cross-segment assumptions are considered versus when they are not. What are the assumptions that most impact the results? A tornado diagram, as discussed in a previous entry, may provide a good way to show the sensitivity to the assumptions

As more dynamic effects are considered for inclusion in a model, it is better to move from a tool like Excel to something like Vensim, or iThink. Chapter 9 of John Sterman’s excellent book “Business Dynamics” talks about both the logistic and Bass models as shown here, and expands on ideas on how to extend them.

Here are some other very good references on the topic

• Forrester, J. W. 1980. Information Sources for Modeling the National
  Economy. Journal of the American Statistical Association 75 (371):
  555-574.
  Argues that modeling the dynamics of firms, industries, or the economy requires use of multiple data sources, not just numerical data and statistical techniques. Stresses the role of the mental and descriptive data base; emphasizes the need for first-hand field study of decision making.
• Legasto, A. A., Jr., J. W. Forrester & J. M. Lyneis, eds. 1980. System Dynamics. TIMS Studies in the Management Sciences. Vol. 14. Amsterdam:
  North-Holland.
  Collection of papers focused on methodology. Includes Forrester and Senge on Tests for Building Confidence in System Dynamics Models and Gardiner & Ford’s discussion on Which Policy Run is Best, and Who Says So?
• Randers, J., ed. 1980. Elements of the System Dynamics Method.
  Cambridge MA: Productivity Press. Includes Mass on Stock and Flow Variables and the Dynamics of Supply and Demand; Mass & Senge on Alternative Tests for Selecting Model Variables; and Randers’ very useful Guidelines for Model Conceptualization.
• Richardson, G. P., and A. L. Pugh, III. 1981. Introduction to System Dynamics Modeling with DYNAMO. Cambridge MA: Productivity Press.
  Introductory text with excellent treatment of conceptualization,
  stocks and flows, formulation, and analysis. A good way to learn the
  DYNAMO simulation language as well.
• Morecroft, J. D. W. 1982. A Critical Review of Diagramming Tools for
  Conceptualizing Feedback System Models. Dynamica 8 (part 1): 20-29.
• Critiques causal-loop diagrams and proposes subsystem and policy
  structure diagrams as superior tools for representing the structure of
  decisions in feedback models.
• Roberts, N., D. F. Andersen, R. M. Deal, M. S. Grant, & W. A. Shaffer.
  1983. Introduction to Computer Simulation: A System Dynamics Modeling
  Approach. Reading MA: Addison-Wesley.
• Easy-to-understand introductory text, complete with exercises.
• Homer, J. B. 1983. Partial-Model Testing As A Validation Tool for
  System Dynamics. In International System Dynamics Conference: 920-932
• How model validity can be improved through partial model testing when
  data for the full model are lacking.
• Sterman, J. D. 1984. Appropriate Summary Statistics for Evaluating the
  Historical Fit of System Dynamics Models. Dynamica 10 (2): 51-66.
• Describes the use of rigorous statistical tools for establishing model
  validity. Shows how Theil statistics can be used to assess
  goodness-of-fit in dynamic models.
• Forrester, J. W. 1985. ‘The’ Model Versus a Modeling ‘Process’. System
  Dynamics Review 1 (1): 133-134.
• The value of a model lies not in its predictive ability alone but
  primarily in the learning generated during the modeling process.
• Richardson, G. P. 1986. Problems with Causal-Loop Diagrams. System
  Dynamics Review 2 (2 ): 158-170.
• Causal-loop diagrams cannot show stock-and-flow structure explicitly
  and can obscure important dynamics. Offers guidelines for proper use
  and interpretation of CLDs.
• Forrester, J. W. 1987. Fourteen ‘Obvious Truths’. System Dynamics
  Review 3 (2): 156-159.
• The core of the system dynamics paradigm, as seen by the founder of the field.
• Forrester, J. W. 1987. Nonlinearity in High-Order Models of Social
  Systems. European Journal of Operational Research 30 (2): 104-109.
• Nonlinearity is pervasive, unavoidable, and essential to the
  functioning of natural and human systems. Modeling methods must
  embrace nonlinearity to yield realistic and useful models. Linear and
  nearly-linear methods are likely to obscure understanding or lead to
  erroneous conclusions.
• Barlas, Y. 1989. Multiple Tests for Validation of System Dynamics Type
  of Simulation Models. European Journal of Operational Research 42 (1):
  59-87.
• Discusses a variety of tests to validate SD models, including
  structural and statistical tests.
• Barlas, Y., & S. Carpenter. 1990. Philosophical Roots of Model
  Validation: Two Paradigms. System Dynamics Review 6 (2): 148-166.
• Contrasts the system dynamics approach to validity with the
  traditional, logical empiricist view of science. Finds that the
  relativist philosophy is consistent with SD and discusses the
  practical implications for modelers and their critics.
• Wolstenholme, E. F. 1990. System Enquiry – A System Dynamics Approach.
  Chichester: John Wiley.
• Describes a research methodology for building a system dynamics
  analysis. Emphasizes causal-loop diagramming, mapping of mental
  models, and other tools for qualitative system dynamics.
• Mass, N. 1991. Diagnosing Surprise Model Behavior: A Tool For Evolving
  Behavioral And Policy Insights (written in 1981). System Dynamics
  Review 7 (1): 68-86.

Relationship between the Bass and the logistic market adoption models

The simplified market adoption model I described on previous postings (1,2) is an Excel implementation of a kind of logistic function. The Bass model is one of the most popular models used in marketing, and management of technology to think about product introductions. (See Wikipedia article). From a mathematical perspective, when the parameter p is 0, the Bass model reduces to the logistic function.

What is most interesting, from a business perspective, is how you arrive to each of those functions by modeling real-world interactions. On both models, you can conceptualize the world as two different pools of people (or stocks, in the system dynamics terminology). One is the pool of potential adopters, and the other is the pool of adopters. The flow between these two pools is controlled by the adoption rate, a variable that models how probable is that a potential adopter becomes “infected” by a current adopter. On the logistic model, it depends solely on how much they interact, how big the total population is, and how “contagious” the product is. On the Bass model, an additional parameter accounts for external factors, the most common being advertising. The Bass model overcomes what is called the startup problem of the logistic model: how a initial base of zero adopters can spread “infection” of the product.

There are more refinements that can be done to the Bass model: accounting for changes in the total population over time, learning and experience curves, etc. For projects where the sensitivity of the model to these factors is high, I definitely recommend to spend more time calibrating your model, understanding which of the different available curves fits better any data you may have, and most critical of all, whether the chosen coefficients for any of the functions have strong impacts on the critical business issues you want to model — capacity planning, pricing, profitability, etc.

For many projects like business plans, revenue projections, etc. I’m willing to sacrifice the ability to fine tune parameters in a model like the BDM for the clarity provided by a model like the Excel logistic function I described. I can generate more tangible conversations with executives by discussing what they believe will be the takeover time, when they believe it will be the start of the fast growth, how much share they believe will be reached in steady state, etc.

Market partition – Mekko chart in Excel, no add-ins

Mekko charts are two dimensional graphs that analyze how data is partitioned against two variables, the X and Y axes. The width of the columns is proportional to data represented by the columns. Individual segment height is a percentage of the respective bar total value.

Yet another in-cell Excel bar chart technique

Two improvements over the technique described by Juice Analytics and posted in Lifehacker: better resolution and solid-looking bars that show better at different font sizes.

If your values are integers in a range 0-9 or so, you can use the REPT formula as presented there, and perhaps you like the dashed type bars, so the formula as shown would work perfectly for you. If not, keep reading.
(more…)

Math on the simplified market adoption s-curve for Excel

I’ve got a number of questions on the simplified Excel s-curve formula I published some time ago, so here are more details for those interested in the math behind it. The previous posting focused on how business analysts sometimes need to model market adoption, and provided a simple and easy to maintain formula to do so in Excel.

The formula =saturation/(1 + 81^((hypergrowth + takeover/2 – year)/takeover)) suggested for Excel is a simplification of the formula for a sigmoid function (See the Wikipedia article)

The graphic below shows the shape of both functions is identical. The saturation parameter just scales the function to a desired value, instead of going from 0 to 1. The factor 81 on the Excel formula determines how “sharp” the curve is, in this particular case, reaching 0.1 at the period hypergrowth and 0.9 at hypergrowth + takeover. Note that 81^x can be re-written as e^(ln(81)*x), so whatever factor is used there is simply going to affect the shape by compressing or expanding it horizontally.

This is how the scaling factor can be computed. Let’s say we want the penetration to be 5% at the period specified by hypergrowth. We can work out the solution off the second function. We need to solve for 1/(1+e^(-x) == 0.05, which gives x=-2.94444. Since the function is symmetrical, we also know for x=2.94444 P(x) == 0.95.

Since factor^((hypergrowth + takeover/2 – year)/takeover)) can be re-written as e^(ln(factor)*(hypergrowth + takeover/2 – year)/takeover)), we can solve ln(factor)*(hypergrowth + takeover/2 – (hypergrowth + takeover))/takeover == 2.94444. Reducing all the math, we arrive to
1/(1 + e^(-0.5*ln(factor))) == 0.95, and factor would be 361. If the desired penetration at hypergrowth is 20%, then we solve 1/(1 + e^(-0.5*ln(factor))) == 0.80, leading to factor == 16

Junkchart and pistongeekery on the same page!

The sites I refer to when I talk about charting, data analysis and good (and bad) charting techniques are normally quite different from the ones where I satisfy my gearhead lust with the news from the latest car models in the market. This time the guys at motortrend did it for me in their article “Finding the best-handling car in the US”: both fixes in just one page

Look at the following chart: well, it’s easy to tell the winner on their tests is the yellow line (if you are curious, the Porsche 911GT3). But can you tell from the green and red lines in the middle of the pack which one is better?

If you go to their gallery, you’ll see many other charts that fight fiercely for the top place in the Junk Chart museum. Just a couple of samples below

Spider charts may be useful, but only in very limited cases. Leave the webs to the guy in the custome, enjoy the movie, and stick to other chart types in your professional reports. You would be better off. Your audience will get it faster.

Read on to see a proposal on how this chart could be better.

(more…)