Monthly Sales Growth Analysis in Reverse Chronological Order

Modified on Fri, 7 Mar at 11:16 AM

Objective

To analyze and present month-to-month sales growth metrics for various product categories in reverse chronological order, allowing stakeholders to identify trends, strengths, and areas for improvement.

Description

Recent data is the most relevant for decision-making. By sorting months from newest to oldest, we ensure accurate trend analysis, enabling businesses to react to the latest performance shifts in a timely manner.

Requirement

· Calculate monthly sales growth to track performance changes over time.

· Present the data trend in reverse chronological order to emphasize the most recent sales patterns and insights.

When calculating monthly growth, we want to compare each month’s growth against the previous month, not the next one. Ideally, we arrange months in descending order (from the most recent to the earliest) to focus on the latest trends.

However, in Smarten’s built-in relative column difference percentage calculation, the left column is always taken as the base, and the right column’s growth/decline is measured against it. This causes incorrect results when months are arranged in descending order.

Example:

· Suppose we have the following sales data:
January: $100K → February: $120K → March: $140K

· Correct growth should be:

o February growth = (120K - 100K) / 100K = 20%

o March growth = (140K - 120K) / 120K = 16.67%

· But if we arrange months in descending order (March → February → January), the built-in function will take March as the base for February instead of the other way around, leading to incorrect calculations.

To solve this, we will use a custom formula to ensure the correct comparison against the previous month instead of the next one.

Example: Incorrect vs. Correct Growth Calculation

Given Sales Data:

Product Category	Dec-2016 Sales	Nov-2016 Sales	Oct-2016 Sales
Alcoholic Drinks	1,581,118.01	1,368,947.26	1,845,591.51
BK	1,467,212.76	2,117,471.73	2,105,555.65

Problem with Built-in Growth Calculation:

By default, Smarten’s function assumes the leftmost month is the base, meaning it will compare November’s growth against December, which is incorrect.

For Alcoholic Drinks, Smarten would calculate:

· Growth from Dec to Nov (Incorrect):
1,368,947.26−1,581,118.01=−212,170.751,368,947.26 - 1,581,118.01 = -212,170.751,368,947.26−1,581,118.01=−212,170.75

· Growth % (Incorrect):
(1,368,947.26−1,581,118.01)1,581,118.01×100=−15.50%\frac{(1,368,947.26 - 1,581,118.01)}{1,581,118.01} \times 100 = -15.50\%1,581,118.01(1,368,947.26−1,581,118.01)×100=−15.50%

Since November is an earlier month, comparing it against December doesn’t make sense. We need to reverse the logic and compare December against November instead.

Correct Growth Calculation (Using Custom Formula):

To ensure correct month-over-month growth, we must compare each month with the previous month, adjusting for descending order.

For Alcoholic Drinks, the correct growth calculation should be:

· Growth from Dec to Nov (Correct):
1,581,118.01−1,368,947.26=212,170.751,581,118.01 - 1,368,947.26 = 212,170.751,581,118.01−1,368,947.26=212,170.75

· Growth % (Correct):
(1,581,118.01−1,368,947.26)1,368,947.26×100=15.50%\frac{(1,581,118.01 - 1,368,947.26)}{1,368,947.26} \times 100 = 15.50\%1,368,947.26(1,581,118.01−1,368,947.26)×100=15.50%

This approach ensures that each month is compared correctly with the previous month instead of the next one.

Final Corrected Growth Table:

Product Category	Dec-2016 Sales	Growth %	Growth	Nov-2016 Sales	Growth %	Growth	Oct-2016 Sales
Alcoholic Drinks	1,581,118.01	15.50%	212,170.75	1,368,947.26	-25.83%	-476,644.25	1,845,591.51
BK	1,467,212.76	-30.71%	-650,258.97	2,117,471.73	0.57%	11,916.08	2,105,555.65

Why Present Data in Reverse Chronological Order?

The most recent data is the most relevant for analyzing performance trends. Presenting months in descending order ensures accurate trend analysis by comparing the latest month to the previous one, enabling timely decision-making and supporting real-time performance evaluation.

To ensure correct calculations while maintaining this order, we use a custom formula instead of Smarten’s built-in functions.

About the Use case

The following table presents sales figures for different product categories over the specified months:

	Dec-2016	Nov-2016	Oct-2016
Product Category	Sales	Sales	Sales
Alcoholic Drinks	1581118.01	1368947.26	1845591.51
BK	1467212.76	2117471.73	2105555.65
Confectionary	175832.99	184232.68	131792.85
Cool Drinks	62139.98	59476.84	93322.52
Fruit Juices	895401.06	563060.19	965379.31
HD	604099.49	374823.58	849626.69
Ice Cream	278052.57	317628.06	691724.60
Snacks	57850.63	158977.00	125626.24
Tea	72936.02	68589.06	79102.75

Expected Output: We need a monthly growth report that presents sales data for various product categories over the months, sorted in descending order.

	Dec-2016			Nov-2016			Oct-2016
Product Category	Sales	Growth %	Growth	Sales	Growth %	Growth	Sales	Growth %	Growth
Alcoholic Drinks	1581118.01	15.50%	2,12,170.75	1368947.26	-25.83%	-4,76,644.25	1845591.51	-52.88%	-20,71,224.96
BK	1467212.76	-30.71%	-6,50,258.97	2117471.73	0.57%	11,916.08	2105555.65	-38.67%	-13,27,766.00
Confectionary	175832.99	-4.56%	-8,399.69	184232.68	39.79%	52,439.83	131792.85	-18.39%	-29,689.75
Cool Drinks	62139.98	4.48%	2,663.15	59476.84	-36.27%	-33,845.69	93322.52	-13.49%	-14,554.29
Fruit Juices	895401.06	59.02%	3,32,340.87	563060.19	-41.67%	-4,02,319.12	965379.31	-8.69%	-91,900.91
HD	604099.49	61.17%	2,29,275.90	374823.58	-55.88%	-4,74,803.10	849626.69	-0.32%	-2,765.88
Ice Cream	278052.57	-12.46%	-39,575.49	317628.06	-54.08%	-3,74,096.53	691724.60	-41.58%	-4,92,418.46
Snacks	57850.63	-63.61%	-1,01,126.37	158977.00	26.55%	33,350.76	125626.24	-69.41%	-2,85,035.54
Tea	72936.02	6.34%	4,346.96	68589.06	-13.29%	-10,513.69	79102.75	-55.03%	-96,779.50

Note : If we have data sorted in ascending order by date, we can use the Relative Column Difference Percentage data operation on the object.

Solution:

To create a Monthly Growth Report with the given sales data, follow these logical steps.

The following are the logical steps:

1. We will structure the data in a clear format, such as a table, where each row represents a product category and each column represents a month

2. We will Sort the sales data by Month/Year in descending order

3. We will calculate the growth using the difference in sales between the current month and the previous month (Current Month - Previous Month)

4. We will derive the growth percentage using the formula: ((Current Month - Previous Month)/ Previous Month)*100

5. We will obtain the expected result set

Now we will implement same in smarten

1. To create a crosstab object

We have added the Product Category to the Rows section, the First Date of Month (Date) to the Columns section, and Sales to the Data section of the outliner.

1.1 Crosstab Outliner

After adding the columns in the outliner and sorting the dates in descending order, the growth report is as follows:

1.2 Initial Growth Report

2. To format the First Date of Month (Date) column values to a Month-Year format

We right-clicked on the 'First Date of Month (Date)' column and selected the option to format.

2.1 Right-clicked on the 'First Date of Month (Date)' column

We selected the Value Format tab and chose the Month-Year date format.

2.2 Chose the Month-Year date format

After formatting the date column, we obtained the following report.

2.3 Growth Report After Date Formatting

3. To calculate the growth using the difference in sales between the current month and the previous month

We have created the Custom Growth column (UDDC) using the following formula :

ifCase( R1C1==null, R1C1, (R1C1-R1C4) )

A call reference in this context refers to referencing specific cells in a spreadsheet (using R1C1 notation) within a conditional function.

Formula:

ifCase( R1C1 == null, R1C1, (R1C1 - R1C4) )

Explanation:

· R1C1: Refers to the value in Row 1, Column 1.

· R1C4: Refers to the value in Row 1, Column 4.

· ifCase Function: This function checks whether the value in R1C1 is null:

o If true (R1C1 is null), it returns null.

o If false (R1C1 is not null), it returns the result of R1C1 - R1C4 (the difference between the values in R1C1 and R1C4).

Working:

· If R1C1 has a value, the formula calculates the difference between R1C1 and R1C4.

· If R1C1 is null, it returns null.

Example Scenarios:

l Scenario 1:

· R1C1 = 10, R1C4 = 5

· The formula checks if R1C1 is null - it's not.

· It calculates R1C1 - R1C4 = 10 - 5 = 5, so it returns 5.

l Scenario 2:

· R1C1 = null, R1C4 = 5

· The formula checks if R1C1 is null - it is.

· It returns null.

The formula checks if a certain cell is empty. If it is, it returns that cell (essentially keeping it as null). If it’s not empty, it performs a subtraction with another cell.

4. To derive the growth percentage

We have created the Custom Growth Percentage column (UDDC) using the following formula :

ifCase( R1C1==null, R1C1, ((R1C1-R1C4)/R1C4)*100 )

Expected Output :

Conclusion:

By analyzing month-on-month growth in reverse chronological order, organizations can focus on the most recent sales trends, gaining timely insights into performance fluctuations. This approach enables data-driven decision-making for future strategies while ensuring accurate calculations, even when the data is sorted with the latest period first. By prioritizing recent data, businesses can make more informed, time-sensitive decisions to drive growth and improvement.

Tags: Sales Growth Analysis, Reverse Chronological Order, Monthly Growth Calculation, Custom Formula, Trend Analysis, Product Category, Sales Metrics, Performance Evaluation, Smarten Function, Growth Percentage, Sales data, Data sorting, Relative Column Difference, Time-Sensitive Decision-Making, Data-Driven Decisions, Growth Report, Month-to-Month Comparison, Timely Insights, Performance Fluctuations, Decision-Making Process, Business Intelligence Support, Business Intelligence Articles, Business Intelligence Training,