Data Tech: Closeup Look at a PC-based Merchandising System

Oct 01, 2003 9:30 PM  By

Merchandise management is both art and science. The art is in product sourcing and selection. The science is in forecasting, analyzing, and managing demand data to minimize backorders and stock-outs while trying to reduce end-of-season overstock inventories. ▪ Helping out with the science portion of the task are catalog merchandise forecasting tools. Though they’ve been around for some time, these applications tend to be relatively expensive and require a major commitment from management.

In lieu of making such a costly investment, many companies plan product assortments and make space allocations and buying commitments in a somewhat freewheeling fashion, relying on a combination of intuition and the manipulation of massive spreadsheets. This gets the job done, and it gives the reassuring illusion of quantitative analysis. Yet without an overriding structure, such efforts are prone to inaccuracy if not outright failure.

A solution from Down Under

C-Plan from Westam Pty. Ltd. (www.westamco.com) in Pakenham, Australia, aims to change that. A PC-based system, C-Plan is reasonably rigorous yet inexpensive ($5,000 for the first user, plus $1,000 for each additional user and 18% annually for support). While not as elaborate as the pricier merchandise forecasting systems, it still imposes a comprehensive discipline.

Created for Windows 2000/NT or higher on a Pentium 500 MHz or faster, C-Plan uses Microsoft Excel 2000 for its user interface. The software is based on a hierarchical arrangement of products into like groups referred to as a family tree, which is determined by how a company’s customers respond to and buy its products, with up to five tiers or “breaks,” down to the style level.

Along with the family tree concept, planning is based on two assumptions: that demand is a function of catalog space and circulation, and that past behaviors are a guide to future behaviors. These planning outcomes are then linked to profitability via contribution, which is based on gross demand minus lost sales, returns, cost of goods, unusable returns, variable fulfillment costs, and catalog production costs.

The system supports both a “top down” (referred to in C-Plan as “plan down”) and a “bottom up” (referred to as “plan up”) approach to merchandise management, accounting for product assortment, price point mix, and products per page plus historical demand data to recommend space allocations and quantity of items sold to reach profitability.

A key driving force of C-Plan is the use of two ratios: sell ratio (total space cost times 100 divided by demand) and margin ratio (buying margin times sell ratio). All historical catalog and merchandise data are extracted by C-Plan from the user’s order entry system by means of a specially created interface program.

The potential demand for a catalog is calculated by determining the proposed number of pages, the number and types of circulation, the expected response rates and average order values, and the total cost of the catalog, then comparing this to a previous book that is similar in terms of seasonality and merchandise content.

You then enter the proposed circulation quantities for the catalog, along with proposed percentages for lost sales, returns, discounts, and variable fulfillment costs; the planned total costs for the catalog; and the planned merchandise pages and total pages. C-Plan will in turn calculate a theoretical sell ratio and demand figure.

Next you enter a required sell ratio figure, derived from the previous catalog’s value, the theoretical value, and any other information that may affect the planned demand. C-Plan allows you to fine-tune this ratio until the “required demand” figure is consistent with the potential demand established in conjunction with the marketing department.

Finally, you enter an estimated margin ratio for the whole catalog, reflecting the difference between the retail price and the cost of the product (if an item costs $8 and sells at $20, its buying margin is 60%). If, in this example, the product had a sell ratio of 15, then the margin ratio would be 9 (60% × 15 = 9).

By entering the proposed number of items (breaking out the carry-overs from the new items), you produce a guide both to product density per page and to the impact of SKU numbers on warehousing costs.

When the merchandise manager is satisfied that the catalog demand and performance levels are in line with company requirements, the initial stage of the plan-down process is done.

The starting figures are then set as the required performance for the catalog. The required catalog performance is subsequently used to establish a required performance for the first break or tier in the family tree. This performance level is used to establish the required performance for the next break in the family tree, and so on, down to the style level.

At each tier of the family tree, C-Plan shows a comparison between the required performance and the actual performance of the catalog against which the comparison is being made. And at each level the user can access a “space-item performance” screen that shows how well that category of the family tree used its space in the comparison catalog.

The last level of the family tree requires the buyer to plan the individual items in the catalog according to the “required” targets set; this step is the bridge to the plan-up process, which begins with the item plan.

The plan-down process will have identified that for the last level of the family tree a particular branch will require, for example, four items (two relists and two new), that they will be allocated 1.5 pages, and that they will be expected to have a total demand of, say, $75,000 and a profitability (contribution percent) of 18%.

In completing the item plan for this branch, the buyer may access an item report that shows complete details of the performance of all items belonging to that branch in the previous comparison catalog or the item query report. This enables the buyer to review the complete performance of any item in all previous catalogs or in the price point performance report.

The item plan will then calculate a “suggested” sell ratio, and hence demand and profitability for each item based on the space that you allocate to each item. You can look at the demand per square inch and assess its reasonableness. If you think the value is not reasonable then you will need to adjust either the space or the sell ratio for the item.

The item plan then shows the forecast demand dollars and units for each item. In addition it takes into account the lost sales and returns percentages that you have allowed for to calculate the gross less returns (GLR), which is in effect the number of units of each item required to achieve the final demand.

Completed item plans for each branch of the family tree are submitted to the merchandise manager for approval. Each approval constitutes a step in the plan-up process. When all branches have been approved, the plan-up data become the effective financial and merchandise budget for the catalog. This budget should closely match the initial figures set in the first stage of the plan-down process.

C-Plan also provides:

  • an order quantities facility that allows you to quantify the units to be ordered for each item based on its lead time and on the cost of holding stock;

  • a buying plan that summarizes which items and quantities need to be purchased after taking into account any stock that may already be on hand;

  • a notes page that allows you to record freehand notes on a catalog that can be recalled in the future;

  • a catalog review screen that provides a slightly different perspective of catalog performance tailored to the perspective of the marketing team.

The biggest investment with C-Plan will be the time. But with the system providing all the requisite formats and algorithms, you will likely spend about 20%-30% less time in merchandise and space allocation planning and analysis, with improved accuracy and greater visibility and flexibility of the data.

More important are the consistency and accuracy across the enterprise. With C-Plan, everybody literally works off the same page, with the same assumptions, and the information is in one place, which should also lead to faster decision-making.


Ernie Schell is president of Marketing Systems Analysis, a Southampton, PA-based consultancy.

Other Merchandise Forecasting Systems

Of course, C-Plan isn’t the only system out there. Another innovative merchandise planning and forecasting tool, which includes multichannel planning options, is the Direct Integration Enterprise Suite from Mount Laurel, NJ-based Direct Logic (www.direct-logic.com).

Other products include IF/SO from Forerunner (with costs in the mid to high five figures), Forecast*21 from Direct Tech (low six figures), and Connectrix (high six figures or seven figures).

There are also a half-dozen multichannel systems, such as Evant, MarketMax, and Planalytics, that are six-figure solutions as well.
ES