Phew! There’s a surprising amount of work involved in taking a product from my development PC and making it ready for general use. But finally, today, I’ve published the Beta version of Moverve’s first product, Isolist.
As explained in an earlier post, Isolist is an AddIn for Excel, providing a dedicated reconciliation function that matches the records from two data lists and identifies any mismatches. The requirement to perform such list-matching tasks is wide-spread:- certainly for management accountants but also, I believe, more generally too.
To get the ball rolling, I’m giving a special offer to users of the Beta version who can find and let me know of any bugs or other problems with the product. If you find a genuine software problem, I’ll send you a free license for version 1.
Published on
19 April, 2007 in
Excel.
Shortly after first learning how to use spreadsheets ( only 17 years ago ) I had a conversation with a friend along these lines:
Me – Creating spreadsheets is a little bit like programming. In fact, you could say that it is a sort of programming.
Friend – Programming? Spreadsheets? They don’t look like programming to me.
Me – I admit the ‘programming language’ is unusual and very high level, maybe like a 4GL, but look at what’s happening: You start with a blank sheet, you give the computer a set of rules and some input data and it generates outputs. Conceptually that’s a program, isn’t it?
Friend – [with a sceptical look] Hmmm.
I’d forgotten that converstion until today when I read Writing formulas is computer programming.
Of course it makes no difference whether or not you call using a spreadsheet programming. The fact that you could call it programming, though, is interesting because it indicates that programming needn’t be scary or nerdy. If you can accept that then you’re more likely to make more effective use of your PC.
An accountant doing a reconciliation
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.
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.
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
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.