Do you like my new video, showing off Isolist version 2.0?
Come to think of it, do you like my cool new software, Isolist version 2.0?
Isolist adds powerful reconciliation capabilities to Excel. It’s been around for about 18 months and has been popular with a small number of users. The intent with version 2.0 is for Isolist to be popular with a rather larger number of users. To that end, the reconciliation logic is significantly more flexible and at the same time it’s faster and more fun to use.
So, if you reconcile two sets of data, give Isolist a try. It can be downloaded, installed and tested literally within minutes. The video is there so that you can see even more quickly whether Isolist is what you need – take a look!
By the way, I’m well aware that this post is pretty much entirely self-serving on my account. I’ll be posting more for you, the reader, in future, I promise.
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.
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.
[This post was written as a "guest post" for Dennis Howlett, the AccMan. I reproduce it here in case you missed the original.]
“These numbers are wrong. Why can’t you accountants add up?”
Perhaps the worst time to hear these words is during the monthly review, when the management team are assembled and looking at the culmination of your month’s work. Noise levels reduce momentarily as everyone in the room recognizes the “obvious error” in the Actual total. Your heart sinks.
How can this happen? The trial balance proves there’s no problem in the ledger. Maybe the reporting system is doing something strange?
The answer, of course, lies in the “£ k” shown in the table header. “£ k” doesn’t only mean that the report is expressed in thousands of pounds. It also means that any individual number on the report could be misrepresented by up to £500 – and you don’t care about this level of error.
Therefore, to read the column of Actuals as literally correct and conclude that the total must be wrong by £2,000 is an error of logic. What you’re really seeing is the values 190.4, 210.4, 80.4 and 30.4, totaling 512 (to the nearest thousand). But all those 0.4s are too small individually to be of interest.
So there. Don’t be dismayed if you run into this situation. I leave to you the job of gently educating your critic* the next time they make this mistake.
* – A cynic would guess the critic to be the sales manager responsible for Europe, aiming to divert attention from her negative variance.
Recommendations
I have three recommendations for dealing with roundings.
- In the distant past I “corrected” for this problem, adjusting a few numbers up or down whilst taking care to ensure that they remained in line with the same value appearing on different schedules. I now repent. Not only is this practice a waste of time but it actually introduces errors into a schedule that is otherwise self consistent with the “£ k” header.
- Never round your numbers prematurely. Reports should be fed with fully accurate numbers but display them using a custom number format to show the desired precision. Know how to format numbers in Excel and your reporting system to achieve your goal. The danger, otherwise, is that report totals will be calculated using rounded numbers, which can introduce significant, real errors that will be very obvious if the total is supposed to match the same value shown on a different schedule.
- Never forget to specify the units displayed in the report, otherwise you can’t be sure to carry the point of logic with the European sales manager.