Universal way to merge and analyze a reports

By: Roman Ternovsky

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 collect 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.
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 (http://www.merge-excel.com). 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.

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.

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.

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.

Article Directory: http://www.articletrunk.com

| More

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

Please Rate this Article


Not yet Rated

Click the XML Icon Above to Receive Software Articles Articles Via RSS!

Powered by Article Dashboard