Using Hyperion Essbase to Report Comparable Store Sales

One of the commonly used measures in the retail industry is “comps” – comparisons of actual sales for this year versus last year.  The goal of reporting comparable store is to provide information on what portion of a company’s sales comes from increasing sales growth in existing stores versus opening new stores.   This metric is used to measure whether a company’s sales will continue to grow when store base reaches a saturation point, or the company slows expansion.

What are the considerations in defining comp store calculation?

  • Definition of comp store. In addition to having a store open for at least 1 year, it’s important to compare stores that have not changed significantly.  In this case, we are using square footage in the store to identify significant changes to a store.  In our example, if square footage changes by more than 25%, sales are no longer comparable to prior periods.  Also, if the status of a store changes (i.e. opening, closing, moving, temporarily closing), comp store sales are not comparable with prior periods.
  • Definition of applicable time periods. In this case, we used month to date, quarter to date, and year to date.  Each applicable time period is calculated monthly.  The applicable time period amount is calculated only for stores open during the applicable period.  For example, the June YTD amount for 2010 is only calculated for stores in existence from Jan 2009.
  • Calculation of comp sales. Most clients prefer to remove the effects of currency translation on this calculation.  In this case, only net sales are used for comp store analysis.

Implementation

The database outline for the comp sales database contains the following 10 dimensions:

  • An individual store is uniquely identified as a member in the stores dimension.
  • Comp store amounts are only calculated for the comp stores scenario.  Actual data is loaded to the comp store scenario.

Below are sections of the accounts dimensions used for the comp store calculation.

The comp store control stats are used to calculate the comp status counter, which is the first determinant of whether a store is a comp store.

The comp store metrics hierarchy stores the applicable comp store amounts in local currency and USD.  Local currency comp store metrics show amounts for current year and prior year for MTD, QTD, and YTD.  USD comp store metrics show amounts at a constant exchange rate.

Approach

There are 2 different calculations for the comp store process:

  • The calculation of the comp store sales counter determines whether a store qualifies for comp store status based on square footage and store status.
  • The calculation of comp store metrics is dependent on the calculation of the comp store sales counter.  The metrics calculation determines comp store amounts.

The key processes for the comp store sales counter calculation are as follows:

  • Calculate monthly square footage amounts.  Set beginning balance equal to prior December.  Accounts calculated are: square footage, store status, and comp store counter.
  • Calculate monthly square footage change percent.
  • Calculate ending store status and comp store status counter based on inputs for square footage and change type (open, close, move).  The comp store status counter is used to identify qualification for comparable periods.

The following is an example of how the comp store status counter logic would be applied to a store.  Note that the store comp counter is incremented monthly once a store is open, but a change in square feet of the store resets the counter.  This is to assure that sales from the 2000 square foot store are not compared with the 3000 square foot store.

After calculating the store comp counter, the key processes for the comp store sales metrics are as follows:

  • Copy actual (a rollup scenario including general ledger amounts and adjustments) to CompStoreAnalysis  (another scenario).  This allows reporting comp store results in a single scenario.
  • Create blocks for every year based on prior year gross sales.
  • Calculate net sales current year and net sales prior year in local currency for each appropriate time period, based on comp store status counter and the applicable comp time period (MTD, QTD, and YTD).
  • To be included in QTD comps, a store must have a store status counter of 13 and have been in existence since the beginning of the current quarter last year.  For YTD comps, the store must have been in existence since the beginning of last year.
  • Calculate comp store sales in USD using the prior year rate.
  • Aggregate comp store metric amounts in the comp store analysis scenario by stores, products, geographies, and legal organization.

Note in the sample store shown above, comparable net sales on a MTD basis would be calculated for December 2008.  Amounts would be calculated both in local currency and USD.  The USD accounts are for current and prior year would use the same rate (last year’s).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s