Skip to Content

BI for Retail: Merchandise Planning

Business Intelligence can be an enabler for performance improvement across multiple areas of a retail business. In this blog I will focus on merchandise planning.


What is merchandise planning?

A simple definition is a methodology that aims to balance inventory against sales to maximize return on investment.


Why is improving merchandise planning important?

There are 2 major areas of profit leakage in retail. On one hand, you suffer from lost sales as a result of insufficient stock. On the other, your margins are squeezed if unsold merchandise is marked down at the end of a season. If a retail business has $100 million in turnover and loses 10% from markdowns and 10% due to lost sales they are looking at a total loss a $20 million. More importantly each 1% in improvement in either markdowns or lost sales will add $1 million to the bottom line.


How do you improve merchandise planning?

Planners/buyers at retailers typically have to search through many different systems and databases to get the information they need to do their jobs.  This causes significant inefficiencies and many times opportunities/problems go unnoticed until it is too late to take action. Retailers can address this problem by integrating the various data source to better monitor and analyze key performance metrics such as sales, margins, inventory levels, and open to buy (OTB).  


What can BI enable?

The data integration, reporting, analysis and visualization capabilities of BI can improve transparency and clarity into the key activities of merchandising, including;

  • Strategic planning – enables you to integrate your planning processes and ensure they are all driven by the same company goals.

  • Assortment planning – enables you to define store/channel plans, monitor execution against plans, and make smarter buying decisions

  • Promotion planning – enables you to understand how shoppers respond to promotions, so you can create appealing promotions with out sacrificing margin

  • Life-cycle pricing – enables you to manage the trade-off between revenue, profit, and price image to support your brand strategy

  • Markdown planning – enables you to manage end of season pricing decisions to maximize sales and profitability


The following screenshots are from a buyer dashboard that was designed to help improve merchandise planning. It provides rich visualization of key performance indicators and is alert driven to notify the buyer of areas that need immediate attention. 





On the Sales tab the buyer sees information about unit sales, actual versus plan variance, year over year variance, and gross margin. While the gross margin % for the Apparel category is good sales for the category and all its sub categories are well behind plan.




On the Inventory tab the buyer sees information about inventory on hand, inventory turns, and stock to sales ratios. From an inventory perspective the Apparel category is in line with targets.





On the OTB tab the buyer sees information about markdowns, open purchase orders with drill down into the details stored in the ERP system, and open to buy (OTB). There is available OTB to bring in additional apparel products to help with sales. 





On the Vendor tab the buyer sees information about quality, on time and fill rates, invoice and order accuracy. Again there is drill down from the dashboard into the vendor transaction details stored in the ERP system. This information helps the buyer make fact based decisions about which vendors to order additional products from to support incremental store sales.

You must be Logged on to comment or reply to a post.
    • Thanks Mohammad.
      Yes Xcelsius is being used for the dashboard.
      It pulls data from ERP ECC 6.00, with the retail best practices.
      The PO and Scorecard Detail buttons pull up reports in the NetWeaver HTML client.
      • With the fast changing shopper that retailers are facing today the only way that a retailer can really plan, re-plan, execute on plans, and measure plan performance is with a strong BI platform.  In my old planning days we called it “tracking” and at the core of it was a retail EIS.  Nowadays that Retail EIS is SAP Explorer!
        Again, good overview and thanks for sharing
        • Thanks Tom,
          The Retail group has put together some very compelling demos that incorporate BI.
          For example the slow seller management application that uses both Xcelsius and Explorer to address markdowns based on sell-thru rates, and a comprehensive understanding of attributes that impact sales.
  • Hi Dan,

    i would really appreciate if could share some documents that how you have developed it?

    are u using the excel data or the data comes from Retail?

    what are the tools used to create this kind of dashboard..

    is this prebuild or we need to create from scratch?

    looking forward to hear from you?

    Regards and thanks
    Abid PAUL