Improving Performance

with Store Analysis

A Step-by-Step Guide to Creating a Store Analysis

Accelerated Analytics

Improving Performance with Store Analysis

An analysis of your
buyers stores is a
valuable tool to help
vendors better understand and
improve store performance. By
following the guidelines provided
by Accelerated Analytics, we
were able to quickly and easily
create a store analysis that gave

Objectives
The primary purpose of a store analysis is to identify the stores which are making the largest
study the characteristics of those stores, including SKU assortment, demographics, promotions,
min/max on hand, and make recommendations on how other stores can be improved to enhance
performance. An important objective of a store analysis is to grade stores by performance into
major categories to save time and focus out of stock and forecasting on the highest contributing
stores in future analysis.

us the critical insight we needed

Key Considerations

to make recommendations to

When comparing store sales, it is best to use dollars sold because it provides a more accurate cross-

enhance results.
Analyst from Consumer
Products Company

show equal performance. However, if the analysis is dollars-based, the $100 store will have a higher
performance ranking.

Additional considerations for creating the most accurate store analysis include:
Store sales analysis can be skewed by merchandise assortment. If there is more than one planwhen conducted for one plan-o-gram at a time.
Store sales analysis is best conducted using dollars not units. If your retailer provides only unit
sales, it will be necessary to estimate the dollar sales for each store using the units sold multiplied
times the item retail price.
Because the analysis requires two comparable periods, you will need to consider any price
changes which may have occurred during the analysis period. If, for example, a 5% price increase
of the price increase or if you want to factor it out. In some cases, you will want to factor out the
price increase so the store performance is analyzed based on a constant selling price. In that case,
you would determine the date of the price change and adjust the sales by the amount of the
change so that sales remain at a constant selling price. On the other hand, you may be interested
in analyzing if the price change caused an increase or decrease in store performance. If so, you

2012 Accelerated Analytics, LLC. All Rights Reserved

Improving Performance with Store Analysis

Store opening and closing dates should be considered to ensure the two periods being
compared have the same number of selling weeks. This can be done easily by sorting the data
ascending for each of the period sales and then eliminating any rows with a zero or null cell. If you
are unsure, either eliminate the row or reference the by-week selling detail to ensure each period
has an equal number of weeks selling. If you reference the by-week selling activity, you should
consider that some stores may have inventory on hand that has been active, but simply did not
have any sales for the week. These are valid stores and should remain in the analysis. Therefore, you
will sometimes need to review the surrounding weeks sales to determine if the store was active at
a given point in time.

Step-By-Step Construction
1. Compare Store Sales
comparable periods. This period can be YTD with a comparable period for the prior year or if the
data is available, two full year periods can be pulled. The longer the period used for the analysis, the
more accurate the data analysis.
Note that stores should be sorted in descending order for the most current period. If the retailer that

Figure
1

because they will be useful for additional analysis later. To have an accurate two-period comparative
analysis, any stores with no sales in one or both periods should be eliminated. See Figure 1.
FIGURE 1

State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

2012 Accelerated Analytics, LLC. All Rights Reserved

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009
6616 $93,283.80 $122,618.74
6310 $118,534.87 $121,586.31
6611 $89,035.36 $109,954.33
274 $93,036.73 $99,323.34
6613 $87,458.88 $97,812.32
1701 $84,989.38 $95,877.18
224 $93,170.98 $93,914.70
674 $83,998.93 $92,189.73
6353 $92,925.06 $91,264.30
220 $82,792.02 $90,367.64

Improving Performance with Store Analysis

Figure
2

2. Calculate Cumulative Sales

The second step is to add a column to the right of the table to calculate cumulative sales going
down the column for the most current period. See Figure 2.
FIGURE 2

State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

An easy way to get a

total store count in
your Excel worksheet
is to use the following formula:
=count(range1:range2)
with the range being the store
number column

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009 2009 Cum Sales
6616 $93,283.80 $122,618.74
$122,618.74
6310 $118,534.87 $121,586.31
$244,205.05
6611 $89,035.36 $109,954.33
$354,159.38
274 $93,036.73 $99,323.34
$453,482.72
6613 $87,458.88 $97,812.32
$551,295.04
1701 $84,989.38 $95,877.18
$647,172.22
224 $93,170.98 $93,914.70
$741,086.92
674 $83,998.93 $92,189.73
$833,276.65
6353 $92,925.06 $91,264.30
$924,540.95
220 $82,792.02 $90,367.64 $1,014,908.59

3. Calculate Percentage of Store Count

Next add a column to calculate the cumulative percentage of store count. To perform this calculation,
you will need to calculate the total number of stores in your worksheet. Keep in mind total stores in
you may have eliminated stores with no sales in the previous step. Each row will represent one
store divided by the total store count and then sum the values going down the column. This will

Figure
3

of stores; and then use the cumulative sales column to determine, for example, that 50% of total
stores contributed 72% of total sales. These relationships will be important later in the analysis when
grouping stores. See Figure 3.
FIGURE 3

State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

2012 Accelerated Analytics, LLC. All Rights Reserved

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
6616
6310
6611
274
6613
1701
224
674
6353
220

2008
$93,283.80
$118,534.87
$89,035.36
$93,036.73
$87,458.88
$84,989.38
$93,170.98
$83,998.93
$92,925.06
$82,792.02

2009 2009 Cum Sales Cum % Store Count

$122,618.74
$122,618.74
0.05%
$121,586.31
$244,205.05
0.10%
$109,954.33
$354,159.38
0.15%
$99,323.34
$453,482.72
0.20%
$97,812.32
$551,295.04
0.25%
$95,877.18
$647,172.22
0.30%
$93,914.70
$741,086.92
0.35%
$92,189.73
$833,276.65
0.40%
$91,264.30
$924,540.95
0.45%
$90,367.64 $1,014,908.59
0.50%

Improving Performance with Store Analysis

Figure
4

4. Calculate Percentage Contribution Each Store is Making to Total Sales

Next add a column to calculate the percentage contribution each store is making to total sales. To
perform this calculation, you will need to sum the total sales and then use the store sales on each
row to determine the stores contribution to the total. This column will be used later to identify if any
See Figure 4.
FIGURE 4
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009 2009 Cum Sales Cum % Store Count % Contribu7on
6616 $93,283.80 $122,618.74
$122,618.74
0.05%
0.25%
6310 $118,534.87 $121,586.31
$244,205.05
0.10%
0.25%
6611 $89,035.36 $109,954.33
$354,159.38
0.15%
0.23%
274 $93,036.73 $99,323.34
$453,482.72
0.20%
0.20%
6613 $87,458.88 $97,812.32
$551,295.04
0.25%
0.20%
1701 $84,989.38 $95,877.18
$647,172.22
0.30%
0.20%
224 $93,170.98 $93,914.70
$741,086.92
0.35%
0.19%
674 $83,998.93 $92,189.73
$833,276.65
0.40%
0.19%
6353 $92,925.06 $91,264.30
$924,540.95
0.45%
0.19%
220 $82,792.02 $90,367.64 $1,014,908.59
0.50%
0.19%

Figure
5

5. Calculate Cumulative Percentage of Sales

Next add a column to calculate the cumulative percentage of sales. This column is based on the
cumulative dollar sales column, but is expressed as a percentage. This allows you to easily scan
sales. See Figure 5.
FIGURE 5
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

2012 Accelerated Analytics, LLC. All Rights Reserved

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009 2009 Cum Sales
6616 $93,283.80 $122,618.74
$122,618.74
6310 $118,534.87 $121,586.31
$244,205.05
6611 $89,035.36 $109,954.33
$354,159.38
274 $93,036.73 $99,323.34
$453,482.72
6613 $87,458.88 $97,812.32
$551,295.04
1701 $84,989.38 $95,877.18
$647,172.22
224 $93,170.98 $93,914.70
$741,086.92
674 $83,998.93 $92,189.73
$833,276.65
6353 $92,925.06 $91,264.30
$924,540.95
220 $82,792.02 $90,367.64
$1,014,908.59

Cum % Store Count % Contribu7on Cum % Sales

0.05%
0.25%
0.25%
0.10%
0.25%
0.50%
0.15%
0.23%
0.73%
0.20%
0.20%
0.93%
0.25%
0.20%
1.13%
0.30%
0.20%
1.33%
0.35%
0.19%
1.52%
0.40%
0.19%
1.71%
0.45%
0.19%
1.90%
0.50%
0.19%
2.08%

Improving Performance with Store Analysis

6. Calculate Percentage Change in Sales

Calculate a percentage change in sales from last year compared to this year. To calculate the
percentage change in sales, use the following formula:

Figure
This year sales Last year sales
6Last year sales

An important part of analyzing store performance is not only the stores contribution to total sales,
but also the stores rate of growth or decline. See Figure 6.
FIGURE 6
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
20092009 Cum Sales Cum % Store Count % Contribu7on Cum % Sales % Chg in Sales
6616 $93,283.80 $122,618.74
$122,618.74
0.05%
0.25%
0.25%
31.45%
6310 $118,534.87 $121,586.31
$244,205.05
0.10%
0.25%
0.50%
2.57%
6611 $89,035.36 $109,954.33
$354,159.38
0.15%
0.23%
0.73%
23.50%
274 $93,036.73 $99,323.34
$453,482.72
0.20%
0.20%
0.93%
6.76%
6613 $87,458.88 $97,812.32
$551,295.04
0.25%
0.20%
1.13%
11.84%
1701 $84,989.38 $95,877.18
$647,172.22
0.30%
0.20%
1.33%
12.81%
224 $93,170.98 $93,914.70
$741,086.92
0.35%
0.19%
1.52%
0.80%
674 $83,998.93 $92,189.73
$833,276.65
0.40%
0.19%
1.71%
9.75%
6353 $92,925.06 $91,264.30
$924,540.95
0.45%
0.19%
1.90%
-1.79%
220 $82,792.02 $90,367.64 $1,014,908.59
0.50%
0.19%
2.08%
9.15%

Figure
7

7. Rank Stores based on Percentage Change in Sales

Add a column to rank stores based on the percentage change in sales. This can easily be done by
sorting the stores by % Chg in Sales column in descending order. Next, simply enter one into the
so it is again sorted by the most current period dollar sales in descending order. See Figure 7.
FIGURE 7
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

2012 Accelerated Analytics, LLC. All Rights Reserved

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
6616
6310
6611
274
6613
1701
224
674
6353
220

2008
$93,283.80
$118,534.87
$89,035.36
$93,036.73
$87,458.88
$84,989.38
$93,170.98
$83,998.93
$92,925.06
$82,792.02

2009 2009 Cum Sales Cum % Store Count % Contribu7on Cum % Sales % Chg in Sales % Chg Sales Rank
$122,618.74
$122,618.74
0.05%
0.25%
0.25%
31.45%
118
$121,586.31
$244,205.05
0.10%
0.25%
0.50%
2.57%
1419
$109,954.33
$354,159.38
0.15%
0.23%
0.73%
23.50%
275
$99,323.34
$453,482.72
0.20%
0.20%
0.93%
6.76%
1138
$97,812.32
$551,295.04
0.25%
0.20%
1.13%
11.84%
791
$95,877.18
$647,172.22
0.30%
0.20%
1.33%
12.81%
732
$93,914.70
$741,086.92
0.35%
0.19%
1.52%
0.80%
1518
$92,189.73
$833,276.65
0.40%
0.19%
1.71%
9.75%
931
$91,264.30
$924,540.95
0.45%
0.19%
1.90%
-1.79%
1650
$90,367.64 $1,014,908.59
0.50%
0.19%
2.08%
9.15%
981

Improving Performance with Store Analysis

Figure
8

8. Rank Sales by Most Current Period

Add a column for sales rank by the most current period. As the stores are already sorted by
sales by the most current period. See Figure 8.
FIGURE 8
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

An important outcome
of the store analysis is
to identify the groups
of stores which are making the
largest contribution to total
sales. This group of stores will
be used for out of stock and
forecasting in future analysis.

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009 2009 Cum Sales Cum % Store Count % Contribu7on Cum % Sales % Chg in Sales % Chg Sales Rank 2009 Sales Rank
6616 $93,283.80 $122,618.74
$122,618.74
0.05%
0.25%
0.25%
31.45%
118
1
6310 $118,534.87 $121,586.31
$244,205.05
0.10%
0.25%
0.50%
2.57%
1419
2
6611 $89,035.36 $109,954.33
$354,159.38
0.15%
0.23%
0.73%
23.50%
275
3
274 $93,036.73 $99,323.34
$453,482.72
0.20%
0.20%
0.93%
6.76%
1138
4
6613 $87,458.88 $97,812.32
$551,295.04
0.25%
0.20%
1.13%
11.84%
791
5
1701 $84,989.38 $95,877.18
$647,172.22
0.30%
0.20%
1.33%
12.81%
732
6
224 $93,170.98 $93,914.70
$741,086.92
0.35%
0.19%
1.52%
0.80%
1518
7
674 $83,998.93 $92,189.73
$833,276.65
0.40%
0.19%
1.71%
9.75%
931
8
6353 $92,925.06 $91,264.30
$924,540.95
0.45%
0.19%
1.90%
-1.79%
1650
9
220 $82,792.02 $90,367.64 $1,014,908.59
0.50%
0.19%
2.08%
9.15%
981
10

Figure
9

9. Assign a Category to Each Store

It is now time to assign a category to each store. We recommend using categories A through D
for store grouping. The letter assigned to each store is done based on the stores sales quartile. To
determine the quartile, you simply divide the total stores in the worksheet by four, which will tell
quartile, etc. See Figure 9.
FIGURE 9
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
NORTH HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store Nbr
2008
2009 2009 Cum Sales Cum % Store Count % contribu7on Cum % Sales % Chg in Sales % Chg Sales Rank 2009 Sales Rank Category
6616 $93,283.80 $122,618.74
$122,618.74
0.05%
0.25%
0.25%
31.45%
118
1
A
6310 $118,534.87 $121,586.31
$244,205.05
0.10%
0.25%
0.50%
2.57%
1419
2
A
6611 $89,035.36 $109,954.33
$354,159.38
0.15%
0.23%
0.73%
23.50%
275
3
A
274 $93,036.73 $99,323.34
$453,482.72
0.20%
0.20%
0.93%
6.76%
1138
4
A
6613 $87,458.88 $97,812.32
$551,295.04
0.25%
0.20%
1.13%
11.84%
791
5
A
1701 $84,989.38 $95,877.18
$647,172.22
0.30%
0.20%
1.33%
12.81%
732
6
A
224 $93,170.98 $93,914.70
$741,086.92
0.35%
0.19%
1.52%
0.80%
1518
7
A
674 $83,998.93 $92,189.73
$833,276.65
0.40%
0.19%
1.71%
9.75%
931
8
A
6353 $92,925.06 $91,264.30
$924,540.95
0.45%
0.19%
1.90%
-1.79%
1650
9
A
220 $82,792.02 $90,367.64 $1,014,908.59
0.50%
0.19%
2.08%
9.15%
981
10
A

10. Break Stores into Smaller Groups

To best analyze the stores which are making the largest contribution, we recommend categorizing
stores into smaller groups than the A through D categories used earlier. Therefore, the next step is
to categorize stores into eight groups so that each group is one-half a quartile. To do this, divide
the total number of stores in the worksheet into eight equal groups. For each group, calculate the
percentage of sales for the group and the % of stores for the group. Charting the results of the table
will provide a simple visual method for analyzing the group contribution. See Figure 10.

2012 Accelerated Analytics, LLC. All Rights Reserved

Improving Performance with Store Analysis

Figure
10

FIGURE 10

Paretos Principle:
An Italian economist,
Vilfredo Pareto created
a mathematical formula that
described the unequal distribution
of wealth that he observed and

120%
100%
80%
60%
40%
20%
0%

28%

60%

46%
25%

13%

Group 1

Group 2

50%

38%

90%

82%

72%

63%

96%
75%

88%

100% 100%

% Sales
% Stores

Group 3

Group 4

Group 5

Group 6

Group 7

Group 8

measured in his country. Pareto

observed that roughly 20% of
the people controlled or owned
80% of the wealth. In the late
1940s, Dr. Joseph Juran, a quality
management expert, attributed
the 80/20 rule to Pareto calling
it Paretos Principle. Dr. Juran
described how 20% of a set is
generally responsible for 80%
of the results, and described his

In Figure 10, we can see that groups 1 to 5 account for 82% of total sales, but are only 63% of total
stores. In the chart, each group is a cumulative total of the sales and stores so the reader does not
need to add the groups together; it has already been done for them. While this is not quite as dramatic
as Paretos Principle, it does indicate that including stores from groups 6 to 8 provides a diminishing
return for analysis purposes. Keep in mind an important objective of the store analysis is to reduce
the store count to the vital few and the trivial many. This will enable us to reduce the amount of data
that needs to be analyzed when conducting the SKU/store level forecasting and out of stock analysis.
If you added group 6, an additional 8% of sales would be added, but 12% more stores would need to

observation as the vital few and

trivial many.

The median is the

middle point in a
population of data
where half of the values are
above and half of the values are
below. The average (arithmetic
mean) is a single calculated value
meant to typify a list of values.
In a set of data, an average is

by referring to the cumulative % sales column in the data table. Mark these stores using an additional
column so they can be referenced later in the out of stock and forecast analysis.

11. Calculate Average Sales per Store

The next method of summarizing the data is to calculate the average sales per store for each of the
A through D groups. To do this, use the average function in Excel for the store groups in the data
table based on the store rank column. Charts are most useful when there is a reference point for the
user; in this case the best reference point is the median of all store sales. Calculate the median using
the excel function and then plot onto the chart. See Figure 11.

Figure
11

The Excel formulas for calculating the median and average in Excel are:
=median(range1:range2)
=average(range1:range2)

subject to distortion because of

outliers that are typical in a list of

FIGURE 11

Avg Sales Per Store

store sales. However, the median

of the distribution of data and is

$60,000.00
$40,000.00

Avg Sales Per Store

$20,000.00
$-

2012 Accelerated Analytics, LLC. All Rights Reserved

A Stores

B Stores

C Stores

D Stores

Median

Improving Performance with Store Analysis

12. Create Executive Summary

Now it is time to pull the analysis together into a one page executive summary that can be easily
stores by comparable sales sorted by descending dollar sales for the current period. Because the
stores and the bottom ten stores. The table to the right summarizes the distribution of stores by
showing the highest and lowest stores, as well as the median and average. It is important to show
both the median and the average as very large or small stores (outliers) will often skew the average.
See Figure 12.
FIGURE 12

EXECUTIVE SUMMARY
Top 10 Stores
State
CA
FL
CA
FL
CA
HI
FL
CA
FL
FL

City
HOLLYWOOD
HOLLYWOOD
LOS ANGELES
JUPITER
N. HOLLYWOOD
HONOLULU
BOYNTON BEACH
SAN DIEGO
MIRAMAR
LAKE PARK

Store
6616
6310
6611
274
6613
1701
224
674
6353
220

Bottom 10 Stores
2008
$93.2
$118.8
$89.1
$93.1
$87.5
$84.9
$93.7
$83.9
$92.9
$82.7

2009
$122.6
$121.5
$109.9
$99.3
$97.8
$95.8
$93.9
$92.2
$91.6
$90.4

State
KY
NE
IL
MN
CO
CA
MO
CA
MS
NC

City
PADUCAH
GRAND ISL
NORMAL
WILLMAR
STERLING
CONCORD
KIRKSVILLE
PETALUMA
WAVELAND
WILSON

Store
2314
3208
6987
2842
1545
1386
3036
1382
2918
3611

2008
$6.3
$4.8
$3.7
$3.9
$3.8
$7.7
$3.4
$9.5
$3.1
$3.0

2009
$4.1
$4.0
$3.9
$3.8
$3.7
$3.7
$3.7
$3.5
$3.4
$2.7

Store Metrics

2008

2009

Highest

$118.5

$122.6

Median

$19.2

$20.1

Average

$22.3

$24.5

Lowest

$0.85

$2.7

Percent Contribu7on by Store Group

150%
100%
28%

50%

46%

13%

25%

60%

38%

72%

50%

82%

63%

90%

75%

96% 88%

100% 100%
% Sales
% Stores

0%
Group
1

Group
2

Group
3

Group
4

Group
5

Group
6

Group
7

Group
8

Avg Sales Per Store

$60,000.00
$40,000.00

Avg Sales Per Store

$20,000.00
$-

A
Stores

B
Stores

C
Stores

D
Stores

Median

Additional Analysis
Buyers often have detailed information about store demographics which can be used for further
analysis. As a starting point, you could request as much detail as possible about the top ten and
bottom ten stores. Compare and contrast the demographics for these two stores and see if there

2012 Accelerated Analytics, LLC. All Rights Reserved

Improving Performance with Store Analysis

you may want to explore this further with your buyer and even give some thought as to how the
SKU assortment can be optimized in those stores based on the dominant traits.

the buyers categories are typically assigned based on the total sales for a given store. This may result

is considered a B or C store based on total sales. Request a list of store categories from your buyer and
compare to the categories from your analysis. If there are variances, we recommend you meet with
your buyer and discuss adjustments to the plan-o-gram based on store performance for your SKUs.

Next Steps
This analysis is one in a series of articles that provides step-by-step guidelines to creating reports
and analyses that can help you better understand and improve SKU and store performance. The
series includes the following articles:

Store Analysis
SKU Analysis
Out of Stock Analysis
SKU Forecast
To download additional articles in this series, visit www.acceleratedanalytics.com.

About Accelerated Analytics, LLC

Accelerated Analytics is a comprehensive software-as-a-service (SaaS) solution for collecting,
analyzing, and reporting on retail EDI 852, POS, and supply chain data. Accelerated Analytics
includes rich web based analytics and mobile access so that business users can focus on growing
provide years of best practices in retail merchandising and are customizable to your exact
requirements.

For more information, visit www.AcceleratedAnalytics.com

2012 Accelerated Analytics, LLC. All Rights Reserved

10