Introduction to Sales Trend Analysis

Sales Trend Analysis looks at historical revenue data to identify patterns, used extensively in budgeting and forecasting. It is a useful method to detect short-term changes in revenue growth and performance.

Trends

A Trend is an upwards or downwards shift in the development of a metric over time. It’s useful for estimations of future performance, and a straight-forward, measurable way to track our progress towards our goals. Trends show us how data changes over time. We still need to link these changes to the underlying causes and look for actions to take. And this is where Sales Trend Analysis comes to help.

Why Perform Sales Trend Analysis

Sales Trend Analysis is a concise process with results that are of critical importance for the business.

We can plot sales trends from historical data, and use them to estimate future performance. Keep in mind that these can end up being highly incorrect. Therefore, it is better to forecast based on more drilled-down data, and perform additional analysis, to better estimate sales over future periods.

Sales Trend Analysis gives valuable insights into how our business operates. We gather actionable information on the performance of our business, which can significantly improve our decision-making process. Employing Sales Trend Analysis leads to decisions based on the actual data of the company.

The method has various use cases. The most common include:

• Studying sales patterns to predict future performance;
• Finding uncommon developments over the period, which we need to address in our forecasts;
• Estimating and forecasting future sales revenue;
• Identify the volume to stock-up from different components and products;
• Prepare rolling dashboards, outlining the last twelve-month period visually;
• Perform analysis to identify slow-moving inventory;
• Profiling customer behavior, preferences and buying habits;

The main benefit of analyzing Sales Trends is the improvement in the decision-making processes in the long run. The data-driven approach to our sales strategy is better than the common ‘as we go’ decision process.

Performing Sales Trend Analysis

To perform a robust and insightful sales trend analysis in a company that operates with physical goods, we need a sound inventory management system. We can set up our model in Excel, which can take a bit more time, but will ensure that anyone can use it. The alternative is to use specialized software, which might require significant additional training and investment.

Simply plotting all revenue on a chart is not a good idea. Many large companies have diversified product portfolios and operate at different locations and channels within the same organization. Therefore it is a much better idea to split revenue into streams and categories.

Sales Sub-categories

We usually analyze sales broken down into sub-groups based on categorical values. We can drill-down revenues in many ways, depending on what’s essential for the business. The most common ones include:

• Product – this can help us identify products with reduced performance, and modify our strategy accordingly;
• Product category – helps us make timely decisions to cut prices, advertise, discontinue product groups, and set up goals per product line;
• Region – we can analyze new markets separately, and see how well the company is building the brand and distribution network;
• Client – sudden fluctuations in sales to a client need to be investigated, as they might help us pinpoint an issue with the product or service. We can also find indications that a competitor has lower prices or higher quality;
• Channel – usually, when developing new channels, we expect an initial spike in sales and flattening of the trend over time.

When performing Sales Trend Analysis, we need to make sure that the data is complete and up to date. It is critical to use the proper visualizations and know the data well enough to perform adequate drill-downs and gather meaningful insight.

Sales Metrics

Sales-related metrics that are usually subject to trend analysis include:

• Sales revenue;
• Sold quantities;
• Material cost;
• Absorbed overheads;
• Cost of Goods Sold (COGS);
• Gross Profit Margin (GPM);
• Contribution.

It is better to perform Sales Trend Analysis on a year-on-year basis, instead of a month-on-month basis. This way, we ensure that we tackle seasonality in our business.

 

Example

Here’s a breakdown of all sales invoices issued by a company for the financial years 2014 to 2018. The data shows the amount, client, quantity, period, and others.

Next, we have a list of all our clients. The names might look silly, as we have replaced them with randomly generated ones. In this list, we have some additional info on each client.

Using a simple vlookup we can combine the two sheets by transferring the columns that we deem meaningful.

Next, we prepare some pivot tables that will allow us to look at sales aggregated on different bases.

Foreign and Related Party Trends

First, let us look at the Group categorical value that we have for each client. It shows us whether the sales are intercompany (to related parties) or to third parties (foreign).

Looking at sales separated by the type of the client, and plotting those on a column bar chart, we can instantly spot two trends:

• Sales to related parties are amounting to less and less from the total sales, except in 2017 when there was an increase;
• Sales to third parties had a significant drop in 2017 but then recovered in 2018.

Let us look at Plotting the values on a chart and adding a linear trendline. Looking at the trend line, we can reasonably expect that sales to third parties will grow in future periods. To forecast three forward periods, we use Excel’s slope and intercept functions on the historical data. Read more on linear regression in the article Least-Squares Method to Estimate the Cost Function.

Following the same logic, we would expect that sales to related parties hit zero in 2019.

Using the slopes and intercepts of both, we can calculate a forecast for sales per Group for the next three years. As sales to related parties come at a negative value, we adjust it to zero.

Adding the forecast to our chart, we get the following visualization, which shows us that we can expect sales to grow steadily over the next three years. We need to remember that forecasts based purely on trend analysis are highly risky. We need to look into additional information and perform a further investigation to corroborate our findings.

Sales Analysis per Country

Let us look at how sales are developing per country where the transactions occurred.
Looking at our breakdown per country, we can only look at the top 20 clients, as, on average, these account for more than 90% of the total sales.

Many times Sales Dashboards only go as far as listing sales per country. And this is understandable, considering that if we were to plot all countries on a chart, we get something completely unusable.

What we can do is look at some countries individually. Let’s start with China, our biggest market over the period 2014 to 2018.

You can see that adding a trendline shows us that most likely, sales to this region will continue to decrease over time. A downtrend might be a correct assumption. Or our trend might be getting thrown off by a substantial one-off decrease in the financial year 2017. We need to perform further analysis to make sure we are not working with incorrect assumptions.

Looking at our second biggest market, Finland, we noticed a significant decrease in 2018. Our trendline shows that most likely, we can expect a decline in sales to this market to continue.

Using Excel’s forecast.linear function, we can calculate the forecasted sales per country for the next three years. Remember to check if the calculation gives a negative amount and replace those with zero.

Sales Trend on a Total basis

Now let’s see if we get a different projection of sales if we forecast based on the total sales value per year. Following a similar approach, we get the following estimates for the financial years 2019 to 2021.

We can now compare the results we get from each calculation for the years 2019 to 2021.

Forecasting on a total basis yields a more conservative estimate for future sales, compared to running our forecast based on a breakdown per group  (slightly higher projections) or country (highest amounts). Now it is up to us to confirm the underlying assumptions by employing other forecasting and analysis methods to arrive at the most likely forecast for the future sales performance of the business.

If you are interested in other forecasting articles, take a look at our introductory article on Creating a Cash Flow Forecast Model

Conclusion

Sales Trend Analysis is a straightforward process that salespeople often overlook in favor of what we refer to as ‘gut feeling.’ This form of analysis provides crucial information and helps us draw insights into the performance of the business.

Sales trends help us identify both problems and opportunities for our company. We use them to track progress towards goals and as part of our sales strategy decision-making process.

Understanding how our company does in terms of sales is paramount to developing a sense of the past performance of the business and forming reliable assumptions for proper forecasting of future sales.

Don’t forget to share this article with colleagues and friends and download the Excel example file below.