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 next 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 out-performed all other stores at the six-month mark. Store #15 is out-performing 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?
And also, of course, starting with data arranged like this makes it much easier to forecast the performance of new stores.
How to Create a Common-Age 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 gray-cell 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 non-zero 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, 2-D Line, Line; then re-enter 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 Common-Age 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.
