Rosella Machine Intelligence & Data Mining 
Home  Data Mining/ML  MyDataSay/Android  BI Server  Products & Downloads  Site Map  Contact/Sales  
Sales trend analysis and Sales forecastingTimely identification of newly emerging 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. Timeseries predictive modeling can be used to identify market trends embedded in changes of sales revenues. Understanding of sales trends is important for marketing as well as for customer retention. Typical sales trend analysis includes;
Download of Excel Addin Tools is available at the bottom of this page. Segmentation methods and techniques for Sales trend analysis and Sales trend forecastingMarket segmentation is a process that divides a market into smaller submarkets 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;
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 lifestyles tend to buy certaintypes 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 timeseries trend analysis is performed;
PSM is a simple 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 forecasting methods and techniques: Timeseries RegressionRegression 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 timeseries data. A timeseries 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. Segmentbysegment 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 forecasting with Seasonal AdjustmentSales timeseries 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. Timeseries 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, multiyear series data is required. At least three years data is recommended. Excel Sales Trend Analysis and Sales Forecasting: Excel Groupby Addin ToolRosella Groupby Excel Addin provides powerful simpletouse tools for trend analysis. It combines groupby aggregation with timeseries predictive modeling. It employs powerful nonlinear regression. Currently it supports 16 different mathematical functions using advanced function fitting algorithms. The following figure shows an example of Rosella Groupby Addin reports with timeseries analysis. (For fullsize view, click the image.) 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 columnwise high and lowvalues. Red indicates highvalues and blue shows lowvalue cells. Color intensity is based on relative cellvalues. (For fullsize view, click the image.) Segment Timeseries Trend Similarity/Correlation AnalysisThe following figures show seven timeseries trend charts. Consider the three timeseries 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 corelationship 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. 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 corelationship. 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 corrlation. Blue is for negative correlation. Intensity of color is based on (absolute) correlation coefficients. With "Contrast" filters, most important correlations can be identified easily. 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 timeseries trend.
Timeseries Regression Function for Excel Formula with Seasonal AdjustmentRosella Groupby Excel Addin tool also provides the following timeseries regression function that can be used in cell formula specification; =RGTSREGRES(1, $D$1:$D$45, "01D",,4 ...) This incorporates timeseries regression with seasonal adjustment and smoothing (moving average and exponential) with the following parameters;
Examples Of Trend Forecasting with Seasonal Adjustment in ExcelThe 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!
Download: Groupby Excel Addin Tool for Trend Analysis and ForecastingFor one year free license copy of this Excel Addin, click Download Rosella Excel Addin. This features the following tools (Last updated: May 11, 2018);

