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…
Business data is quite often expressed across many dimensions. The profitability equation in a company is very simple in concept, but in practice those revenues come across regions, product lines, products etc., making them in fact multidimensional data.
Users of OLAP systems are very aware of multidimensional data. However, many spreadsheet users are not, so they manage to flatten the data the best they can, using pages or subtotals for dimensions beyond the second one.
Modern spreadsheets have “PivotTable” capabilities, which makes easier to deal with multidimensional data. To feed these pivot tables, the information has to be normalized. This post explains how to normalize data from non-normal representation, using a Visual Basic macro
If you are familiar with OLAP and normalized data, skip to the code. I’ll show samples of non-normalized data, how the same data would look normalized, and why this recipe is useful.
Continue with the rest of this entry…
Not very common, but sometimes useful, this chart is made up of a series of column pairs, where the one of them is a simple column and the other one is broken down in segments.
Last time I used it to show budgeted project expenditures versus actuals, month by month. The right column, actuals, was broken down to show capital investment. The chart can also be created so the “entire” column is to the right and the “broken” one is to the left.
Here is the cookbok to make the chart:
- Create 4 series, horizontally as shown below: broken (bottom part) and broken (top part), entire and dummy. I’ll refer to these series as BB,BT,E and D respectively.
- Fill your data as needed. On the D series, put some value on the first cell - this is just to allow you to select the series and later on you will delete this value. Make this value close to the average of the rest of the data, so you can handle the segment comfortably.
- Select all five lines of the data: titles, the three series for the columns and your D series. Create a chart, choose column type, and stacked column on the subtype. Make sure series in columns is the selected option. Finish the chart wizard.
- Right click on the segment for the D series on the first column and move it to the secondary axis: Choose “Format data series” and on the “Axis” tab choose “Secondary Axis”.
- Do the same with the data corresponding to the E series. Before closing the “Format data series” dialog, go to “Options” and choose overlap -100, gap width 80. This setting will apply to both the D series and the E series.
- In the “Series order” tab you can choose if your E series will go to the left or to the right by moving it up or down
- Go to “Format series”, now for the BT series. overlap should be 100 and gap 320.
- Double-click the right axis to bring the “Format Axis” dialog. Choose the maximum to be equal to that of the left axis. Even if Excel chose by default the same value for both, make sure the checkbox close to maximum is unchecked. You want to make sure manually your axis are synchronized to avoid showing misleading information. By default, Excel charts start at 0, but if you change this, you must do it in both axis. While you are at this dialog, go to the “Patterns” tab and choose “None” in “Thick mark labels”.
- Delete the dummy value, the dummy label on the chart and format the chart to your liking
Enjoy!
