February 27, 2006

Using SQL-Ledger in a small business

Author: Angus Carr

Recently, I helped set up a small co-operative preschool. One of the many things we needed was an accounting software package. Our ideal application would be easy to use, cost little or nothing, and allow users to work with it remotely. We wanted to keep the records on site, but not require the treasurer (me) to come in repeatedly. An analysis of the costs and abilities led us to choose a free solution. SQL-Ledger, which is licensed under the GNU GPL, fits the bill. We chose it for its ease of use and for features like emailed PDF invoices.

SQL-Ledger is a Web-based accounting system that does business-class double-entry bookkeeping. It can have multiple users on the system at one time, with individual privileges, using individual forms. Data entry can be audit-safe, with deletion allowed or not. As the project's Web site explains, "Accounting data is stored in a SQL Server. For the display any text or GUI browser can be used. The entire system is linked through a chart of accounts. Each item in inventory is linked to income, expense, inventory and tax accounts. When items are sold and purchased the accounts are automatically updated."

A chart of accounts is a list of allocations of money. Our expenses are payroll, insurance, and program supplies. Our income is from program tuition and possibly some grants.

We started with a default Canadian set of accounts, which were set up for a manufacturing business. We deleted the inventory accounts and many of the expense line items because they aren't part of our business. We had to add expense accounts for our budget items, such as craft supplies.

One of the strengths of SQL-Ledger is the selection of 53 sample charts of accounts. They provide a starting point from which to work. The accounts may not be perfect, but our system started out in a workable state.

Working with our co-op members

The co-operative sells preschool services to our members. We prepare invoices for our members when they register children for the program. Most members pay monthly, so in our nine-month program, there are nine invoices to fill out. Invoice workflow is well thought out in SQL-Ledger. The software minimizes the number of buttons you have to click and makes effective use of lookups to fill in fields. If it were a desktop application it could be more efficient by using some form of guessing at what you are entering, but we are using a Web-based interface. Browsers now fill in forms automatically to some degree, so common entries are easy enough.

Once an invoice is ready, you can print or email it. One option is to print a PostScript file to the user's browser. We send it to lpr as a helper program, so it prints locally. You may also choose PDF or HTML, and direct print jobs through print queues local to the server. Most of our invoices get emailed as PDF files.

The default invoice is clear and well-designed, but I modified the invoice to include a remittance area. This way, we can also use the invoice as a tear-off receipt and remittance, just like a utility bill. The remittance only prints when a balance is due.

Modifying invoice templates from the default requires editing the source code of the invoice, which is in LaTeX. Unfortunately, it is non-standard LaTeX, so I couldn't use LyX, a GUI editor for LaTeX. Other LaTeX editors were also difficult. I ended up using a text editor, Kate, in order to get syntax colouring. With no GUI support, it was a trial-and-error process. There are instructions in the documentation, but they didn't work when I tried them. I assume any error was mine.

Another approach to modifying invoices would be to modify the HTML invoice template and use HTML invoices, but we wanted to use PDF invoices; they seem cleaner and better laid out for printing.

Keeping track of what families have paid their bills is easy and quick, with outstanding transaction queries and other reports easy to hand. We can report accounts receivable (AR) aging for all our invoices to see who needs to be reminded.

The accounts payable (AP) section of SQL-Ledger is a mirror of the accounts receivable section. We identify suitable vendors, fill in a vendor invoice or AP transaction, set up payments, and print cheques.

SQL-Ledger has no direct support for payroll. We work around that by treating all of our employees as if they were vendors and using vendor invoices. We calculate source deductions using an external program called CdnPayroll. We fill in a vendor invoice to indicate gross pay, deductions, and net pay. Employer contributions are a separate expense to the preschool. We make them as an AP transaction, payable to the government, combining the employer and employee contributions.

After four months

We chose SQL-Ledger because it gave us confidence in and knowledge of our books, the emailed PDF invoices, and remote access. The cost equivalent was only slightly lower than the same cost equivalent with commercial software. We're putting in similar time, but getting more stuff out than we would have with the commercial software choice. Another alternative we considered, contract bookkeeping, would have been more expensive and would have provided us only basic bookkeeping.

Four months after we started this process, we are able to report to our members that 95% of their cost goes directly to classroom activities. The rest is insurance and other fixed costs. I can also tell prospective treasurers that it isn't a lot of work -- it's only about four hours a month to keep up with it all. I am happy using the software, and I can look in on it from remote locations as easily as I can work on it from the preschool itself.


  • Open Source
Click Here!