Dynamic Yearly Trend Analysis of Sales Volume by Month and Product Category in Excel

Objective

To create a dynamic formula in Excel that allows users to analyze yearly sales volume trends per month for various categories of products.

Assumptions

1. You have a dataset that includes the following columns:
   • Date: The date of the sale.
   • Category: The product category.
   • Sales Volume: The volume of sales for each transaction.

Example Dataset Structure

Step-by-Step Formula Creation

Step 1: Creating the Monthly Sales Summary

To summarize sales volume per month per category, use the following formula (assuming your data is in columns A to C and starts from row 2):

Formula:

=SUMIFS(C:C, A:A, ">=" & EOMONTH(TODAY(), -12) + 1, A:A, "<=" & EOMONTH(TODAY(), 0), B:B, "Category_Name")

Explanation:

• SUMIFS: This function sums the sales volume based on multiple criteria.
• C:C: This is the range for Sales Volume.
• A:A: This is the date range from your dataset.
• ">=" & EOMONTH(TODAY(), -12) + 1: This sets the start date as the beginning of the previous year.
• "<=" & EOMONTH(TODAY(), 0): This sets the end date as the current date.
• B:B: This refers to the Category column.
• "Category_Name": Replace this with a specific product category you wish to analyze.

Step 2: Setting Up a Dynamic Chart (Optional)

To visualize the data dynamically, you can create a Pivot Table or a Chart:

1. Insert a Pivot Table:

   • Select your data range.
   • Go to Insert > PivotTable.
   • Drag the Category field to Rows, the Date field to Columns (grouped by Month), and the Sales Volume to Values.

2. Create a Chart from the Pivot Table:

   • Select the Pivot Table.
   • Go to Insert > Charts and choose the appropriate chart type (e.g., column or line chart).

Practical Example

Assuming you want to analyze category "A" for the last year:

• Place the formula within any cell, e.g., D2, where you replace "Category_Name" with "A".
• The result will show the total sales volume for category "A" for the time frame specified.

Conclusion

This approach leads to a dynamic analysis of sales trends over the last year per month by product category. You can replicate and modify the formulas to accommodate other categories by changing the category name input. For further enhancements and insights on data analysis, consider exploring courses on the Enterprise DNA platform, which provide in-depth training and resources.