33rd International Conference on Organizational Science Development
March 19th – 21st 2014, Portorož, Slovenia
Using Spreadsheet as a Tool in Organizational Analysis
Jovan Krivokapić
Faculty of Organizational Sciences, University of Belgrade, Serbia
Ivan Todorović
Faculty of Organizational Sciences, University of Belgrade, Serbia
Stefan Komazec
Faculty of Organizational Sciences, University of Belgrade, Serbia
Miha Marič
Faculty of Organizational Sciences, University of Maribor, Slovenia
Miloš Jevtić
Faculty of Organizational Sciences, University of Belgrade, Serbia
Abstract
In business consulting it is very difficult to establish an ideal methodology and use it in every scenario. For this reason, business consultants are constantly developing new approaches for solving problems. Usually, new method does not include development of utility or software, but usage of existing tools in new situations, under different circumstances. This paper will describe how spreadsheets can be applied as a tool in the process of organizational analysis. Consulting team from the Faculty of Organizational Sciences at the University of Belgrade was engaged on the project of restructuring public enterprises in the City of Belgrade from 2009 until 2012, during which the methodology presented in this article was developed. Spreadsheets proved to be efficient and reliable utility for data processing, especially in cases when unsystematic data needed to be formatted and analyzed.
Keywords: organizational model, organizational structure, analysis tool, public sector, public enterprise, Porter’s value chain, Mintzberg’s model, spreadsheet, Excel
1 Introduction
Spreadsheets today present an unavoidable tool in everyday business. Their utilization automatized and facilitated wide set of data processing operations, leading towards increased preciseness and simplicity at the same time (Ragsdale, 2010). The term “spreadsheet” comes from the English language, and it presents the combination of two words, “spread” and “sheet”. First spreadsheets were used by accountants, in order to spread all the data about income, expenditures, assets, sources, liabilities etc. on one sheet, for the purpose of matrix analysis, by rows and columns (Power, 2004). Later the exploitation of spreadsheets widened to other business functions, but the name of the tool remained the same. Spreadsheets especially gained in importance in the end of the 1970s and beginning of the 1980s, while today they mostly consider an electronic tabular view of different data types and forms. They enable rapid adjustments of obtaining model and have wide usage in research problems (Grossman, 2002).
Spreadsheets can be used for different methods of numeric and textual cell data processing and for generating various graphical reports and diagrams based on data from the matrix. They can also be linked and organized as stable system for data storage and processing, while more complicated operations may be automatized using macros (Kostić, 2010). Besides that, documenting the spreadsheet utilization provides additional benefits, in terms of specifying their application, saving their content, explaining the principles of their usage and educating other end users of this tool (Pryor, 2006). However, it should be pointed out that despite all the advantages that were mentioned, it is very difficult to develop the best practice of spreadsheet utilization, since it would limit the applicability of this tool and damaged its flexibility and further development (Vemula, Ball, Thorne, 2006).
Generally, spreadsheets can be categorized as following (Kostić, 2010):
· spreadsheets used in application software
· spreadsheets used for financial risk management
· spreadsheets which provide reports for top management
· spreadsheets that follow business process infrastructure
· spreadsheets used for complex analytics, when used in scientific or engineering analysis, which will be covered in this paper
One of the most common spreadsheets today is Microsoft Excel, a tool within Microsoft Office software package. It possesses all basic characteristics of spreadsheet and contains a two-dimensional network of cells which enables processing of various data types (Harvey, 2006). Due to its simple usage and numerous possibilities, it quickly became extremely popular tool, so it is used for solving wide set of problems.
This article will present the benefits of Excel application during analysis of public enterprises in the City of Belgrade. Consulting team from Faculty of Organizational Sciences at the University of Belgrade was engaged on this project in the period from 2009 to 2012. The project consisted of several phases and included 28 public companies from Belgrade, with more than 20.000 employees in total.
2 Methodology
Long-term goal of the project was the development of public sector organization in Belgrade. The hypothesis was that public enterprises could significantly increase business performance by dealing with internal organizational issues, so the task of consulting team was to determine possible organizational improvements. However, the analyzed companies were rather divert, with various activities and different organizational models, so there was a need for the tool that would enable the comparison among companies. For this purpose, the consulting team developed new organizational model as a combination of two recognized and widely accepted concepts in organizational design, Mintzberg’s organizational model and Porter’s value chain (Krivokapić, Čudanov, 2010).
Henry Mintzberg (1983) proposed a model that identifies following five main parts in organization:
1. Strategic Apex - includes general manager, with top management and staff that support them;
2. Middle Line - includes middle line management and all officials subordinated to the strategic apex, but superior to the operating core;
3. Operating Core - consists of the employees who perform operational activities related to providing inputs into the production process/service delivery, the very transformation of inputs into outputs and delivery of output to the user;
4. Technostructure - consists of the employees who provide direct technical assistance in planning, analysis and control to core businesses;
5. Support Staff - includes employees who indirectly help by assisting in jobs that do not belong to the core business of the company.
On the other hand, Michael Porter (1985) divides all the activities in organization to primary and support. Primary activities include:
· Inbound Logistics,
· Operations,
· Outbound Logistics,
· Marketing and Sales,
· Service;
While the set of support activities consists of:
· Firm Infrastructure,
· Human Resource Management,
· Technology Development,
· Procurement.
Combinations of relevant positions from Mintzberg’s model and Porter’s value chain generated new organizational model, which could be applied in all 28 companies. Such model contained a list of 91 standardized positions, without the operations that remained unique for each public enterprise. Standardization of core activities would have caused loss of data, due to their significance and specifics. This list was named “CodeBook”. The idea was to assign one standardized position from the CodeBook to each existing employee, in order to facilitate comparison of different organizations. Association of actual and standardized positions was based on job descriptions from official company documents.
3 Spreadsheet application during analysis
The CodeBook was developed using MS Excel, and it consisted of seven columns with different organizational parameters of new model for each standardized position, as presented on Image 1.
Image 1: [ CodeBook with standardized positions ]
Databases of each public enterprise were also imported to unique MS Excel spreadsheet with standard columns, called “Employees Database”. Each row was assigned to one employee and contained its personal data, as well as organizational data about its actual position in company structure. This spreadsheet is shown on Image 2.
Image 2: [ Structure of Employees Database ]
Consulting team had previously gathered necessary data about the employees from all 28 public enterprises. Such data were essential for HR analysis by different criteria, such as age, gender, education, internship etc. Company data were also necessary for analyzing performance and earnings. The key problem in this phase of the project was the lack of certain data and the gap between delivered data, due to different information systems used in the companies. For this reason, consulting team had to create integrated database with standardized data form, and MS Excel proved to be very useful tool for such task. It enabled uncomplicated adjustment of received data to defined form. Besides that, missing data were easily subjoined after they had been delivered, by using formulas in MS Excel and unique identifiers, such as personal number of an employee or code of its position, for example.
After generation of Employees Database and determent of standardized position for every employee in 28 enterprises, it was very easy to assign other parameters of new organizational model from the CodeBook to the relevant row in the Employees Database, by using MS Excel formulas. Spreadsheet application significantly accelerated the linkage, since complete process was automatized with the help of well projected CodeBook and MS Excel features, while at the same time the possibility of error was minimized.
New organizational model, available in spreadsheet, reduced the gap between the companies in term of data form and enabled consulting team to perform several types of analysis in different companies using the same methodology.
The goal of structural analysis was to determine the structure of employees and division of labor in each public enterprise. Functional model of organizational structure was identified in most of the companies, which is characteristic for public entities in developing countries (Todorović, Čudanov, Komazec, Krivokapić, 2013). This issue was important for inquiring into the specifics and characteristics of analyzed group of organizations. Besides this, it was essential to recognize primary (core) activities in each company, to separate them from support activities, and to determine the capability of enrolled employees for performing them successfully. Compatibility between the tasks and available human capacity needed to be questioned for all functions within organization, in term of qualifications and abilities. This part of project also included analysis of span of control and fluctuation of employees.
It is considered that there are many ways to project the spreadsheet, the same way as there is more than one model that is “good enough” for solving one problem (Grossman, 2008). Since consulting team decided to use MS Excel spreadsheet as possible solution for identified problems, structural analysis easily showed similarities and gaps between companies and positions in them. Pivot tables in MS Excel were used for generating clear reports with key results. Considering the fact that the database contained the information about more than 20.000 employees, it is clear that such analysis would be rather difficult to perform without using spreadsheets.
Next step was to calculate the performance of each employee. Analysis of performance was first performed on company level, then on the level of organizational unit, and finally, on the level of standardized position, in order to enable comparison among companies. The results showed the potential for rationalization for standardized positions with reserves in human capacities, as well as the overload and lack of human resources for certain standardized position.
Complete analysis was performed using the spreadsheets and the data from Employees Database that had previously been prepared. Performance indicators were defined for each standardized position, and the values of those indicators received from the companies were linked to the employees (rows) from the database. As a result, it was possible to calculate performance and percentage of capacity usage for every activity that is performed in Belgrade public sector. This tool also provided graphical view of Porter’s value chain, with calculated performance for each activity, as presented on Image 3.
Image 3: [ Analysis of performance for activities from Porter’s value chain ]
Considering the specifics of each enterprise, this methodology had limited application in comparison of primary activities among companies, but the spreadsheet was very useful tool for comparing support activities in different companies. Having in mind that support activities are standard, it was rather easy to determine the gaps in performance between analyzed organizations and positions, by using the parameters of proposed organizational model in the spreadsheets.
Graphical view, together with performance indicators, was also provided for Mintzberg’s model of organization, as well as numerous graphs, diagrams, pies etc.
On the other hand, by using adequate filters and pivot tables in MS Excel, the performance could also be measured for individual companies, organizational units, standardized positions, parts of organization defined by Mintzberg, activities within single company, or any other criteria, as presented on Image 4. Spreadsheet application enabled parallel analysis of data that had seemed to be incomparable. This provided city authorities with more precise and detailed reports about human capacity usage in public enterprises.
Image 4: [ Analysis of performance for standardized positions ]
In the final phase, consulting team analyzed the earnings systems in these enterprises, in order to introduce a unique payment system on the level of the observed group. Previously described organizational model enabled the determination of the relative ratio between different positions. That was the first step towards determining the salary scales and the classification of specific positions in each enterprise, in order to obtain consistency and coherence among different public company from Belgrade.
4 Conclusion
Results of these analyses were significant input for the city authorities in making many strategic decisions. Due to the economic crisis, it was necessary to find the direction for enterprises restructuring, and that trend did not pass the public sector, neither. In consultation with the team from the Faculty of Organizational Sciences at the University of Belgrade and other experts from field of restructuring, the city authorities have estimated that further stages of this project should refer to full or partial centralization of certain support activities that are performed in several of the observed enterprises (Todorović, Komazec, Čudanov, 2014).
It is important to underline the fact that, due to the implementation of this project, city authorities in Belgrade obtained analysis of organizational aspects in observed enterprises, which was of high quality and thus enabled internal benchmarking in this system. It is evident that the entire analysis was performed through the application of spreadsheets. Each row in the database has been crossed with about thirty columns, with the possibility of further expansion in case of new requirements. This means that by the end of the project the Employees Database had over 600.000 filled cells, which could first provide a proper angle for looking at the organizational problems of public sector, and then indicate the course of action that would enable further development of analyzed enterprises. Despite such large amount of data, its processing was very fast, and the results were presented with different levels of abstraction, depending on the specific needs.