To make KIVALUE work and improve your user experience, we log user data and employ essential cookies. By using KIVALUE website, you agree to our Privacy Policy, including cookie policy.

Mastering Merchandise Hierarchy Calculation in Excel // A Guide


Merchandise hierarchy plays a vital role in fashion retail, enabling retailers to organise their product assortment in a structured manner. By utilising Excel, retailers can efficiently calculate and manage merchandise hierarchy, facilitating effective assortment planning and merchandise financial planning.

In this article, we will delve into the process of calculating merchandise hierarchy using Excel, providing step-by-step guidance to help fashion retailers streamline their operations.
Understand the Significance of Merchandise Hierarchy

Merchandise hierarchy involves categorising products into logical groups based on various attributes such as department, category, subcategory, and item level.

This hierarchical structure aids retailers in managing their product assortment, analysing sales performance, and making informed merchandising decisions.
Define the Merchandise Hierarchy Structure

Start by defining the merchandise hierarchy structure specific to your fashion retail business. Identify the levels of hierarchy, such as department, category, subcategory, and item.

Each level should have a unique identifier and a clear relationship with the higher levels.
Create a Spreadsheet in Excel

Open a new spreadsheet in Excel and set up the columns to represent the different levels of the merchandise hierarchy. Assign appropriate headers to each column, such as Department, Category, Subcategory, and Item.

Ensure that the columns are organised in a hierarchical order, with the highest level at the leftmost column.
Enter the Data

Enter the relevant data for each product in the corresponding columns. Assign the appropriate department, category, subcategory, and item to each product based on your merchandise hierarchy structure.

Make sure to maintain consistency and accuracy while entering the data.
Group and Sort the Data

Utilise Excel's grouping and sorting functions to organise the data according to the merchandise hierarchy.

Select the columns representing each level and apply the grouping function to create collapsible sections for easy navigation and analysis.
Apply Formulas for Aggregated Views

To generate aggregated views of the merchandise hierarchy, use Excel's SUMIF or COUNTIF formulas. These formulas allow you to calculate the total sales, units sold, or any other relevant metrics for each level of the hierarchy.

For example, you can calculate the total sales for each category by using the SUMIF formula based on the category level.
Utilise Pivot Tables for Analysis

Excel's pivot tables provide a powerful tool for analysing and visualising merchandise hierarchy data. Create a pivot table using the merchandise hierarchy columns and select the desired metrics to display.

Pivot tables allow you to drill down into the hierarchy, filter data, and gain valuable insights into sales performance and assortment planning.
Update and Maintain the Merchandise Hierarchy

Regularly update and maintain the merchandise hierarchy as new products are added or existing ones are discontinued.

Update the Excel spreadsheet with the new data and ensure that the hierarchy remains consistent and accurate.

Excel serves as a valuable tool for fashion retailers to calculate and manage merchandise hierarchy effectively.

By following the steps outlined in this guide, retailers can organise their product assortment, analyse sales performance, and make informed merchandising decisions.

Embrace the power of Excel in merchandise hierarchy calculation and optimise your assortment planning, retail merchandise planning, and overall merchandise financial planning for enhanced success in the fashion retail industry.
At KIVALUE we cover end-to-end
fashion retail processes
From connecting merchandising, buying, and planning processes to omnichannel inventory and markdown management

We offer FREE assessment of your current solution requirements.