Maintaining flexible foundations: A few simple data organizational tips

Growing up I never expected to become an accountant and I definitely never expected to fall in love with spreadsheets, databases or any form of data organization. I recall doing everything possible in a high school Microsoft Office class to avoid reading or actually learning. I breezed through the excercises copying the steps, without internalizing the message.

Still, here I am however many years later, writing a post to illustrate a few basic tips for organizing data. I find that this is one of the greatest skills that I have broght to my fellowship. We are confronted with a large quanity of information and it is imperative that we organize it correctly and rapidly. If we don’t, not only will the project of my MFI stumble, the prices of the much needed credit that my MFI provides will increase.

I won’t bore you with the specific functions from spreadsheet programs. Instead I want to share a few tips that will help to avoid the creation of stagnant data. Data sets need to be sortable, searchable and open for manipulation in order to be useful. In this manner it can form a flexible foundation for an organization allowing it to grow and change over time without having to fully rebuild. I hope these basic ideas and the related examples from my short career in the financial world can help you avoid some of the basic pitfalls that lead to stagnation of data. Please read on:

#1: Always, always, build your systems so that they are related.

This is to say, build your systems so that there is at least one portion of the data that is repeated in all appropriate systems. Doing so greatly assist manipulating, comparing and using data later on. Systems may include accounting systems, marketing records, calendars, legal records, applications, photos, studies, etc. Note: I highly recommend that systems not use names as the relating factor. Names are too likely to exhibit repeats and too easy to write in different formats. It is far better to convert names of products, clients and other qualifiers into a unique code.

Real Life Issue: The company wanted to obtain satisfaction information from individual clients. A program was initiated to complete individual check-ins using a short questionnaire and a number of contracted interviewers. When the questionnaires were returned, they were divided by zone (because interviewers went to specific zones) and titled by name. The interviewers had bad handwriting and a habit of writing the names in a variety of forms (first name followed by last name, last name followed by first name, last name and middle name only, etc). The database system contained many individuals with similar names, (eg. John Braxton Smith, John Smith Braxton and Braxton Jason Smith). It was therefore be very difficult to match the client in the system with the questionnaire. At times staff had to search the client list for Smith and scan through all the entries for Smith within the zone, and finally insure that the individual is involved in the right business.

The back end work of matching questionnaires to borrowers would have been far simpler if the interviewers had been required to write the client name and the identification number on the questionnaire. Although this fix would still yield imperfections, it would narrow the margin for error substantially. There would be two quickly assessable items for comparison.

#2: Avoid using spreadsheet programs such as Excel like a page layout program.

The practice of merging cells so that the title line looks pretty, but can be quite damaging for the data and cause significant delays in manipulating the data later. Instead, always maintain a separate database which includes qualifiers in a separate column right along with the data. For example, if you need to separate data between “projects” and “completed projects”, add a column, leave it blank if it is still a project. Once completed enter the word completed in the column. With this database you will be able to use other spreadsheet tools, such as order, filter or a table to view the data in organized formats. The information can be transferred into another to provide a more legible and report like format as needed. Preparing such a report will require far less effort than battling with a segregated system continually.

Real Life Issue: An employee was in charge of managing the follow up and recovery of insurance proceeds when clients passed away. In managing the information, the staff member divided the information between several different types, including “Deceased,” “Requested from Insurance,” “Insurance requested additional information,” and “Paid,” and “Denied.” Each section was stacked on top of each other so that it looked pretty when printed. In order to manage the data in this manner, the staff member would manually copy and paste the client’s names between the different sections. The “deceased” section included all names was sent to the insurance company on a regular basis to inform them of the deceased clients and the requests they should expect. Unfortunately, one day a staff member copied information about a newly passed away client into requested section, without placing it in the deceased section. By doing this, he later assumed that he had already sent the request and the insurance company never saw the name in the deceased list, thus they never notified the staff member of the lack of a request. This caused a number of months of delay.

Had all the data been maintained in a single database with a column for the status, the data from the database could have been ordered by that column and sent to the insurance company in full. The insurance company would have received a list that included the name of the deceased client and would have been able to advise the staff member that they had yet to receive a request for payment.

#3: Learn how to convert text into columns.

A major problem in many office is the over use of word processing programs to record data or the export of text documents from database systems. These reports are easy on the eye, but difficult to otherwise use. Luckily there is a fix that is included in most spreadsheet programs. It is called “Separate text into columns,” and is usually located under the drop down menu, “Tools”. To use it, select the column in which the text is pasted, and then select the option “Separate text into columns.” You will have the option to divide the column based on a fixed width or based on a specific value. For most text documents, you will want to use divide based on fixed width. For other exports, you may find that separating the data based on spaces, tabs or any other specific character will be useful. Once you click on fixed width you will be able to apply separations for each pseudo-column that existed in the text document. Once the data is in columns you can order it, filter it, or even complete any number of other spreadsheet function.

Real Life Issue: The system exported a text document containing each individual sale during the month. The data included a pseudo-columns in the text document, one of which indicated the product name. The staff member would save this text document and then scan through it deleting all the transactions that did not related to the specific product he was looking for. He would then create summation by hand and complete other actions. By transferring the data into a spreadsheet and dividing the text into columns we were able to have a specific column that indicated the product involved in the transaction. This saved a significant amount of time and reduced the margin for error because we would then simply filter or order the data based on the column that indicated the product type. (Note: converting text into columns is almost always a work around, not a true fix. In an ideal world the programmers of the database would create a report that only included the information the staff member wanted.)

#4: Think about the future when creating systems.

Think about the next steps, think about how the data may be used and think about what other systems may dovetail with the one you are completing. Further, above all, contemplate scale. Systems should be created so that they will provide sufficient space for growth. There are few things more painful than creating an entire system, entering all the data and then finding out a year later that you really should have done one minor thing different. With luck it will only cost a few hours of assistant time to add additional data, without luck it may be a permanent limitation of the system.

Real Life Example: First see examples from items #1, #2 and #3 above. It is clear that a few small adjustments in the planning phase would have eliminated a significant amount of back end work and potential error.

The most extreme examples I have relate to the maintenance of hard copies. The options for combining, comparing and organizing physical information are terribly limited. No matter how good the system, a physical system always requires significant labor. In one company all of their hard copy receipts and documents from transactions were organized by day of transaction. This system was sufficient when that company was small because searching for an individual transaction only required searching through a few documents within the specific day, however as the business grew so did the number of documents in each day.

Unfortunately, for reasons of verification of new bills or agreements, it was necessary to look at the old transactions on a regular basis. It became a very arduous task to find a specific document. The system had to be changed to include additional organizational properties. There were many options, but the company settled on the following: Division of documents by function and then by client, reducing the number of documents included in any individual day to a manageable number. This system exhibited higher costs to organization than the prior system and would be potentially limiting if the number of clients grew substantially. However it met the current and future needs of the company because they didn’t expect the number of clients or the number of transactions per client to increase dramatically over the life of the business.

Casey Unrein, KF 12, joined the Kiva Fellows program in Sept. 2010. Prior to becoming a Fellow, Casey worked with a fiduciary management company in Seattle, WA, providing financial management services for minors, the elderly and the disabled. Casey completed a bachelors in Economics and Education at Occidental College. He expects to become a certified public accountant by then end of 2010. Casey is currently a Fellow with EDPYME Alternativa in Chiclayo, Peru. Please support Kiva.org and EDPYME Alternativa by joining lending teams and expanding the community. You can join Friends of EDPYME Alternativa by clicking on the link.