Tornado Charts in Excel 2007/2010 Update

Posted on May 8, 2009 by Juan-Carlos Méndez García

Tornado diagrams are a classic tool of sensitivity analysis to provide decision makers a quick overview of the risks involved. A tornado chart to show a financial analysis for a project may look like this:

In this particular case, we are assuming that the tornado shows the NPV of a project. We expect the project can be valued at $7 billion (the point where the vertical axis crosses), subject to uncertainties.

The tornado helps visualize these uncertainties. In the example, Conversion (i.e. how many of the people that shop for our product become a customer) is the largest uncertainty. We believe 35% of the shoppers would convert. If only 25% convert, the project’s NPV would drop to $4 billion, from the base case, $7 billion. On the other hand, if 45% convert, we have a large upside and the NPV would be $12 billion.

Next in relevance would be pricing, $25,500 in the base case. If it goes down to $20,500 the NPV would reduce to $5 billion. If we can raise price up to $29,500 due to a favorable competitive environment, then the upside is $4 billion from the base case.

By now, you can follow the logic of the chart, with the other variables. Do you still have questions? Go ahead and drop us a line. We are happy to help.

Tornado diagrams are not used as frequently as one would expect, given how clearly they help showing the impact of different variables on a geven outcome. As suggested by Ted Eschenbach on a recent article of Engineering Economist, (issue of 06/22/2006), perhaps this is due to difficulties in constructing them.

Sensitivity analysis is needed to address the inherent uncertainty in engineering economy applications because (1) time horizons are measured in years or decades and (2) much economic analysis is done at the feasibility and preliminary design stages. This is often shown using relative sensitivity analysis charts or spiderplots, which have a long and rich history in practice and texts (they are described in 10 of 18 texts reviewed, including Blank and Tarquin (2002), Canada et al. (1996), Eschenbach (2003), Lang and Merino (1993), Park (2002, 2004), Sullivan et al. (2003), Thuesen and Fabrycky (2001), White et al. (1998), Young (1993). Tornado diagrams are not new, but they have not been used nearly as frequently. Only one of the 18 texts included a tornado diagram (Eschenbach, 2003)–

Searching Google on how to make tornado charts, you’ll get many results, most of them requiring you to download an add-in. Keep reading to see how you can create tornado charts with plain Excel in just 5 steps… very easy and straightforward!!

Continue reading →

The Devil’s guide to spreadsheet creation

Posted on April 21, 2009 by Juan-Carlos Méndez García

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

The Devil’s guide to spreadsheet creation

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

Posted on December 21, 2008 by Juan-Carlos Méndez García

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:

Fit the data to a logistic model

Apply the derived model to the new project duration

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

Harmonic Averages

Posted on July 4, 2008 by Juan-Carlos Méndez García

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)

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

Posted on December 21, 2007 by Juan-Carlos Méndez García

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.

Sigmoid Formula

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.

Business Dynamics Book

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

Posted on July 30, 2007 by Juan-Carlos Méndez García

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

Posted on July 16, 2007 by Juan-Carlos Méndez García

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

Posted on July 13, 2007 by Juan-Carlos Méndez García

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.
Continue reading →

Math on the simplified market adoption s-curve for Excel

Posted on July 6, 2007 by Juan-Carlos Méndez García

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)

Sigmoid Formula

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.

Sigmoid math

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

Modeling market adoption in Excel with a simplified s-curve

Posted on April 24, 2007 by Juan-Carlos Méndez García

UPDATE 10/31/2007: Here is a screencast for this model

Often business analysts need to model the adoption of a new product or service for financial planning. There are several approaches, but a common one is the s-curve (see Wikipedia article). Here is a simple implementation in Excel that can be easily added to your spreadsheets. It reduces all the math to just three parameters:

saturation – What is the maximum expected penetration after the product becomes mainstream? i.e. what is the value that the top of the s-curve will reach?
start of fast growth – By this year, the penetration will be 10% of the saturation value, and it will start to grow rapidly. 10% was an arbitrary choice to simplify the model, and by doing some math you could change the formula to any value. It is a reasonable choice in most cases. We’ll call this parameter hypergrowth
takeover time – How long it will take for the product to “catch on”? – The operational assumption in the formula is that this number of years after the start of fast growth, the product would have reached 90% of the saturation value and will start to slow down. Again, 90% is an arbitrary value I chose.

The s-curve model focuses in the early phases of the product lifecycle, until maturity is reached. Penetration decay is NOT covered by this model.

The formula for each year’s penetration would simply be:
=saturation/(1+81^((hypergrowth+takeover/2-year)/takeover))

See it in action:

s curve example

In the sample spreadsheet above, look at cell B8 where you can see the formula in use. It is the same for all row 8.

saturation, hypergrowth and takeover are names defined for the parameters on rows 2 to 5 (you use names in your models instead of plain cell references, don’t you?)

Very simple, easy to maintain, light on calculation times… happy market adoption modeling!

PS: The chart shown is NeoOffice, an open source alternative to Excel for Macintosh users, based on OpenOffice