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…)

The Devil’s guide to spreadsheet creation

From http://www.sysmod.com/praxis/prax0404.htm#Excel

The Devil’s guide to spreadsheet creation

1. Just do it. Jump in and do it. The users will have to accept whatever you produce anyway.
2. Fire, then aim. You know what is really needed without having to ask.
3. Never simplify (that just makes it easier for other people to get your job); just keep adding bits without removing old stuff.
4. Deadlines live on.
5. Documentation is for wimps; specifications are for the timid.
6. Don’t obtain test data; whatever the spreadsheet result is, is right.
7. Don’t protect the sheet; that restricts the users’ right to improve your formulas by typing in what they want.
8. Don’t fill in the properties sheet, they’ll find out you were the author.
9. VBA (Very Buggy Application) debugging is easy; just keep making changes until something appears to work, then your responsibility is finished.
10. Never use in-cell comments or help text on the page; users should just know what to do.
11. If you know what units of measure are used, you can safely assume everybody else does too.
12. Mix input data with calculation cells to keep the users on their toes.
13. Never mix absolute and relative references, it can shorten billable time.
14. Hide some data in cells so that when users trip over it, their respect for your cleverness increases.
15. If asked to do a test run, ask “Don’t you trust me?”
16. Format with as many decorative colours and styles as possible, to relieve boredom.
17. Don’t keep backup copies of different versions of a spreadsheet, the latest is always the best.
18. Hardcode constants in formulas; after all, they don’t change.
19. Cross-tot checking is merely redundant calculation.
20. To test a spreadsheet, you only need to check whether the answers look reasonable.

Great list! I can’t recall a day when I’ve not seen a spreadsheet that evidences 3 or more of these

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))

Extreme Makeover: Chart Edition – July 19

As every week I end up receiving at lest one junkchart specimen, I’m starting the Extreme Makeover: Chart Edition series. I’ll show the old chart, the proposed improvement, and why I believe the old one doesn’t work. I’d love to hear your comments as well.

Here it goes:

And the makeover:

Problems:

• Vertical labels: Even when the list of items is long and is tempting to use columns to use the width of the screen versus the height to show the items, it forces the audience to turn their heads to read the labels (or to not read them at all)

• Clutter: Does your audience need to know to the dollar the GDP in each region? The new chart uses ‘000s. People will remember more easily data they get interested in. Will someone interested in the Shangdong province remember the GPD per capita is $3,162? More chance they will recall it was like 3.2 thousand, right?
• More clutter: Once you label the bars/columns, why keeping the axis? Also, the arrow to highlight the 9x difference adds clutter to the chart
• Lack of labels/legends/source: The original chart has an average line, which is evident only on close inspection because it is not labeled. Also, the columns are grouped in colors, without legends, so a reader, like me, is left clueless about their meaning. Finally, every chart using data must show its source (I’d have noted one if I knew where the original came from)

In charts, Simplicity is King: I’m assuming in the context of the presentation it was necessary to include every province by name.
“Is this cart needed? Is this level of detail needed?” That’s a question you should ask yourself before including any chart. The answer is completely situation-specific. What is the key message to communicate? Who is the audience? What will they look for from their specific viewpoint?

So, what do you think? How would you further improve my proposal?

Harmonic Averages

This is a short note to talk about Harmonic Averages. Most people are familiar with Weighted Averages, as they are a valuable tool for aggregation. For instance, with the data below, the average profitability (~1735) can be easily calculated using weighted averages.

Avg_Profit = (1000*1200 + 200*300 + 500*2500 + 10*600 + 100*300) / (1000 + 200 + 500 + 10 + 100)

or

Avg_Profit = SUMPRODUCT(UnitsSold,ProfitPerUnit)/SUM(UnitsSold)

I’m using Excel notation, and assuming it is clear from the context that UnitsSold is a range that covers the second column, for all models, etc.

A less known way of averaging are Harmonic Averages. It is relevant when the data to aggregate is actually a ratio whose denominator is proportional to the weighting factor. A typical case is miles per gallon (MPG) for a bunch of vehicles. Gas consumption is directly proportional to the number of units.

Let’s add some MPG data to the table above.

Using Weighted Averages for an inverse ratio like MPG is plain wrong (24.3 MPG is NOT the average fuel economy)

The right thing is to use Harmonic Average:

Harm_Avg_MPG = (1000 + 200 + 500 + 10 + 100) / (1000/22.5 + 200/15.0 + 500/32.0 + 10/12.0 + 100/24.0)

As Excel doesn’t have a similar function to SUMPRODUCT for adding 1000/22.5, 200/15.0, etc. I will not use Excel notation, but plain math notation:

UPDATED formula

If you have to deal with Harmonic Averages, you may find interesting this note on how to do PivotTable Multidimensional Analysis with Harmonic Averages. There’s a similar one for Weighted Averages as well.

Let me know what you think.

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