Management Reporting
Use CommonAge
Excel Charts to Compare Performance for the Same Number of Periods
After a Launch
You can use Excel charts to track how quickly new products
or new stores ramp up their performance compared with similar launches in the past.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

In the early lives of new products or new stores, managers often
are anxious to track and improve sales performance. To do so, it's
often useful to use a different approach than you use for tracking
mature products.
Charting
sales performance is easy to do with mature products. Many companies
track sales using an Excel chart somewhat like this, which shows sales by month for
four stores, #14 through #17.
Unfortunately, the four stores shown were new when the report was
generated. The eldest
store was launched in September, 2003, and the youngest was launched
the following March.
In the early lives of new revenue sources, comparing sales performance by
calendar month makes little sense. It's like comparing the height of a
baby with that of an adult. We learn nothing by this comparison,
because the adult obviously will be taller.
Instead, it makes more sense to compare the height of the baby to
the height of the adult when he was the same age as the
baby. That way, we compare performance of the two individuals under
equivalent conditions.
This chart
illustrates the approach. It compares each store's
sales at the same age as they develop.
Notice how your conclusions about store performance will
differ, depending on which chart you study.
In the first figure, stores #14 and #15 seem to perform
similarly, as do stores #16 and #17. But notice the change when
we compare them by age.
In the second figure, store #17 has outperformed
all other stores at the sixmonth mark. Store
#15 is outperforming store #14 significantly. And store #16
isn't tied with #15; it's running last.
Charts like this encourage managers to ask useful questions:
 What did the manager of Store #17 do in her first six
months to generate such growth?
 What has the manager of
Store #14 done to improve growth since month seven?
 Why were the sales
in Store #16 flat for the first three months?
 Can these managers' recent
experiences help the manager of Store #17 perform even better in the months ahead?
 Can store #17—young as it is—offer any lessons for
the other stores?
How to Create a CommonAge Chart
The workbook is much easier to create if you use a spreadsheet database,
rather than just entering the numbers for display.
In Excel,
sales data typically will come from a file imported into a spreadsheet,
or from a Pivot Table.
This figure shows the top and bottom of this data after I imported it
into a spreadsheet and formatted it as a graycell database. Notice that
I've hidden several dozen rows
of data in the display.
To name each column, first select the range from the top row of
labels to the bottom shaded row. Choose Formulas, Defined Names, Create
from Selection, or press Ctrl+Shift+F3. In the
Create Names dialog, make sure that only Top Row is checked. Then choose
OK.
The shaded rows are very useful, because they show where each range
name is anchored at its top and bottom. To add new data to this display,
you insert new rows between the shaded borders. By doing
so you expand the range names as needed.
To display
this data in the chart, we need to arrange the data differently. Here,
each store is in its own column. And the starting date for each store is
shown in row 3.
Notice that the younger stores display #N/A rather than zero in the
months for which the stores don't yet have sales. By forcing the
formulas to return #N/A we force our chart to display no value for these
cells. If we were to display zero, Excel would chart those zero values.
The formula with the
SUMIFS function in cell B5 is a long one. To begin, enter this formula:
=SUMIFS(Sales,Date,DATE(YEAR(B$3),MONTH(B$3)
+$A5,1),Store,B$4)
This formula returns the sum of all data in the Sales column that meets two
criteria:
 Dates that are equal to the date in cell B3
incremented by the number of months shown in cell A5.
 The store specified in cell B4.
With the exception of one problem, this formula does just what we
want: It summarizes the data into a layout that we can chart easily. The
problem is that if we copy it to the rest of the display, it will return zero when no data is found; and Excel
will chart
these zeros as legitimate data values.
We therefore need to modify this
formula to return #N/A rather than zero when data is missing. The general format of the
modified formula is:
=IF(SUMIFS(...)=0,NA(),SUMIF(...))
That is, we need to summarize the data twice: first to test whether
the summary equals zero, second to return the nonzero amount as needed.
That is, we need to create this long formula:
=IF(SUMIFS(Sales,Date,DATE(YEAR(B$3),MONTH(B$3)+$A5,1),Store,B$4)=0,NA(),
SUMIFS(Sales,Date,DATE(YEAR(B$3),MONTH(B$3)+$A5,1),Store,B$4))
After you enter the formula, copy it to the range B5:E17.
To create the chart, start by temporarily deleting the "Mos" label
from cell A4; select the range A4:E17; choose Insert, Charts, Line, 2D
Line, Line; then reenter the "Mos" label. (By launching the chart with
the "Mos" label missing, we tell Excel that column A contains the
chart's category axis values, not another data series.)
Note that the SUMIFS function was introduced in Excel 2007.
Earlier versions of Excel therefore needed to use an array
formula with this general form:
=IF(SUM(IF(...))=0,NA(),SUM(IF(...)))
Although you can use this version of the formula in more
recent versions, SUMIFS is preferred. This is because SUMIFS
tends to calculate more quickly and is easier to use.
Ideas for Extending CommonAge Charts
You can create many variations of this chart.
One possibility is to chart growth rates by dividing the sales of
each product by its sales in its first full month of operation. This
forces all sales to have a value of 1 in month 1 and displays relative
growth in sales from that point on.
You could display other measures, like number of customers, sales per
customer, gross profit margin, or dollars spent for advertising and
promotion. In these instances, however, the charts act more like
analyses than reports. You use them to search for keys to a more
successful store or product launch.
