Accounting for Business

The Reconciliations of an Accountant

Published on 3 April, 2007 in Isolist, Management Accounting and Reconciliation. 0 Comments

An accountant doing a reconciliation
An accountant reconciling
Relationships aside, management accountants often find themselves having to reconcile a number of things. The obvious one is the reconciliation of a bank statement to the accounting records. Since most organisations in the developed world have a bank account, the need to perform bank reconciliations ought to be universal.

Anecdotal aside

I say ‘ought’ because I’ve had one of those experiences where I’ve taken a job in a business and found that bank reconciliations had not been done for eighteen months. Having the most fundamental of accounts not in good order is a clear indicator that lots of other problems are hidden in the accounts. It makes for an uncomfortable period as Controller when, over several reporting periods, more and more things come out of the woodwork as the balance sheet is trued up. In my case, bringing the bank account up to date took several solid days of work, but bigger problems were found elsewhere. Maybe others of you have had similar experiences.

Other sources of reconciliation

Back to the subject of the post, in addition to reconciling bank statements, accountants may face several other reconciliation tasks, on either a regular or an occasional basis. Do you recognise any of these?:

Business systems to general ledger. Some organisations operate a mix of applications, either through historical accident or by design in order to cover needs that are not met by a single application. For example, if the sales order and customer invoicing system is separate to the accounting system then reconciliations are generally required to ensure that revenues are correctly accounted for.
Intercompany and branch office accounts. Organisations with more than one location or subsidiary frequently share resources and fund or borrow from one another. Where a separate ledger is run for each subsidiary, it is necessary for the intercompany accounts in each one to be matched in order that they remain consistent with one another.
Stock-take to inventory system. Common in organisations that hold physical stock, different inventory systems give varying levels of assistance to the stock-take process. An accountant or colleague may be required to reconcile a physical count with the system inventory balances.
Sub-ledger to general ledger. Even where systems are well integrated, it’s often prudent to check that transactions originating in a sub-ledger have not gone awry when posted to the general ledger. Reconciling control reports from each side is not uncommon.
Closing balances to opening balances. You might think that it should never be necessary to reconcile a previous closing balance to a current opening balance but, unfortunately, the world simply isn’t perfect. When posting a year end journal, both human and system errors are possible. A different scenario might be where a group accountant receives trial balances from subsidiaries month by month, but the latest figures somehow don’t match properly with last month’s…

It’s at the boundaries between systems where the need for reconciliations arises. Maybe you recognise some of these tasks in your own work. I’ve certainly spent much time working on a wide variety of reconciliations over the years and the famous bank reconciliation is typically the easiest and quickest. It’s because reconciliations are so common that I decided, as my first product, to create a tool to make the task as easy as possible. Progress is steady and I’m planning to have Isolist ready for public testing about one month from now.

OpenCoffee Meetup, London

Published on 23 March, 2007 in Business and Other. 0 Comments

I went along to the London OpenCoffee Meetup for entrepreneurs and investors yesterday. Many enthusiastic business people filling the Starbucks inside the otherwise quiet Esprit clothes shop on Regent Street provided an incongruous scene, but it works well and there was a steady buzz of conversation throughout the time I was there. If you are starting a business in the London area and want to chat to others in the same boat, even find some leads, then I’d recommend it.

Separation of Data and View

Published on 18 March, 2007 in Data Management and Excel. 1 Comment

Simon Murphy’s blog is an interesting read for those of us keen to make the best use of Excel. Identified in his post on the disadvantages of spreadsheets is that not separating “data” from “view” is a bad thing. This is the key reason why I recommend keeping data in clean lists, without contamination from the layout, subtotals, etc. that belong in a “view”.

MVC

Many people will be unfamiliar with the “Model-View-Controller” pattern used in programming, so I’ll expand briefly on the idea and reasoning.

The basic idea is that a complex program is usually more robust and easier to work with when composed of separate, well-defined modules that work co-operatively. In the MVC pattern, the “model” broadly stands for the program module that holds data and the “view” is the program module that presents the data to the user. The pattern involves making these separate and, in particular, ensuring that the data is independent of how it is viewed.

Achieving this separation provides several huge benefits, including:

data can be tested for accuracy and completeness independently of any other factors, such as whether a report has ‘read’ the data correctly,
the work involved in maintaining and updating views/reports is limited to only one part of the program; the data itself remains intact and safe,
it becomes very easy to provide multiple and different views of the same data, without having to duplicate or modify the data in any way.

Implication for Excel

Separate Views from Data in Excel

Excel provides blank worksheets, inviting you to structure your data however you like. Learning from the MVC pattern, we should provide good structure, even though Excel doesn’t require it. Generally this will involve capturing data in clean lists and using separate sheets, pivot tables, etc. to provide the output that we want.

For small, one-off workbooks, don’t bother. But if you have lots of data, or will be using the same workbook over a period of time, then consider keeping your data and your reports separate.

Review of SQL-Ledger

Published on 28 February, 2007 in Accounting systems and Management Accounting. 4 Comments

Here’s the second in a series of reviews of open source accounting packages - part of my mini-survey investigating whether there is any merit in open-source software for accounting.

SQL-Ledger Introduction

SQL-Ledger is a system for accounting, order processing and stock control and follows a traditional design - with the interesting exception of being a web application. It uses PostgreSQL, a respected and mature open source database system, for data storage, although if you already use Oracle or DB2, these databases can also be used with SQL-Ledger if preferred.

The software is freely available from DWS Systems Inc., the primary developers, who also provide documentation, support and customization services for a fee. It is now apparent in only my second accounting system review that this business model (aiming for wider market share by making software available for free and then aiming to profit from related products and services) is common for open-source accounting systems.

The prices for support of open-source systems are inevitably similar to the prices for support of commercial systems and so businesses of any appreciable size will not gain a significant cost advantage in using open-source systems over the cheaper commercial products available. (This is part of the reason why the cost advantage of using Linux over Windows is so debatable.) The decision on which route to go will always need to be made specifically for each individual business.

Although the documentation for SQL-Ledger can only be obtained by spending US$ 190, I found a useful introduction to SQL-Ledger provided by Metatron Technology Consulting, which discusses system capabilities, usage guidance and customization options. If you are considering SQL-Ledger for yourself, that document would be a very good place to start.

Installation

Being a web application, all installation is to a server. Client PCs need no software installed since they use only a web browser to access the system. On the server, SQL-Ledger is platform-agnostic as long as the dependencies (Perl with database modules, Apache, PostgreSQL and LaTeX) can be met. These are all readily available for both Windows and Unix-based servers. Alternatively, you can do what I did and obtain a “virtual appliance” as a single package and run it in a VMware host.

Having booted the virtual server, I ran the update script supplied with SQL-Ledger which updated my installation to the then current version 2.6.22. I understand from the SQL-Ledger website that a version 2.7 is available with some new features, although oddly they don’t list this as their latest version and the automatic upgrade didn’t download it. I interpret this to mean that it’s not ready for production use.

Configuration

Much of the setup and configuration for SQL-Ledger can be done from the web browser. An administration page is provided for setting up databases and users, following which you can log in to the actual system and set up your required accounts, codes, parts and services, etc. There are, however, some configuration options that need to be made in a traditional Unix-style text file, the most important of which is setting up printers. This is inevitably a bit messy, particularly if printing from a Unix server to a Windows printer. It would be much better if this sort of thing were fully handled by an extra admin screen.

SQL-Ledger can handle multiple companies, each of which is held in a separate database. Multiple user ids can be created for each company, but each user id can only access one company. Therefore, if you want one user to use two companies they will require two separate user ids.

Loading data. For small businesses it may be practical, if tedious, to enter all accounts, customers, suppliers and products one by one into the appropriate screens. If lots of data needs to be imported, some scripts are available on the main website to read data from tab-delimited files directly into SQL-Ledger’s database. These, however, are heavily-caveated and so probably require some serious tinkering to do the job correctly. Again, this task would be much better handled by a user-friendly screen in the application.

For setting up opening balances the only option available is to enter normal transactions. SQL-Ledger recommend posting a supplier invoice to set the stock levels for each item, posting a GL journal to set up the opening trial balance and presumably posting all open sales and purchase invoices, unmatched receipts and payments, etc. as originals.

In Use

Part of the SQL-Ledger menu The menu of commands in SQL-Ledger is displayed in a column on the left of the screen, with sub-menus that can be expanded or collapsed by clicking on the header. This is simple but effective and easy to use.

The main body of the screen is where most of the action takes place. Data is presented cleanly, if not particularly stylishly, and all screens follow a common pattern which quickly becomes familiar.

The various enquiry and report screens are all prefaced by a configuration screen where search-text, report filters and columns to be included are specified, resulting in good flexibility in the data that is presented on screen. However, it would be much less cumbersome if the system remembered the report settings made between one use of the report and the next - as it is, you need to re-specify them each time.

One error occured whilst I was using SQL-Ledger, related to updating the average cost for a stock item when posting a purchase invoice. This didn’t seem to have any wider ramifications and other than that the system appeared stable and reliable.

Order Processing

Transactions can be started at any stage, so in the sales process, for example, you can start with a quote, an order or an invoice. Entering data is straightforward and it’s possible to view a lookup screen of parts or services to find the ones required for the order.

When displaying an order or invoice on screen, several related actions can be performed by clicking on buttons across the bottom of the screen. The actions presented are independent of order status, so the opportunity to progress transactions just as you like is very fluid.

Some SQL-Ledger action buttons

Superficially useful, this fluidity strikes me as being very dangerous. Not only is any action allowed at any time, but no warnings or second chances are ever given prior to an action being performed - regardless of how unwise that action might be.

Here’s an example of what I mean:-

having entered a sales order you may then process a shipment, but no invoice is generated until you specifically ask for one - there is no warning that an order hasn’t been invoiced
after shipment but before invoicing, you could choose to close an order - no warning is given on this action
from this situation (assuming you realize that you are missing revenue) all is not lost. Simply search for and display the closed order and then press the sales invoice button to generate an invoice - no warning is given
But you could do this any number of times, generating multiple invoices for the same order - and again no warning is given on this action.

Such fluidity is a double-edged sword but the downside edge is much sharper than the upside one. Careful patrol of open orders and diligent following of procedures are necessary to ensure transactions are processed correctly.

A problem worse than this becomes apparent when examining the effects of shipping and receiving stock items. On processing a shipment or receipt, the on-hand stock balances are adjusted accordingly but no accounting entry for stock movements is recorded until the order is invoiced. Therefore the reported stock list quantities and values will not reconcile with the balance sheet stock values if any invoices are outstanding - a highly undesirable circumstance.

SQL-Ledger doesn’t track individual shipments and receipts on an order, so if a part-shipment is made, followed by a second shipment to complete the order there is no subsequent way of knowing the detail of what happened.

Accounting

Regarding accounting functionality, some features that I consider desirable are missing:

there is no batch holding and processing facility - each transaction is entered and posted directly (although version 2.7 claims to include a “voucher system”, which may be what I am looking for)
there is no period management independent from the date for each transaction and there is no way to close a period in a sub-ledger whilst leaving other ledgers open for further postings
there is no facility for holding budget data

Sales and purchase ledgers integrate automatically and fully with the general ledger so there is no separate task to transfer data from sub-ledgers at the end of each period.

Stock receipts and shipments are posted directly to the general ledger using account codes that are defined individually for each stock item. This provides useful flexibility for posting different product ranges to different accounts for analysis, but less flexibility for non-routine stock transactions which may need to be recorded to different accounts.

Department codes can be created for segmentation of the accounting data. Departments can be specified either as cost centres or profit centres, depending on whether just expenses or both revenues and expenses require analysis. These department codes can then be used to classify sales transactions, purchase transactions and accounting journals.

There is no general ledger transaction report other than the report on transactions in a single account. A broader GL transaction report would commonly be used to trace bookings across several accounts and as a general accounting audit report.

When selecting invoices for payment, each one must be selected individually: there’s no routine for selection based on due dates or anything else. In fact, the due date doesn’t even appear on the payment selection screen.

Viewing of transactions on a customer or supplier account is either awkward or not possible - I couldn’t find a report showing both invoices and payments within a customer account.

SQL-Ledger Audit Controls Closed period control is possible, implemented by entering a cut-off date prior to which no transactions can be made or amended. This affects all system areas uniformly.

Year-end processing is a very simple affair. A date and description must be provided, following which the system generates a year-end journal. Processing is very abrupt so some care is required, although it’s always possible to open up the audit control and amend or delete the journal.

Standard financial reports are extremely basic. It is possible to define reports in a style that you require, but the tools used for this are HTML and LaTeX together with some system codes for picking up the required values (which presumably are only documented in the $ 109 manual). This is all too hard to warrant further investigation and I’m sure other accountants will feel the same.

Conclusion

Functionality falls short in a number of the areas that I would look for, which I listed in my introductory post.

Requirement	Score
Include GL, SL, PL	1
User-defined account codes	1
Period accounting	0
Good year-end close controls	1
Hold >= 2 years of data online	1
Efficient journal entry screens	0
Separate journal entry from journal posting	0
Import journals from external files	0
Budget data with efficient management	0
Efficient and informative account enquiry	0
Effective report generator	0
Access to underlying data	1
Provision for full data export	0

So that’s a score of 5 out of 13 - pretty poor.

SQL-Ledger is likeable for its ease of use, clear screens and freedom of action. But this freedom of action is also a major concern for both financial and inventory control. The features provided are disappointingly simple - more so than one would expect given the marketing copy on the SQL-Ledger website. I guess for small service businesses that don’t need to track many stock items it’s perhaps worth a try, otherwise you should probably look elsewhere. I’m going to.

Reviews update

Published on 22 February, 2007 in Management Accounting. 5 Comments

Two or three weeks have passed since my review of TurboCASH, so here’s a brief update on where I am with reviewing other open source accounting systems.

I’ve downloaded, installed and generally messed around with three or four other accounting systems and am working up to the next review - which will probably be of SQL-Ledger.

The others I’ve installed and scouted are:

Compiere - which makes some intriguing promises through having a non-standard design. This is a fairly substantial ERP and CRM application targeted at small or medium businesses and so will take me a while to assimilate. Nonetheless, I’m looking forward to trying this one.
VT Transaction+ - not open-source but a low price and a real alternative to the likes of Quickbooks. I installed the “plus” version which is in beta testing/ongoing development and loved the directness, simplicity and usability features. VT Transaction has an unusual spin on the meaning of “ledger” which might take a little getting used to and I’m not sure that my data import/export desires will be met. Anyhow, as it’s not open source I’ll leave a possible review ’til later.
TASBooks 1. I installed a trial version of this in a moment of weakness (I had been losing the battle that often accompanies installation of open-source software). Again, this is a low cost commercial product but, unlike VT, follows the common pattern of equating low cost with noddy users. It has those trite graphical workflow things, which I suppose are fine in Toyland but the real world is much more complicated. Nonetheless, if you find you can work happily within the straight-jacket workflows then the actual functionality seemed fine on initial viewing. Again, I won’t review just yet because it’s not open-source. In fact, I probably won’t ever review since the trial version lasts for only 15 days.
SQL-Ledger. Back to the real open-source stuff. SQL-Ledger has a sturdy composition in the company of most of the others mentioned, using a proper database at the back and being a full multi-user web application. On the face of it, SQL-Ledger looks reasonably competent so I’ll do some more work here and report back.

Jason Holden has suggested I look at Interprise, which I’m willing to do but haven’t found a download link. It looks like I actually need to ask for it.

In case anyone else wants to look at Compiere or SQL-Ledger, the installation of database systems and other dependencies along with the actual programs can be alot of work. To save a day or two of frustration I downloaded virtual appliance images, which can be played in the free VMWare Player or VMWare Server applications:- a godsend.