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:
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
Martin Theus posts here an example of poor charting or “junk charts”. I’ve had the opportunity to work with many organizations as a management consultant, and it’s sad to report that is more the norm than the exception to see charts like that one in the workplace.
Feeding my bad habit of thinking anytime anytime I see a chart how would I restate it to make it cleaner, here is how I would do it. On top, the original chart, below a proposed improvement.
The key questions, whenever one draws a chart, are “Who is my audience?” and “What is the message I want to communicate to them?”. For such chart, I would imagine the audience are the users of a software system for which we are reporting the different sources of errors”, and the underlying message, most likely, is what are the most relevant sources, so we can fix them.
In the original chart, transparency, 3D and color are used, but they are not adding any new information. The pieces of the pie do not show any meaningful order, either. The audience will have to look at the callouts to see which category corresponds to which piece of the pie, and their work is going to be harder by having to follow the callout lines that in some cases converge.
What makes the second chart better?
- The improved chart uses sorting as a way to help the audience. The largest sources of errors appear first.
- Once the data is sorted, it uses the Pareto principle to focus on the main sources of errors, removing from the audience’s eyes a lot of unnecessary detail. Today’s interactive media allows to drill-down into details with a click. Printed presentations can always have backup charts. Simple is beautiful. The Pareto principle is one of those “business commonsense” things that almost everyone has heard about, to the point is almost a cliche, yet people fail to appreciate how powerful it is.
- The use of color is non-gratuitous. Color is very powerful. Most people can differentiate between colors without effort. But they can also get quickly overwhelmed if many colors are used in a chart. Think twice before adding a new color to your chart. Is it communicating something?
The improved chart has some visual effects, like drop shadows and some color gradient, to make it more appealing. However they don’t work against how easily the message will be understood by the audience, they don’t leave out people with difficulties to tell colors apart, and they will not break when you make a black and white printout of the chart.
One thing that can’t be denied is Kerkorian’s persistence to get part of the action in the US automotive industry.
First, his attempt to take Chrysler private in the early nineties followed by the 2003-2005 drama of a Kerkorian-initiated federal lawsuit in which he charged that he had been deceived by DaimlerChrysler management, winning his support for the $36 billion deal in 1998 by portraying it as a “merger of equals” when, in fact, it was a takeover of Chrysler by Daimler-Benz.
More recently, when he then tried to broker an alliance between General Motors, Renault of France and Nissan of Japan, and replace Rick Wagoner by Carlos Ghosn.
Now his investment arm, Tracinda Corporation, made a $4.5 billion cash offer yesterday for the Chrysler Group.
The offer looks to obtain winning the exclusive right to negotiate with DaimlerChrysler and a deal with the United Automobile Workers union that could mean worker concessions.
The U.A.W. did not comment on the Tracinda bid, but its president, Ron Gettelfinger, previously said that he preferred to see the company remain part of Chrysler rather than be sold to an investor. Gettelfinger sits on the DaimlerChrysler supervisory board, which will ultimately decide Chrysler’s fate.
Without being able to show actual graphics from the data I’m working on, due to confidentiality with my employer, this post is a note on a good technique I’ve found that could be helpful to others in the portfolio management space
I’m finding PivotGraphs a powerful tool to visualize and communicate portfolio interactions. One example would be how different vehicles in a manufacturer’s portfolio interact with each other. Through proper aggregation, it provides insigths that a traditional segmentation will miss.
Here is how a PivotChart looks like, linking to an IBM research article on the topic:
PivotCharts are not related to Excel PivotTables at all
If you receive Excel files from external sources (analyst reports, etc.), chances are that you will start encountering the Excel 2007 format. Those files will end in .xlsx, .xlsb or .xlsm instead of the current .xls
Office 2007 has a significant number of advantages, but some of us will be for a while stuck with Office 2003. My employer, for instance, will not upgrade anytime soon.
Microsoft recently made available an extension to read or write those files with our current version of Office. Once you install it, you can seamlessly open the file or save them as you would with a normal Excel file. Follow this link for the download page.
One very common need in almost any industry is to show a given market, its size, the key participant and how all has evolved over the last few years. I have used since a few years a technique I like, and yesterday I read on Juice Analytics’ weblog an article describing basically the same approach. By the way, these guys have an interesting blog.
In their sample chart, there’s only one bar and one line, so many people may ask “why bother?” — it’s not that confusing to have the overlay in the default way Excel will leave the combination chart.
The chart below is a good example of why the technique is powerful. Click to see full size. Disclaimer: as this is an excerpt from a slide, the sample is missing a lot of must-have elements of a good chart, like units, meaningful title, etc.
When showing a market and how it is partitioned across players, there are some useful elements you’d like to show:
- Is it a growing, stable or shrinking overall market?
- What is our share? Is it growing?
- How are competitors doing? Are there changes that merit closer understanding?
Most common charts I’ve seen people use for the problem are:
- One pie chart with the last year figures. Ok, but you miss the interesting historical perspective
- Two pie charts side by side. That one hurts my eyes! There are many reasons it’s a bad representation. People are not intuitively good at comparing areas. Requires a lot of effort to follow where in each chart is each player, and how is it doing relatively to competition… enough said!
- Stacked column charts. This is a reasonable approach. Shows the trends nicely. It is hard for people to judge the relative percentage of the bar segments, so sometimes people end up with the percentages annotated in each segment, and in that case the chart is very busy
- Lines showing the share. Basically, the bottom part of the chart shown. It is a nice approach, you can follow what happened to the market players along time. In stable markets, may be all what is needed. The chart may be misleading though in growing markets because it loses the overall market size.
The combination of a bar chart to show the overall picture, plus lines to show the individual player trends has the best mix of good features I’ve found. I’d love to hear suggestions on other ways to approach the problem. It is not easy to spot growth in absolute volume for a given player, but in this is not usually a major drawback. If you are happy with your 3% growth and the market has been growing at 45% you’ll be soon out of business. Talk about charting failure 
On my job I usually have to present data-rich slides that are the result of analysis on sets of data. One of my pet peeves on this types of slides is the omission of the data source. As I always point out to analysts in training, *please* do yourself a favor: put the footnote with the source. You’ll be happy when 5 months later you are asked to support some results of your analysis.
That answers the question “What is the source of your data?”. More often than not, when you have to support your analysis, its also handy to know “Where is the source of my data?”. My recommendation decks are usually in Powerpoint, and I only sparringly use Office’s embedded file feature, so what is on the slide is just a picture of the spreadsheet, chart or table.
My source notes usually look as shown below:
The way I recommend analysts in training to do it is through the CELL function in Excel.
=CELL("filename")
The formula above will print the whole path and filename of the file. If you want only the filename, you can use
MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 1), FIND("[",CELL("filename", A1))+1, 255)
Tornado diagrams are a classic tool of sensitivity analysis for decision analysis. They are not used as frequently as you 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!!
This is a tornado chart created with the method described below:
Continue with the rest of this entry…
I had old scans of many guitar tab books as a directory of JPEG files, sequentially numbered. That was the way the old scanner stored documents. My SnapScan has an automatic document feeder and scans directly to PDF, which is much nicer to use. I wanted to join these JPEGs into a single PDF. After looking for different ways to do it, AppleScript, available software, etc., decided none of them were acceptable for my needs.
To accomplish the task, I used the following process (not optimal, but works):
cd directory
ls *.jpg | sort | xargs SavePDF
Where SavePDF is a program I hacked in an hour or so, using the CoreImage interfaces on the Mac. The code for SavePDF is shown below. Compile it with XCode, as of this writing Version 2.4.1
Precompiled binary for Mac OS X Intel platform is available here (in some browsers you’ll need to right-click and select “Save As…”)
Note that there is no error checking at all for the arguments. The resulting file is left at the current directory with the hardwired name “joined.pdf”
Use at your own risk - Works for me, YMMV. Other formats besides JPEG are supported by CoreImage and this code will work also with BMPs, PNGs and others. I personally have not used it for any other format.
Continue with the rest of this entry…
Scot Finnie writes this article on Computerworld named “A Windows expert opts for a Mac life, Part 2″ where he describes his experience moving to the Mac. His biggest hurdle to move was the transtion of Eudora mailboxes, and he ended up doing a very labor-intensive process:
The only thing that worked was to open each mailbox (and in fact, all the Eudora data or settings files) in TextWrangler, convert the line ending to Mac format and then use TextWrangler to save the file. I came up with some shortcuts along the way, but in the end, after several days of trying other methods (including dabbling with AppleScript and other batch processes) I wound up opening up more than 1,200 mailbox files and putting each through a nine-click process to save it in the proper format for the Mac
I had the same problem back at MIT, where Eudora was the preferred client because of its Kerberos capabilities. But I moved out my email in a much less painful way, which I’ll describe:
- Obtain access to an IMAP server with enough quota/space for all your files. In my case, I set up one on my own linux box at home.
- Set up Eudora to connect to this IMAP server. Test that you can down load a message and that you can upload a message. Eudora allows to do these operations through the GUI, dragging the message item from a server folder to a local folder and viceversa
- Once this works, drag all your local mailboxes to the server. Go, enjoy a cup of cofee, a bike ride or whatever… it will take a while, depending on the size of your mailbox
- Set up the IMAP client on your new machine, connect to the server, test your settings, now drag all the messages to your local folders… Voila, you are done.
