Julian VASILEV^1* and Maria KEHAYOVA-STOYCHEVA²

Julian VASILEV1* and Maria KEHAYOVA-STOYCHEVA2

Julian VASILEV^1* and Maria KEHAYOVA-STOYCHEVA²

Mathematics, Informatics

Sales analysis by the rectangle method

¹Department of Informatics, University of Economics, Varna, Bulgaria

²Department of Marketing, University of Economics, Varna, Bulgaria

E-mail(s): ¹ vasilev@ue-varna.bg; ² kehajova_m@ue-varna.bg;

^*Corresponding author, phone: +359882164711

Received: February 12, 2016 / Accepted: June 14, 2017 / Published: June 30, 2017

Abstract

Sales analysis is a topic discussed by many researchers. Finding trends within sales and discovering the most important factors affecting sales are interesting issues. A lot of methods are created to analyse sales. Furthermore, transactional data are stored in databases. This data is used by many managers to make different types of analysis, e.g. ABC, XYZ, what-if analysis, top 10 customers and top 10 items. The research aim of this article is to reason and illustrate the rectangle method for sales analysis. The illustration is done with a sample dataset. The core of the rectangle method is to find product items that may be offered to shops which have not ordered them. The practical implications of this paper concern other researchers and practitioners. They may use the rectangle method as an additional one to other frequently used sales analysis methods. Conceptual issues of the rectangle method are discussed.

Keywords

Programming; Delphi XE; Sales management; Product groups

Introduction

Analysing sales is a widely-discussed topic. Methods and methodologies for sales analysis are created. Managers and sales specialists are interested in sales analysis. Statisticians use a great variety of methods to predict future sales. Managers are interested in finding top selling goods, as well as worst selling goods.

Transactional data for sales usually contain similar data in every enterprise. Transactional data consist of many records. Filtering and pivoting information are common techniques in sales analysis. Instruments like dashboards help managers in visualizing aggregated data. Traditional instruments like Auto Filters usually show the items that are sold by different sales’ points.

Some authors [1] use data from eBay.de to make analysis of ticket sales. Regression analysis is used. The authors propose a model with 23 input variables which explain 50% of the variance in the willingness to pay tickets in eBay auctions. The authors say that sophisticated dynamic pricing may be done on the basis of the proposed linear regression model.

Sales analysis of vacant land is a topic discussed by many authors. For instance, [2] calculates the relative weights of some factors (size, location, utility availability, access) influencing the price of land. Market conditions and conditions of sale are also factors but with weaker influence than the mentioned ones.

Some articles [3-4] focus on the instruments used for data analysis. They focus on the ease of using dashboards, direct connection to databases and ease of information sharing. A dashboard is used to visualize the sale amount by the salesperson on a map with different colours. Sales by salespersons and sales by country are also visualized on the same screen.

Mathematical methods are usually applied in sales analysis when seeking the appropriate function describing the dependency between time and sold quantities. Two authors [5] compare seven models to find out the one that may be used for modelling retail sales data.

Globalization has a strong impact on sales. Thus, sales analysis has to consider regional as well as international influences. A recent article [6] investigates patterns of globalization of 1 276 companies across 10 countries and 10 industries from 1998-2012. They find out that international sales are rising and home sales are falling. Sales in Asia are concentrated in four industries. For some companies [6] prove that regional sales are moving towards matching global economic activity.

Sales analysis of a specific product on a company level is a typical practice [7]. Statistical methods and SPSS are used to find dependencies. It is proven that some factors affect sales (year of the transaction and group of stock). It is proved that the month of sale and the average sale price of an item do not affect the sold quantity.

Existing sales analysis are focused mainly on the sales behaviour of organizations. Sales managers have different information needs in various situations. Some information needs in sales management are summarized from the point of view of using different sales analysis methods in table 1.

Table 1. Possible methods for sales analysis in different decision making situations

Some information needs within the decision-making process in sales management	Methods
	Affinity analysis	Cross-selling analysis	Market basket analysis	Complex sales models	Sales engagement methods	Sales forecasting	Sales and cost analysis
Decisions about future sales for specific products	+	+	+	-	-	+	-
Measuring profitability of sales	-	-	-	-	-	-	+
Identifying products that are sold together for a specific customer	+	+	+	-	-	-	-
Creating customer profiles	-	-	+	+	+	-	+
The probability to sell a certain product	+	+	+	-	-	+	-
Comparing customers with similar baskets	-	-	+	-	-	-	-
Offering new products to existing clients	-	-	-	-	-	-	-

^A Table note: Own elaboration

Each method (mentioned in table 1) may be applied using transactional datasets within spreadsheets or by using specialized software. The research aim of this article is to reason and illustrate the rectangle method for sales analysis.

Material and method

Distributors usually seek methods for increasing sales. One of the possible methods concerns the sophisticated sales analysis by points of sales. The scope of this research is within distributors who use the pull system to receive orders. The scope is narrowed to distributors who classify their clients’ database by different criteria. It is assumed that the compared points of sales are similar by main properties (turnover, commercial area, sales point type). The distributor has to solve the following problem. The distributor has to make a comparison between product items sold to both clients (points of sales). He /she/ has to identify equal product items sold in both shops. It means similar baskets. This problem cannot be solved with the methods mentioned in table 1. That is why innovative solutions are needed. One of the possible ways is to make cross-selling analysis with Pivot tables.

Pivot tables are used to show sales grouped by products and points of sales. In some cases, the research interest is focused on cells with values. In other cases, empty cells are important for sales analysis. Empty cells may be used to show out-of-stock situations.

Pivot tables should be used to show product items that are not sold in certain shops. The grouping is usually done by two dimensions – shops are shown by rows and items (stock keeping units) are shown by columns. The cells of the Pivot table with numbers show sold quantities by items and shops. The cells of the Pivot table without numbers (empty cells) show items that are no sold in certain shops. Transactional data from a distributor (wholesale or retailer) or a manufacturing enterprise (selling products to distributors) are used to make such a Pivot table.

The empty cells of the Pivot table show possibilities to sell certain items in different shops. But a distributor may take the initiative and offer some items to a certain shop. He /She/ may use his /her/ intuition or he /she/ may use an analytical approach to offer those goods that may be sold with high probability. The application of an analytical approach means that the distributor has to apply different method from those mentioned in table 1.

Transactional data of sales generate datasets with many records. Standard instruments such as filtering allow visualizing part of the dataset. Instruments like Pivot tables and SQL clauses containing “GROUP BY” and “HAVING” allow aggregation of data at a higher level. The aggregated data may be used for further analysis.

The rectangle method is applied for a set of product units (stock keeping units) within a product group. Transactional sales data have to be grouped by shops, product groups and product units.

Pivot tables are a popular instrument for aggregating transactional datasets. Information about the monthly sales may be aggregated by shops and stock keeping units (SKUs) within a product group. Such a Pivot table may be made easily by a distributor or a manufacturer. Let’s suppose that a distributor supplies a certain product group to n shops. The distributor uses an item list of m SKUs. The distributor receives daily orders from shops for the next day. Every day he fulfils the orders of each shop. Sample aggregated data are used to illustrate the rectangle method (table 2).

Table 2. Sample aggregated data from a transactional dataset of a product

group for a monthly period

The sample aggregated data are given as an example dataset. It is not real data from business subjects. The focus is testing the algorithm of the rectangle method.

A number of empty cells exist. It is easy to make a computer program which may describe all empty cells of table 2 (by shops or product units). The distributor may take the initiative to offer some product items to certain shops. Not all empty cells mean a reasonable offer.

A computer program based on the rectangular method may say that:

An offer for Shop 2 may consist of SKU1 and SKU3 (grey cells in table 2).

An offer for Shop 3 may consist of SKU2 and SKUm (grey cells in table 2).

Just highlighting all empty cells is applicable for distributors offering a comparatively small variety of items within a product group. Increasing the number of items and increasing the number of corporate clients determines the need of a complex approach. If we look at Shop 1 and Shop 2 we see that there are two common products that are sold in both shops (SKU2 and SKUm). But we see that two other items (SKU1 and SKU3) are sold only in one of the shops. The following question arises in this situation. Can non-selling product items be offered to the second shop? A basic factor for the product items variety sold in a retail shop is the needs and wants of retailers’ customers. To make such an offer it is important to have a similarity in the client audiences of retailers. The appropriate criterion for this condition is a key point for defining the rectangle method. The following definition of the rectangle method is defined.

The sales of items (within a product group) by two shops (Shop i and Shop k) are compared. The numbers of sold product items are calculated by both shops. This value may be stored in the last column of table 2 or it may be calculated by a simple program code analogical to the COUNT function in MS Excel. The minimal value is selected. 50% of this value is calculated as a similarity criterion. The number of matching items is calculated. The number of matching items is compared with the similarity criterion. If the number of matching items is greater than the similarity criterion, there is a similarity of client audiences of both shops. That is why the rectangle method will automatically propose items (sold only in one shop) to be offered for the other shop (figure 1).

Figure 1. Algorithm of the rectangle method

The practical implication of the rectangular method may be the following. If both shops are near schools, one of the shops offers three types of snacks with cheese, strawberries and chocolate. The other shop offers two types of snacks – with strawberries and chocolate. We may easily assume that there may be sales of snacks with cheese in the second shop.

A distributor or a manufacturer may use the rectangle method to offer several product items (within a product group) to shops which have not ordered these product items.

Results and discussion

The programming code and the software project are written within Delphi XE (Embarcadero Technologies, 2015) [7] using Pascal language. The following steps are executed within Delphi XE.:

1) A new folder “Rectangle method” is created. With Delphi XE a new Intraweb project is created (File/New/Other/VCL for the web/VLC for the Web Application Wizard).

2) An IWGrid and an IWMemo are put on the form.

3) An event procedure (TIWForm1.IWAppFormCreate) is created. This procedure is used to fill the IWGrid with sample data. In a real situation (in business) a button or link should open a text file (e.g. a CSV file) containing Pivoted data for sales (by shops and items).

The programming code is given in an annex. The project is compiled and run within a browser. A numeric example is created to illustrate the rectangle method (table 3).

Table 3. A numeric example

The result is the following (table 4).

Table 4. A part of the result of the executing the programing code with the sample dataset and the rectangle method

Offers for shop 1	Offers for shop 2	Offers for shop 3
Shop 1 may offer SKU 2.	Shop 2 may offer SKU 2.	Shop 3 may offer SKU 1.
Shop 1 may offer SKU 4.	Shop 2 may offer SKU 5.	Shop 3 may offer SKU 3.
		Shop 3 may offer SKU 5.

From the distributors’ point of view the rectangle method may better the process of offering different products to points of sales. The rectangle method enhances the analytical possibilities in the decision making process. It allows the automatization of sales analysis processes. An increased productivity is a result of the automation. Unlike existing methods (described in Table 1), the rectangle method finds differences in the products offered by similar customers (in terms of distributor’s point of view). Other methods (described in Table 1) may provide indirectly similar results, but the rectangle method automates just the comparison of similar customers. The rectangle method is an addition to the existing sales methods such as cross-selling analysis and market basket analysis.

The rectangle method is tested with a sample aggregated dataset. A programing code is written to test the algorithm of the rectangle method. The code is compiled and run. The algorithm may be coded in another programing language for example C++, C#. This possibility allows the rectangle method to be implemented as part of existing ERP systems. This implementation may allow an automatic generation of sales offers. It is one step ahead of the traditional process of making reports by Pivoting techniques.

The algorithm of the rectangle method should be tested with real datasets. This testing will show the reliability of the algorithm with real data. The usefulness of the result of the rectangle method may be estimated by sales managers and freelancers (computer programmers, marketing specialists) when the method is tested with real datasets. Further research on the rectangle method may be focused on the practical usefulness of the rectangle method from sales managers’ view point. The used sample dataset has a limited number of rows and columns. That is why the algorithm should be tested with a local database, as well as with a data warehouse and data marts. The algorithm should be tested with various product groups.

Conclusions

The information needs of sales managers concerning the offering of new products to existing clients may be solved applying the rectangle method. The core of the rectangle method is a sophisticated algorithm finding product items that may be offered to shops which have not ordered them. Since this algorithm has a formal description, it may be incorporated within the business logic of existing ERP systems. The sample source code in Pascal language (Delphi XE) can easily be modified in other programing languages.

The sales of items (within a product group) by two shops (Shop i and Shop k) are compared. The numbers of sold product items are calculated by both shops. The minimal value is selected. 50% of this value is calculated as a similarity criterion. The number of matching items is calculated. The number of matching items is compared with the similarity criterion. If the number of matching items is greater than the similarity criterion, there is a similarity of client audiences of both shops. That is why the rectangle method will automatically propose items (sold only in one shop) to be offered for the other shop.

The practical implications of this paper concern other researchers and practitioners. They may use the rectangle method as an additional one to other frequently used sales analysis methods. Distributors of goods with short expiry dates and fast-moving goods may use the rectangle method to offer some shops other products (that are not ordered) but these products may be well accepted by the market.

Appendix

The program coding (the source code) below is used to illustrate the rectangle method. The source code of this procedure is used for filling in the grid with sample data and executing the rectangle method.

The source code is written in Pascal language:

procedure TIWForm1.IWAppFormCreate(Sender: TObject);

var

aRow,

aCol,

aSecondShop,

Number_of_matching_items,

Similarity_criterion,

Cell_1, // Sold quantity in Shop i

Cell_2 : Integer; // Sold quantity in Shop k

Proposal : String; // A single proposal

List : TStringList; // A list of proposals

begin // Initializing the Grid

IWGrid.Caption := 'Pivot table about monthly sales by shops and items';

// Expanding the Grid by rows

IWGrid.RowCount := 6;

// Expanding the Grid by columns

IWGrid.ColumnCount := 7;

// Filling column titles

for aCol := 1 to IWGrid.ColumnCount - 2 do

IWGrid.Cell[ 0, aCol ].Text := format( 'SKU %d', [ aCol ] );

IWGrid.Cell[ 0, IWGrid.ColumnCount - 1 ].Text := 'Number of sold items';

// Filling row titles

for aRow := 1 to IWGrid.RowCount - 1 do

IWGrid.Cell[ aRow, 0 ].Text := format( 'Shop %d', [ aRow ] );

// FILLING SAMPLE DATA IN THE GRID

// Sales in Shop 1

IWGrid.Cell[ 1, 1 ].Text := '10';

IWGrid.Cell[ 1, 3 ].Text := '20';

IWGrid.Cell[ 1, 5 ].Text := '30';

// Number of sold items in Shop 1

IWGrid.Cell[ 1, 6 ].Text := '3';

// Sales in Shop 2

IWGrid.Cell[ 2, 1 ].Text := '11';

IWGrid.Cell[ 2, 3 ].Text := '22';

IWGrid.Cell[ 2, 4 ].Text := '33';

// Number of sold items in Shop 2

IWGrid.Cell[ 2, 6 ].Text := '3';

// Sales in Shop 3

IWGrid.Cell[ 3, 2 ].Text := '9';

IWGrid.Cell[ 3, 4 ].Text := '7';

// Number of sold items in Shop 3

IWGrid.Cell[ 3, 6 ].Text := '2';

// Sales in Shop 4

IWGrid.Cell[ 4, 2 ].Text := '12';

IWGrid.Cell[ 4, 5 ].Text := '21';

// Number of sold items in Shop 4

IWGrid.Cell[ 4, 6 ].Text := '2';

// Sales in Shop 5

IWGrid.Cell[ 5, 2 ].Text := '21';

IWGrid.Cell[ 5, 3 ].Text := '13';

IWGrid.Cell[ 5, 5 ].Text := '31';

// Number of sold items in Shop 5

IWGrid.Cell[ 5, 6 ].Text := '3';

// Align right side all numerical values

for aRow := 1 to IWGrid.RowCount - 1 do

for aCol := 1 to IWGrid.ColumnCount - 1 do

IWGrid.Cell[ aRow, aCol ].Alignment := taRightJustify;

// The List variable is used to store proposals when comparing two shops

List := TStringList.Create;

try

// THE RECTANGLE METHOD

// A cycle by the first shop (rows of the Grid)

for aRow := 1 to IWGrid.RowCount - 2 do

begin

// A cycle by the second shop

for aSecondShop := aRow + 1 to IWGrid.RowCount - 1 do

begin

Number_of_matching_items := 0;

// Calculating the similarity criterion -> calculating the minimum of sold items

Similarity_criterion := min( StrToIntdef( IWGrid.Cell[ aRow, IWGrid.ColumnCount - 1 ].Text, 0 ),

StrToIntdef( IWGrid.Cell[ aSecondShop, IWGrid.ColumnCount - 1 ].text, 0 ) );

// Calculating the similarity criterion -> 50% of the minimum value

Similarity_criterion := Round( Similarity_criterion / 2 );

// A cycle by items within the compared two shops

for aCol := 1 to IWGrid.ColumnCount - 2 do

begin

// get the value of one cell for the first shop

Cell_1 := StrToIntDef( IWGrid.Cell[ aRow, aCol ].Text, 0 );

// get the value of the corresponding cell for the second shop

Cell_2 := StrToIntDef( IWGrid.Cell[ aSecondShop, aCol ].Text, 0 );

// If values in both cells are found, we increase the counter Number_of_matching_items

if ( Cell_1 > 0 ) and ( Cell_2 > 0 ) then

Inc( Number_of_matching_items );

// If there is only cell value in the first shops

if ( Cell_1 > 0 ) and ( Cell_2 = 0 ) then

begin

// Generate a proposal

Proposal := IWGrid.Cell[ aSecondShop, 0 ].text + ' may offer ' + IWGrid.Cell[ 0, aCol ].text;

// Add the proposal to the List variable if it is not already proposed

if ( IWMemo.Lines.IndexOf( Proposal ) = -1 ) and ( List.IndexOf( Proposal ) = -1 ) then

List.Add( Proposal );

end;

// If there is only value in the second shop

if ( Cell_1 = 0 ) and ( Cell_2 > 0 ) then

begin

// Generate a proposal

Proposal := IWGrid.Cell[ aRow, 0 ].text + ' may offer ' + IWGrid.Cell[ 0, aCol ].text;

// Add the proposal to the List variable if it is not already proposed

if ( IWMemo.Lines.IndexOf( Proposal ) = -1 ) and ( List.IndexOf( Proposal ) = -1 ) then

List.Add( Proposal );

end;

end; // A cycle by items within the compared two shops

// The core of the rectangle method

// If the number of matching items is greater than the similarity criterion

if ( Number_of_matching_items > Similarity_criterion ) then

begin // Make a proposal

// Moving the proposals from the List variable to the Memo on the web form

IWMemo.Lines.AddStrings( List );

// Clear proposals (the List variable) after adding them to the Memo

List.Clear;

end;

end; // A cycle by items within the second shop

end; // A cycle by the first shop (rows of the Grid)

finally

List.Free;

end;

end; // Initializing the Grid

References

1. Kemper C., Breuer C., What Factors Determine the Fans’ Willingness to Pay for Bundesliga Tickets? An Analysis of Ticket Sales in the Secondary Market Using Data from ebay.de, Sport Marketing Quarterly, 2015, 24, p. 142-158.

2. Wincott D., An alternative sales analysis approach for vacant land valuation, The Apprisal Journal, 2012, p. 310-318.

3. Ioana B., Claudia S., Ioan B., Using dashboards in business analysis, Annals of the University of Oradea, Economic Science Series, 2014, p. 851-856.

4. Stoyanova M. Architecture of a gamification system. Economics and Computer Science, 2015, 2, p. 18-34. Available at: http://eknigibg.net/spisanie-br2-2015.pdf (accessed 28/10/2016).

5. Kruger M., Johnson A., A comparison of distributional assumptions for rapid screening of retail sales data, International Journal of Business, Marketing and Decision Sciences, 5(1), 2012, p. 26-36.

6. Mullen C., Berrill J., Minoritynationals: an empirical analysis of the concentration of geographic sales expansion in MNCs, Multinational Business Review, 2015, 23 (4), p. 277-305.

7. Vasilev J., Data mining of transactional data for sales of dairy products, Theoretical and Applied Economics. 18(12), 2014, p. 3-12.

Available at: http://store.ectap.ro/articole/1041.pdf (accessed 28/10/2016).

8. Embarcadero Technologies, Available at: www.embarcadero.com (accessed 28/10/2016).

Number of sold items