Home >  Excel Solutions > 

An Excel Tutorial:

Common-Age Excel Charts
Compare Sales Performance

Do you launch products, stores, or sales offices occasionally?
Here's an easy way to use Excel charts to track their early sales
performance.

In the early lives of new products, managers often are anxious to track and improve sales performance. To do so, it's often useful to use a different approach than you do for tracking mature products.

Charting sales performance is easy to do with mature products. Many companies track sales using an Excel chart like this one, which shows sales by month for four products.

Unfortunately, the four products shown aren't mature. The eldest product was launched in September, 2003, and the youngest was launched the following March.

In the early lives of new products, comparing sales performance by calendar month makes little sense. It's like comparing the height of a newborn with that of an adolescent. We learn nothing by this comparison, because the adolescent obviously will be taller.

Instead, it makes more sense to compare the height of the newborn to the height of the adolescent when he was the same age as the newborn. That way, we compare equivalent ages.

The chart shown here illustrates this approach. It compares each store's sales by its age.

Here, Store #15 grew quickly in its first five months of operations, then stalled out for several months. In contrast, Store #14 grew slowly for 7 months, but has grown more quickly since then.

Charts like this encourage managers to ask useful questions. Specifically, what did the manager of Store #15 do in her first five months to generate such growth? Specifically, 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 to outperform the other new stores in the months ahead?

How to Create a Common-Age Chart

The formulas are easier to create if you use an Excel-friendly OLAP, as I illustrate at Use Common-Age Charts with an OLAP Database. Here, however, I'll explain an approach that uses only Excel.

In Excel, sales data typically will come from a file imported into a spreadsheet. This figure shows the top and bottom of this data after I imported it into Excel and then formatted it. The dashed line shows where many rows of data are missing from the display.

To name each column, first select the range from the top row of labels to the bottom shaded row. Choose Insert, Name, Create. 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 1.

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 in cell B3 is a long one. To begin, array-enter the following formula:

=SUM(IF((Date=DATE(YEAR(B$1),MONTH(B$1)+$A3,1))*(Store=B$2), Sales))

(To array-enter a formula, you hold down Ctrl-Shift and then press Enter. When you do so, Excel encloses the formula shown in the formula bar with braces: "{" and "}".)

This array formula returns the sum of all data that meets two criteria. It finds those dates that are equal to the date in cell B1 plus the age (in months) shown in cell A3. It also finds values for the store specified in cell B2. Finally, the formula returns the sum of all cells where both criteria are met.

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. The general format of the modified formula is:

=IF(SUM(IF(...))=0,NA(),SUM(IF(...)))

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(SUM(IF((Date=DATE(YEAR(B$1),MONTH(B$1)+$A3,1))*(Store=B$2), Sales))=0,NA(),SUM(IF((Date=DATE(YEAR(B$1),MONTH(B$1)+$A3,1))*(Store=B$2),Sales)))

Be sure to array-enter this formula.

When you copy this formula you probably will try to copy it from cell B3 to the range B3:E14. If you do so, Excel will launch an error dialog telling you that you can't copy an array formula to itself.

Therefore, you need to copy the formula in two steps. First, copy it to the range B4:B14. Then copy the range B3:B14 to the range C3:E3. When you recalculate, your figure should resemble the illustration.

To create the chart, select the range A2:E14, launch the chart wizard, and format the chart as shown.

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 product launch.

An Easier Approach With OLAP

If you use an Excel-friendly OLAP, you don't need to use an array formula like the one shown above. Also, your source data doesn't need to be copied to Excel. Instead, you can use a simple formula to return your data directly from a Sales cube. The article Use Common-Age Charts with an OLAP Database explains this variation.