Rosella       Machine Intelligence & Data Mining

  Home | Data Mining/Machine Learning | MyDataSay/Android | BI Server | Products & Downloads | Site Map | Contact/Sales |

Sales trend analysis and Sales forecasting - Customer Trend Analysis

Sales trend analysis and timely identification of newly emerging customer trends is very important to businesses. Sales patterns of customer segments may indicate market trends. Upward and downward trends in sales signify new market trends. Time-series predictive modeling can be used to identify market trends embedded in changes of sales revenues. Understanding of customer and sales trends is important for marketing as well as for customer retention. Typical customer and sales trend analysis includes;

  • Which customer segments are having highest growth in dollar terms?
  • Which customer segments are having highest revenue decline in dollar terms?
  • Which customer segments are having highest growth rates in percentage terms?
  • Which customer segments are having highest revenue decline rates in percentage terms?
  • Which customer segments have similar or opposite sales patterns?
  • How solid the growth (or decline) trend is?
  • Which customer segments are showing exponential growth (or decline)?
  • And so on.

Download of Excel Addin Tools is available at the bottom of this page.
For CMSR Studio time-series tools, please read Big Data Analytics and Tools.

Excel Addin - Groupby and Sales Trend Analysis and Sales Forecasting in Excel.

Time-Series Analysis and Projection Marketing

Time series analysis and forecasting is very important for projection marketing. Based on time-series projections, inventory levels can be maintained efficiently, for example. Retailers and wholesalers can maintain optimal inventory levels based on time-series projections. Manufacturing companies can plan manufacturing capacities. Time-series analysis and projection marketing is very important.

Segmentation methods and techniques for Customer trend analysis and Sales trend forecasting

Market Segmentation is a process that divides a market into smaller sub-markets called segments. Normally, market is segmented in such as way that customers of a segment have the same attributes. Commonly used segmentation methods and techniques for sales forecasting include the followings;

  • By products.
  • By product and service types (or product categories).
  • By geographical regions: regions, countries, states, zip-codes, counties, etc. (Geographic Segmentation)
  • By sales channels, branches, and departments.
  • By sales representatives.
  • And so on.

Why Customer Segmentation? People with similar attributes tend to exhibit similar purchasing patterns. This fact is particularly important in customer relationship management, marketing, and risk management. For example, people with certain life-styles tend to buy certain-types of products. Promoting products particularly targeted towards the demographic group can lead to successful marketing. Customers are segmented along the following demographic and psychographic attributes, and time-series trend analysis is performed;

  • Demographics: gender, age, income, education, etc.
  • Psychographics: life style classification.
  • And so on.
PSM: Profile -> Segment -> Monitor Trends

PSM is a simple customer and sales trend analysis method to manage your most valuable business resources: customers and markets. Profile your customers and markets as suggested in Customer Profiling. Based on profiling, develop customer and market segments. Finally, monitor the following trends;

  • Sales revenues and volumes
  • Profits and losses
  • Customers: new customers, churns
  • Debts, defaults and delinquencies
  • And so on

Sales forecasting methods and techniques: Time-series Regression

Regression is an analytic technique used in developing predictive models for numerical data. It automatically derives mathematical functions that summarize trends embedded in past historical data, in such a way that minimizes the errors between actual input data and predicted values by the models. Regression can be applied to sales time-series data. A time-series consists of a set of observations which are measured at specific time intervals, say, monthly, quarterly, yearly, etc. Observations we are interested are sales revenues.

Customer (or market) segments have different sales trends. Some segments may be growing, while others are declining. Segment-by-segment sales forecasting can produce very useful information. Forecasting can be short term, mid term and long term. Long term forecasting may not produce accurate predictions. However it is very useful in understanding market trends.

Sales Linear Growth Estimation by Linear Time-Series Regression

Linear growth can be best understood with linear time-series regression. Linear regression formula is stated as "Y = a + b * X" where "b" is the periodic average increase/decrease amount. "b" is the "Linear growth". The red line in the following figure shows the linear regression line. It represents the exact linear average growth amount. The gradient "b" of the red line is the linear growth. This method of growth computation is best for linear growth data. If data is not linear, this should be used with caution. "Linear growth %" is the percentage ratio of the "b" value over the average of input series data.

Time-series linear growth estimation.

Sales forecasting with Seasonal Adjustment

Sales time-series data often contain seasonal patterns. For example, clothing and fruits sales can fluctuate based on seasons. This hides underlying sales patterns and makes it difficult to project sales figures accurately. Seasonal adjustment is used to overcome this problem. It removes seasonal factors. Time-series regression on seasonally adjusted data can capture hidden patterns. Predicted values on seasonally adjusted data are then converted back to actual values. This process can significantly improve accuracy of predictions. It is noted that to make seasonally adjusted sales forecasting works, multi-year series data is required. At least three years data is recommended.

Forecasting with Seasonal Adjustment Using Neural Network

As an alternative approach to regression, neural network can be used to capture time-series trends and seasonal patterns. Note that regression is limited in terms of information used. Neural network can include various related indicators. Neural network is a robust modeling tool. It can capture time-series trends along with seasonal patterns. Details are discussed in the following link. The link also describe how to import neural network models into Excel sheets.

The following is a plot showing gradual increase with seasonal patterns. Brown line is from time-series data. Blue line is from neural network predictions.

neural network vs regression

For more on neural network time-series modeling, please read;

The following YouTube video shows how to develop Time-series Neural Network Models;

Excel Sales Trend Analysis and Sales Forecasting: Excel Group-by Add-in Tool

Rosella Group-by Excel Add-in provides powerful simple-to-use tools for trend analysis. It combines groupby aggregation with time-series predictive modeling. It employs powerful non-linear regression. Currently it supports 16 different mathematical functions using advanced function fitting algorithms. The following figure shows an example of Rosella Groupby Add-in reports with time-series analysis. (For full-size view, click the image.)

Excel Addin - Groupby and Sales Trend Analysis and Sales Forecasting in Excel.

Columns "MTH1" to "MTH7" indicate recent actual trend values. Columns "Next 1" to "Next 3" show projections for the coming months. The "Chart" column displays them in charts. Notice that charts show that projections are not necessarily linear! The Addin automatically detects a most suitable function for you. Actual function description can be viewed from the notes. Prognostics columns provide information showing fitness and trends. The "Signal" column indicates which row items to pay attentions in three colors: green (for good), red (for bad), and yellow (for warning).

The following figure shows highlighted cells. It is based on column-wise high- and low-values. Red indicates high-values and blue shows low-value cells. Color intensity is based on relative cell-values. (For full-size view, click the image.)

Excel Addin - Groupby and Sales Trend Analysis and Sales Forecasting In Excel- Highlighted.

Sales Trend Analysis and Sales Forecasting with CMSR Studio

CMSR Studio also supports various tools for sales trend time series analysis. The following figures show examples of group-by-group time-series trend data visualizations. For more information, please read Big Data Analytics.

Group by group time-series analysis.

Group by group time-series analysis.

Segment Time-series Trend Similarity / Correlation Analysis

The following figures show seven time-series trend charts. Consider the three time-series trends of the left figure. The first two series have identical positive growing trends. As the first series values rise, the second series values also rise. The third has opposite trend. As the values of the first series rises, the values of the third series decrease. This type of co-relationship is very common in business data. These three are correlated! The first and the second are positively correlated. The third is negatively correlated with the first and the second. Now consider the two series of the middle figure. Although not related in linear fashion, two values rise and fall together. That is, they are also closely correlated.

Time-series trend similarity. Time-series trend non-linear similarity. Time-series trend time-lag similarity.

Consider the two series of the right figure. Notice that the second series values resemble the first series values with one period later. That is, one period time shift or time lag pattern. Second pattern occurs after one period lag. This this is also very important co-relationship.

Level of correlation can be measured in terms of correlation coefficients. It ranges between -1 and 1. If there is perfectly positive correlation, it is 1. If there is perfectly negative correlation, the value is -1. If no correlation, it is 0. The following chart shows a correlation matrix table of groups/segments. Red color indicates positive correlation. Blue is for negative correlation. Intensity of color is based on (absolute) correlation coefficients. With "Contrast" filters, most important correlations can be identified easily.

Excel Addin - Group/Segment Time-series Trend Similarity/Correlation Analysis.

The following figure shows top 10 most correlated other groups of a selected group. It can be used to identify other groups with similar or opposite time-series trend.

Excel Addin -Group/Segment Time-series Trend Similarity/Correlation Analysis.

Excel version of this tool is limited to several dozen items. CMSR Data Miner / Machine Learning Studio supports larger numbers of items.

Time-series Regression Function for Excel Formula with Seasonal Adjustment

Rosella Groupby Excel Add-in tool also provides the following time-series regression function that can be used in cell formula specification;

          =RGTSREGRES(1, $D$1:$D$45, "01D",,4 ...)

This incorporates time-series regression with seasonal adjustment and smoothing (moving average and exponential) with the following parameters;

  • Prediction period. Function description. RR value.
  • Time-series range.
  • Number of seasonal adjustment periods.
  • Number of smoothing periods and exponential smoothing rate.
  • There are 16 different mathematical functions supported. Multiple-function selection can be specified.

Examples Of Trend Forecasting with Seasonal Adjustment in Excel

The following codes can be used to produce long term forecasting work sheet with seasonal adjustment. The screen image shows example output of these formulas. It's very easy to do!

  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4,2) ...... For moving average seasonally smoothed value.
  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4,3) ...... For ratio of seasonality.
  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4,4) ...... For seasonal index/ratio.
  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4,5) ...... For seasonally adjusted value.
  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4,1) ...... For prediction based on seasonally adjusted data.
  • =RGTSREGRES(-41+ROW(), $B$2:$B$41, "01",,4) ...... For forecasting value which is seasonal adjust reversed. This is the forecasting value!
  • =RGTSREGRES("RR", $B$2:$B$41, "01",,4) ...... For RR squared correlation coefficient.
  • =RGTSREGRES("FUNCTION", $B$2:$B$41, "01",,4) ...... For function description.
  • =RGTSREGRES("GRADIENT", $B$2:$B$41, "1") ...... For linear growth estimate.

Excel =RGTSREGRES example.

Download: Group-by Excel Addin Tool for Trend Analysis and Forecasting

For one year free license copy of this Excel Addin, click Download Rosella Excel Addin. This features the following tools (Last updated: May 11, 2018);

  • "Group-by tables" with trend analysis, time-series regression incorporating smoothing and seasonal adjustment.
  • Group/Segment Time-series Trend Similarity/Correlation Analysis.
  • "Cross tables" with deviation analysis based on chi-square statistics.
  • "RGTSREGRES formula function" for time-series regression incorporating smoothing and seasonal adjustment.
  • "Data import" into Excel from database through ODBC and SQL queries.

Download: Time-Series Regression Models for Android

For Time-Series Regression Models and Downloads, read Time-Series Regression Models for Android.