Building customized financial documents with free spreadsheets

Spreadsheets are the do-it-yourself version of financial software. They have all the power of other financial software, but in a less finished package. Instead of wizards and ready-made forms, they offer the raw material of hundreds of functions, only some of which are relevant to personal or business finance, and leave you to arrange them in a meaningful fashion.

Under these circumstances, the fact that many people choose friendlier financial software isn’t surprising, but you don’t need to be among them. The truth is, you can start to employ spreadsheets for business or personal finances with knowledge of only the most basic arithmetical functions, and branch out into the more complex spreadsheet tools and functions later — or not all. Meanwhile, unlike some other financial software, you’ll have the advantage of being able to customize your documents exactly according to your needs and preferences.

Any free software spreadsheet, including KSpread and Gnumeric, can be used for finances, and you’ll find the names of functions are much the same no matter which you choose, largely because the prominence of Microsoft Excel has established the conventions for the entire category of software. Here, I’ll focus on OpenOffice.org’s Calc, because it is widely used and because it has tools that other free spreadsheets haven’t implemented yet.

Building basic financial documents: an example

Templates for Calc are available on the Internet. OO Extras has templates for more than a dozen types of financial documents, in both OpenOffice.org 1.0 format and Open Document Format (which may be usable now in the latest versions of other free spreadsheets). These templates include a budget, petty cash sheet, and investment portfolio. The templates let you get to work quickly, but the logic behind the layout of some is obscure, especially when they come with few instructions or you lack experience with spreadsheets. You may need to modify others by changing the currency or tax rates, or adding rows to suit your needs. In many cases, you are better off using ready-made templates as examples, and building your own financial documents.

To show you how easy building a financial document can be, here are instructions for designing an invoice for a consultant in Calc. It was one of my own first forays into spreadsheet design:

1. Define the widths of columns A through D. Since an invoice, unlike many spreadsheet documents, may be printed, make sure that the total widths do not exceed 6½ inches, which gives a generous margin on each side. A width of 2¾ is good for Column A, and 1¼ works for the others.
2. Enter headings for each column about 20 rows down: from left to right: Services; Hours, Price, and Total. You may prefer to shade the headings row cells by selecting them all, right-clicking with the mouse, and selecting Format Cells followed by the Background tab. Depending on the color you select, you may also want to change the font text on the Font Effects tab.
3. Select the next 15-20 cells in the Price and Total columns (Columns C and D) and right-click with the mouse. Select Format Cells -> Numbers from the pop-up menu, and set the columns to your desired currency. When you later enter numbers in these columns, the appropriate currency symbol and format will be entered for you. You only have to type 20 for the spreadsheet to convert the entry to $20.00 and align it to the right of the spreadsheet cell.
4. In the first row below the column heading in the Total column, enter a formula to multiply the contents of the Price and Quantity columns. Select Insert -> Formula -> Product, then add the cell addresses for the Hours and Price columns. For instance, if first column is in the 20th row, enter in D20 in the Total column =Product(B20;C20). When you click the OK button on the Function Wizard, the Total column reads 0 because it has no input. However, if you add figures to the Price and Quantity columns, you’ll see the figures change. You can then quickly add the formula to the rest of the rows in the invoice by dragging on the small black rectangle on the lower right of cell D20. Calc will automatically alter the formula so that it works on each row without you having to make manual changes.
5. At the bottom of the Total column, enter a function to all cells in the column — for instance, =SUM (D20:D40). In the Services column, or possibly the Price column, you can identify this function as the Sub-Total.
6. In the next row or two of the Total column, enter a function for each tax or surcharge. Since taxes are often a percentage of the total, you can determine the tax automatically by multiplying the total by a decimal fraction. For instance, if your local sales tax is 7%, the formula might read =PRODUCT(D40;0.07). Each tax or surcharge should also be labeled, just like the Sub-Total.
7. Add a function labeled Total to the bottom of the column. If you had one tax to mention, the formula would be something like =SUM(D40;D41).

You now have a fully automated invoice spreadsheet. You only need to input Hours and Price, together with a brief description in the Services column. Any time you enter figures to the Hours and Price columns, the invoice is automatically updated for you by the series of interdependent formulas. Yet it consists entirely of addition and multiplication — basic arithmetic that most people are familiar with from grade school.

Invoice, showing precedents and dependents for Sub-Total cell – click to view

Once you check the functionality, you can format to suit. You can use the empty space you left to enter details such as the name of your consulting company, the client’s address, and your logo, while the blank space below can be used for your signature and to add an invoice number. You can also set the rows within the invoice proper so that their borders are printed; you can shade or color columns or individual cells, or use Insert -> Note to provide pop-up help that can guide new users.

Incidentally, I kept the design to about 50 rows, enough so that it can print to a single US letter-sized page. Should you need a longer invoice, use Format -> Page to adjust the layout of the printed page.

When you are finished formatting, save using File -> Templates -> Save, so that the invoice becomes available whenever you need it from File -> New -> Templates and Documents.

Many financial documents are equally simple, particularly those for personal use. For instance, from this example, you can probably extrapolate how you keep track of a bank account by setting up one column for credits and another for debits, then adding a formula that adds the total of each column to give the current balance.

With minimal trial and error, you can create these simple documents in any free spreadsheet program. While the location and names of the tools mentioned here may differ, most should be available no matter which program you prefer.

Using function tools

You can automate your documents even further by using the tools that are a typical part of many spreadsheets. Calc is particularly rich in tools for dealing with functions, including:

• Scenarios: Scenarios allow you to define several different sets of data, then select which one you want for your current document. For instance, in the invoice, if you regular provided a standard set of services, you could use scenarios to color-code each for your convenience while you work, then select the appropriate one for each invoice, rather than inputting it manually. Alternatively, you might use scenarios to set up discount rates for the same service, or different rates depending on the size of the job.

• Goal Seek: Usually, you run a formula to get the result when certain values are entered. With Goal Seek, you work with a completed formula, changing one piece of input at a time. This tool is ideal for reducing the calculations when planning a personal or company budget.

• Multiple operations: Like scenarios, multiple operations are a way of providing alternate versions of data. Using multiple operations, you can set up several different formulas to be applied to the same data. This tool is especially useful in planning documents, in which you might want to see the results of slightly different versions of the same formula to take into account — for example, changing interest rates or currency exchange rates.

Besides these formula tools, you may also want to apply Notes as a form of online help. And if you ever need to revise a document after you make it, you may find the Detective tool handy for tracing from which cells each formula gets its inputs.

Not all spreadsheet applications have all these tools. However, where they exist, you’ll find they can take even more drudgery out of your financial planning and record-keeping.

More advanced financial formulas

Once you feel comfortable with basic formulas and the tools for using them, you may want to investigate the more advanced financial functions. Calc has more than 70 of them, most of them concerned with estimating the worth of assets and with tracking loans and investments. They include functions for determining asset amortization, interest on loans and investments, coupon days (regularly scheduled payments on investments), and investment duration, payment, price, and yield. Many require half a dozen or more arguments.

Some of these functions are relatively easy to use, provided you have the necessary information. For example, for COUPDAYS, COUPDAYBS, and COUPDAYSNC, all of which are concerned with calculating when payments are due on an investment, all you need to know is such details as when you must finish paying and when the investment reaches maturity.

However, to use many of the other advanced financial functions effectively, you may need a general knowledge of accounting and investment, as well as of local laws. For instance, you will find more than half a dozen functions for calculating asset depreciation, such as AMORDEGRC, AMORLINC, DB, and DBB, but you’ll need to know what form of amortization you should apply to different assets. Some forms of property, such as desks and chairs, are typically assumed to depreciate at a steady rate over their lifespan. Others, such as cars, lose most of their value at the start of their life cycle, then depreciate more slowly. Apply the wrong one, and you may find yourself paying unexpected penalties.

The same is true for formulas for calculating investments, whose definitions of a reporting year, for instance, may have a specific meaning that is different from what you think.

Conclusion

If the comments about advanced financial functions sound ominous to you, don’t worry — you probably don’t need them. Probably, your financial affairs are straightforward enough that simpler functions and intelligent planning are all you need to use a spreadsheet rather than a dedicated financial program.

The only thing you might miss is some of the formatting available in other programs. Yet, here, too, the problem is not limited choice, so much as the fact that you have to do it yourself. Most spreadsheet programs allow you to set the background colors to cells, or to import pictures or add your own diagrams to them. Calc even includes an Autoformat tool with predefined formats. Whether you use to linger over such options, though, is your call.

Compared to dedicated financial programs, spreadsheets take longer for initial setup of documents. However, if you prefer to do things your own way, then chances are you’ll find them the most versatile choice for managing your finances.