| 3601 Richmond Road | Texarkana, TX 75503-0716 | Phone: (903) 793-5651 | Fax: (903) 794-5651 |
|
Spreadsheets for LawyersCHARLES A. MORGAN
What is a Spreadsheet? A spreadsheet program is nothing more than a replacement for the familiar accountant's columnar pad, pencil, and calculator. It is an electronic ledger with which you organize information into worksheets that consist of rows and columns. A row is a horizontal line of information generally identified by numbers on the left hand side of the worksheet which begin at 1 and continue consecutively to the end of the worksheet. A column is a vertical line of information that is identified by letters along the top of the spreadsheet. Each intersection of a row and column is called a cell. A worksheet is the file or document that you create with a spreadsheet. Just as you do with other computer applications in your office, you can create a worksheet and then save it later for editing or output. Most spreadsheets programs consist of four basic components: a worksheet, graphic features, a database, and macros. The worksheet enables you to work with and manipulate numbers and text by providing a "grid" of rows and columns. Numbers or text is entered into the intersections of rows and columns, cells, and once entered you can perform a variety of operations on the information, such as sorting or mathematical calculations. The graphic features permit an attorney or support staff to represent numerical data in the form of a chart or graph, and the best spreadsheet programs have a graphing capability that rivals the best graphics programs. The best programs offer line, area, pie, column, and bar graphs. Such graphics capability permits the quick production of trial exhibits that can be instantly altered to reflect testimony received during trial. The database feature of most spreadsheets allows you to organize data in the form of records and then use the information in the records to search for and select specific records based upon the criteria designated for the search. Macros work with spreadsheets as they do with word processors, that is, they automate mouse operations and multiple keystrokes. These electronic worksheets look very similar to the accountant's 11" by 14" paper counterpart that some of us may remember from accounting 101 in college, except that this electronic spreadsheet can be much, much larger. For example, Microsoft's Excel@, can have over 4 million cells in a worksheet that can be as large as 256 columns and 16,384 rows. Multiple worksheets can be "linked" or connected together so as to contain an almost infinite amount of information. What If Analysis In 1977 Dan Bricklin, a Harvard graduate student, developed the idea of an electronic ledger for business forecasting. This idea lead to the development of the VisiCalc (for visible calculator) program for the Apple II computer. It was this combination of the computer and this original spreadsheet program that made the personal computer a viable business machine, and an indispensably tool in today's law practice. A vital part of forecasting is what-if analysis, a procedure whereby you assign values to each variable in such a way that any one variable can be changed and you see effect of that change on all other variables. Calculations become easy, quick, and neat. When you change one critical value, all others are adjusted automatically to conform to the change saving hours of work. Working with Spreadsheets Maneuvering through a large worksheet can be very frustrating for the novice, and often difficult for the experienced user. As the formulas and functions within the worksheet become more involved and complex, the greater the need the keep the worksheet simple and well defined. As a spreadsheet grows larger, you often see a marked reduction in calculating speed. When this occurs, you have the worksheet calculate manually rather than automatically. In this way, you only request the computer to calculate after all data has been entered rather than after each cell is revised. The data in a worksheet can be displayed in many formats. In other words the number 31069 can represent a date (January 23, 1989) or it can be represented as $31,069.00 or a dozen other ways. The ability of subtract one date from another permits you to compute the number of days, hours, or even minutes, between two even events. This is very useful for computing the number of days of interest from the date of a loan to the date of the first payment, or the number of days or months from the present date to any date in the future. Formulas and Functions Formulas and functions are the true workhorses of any spreadsheet. By using a formula, you can manipulate information to meet specific needs. A formula is an equation that you write. It contains references to particularly cells and mathematical operators. A function is a special kind of formula that has been predesigned by the programmer so that all you need to do is to plug in the values (or the cell references of the values). Although your worksheet gives you almost unlimited options in using formulas, you are limited in the size of any formula within any single cell. Since you often can have no more than 255 characters in a formula, you need to be creative and break a part very complex formulas so that they will fit within the space or memory allocated to any cell. Every spreadsheet provides a way to "copy" or "paste" formulas or functions from one cell to another. The usual method is copy "down" or "across" and the computer will automatically rewrite the formula. The reason that spreadsheets can perform this task is they consider the relative, and not the absolute, position of cells in the worksheet, unless you specify otherwise. Absolute means that the cell address that is referenced in the formula or function does not change as the position of the formula does. Relative means that cell addresses change relative to the position of the formula. It's like the difference between saying "the house is on the left" (relative to where you are standing) and saying "the house is one the northwest corner" (absolute position). Special Functions The use of special functions permits the user to easily and quickly compute standard values such as the monthly installment payment due on an amortized loan or the internal rate of return on an investment. Because the formula that computes this valuation is built-in, there is less chance for error in the worksheets which use these functions. For example, to calculate the monthly payment in an amortized loan you need to use the following formula:
Although this formula looks complicated and would be difficult if not impossible to enter with a calculator, it can be incorporated one time into a spreadsheet, and can then have the values (interest rate, term, amount of loan) revised without requiring the formula to be rewritten. Other special functions that are incorporated into most spreadsheet programs are:
Most spreadsheets programs have at least 100 and often as many of 300 special functions which will permit you to quickly construct a worksheet for you special need. Once a special spreadsheet is developed you can use it repeatedly without having to invest the time required to "setup" to various relations between the data and formulas in your worksheet. Saving Time with Templates The principle advantage of the electronic worksheet is its ability to perform thousands of calculations automatically. A formula placed in a cell can relate to other cells and will be instantly updated, when the information in other cells is changed or updated. Any type of application that involves numerical data including dates and times, can take advantage of this electronic tool. The back office functions of a law office, administration, record-keeping, check registers, expense reports, annual reports, long-term forecasts, cash flow projections, general ledgers, accounts receivable, accounts payable, net work, balance sheets, and tax planning are many of the applications that can be streamlined by a spreadsheet. For the lawyer involved in the day to day practice of law, there are a number of other functions or tasks that can quickly accomplished by this electronic tool, but most lawyers do not have the time or the inclination to become adapt at the preparation of sophisticated spreadsheet programs. For this reason, spreadsheet templates can be prepared to simplify a lawyer's use. A template is nothing more than a worksheet where data is added to a previously prepared form much like completing a printed deed form. The formulas and numerical relationships between the cells have already been established, and the additional data for that particular template is all that need be added. For example to prepare an amortization schedule a lawyer only needs to furnish the following information:
From this information, the attached amortization schedule is generated. This worksheet can be further customized by changing the payment amounts throughout the loan period. Templates can be developed for almost any area of practice. Some of those that have been developed for use in our office are:
File-Saving Formats Most of the popular spreadsheet programs permit you to save your worksheet in format that can be opened or read in another application, such as a competing spreadsheet, word processor, or database. These formats may include one or more of the following:
If you will be transferring data between different software programs (spreadsheets to word processors or databases) or between different computer systems (IBM to MAC or MAC to IBM) it is essential that you select programs that have a common file-saving format so that you will not have to rekey the data or text. This can be a real time saver. Designing a "Template" Spreadsheets are very powerful tools, but they require more planning and development than a master form to be used in a word processing document. Before you can create a spreadsheet to answer or solve a particularly problem in your practice, you must answer for yourself a series of questions. The first, and perhaps the most important question is, "What solution am I trying to develop? or What problem am I trying to solve ?" The answer to this question determines the information that you will need and how it should be arranged on the worksheet. You must then determine what formulas and functions the template will require to perform the calculations. Although the development of a spreadsheet template can require a considerable amount of time, it can also save an attorney or the attorney's staff a tremendous amount of time in solving numerical problems or sorting numerical data. Once a template is developed it can be readily updated to meet changes in the law, or can be adapted easily to a particular client's special problem. Where to get "templates" Spreadsheet Templates can be downloaded from computer online services such as America Online or other internet sources. The simpler templates are frequently included as part of the tutorials that are included with the spreadsheet software. There also a number of companies that design and sell templates. The Form 706 and Form 709 templates used by our office in preparing Federal Estate and Gift Tax Returns were prepared by a company in California and costs only $99. Sorting Data Another use of spreadsheets is to quickly sort data a much as you would in a data base. The advantage of a spreadsheet is that you can create a spreadsheet much faster than you can create a data base. With a spreadsheet you can sort alphabetically or chronologically by date. The names can then be pasted into a word processing document without having to be retyped. This can be a real time-saver. In Closing If you haven't utilized a spreadsheet program in your day to day practice, consider this as another "tool" to assist you in better serving the needs of your clients. Within a few years, these programs will be a necessity for a lawyer to remain competent and productive regardless of the nature of a lawyer's practice. To productively use this "technological tool" we must begin today. In the words of J. Harris Morgan, "Begin --- and the rest is easy! " DUNN, NUTTER, & MORGAN, L.L.P. Continuing the tradition of service to clients that began in 1926. From our offices in Texarkana, Texas, we represent clients throughout south Arkansas and east Texas, including Miller, Columbia, Union, and Garland Counties and Magnolia, El Dorado, and Hot Springs in Arkansas; and Bowie, Cass, Gregg, and Smith Counties and Texarkana, Longview, and Tyler in Texas. 
|
