Roman Ternovsky, Ph.D., Associate Professor of Surgut State University

Nowadays for almost every manager who supervises more than 5 people the question of getting the actual information, proceeding it and sending it back to the employees is still actual. What do the employees do? What resources are necessary for the result achievement? What are the detailed conclusions of the activities? Those and billions other question appear during the working process.

This article will be interesting for those companies who don’t have “huge” information system. But not for those only. The problem is that every big system has so called “reacting period”. Also the resources used for the improvement of big information system could be extremely wide and inadequate to the task given.

As an example say you want to give a trip to the sea as a bonus to your successful employees. Let’s imagine that you already have informational systems CRM and ERP. However we have a very specific task and none of the modules of information system provides an opportunity to collect recommendations and motivational explanation for this bonus from all the managers.
So what are we supposed to do? Improve the system? But it is going to take about half an year and cost around 20 000 dollars. And it also might be useless in the future. Ask the managers to send the information over? Than it is going to be hard to proceed and analyze. So what should one do?

I offer you one of the approaches that can be considered relevantly universal.

For solving the issues above you should answer 3 questions:

1. How to unite information from managers?
2. How to analyze the information?
3. How to inform managers about the decision made?

Collecting and consolidating the information.
The most convenient easy and understandable way of information collection is spreadsheet. For further data analyses one should make a template with such columns that we are going to analyze.

As an example let’s make a table.

• Column B – “Manager”- the manager who prepared the table. In this column we put the information about managers in.
• Column C – “Employee” – contains the information on employees that are listed by manager for the trip.
• Column D – “Department” – contains the information about the department where the employee listed works.
• Column E – “Sales if Employees USD”- contains the information on how much sales did the employee accomplished
• Column F – “Sum needed UDS” – contains the information on which sum of money is necessary for providing employee with the trip voucher.
• Column G – “Sum approved USD” – contains the information on the sum approved.

After completing the table one should send copies to all the managers in order to fill in the information. Managers have to fill in columns from B to F. After the columns are followed up managers are supposed to send it back. There could be hundreds of files.

Let's give an example how the filled spreadsheets can look:

Before the information could be analyzed it should be consolidates (meaning combined into one data base). The most suitable software for this purpose is MergeExcel. In fact in order to combine all the MS Excel file into MergeExcel one should only fill in the import parameters (“explain” to the software which columns it is supposed to analyze) and press the Import button. Software will do all the rest.

Configuration of import parameters in MergeExcel

Information Analyses.
After the information is merged the analyzing process is to be started. Pivot table could be used for those purposes. Pivot table is a table which summarizes the datum and provides an opportunity to analyze the information according to various criteria. There are a lot of various books about such table and I don’t want to quote it here. But there is one particular topic I would like to talk about here.
Maybe the most popular summary tables are MS Excel, but they have one strong disadvantage - they don’t give an opportunity to edit source data. Just imagine - you analyze expenses for employee's benefits (F column in spreadsheets). When customizing information you realize that expenses on one employee are much bigger than on the other one. Most probably one of the managers made a mistake and put one additional zero to the sum. But what are we supposed to do with this knowledge? For solving this problem you have to check all the raw data and undo the whole table. This is very inconvenient and takes a lot of time. Thankfully there exist pivot tables in which you can edit source data. I personally use MergeExcel. Using the analytical resources of this software you can easily find any information.

Let’s go back to our previous example. First of all we should calculate what is the total sum needed to pay bonuses to all our employees and their number. For this purpose we should build up a table as shown in a picture below

As one can see in the picture the sum in column “Sum needed, USD” is 69.000 USD and there are 10 employees nominated to go on a trip.

Now one should see how much are the expenses on each of our managers. Let’s put in the field “Manager” into left vertical column of summary table.

As we can see from the table John Smiths offers to spend 49.000 USD on his employees, and Valentino Loretty only 20.000 USD, and the number of employees supervised by both managers is the same – 5 people. Why the sum does differ so much? Let’s have a double click on the cell which states 49.000 USD. In the window appeared one should make a descending sorting in the column “Sum needed USD”. 

So what do we have here? John Smith offers to spend 35.000 USD on John Cubic but 3 500 only on all the rest. It is pretty obvious that we have to deal with a technical mistake. The Manager just put one extra zero while following up the table. We can just easily correct this mistake and click OK.

The other way to sort out all the mistakes is sorting data in the table itself. We can just replace the field “manager” with the field “employee. Then one can just sort out the data as shown in the picture below

As you can see the first and the last sums in the column “Sum Needed, USD” are relevantly similar so that means that we corrected the obvious mistakes.

Now let’s analyze what managers are offering to give bonuses for. Let’s calculate the ration between price for the trip and total amount of employee’s sales. For that purpose we should set the table as shown below. The “Is it good" field is countable and is equal to quotient between “sum needed” and “amount of prescribers” * 10 000.

Ex fact we are about to give bonus to Peter Muller for nothing. So we have to contact the manager filling in the table in order to ask him what is going on here.

Let’s analyze how the expenses were distributed between employees from different departments.

This is how we figure out that we about to spend 20.000 USD on employees of retail department and 17.500 USD on employees of wholesale department.

As a conclusion I can say that the number of various analyses depends only on your imagination and the structure of source information.

One should also fill in the field “Sum Approved, USD” with only those sum that one considers to be reasonable. In the example given I fill it in with the sums from the field “Sum Needed, USD”, except for Peter Muller.

Export of the data into source files.
For managers to get the information on which offers were approved I export the results of my work back to source files. It is easy in MergeExcel – just press the Export button. After that files go back to senders.


This is how one can analyze all the necessary expenses and send it back to sender the easiest and quickest way.


But there is a problem, and it is connected as usual with the human factor. The thing is that it is quite difficult to make 100 people follow up the table without mistakes. Someone surely will type “100 dollars” instead of “100”. Someone will type his name in 10 different ways. Of course there do exist various administrative ways to control this but they all are waste of time.
Thankfully there is one way to solve this issue. One should restrict the number of data variations managers can fill in the blanks. This is conducted by special operation called “Validation” (you can find it in menu “Data”). For example to the cells which can only conduct numbers “Whole number” restriction could be stated. If some of the cells are not to be change than one can just put the “Lock” restriction.

Using of validation mechanism of MS Excel

The sphere of usage of this way of information management is barely restricted.
Here I am going to list some examples.

• Analyses of working plans and reporting. The Excel file should contain information about
• Name
• Date
• Type of activity
• Working time spent on this activity
• Analyses of representative expenses. Should contain information about:
• Name
• Date
• Type of expenses
• Sum
• Survey. 2 columns needed:
• Questions
• Answers. It would be useful to choose variants for this column from a number of set variants.
• Menu plan. Employees should choose from a number of set variants. (fish/meat/ chicken)
• Information on resale.
• Name of reseller
• Date
• Type of production sold

Those examples are given only to illustrate the fact that variant of information procedure given is almost universal.

Roman Ternovsky, r.ternovsky@gmail.com

© 2008 Guz Software. All Rights Reserved.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.