The technology of data analysis is offered for finding competitiveness of the enterprises. To determine the competitiveness of enterprises, a methodology based on the theory of effective competition is used. This method is proposed by economists Joseph Schumpeter and Friedrich Hayek and is one of the most widely used methods. According to this theory, the most competitive enterprises are those, where there is the best organized work of all units and services as a system. An example of using the Microsoft Excel Power Pivot technology to download large volumes of data, their processing and analysis of unstructured information and its organization into an ordered database is given. Examples of methods for working with non-structured data arrays are given. Scientific directions for the analysis of cumbersome data are determined. The principles of work of unstructured data in distributed information systems are formulated. The work of the Microsoft Office Excel spreadsheet process is presented to achieve goals. Its properties and highlights of advantages and disadvantages of finding and comparing the competitiveness of enterprises were analyzed. An analysis of the ease of connection to the database, the data transferfrom the model, the formation of tables, conducting complex data calculations, updating information in tables, grouping data and outputting totals is made. The experiments conducted have proven the feasibility of using the Microsoft Office Excel spreadsheet to process large volumes of data presented in text files, modeling and grouping data with poor quality information representation. This work analyzes the data structure in order to determine the state of the enterprises in the Ukrainian export market and develop algorithms for the research of competitiveness. Further research may consist in the widespread use of information systems that would provide an easy-to-understand and effective set of technologies to determine the company's competitiveness and make recommendations for its improvement.
In recent years, the promising area of scientific research is the application of IT in
various fields: economics, management, education, medicine, etc. To date, most of the
novelties in the field of information technology are used to solve managerial
problems. Information technologies are being improved in the following key areas: a
significant increase in the efficiency of technology; simplification access and expanding
the potential of software tools and widespread use of "open technologies"; creating a
user friendly interface; significant improvement in the quality and function of
information technology and the reduction of their value [
For example, the use of spreadsheets is particularly useful in financial control of
enterprises, as well as in analysing of their work. In order for the company to grow
up, it is necessary to watch after its position on the world stage. For this, it is
necessary to regularly evaluate its competitiveness. By doing this, the owner of the
enterprise has the opportunity to assess the strengths and weaknesses of the enterprise, to
identify its hidden potential and, accordingly, to maximize its working strategy [
The subject of the study is methods and tools for downloading, transmitting, modeling and processing data in Microsoft Excel 2013.
The purpose of the work is to investigate the feasibility of using Microsoft Excel 2013 to build an OLAP cube, to determine the competitiveness of the enterprise.
The research objective is to provide IT the development of the Microsoft Office Excel
2013 table processor information system for processing large volumes of unstructured
data to examine the competitiveness of enterprises. To achieve this, the following
tasks must be solved [
Today, information systems are widely evolving in different areas and simplify their
lives. One of the examples is the economics, an integral part of which is the new
technology that helps to improve and accelerate its development. The work of foreign
scientists [
After analyzing it was found that there is no single specific and common methodology and technology for calculating the competitiveness of enterprises. Therefore, working with a detailed description of the development of OLAP - cube for determining the competitiveness of enterprises is relevant [11, 14].
Probably everyone who connects their field of activities with computers knows what databases are. But unlike the usual relational DBMS, the concept of OLAP is not so well known. So let's dash what it represents On-Line Analytical Processing. This is a technology for operational and analytical (online) data processing. The basic principle of OLAP is the multidimensional representation of the data. Some measurements are made highlighting the factors that influence the activity of the multidimensional model, and receive a hypercube, which is filled with performance indicators. Data can be real or predicted based on data accumulated over time. Indicators can be collected from different sources, cleared, merged and compiled into a relational repository. Moreover, they are available for analysis.
In 1993, E.F. Codd have formulated OLAP criteria. He identified 12 rules that the OLAP class software product should meet [12]: 1. Multidimensional Conceptual View (Multi-Dimensional Conceptual View). Analysts should be able to perform such operations as "longitudinal and longitudinal analysis", rotation and placement of data. 2. Transparency 3. Accessibility. OLAP must perform all the transformations that are necessary to provide a holistic view of the user to the information. 4. Sustainable Performance (Consistent Reporting Performance). 5. Client-server architecture (Client-Server Architecture). 6. Generic Dimensionality. Baza data structure, report formats should not be based on one dimension. 7. Dynamic Sparse Matrix Handling (Dynamic Sparse Matrix Handling). 8. Multi-User Support. 9. Unrestricted Cross-Dimensional Operations. Work with data from any number of measurements can not be prohibited. 10. Intuitive Data Manipulation. Manipulations inherent in the structure of the hierarchy must be executed in a convenient interface. 11. Flexible Reporting (Flexible Reporting). 12. An unlimited number of measurements and levels of aggregation (Ed Dimensions and Aggregation Levels).
Subsequently, these requirements were transformed into the FASMI test. This test
also defines the requirements for OLAP products. The decipherment of this
abbreviation is [
Intelligent data analysis allows you to automatically generate hypotheses based on a
large amount of accumulated data, which can be verified by other analysis tools, such
as OLAP. On-Line Analytical Processing (OLAP) is a promising service in terms of
the subject field, which provides a flexible view of the information, obtaining an
arbitrary cut of data and performing analytical operations of detail, convolution,
throughdistribution, comparison of the document circulation of communicative structures in
time. OLAP does not in fact mean specific software products, but multidimensional
data analysis technology. The basis of OLAP technology lies in the conception of the
hypercube of the data model. In this regard, depending on the answers to the
questions on whether there is a hypercube as a separate physical structure, or it is only a
virtual data model, there are two main types of analytical data processing: MOLAP
and ROLAP [
The data in the OLAP model are presented as measures, each of which is defined in a certain set of dimensions.
Working with OLAP - cube in the Microsoft Office Excel spreadsheet using the Power add-in that can be used to perform powerful data analysis and creation of complex data models. With Power Pivot, you can blur large volumes of data from a variety of sources, quickly analyze information and easily share information. In Excel and Power Pivot you can create a data model, a set of relations tables.
The data you work with Excel and Power Pivot is stored in an analytical database
in the Excel workbook, and a powerful local engine loads, performs queries, and
up
dates the data in this database. Because the data in Excel, they are immediately
available for PivotTables, PivotCharts, PowerView and other Excel functions that you use
to aggregate and interact with the data. Power Pivot supports files up to 2GB and
allows you to work with up to 4GB of data [
Our goal is to find the most competitive company with OLAP cube.
For this example, determining the company's competitiveness will use a method
based on TEC. According to this theory, the most competitive enterprises have the
best organized work of all units and services as a system. There are many factors that
affect the performance of each service. Evaluating the effectiveness of each unit
involves assessing the effectiveness of the use of resources provided to them. The
method defines the main economic indicators, which are divided into 4 groups [
Indicators of the financial position of products.
Indicators of sales of products.
Product Quality Indicators.
As indicators of the fourth group characterize the ability of the enterprise to meet the needs of consumers in accordance with its purpose, we do not take it into account, because our enterprises produce products of various purposes.
Consequently, KCO = 0,15EO + 0,29FP + 0,23EZ, where KCO is the coefficient of competitiveness of the organization; EO - the value of the criterion of the effectiveness of the organization's production activities; AF - value of the criterion of the financial position of the organization; EZ - value of the criterion of the effectiveness of the organization of sales and promotion of goods. All of these criteria are calculated as follows:
EO = 0,31V + 0,19F + 0,4Rp where B is the relative indicator of production costs per unit of output, which reflects the cost effectiveness of production. Rule of calculation of the indicator: = relative indicator of return on capital, characterizing the efficiency of the use fixed assets.
Rule of calculation the indicator: = product, characterizing the degree of profitability of products. Rule of calculation of the indicator:
= labor.
Rule
calculation bor productivity, reflects the degree of organization of production and use of ∗ 100; PP - relative indicator of laof the indicator: of ℎ FP = 0,29Ka + 0,2Kp + 0,15Ko, where Ka is the relative indicator of the autonomy of the organization, characterizes the enterprise's independence from external sources of financing. Rule of calculation of the indicator: ; Кp - the relative indicator of solvency of the organization, reflects the ability of the enterprise to fulfill its financial obligations and determines the probability of bankruptcy. Rule of calculation of the indicator: = ℎ
of ; F = = ; ; Pn - a relative indicator of profitability of a ; Ko - a relative indicator of turnover of .[11] working capital, analyzes the efficiency of the use of working capital. Rule of calculation of the indicator: = .
EZ = 0,37Rp + 0,29Kz + 0,2 IKm + 0,14Kp, where Rp - the relative indicator of profitability of sales, characterizes profitability of the enterprise on the market, correctness of the price setting. Rule of calculation of the indicator: = ∗ 100 ; Kz - a relative indicator of foraging in finished products, reflects the degree of forging in finished products. An increase in the indicator indicates a fall in demand. Rule of calculation of the indicator: = ; Km - a relative indicator of the loading of produc tion capacity, shows the business activity of the enterprise, the efficiency of the sales service. Rule of calculation of the indicator: =
After downloading all the data, the links between the tables are established. To do this, the data is displayed in the form of diagrams (Start → Presentation in the form of a diagram). The corresponding "Date" fields of the business tables and the "Date" table are merged (the table of substitution will be the "Date" table). This connection will serve as a breakdown of time data in the OLAP- cube. Also, the connection of the corresponding fields "Enterprise Code" of the business tables to the Enterprise table is created (the table of substitution will be the "Enterprise" table). This connection collects information by displaying the product categories in OLAP - cubes. Another important link is the connection of the corresponding tables of the enterprise with the export of goods (Fig. 2).
Fig. 2. Chart of links between tables The next step is to export data from tables on the corresponding sheets, for their processing (Fig. 3). After that data from the tables on the export of goods by enterprises on the corresponding sheets is exported for processing (Fig. 4). To the tables containing data about the export of goods, you need to add the column "Profit" = Amount of Exported _product × Price, where "Price" = Cost_product × Coefficient, where the Coefficient - the value of the margin on the product (different for each country).
To the tables that contain the company's work data following is attached: Column "Total Costs" = Taxes on Production + Expenses on Payroll.
Column "Costs of production" = Quantity_product_product × Cost_production.
Column "Capital" = Initial_Capital - Mandatory _costs - Costs_production + Profit + Tenders:
Since, with Power Pivot, you can also create calculated columns, so the columns "Production Costs" and "Compulsory Expenses" are created in it.
The next step will be to calculate the indicators for computing the competition for a given date range. A diagram of the relationship between components is shown (Fig. 5), which highlights three components that affect the value of the "Competitiveness" object. Each of these components represents the component of the instance level that implements individual objects.
Efficiency and organization of sales and promotion
of goods Relative rate of return
Relative utilization rate
Relative capacity utilization rate Competitiveness
Efficiency of production activity Relative rate of
return on assetsrelative
Relative cost Relative rate of return on goods
Financial position Relative measure of organization
autonomy Relative solvency ratio
Relative turnover
rate Relative indicator of labor productivity The "Competitiveness" column is created and is calculated according to the given formula:
=0,15*(0,31*[@[relative cost]]+0,19*[@[relative rate of return on assets]]+0,4*[@ [relative rate of return]]+0,1*[@[relative indicator of labor productivity]])+0,29*(0,29*[@[coefficient of autonomy]]+0,2*[@[solvency ratio]]+0,15*[@[turnover factor]])+0,23*(0,01*[@[profitability sale]]+0.29*[@[ utilization rate]]+0,02/[@[utilization rate of own products]]+0,14*[@[solvency ratio2]])
Further, an OLAP cube is constructed to compare production volumes between enterprises over a certain period of time.
There is a composite table that will contain two dimensions: time (as the study priorities are certain intervals) and the measurement of the products of production and their respective enterprises. At crossroads will be displayed the sum of the number of manufactured goods. Conditional formatting shows enterprises with the largest volume of production by product category. A timeline is also added according to the "Date" table, to display the data at the required intervals (Fig. 6).
For a better visual perception, a composite diagram is constructed (Fig. 7). The final version of the experiment is the construction of OLAP - a cube for comparing the competitiveness of enterprises over a certain period of time.
There is a composite table that will contain two dimensions: time (as the study priority is at certain intervals) and the measurement of the products of production and their respective enterprises. At crossroads the value of competitiveness will be displayed, and the value of the totals will reflect the average competitiveness. Conditional formatting shows the most competitive companies in their industry and the most competitive enterprise among all enterprises. A timeline is also added to the "Date" table to display the data at the required intervals (Fig. 8).
Fig. 8. Summary diagram "Competitiveness".
For a better visual perception, a composite diagram is constructed (Fig. 9). The given research has shown, that the leader-exporter in Ukraine in the field of production of butter from the enterprises of our choice is PP "Alma-Vita", for the production of honey - Ukrainian Honey Company; in the sugar production industry - OJSC "Radekhiv Sugar Plant". This calculation of the level of competitiveness accumulates all the important assessments in the enterprise, avoids the duplication of indicators, provides an opportunity to quickly find out the state of the enterprise in the sectoral market. The method used is convenient for use, for researches about the competitiveness of the enterprise, covers all the main activities of such organization. Also, this experiment shows us that sugar is the most competitive among the selected export goods, and OJSC "Radekhiv Sugar Refinery" is the largest exporter.
Today, the basis of increasing competitiveness is the use of a systematic approach that allows us to use a full set of strategic capabilities, to prioritize the development of certain departments of the enterprise in accordance with the goals set. With the help of the developed OLAP cube, you can easily compare the competitiveness of different enterprises from different industries, and determine the best in a particular industry, under certain conditions. 11. Using Microsoft Excel With OLAP Cubes [Online Resource] http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/olap/olap_excel/ola p_excel.htm 12. OLAP data analysis systems [Online Resource] - http://ukrbukva.net/988-Sistemyoperativnogo-analiza-dannyh-OLAP.html 13. Kaflevskaya, S., Ganza, T.: Competitiveness assessment of enterprises by the methodology of efficient competition theory [Online Resource] http://econjournal.vsau.org/files/pdfa/831.pdf 14. Shakhovska, N., Boyko, N., Zasoba, Y., Benova, E.: Big data processing technologies in distributed information systems. Procedia Computer Science, 10th International conference on emerging ubiquitous systems and pervasive networks (EUSPN-2019), 9th International conference on current and future trends of information and communication technologies in healthcare (ICTH-2019), Vol. 160, pp. 561–566, Lviv, Ukraine (2019)