Archive for the 'Data Management' Category

The Gap between Finance and IT

Published on 7 June, 2007 in Accounting systems, Data Management and Management Accounting. 3 Comments

What is your organisation missing? Let’s see:

  • Have a hot IT team, capable of establishing reliable, secure, performing systems within budget? Check.
  • Have a keen finance team, dedicated, involved, trusted? Check.

So where’s the gap?

It’s certainly there. I know it is because I often can’t deliver the information that managers want; I can’t answer the root cause questions to the numbers that I present. This gap is a cause of systems falling short of expectation, of organisations not being able to get out of their systems what they are looking for.

The bases are not covered

I believe the gap arises where

  1. accountants take responsibility for the content and meaning of data but not how to store and retrieve it, and
  2. IT people take responsibility for storage and retrieval of data but no accountability for what the numbers mean or how they are presented*, but
  3. nobody understands both.

* – I’m grateful to Stephan-Robert Langer for giving me this characterisation of how accountants and IT people relate to data.

The same gap is identified by David Carter in his call to accountants to break up the BI party.

Filling the Gap

I think I’ve long recognised this gap, because I invariably took on the role of filling it in all of the companies I worked for. So who is in the best place to fill it, an IT person willing to learn financial concepts and take on responsibility for the meaning of data, or an accountant not afraid to grapple with SQL, data normalisation and storage technologies?

It sounds a tough call on both counts, doesn’t it? However, the pragmatic answer is clear. At the end of the day, the accountant is accountable for the numbers, and if that means straddling the gap into IT skills to ensure they can be delivered and explained then that needs to be done. No one else is going to do 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.

The Data Menu Test

Published on 13 November, 2006 in Data Management and Excel. 0 Comments

Unstructured data in ExcelHaving introduced the idea of a test for a well-constructed Excel data list, I ought actually to fill in the details for you. The basic idea of the Data Menu Test is to structure your data in such a way that you can easily use the commands under the word ‘Data’ in your Excel menu. This is highly beneficial because the Data menu is very powerful.

Actually, passing the test is dead easy; well, it is for me at least since I always construct lists this way.

To find out whether you do the same, take the following three simple steps

1. Go To Current Region

Do you know how to do this? It’s quite useful. The ‘Current Region’ is found as follows:

  • Place your cell pointer on any cell in your list
  • Increase the rectangle of selected cells in all directions until it meets a completely blank row or column (or the edge of the spreadsheet) on each side
  • The cells you have selected are the Current Region.

Excel can do this for you automatically using one of the “Special” options on the Go To dialog box, but it’s quite long-winded. On the menu, click Edit, then Go To… In the Go To dialog box click the button labelled Special… then click the radio button labelled Current region and press ‘OK’.

Structured data in ExcelForunately for everyone involved, there’s a quick way of doing all of this in an instant: simply place the cell-pointer somewhere within your list and press Ctrl+*. The Current Region will be selected.

This test is passed if the Current Region includes all of the list data and nothing else. Check around the boundaries of the selected cells using the scroll bars – or by jumping to each corner of the selected range using Ctrl+. (Ctrl + ‘period’).

2. Data Sort

Excel Sort dialog boxPlace your cell-pointer somewhere within your list and click Data, then Sort… The Sort dialog box will appear, based on what Excel finds in your list. Take a closer look at this box to find out whether you pass the test. You should see

  • ‘Header row’ selected in the ‘My data range has’ section
  • The ‘Sort by’ selection boxes should contain the full header names of your list columns, not partial names or ‘Column A’, ‘Column B’, etc.

After deciding whether or not your list passes, you should click the Cancel button – unless you really do want to sort your list at this point…

3. Summary Data

I actually don’t have a simple automated test for this. The rule is that the list shouldn’t contain any rows of summary or subtotal data. Probably the easiest thing to do is to scan the list. Look particularly at the bottom of the list for totals. You might want to sort the list on a numeric column and look at the extremes (top and bottom) where subtotal and total rows are likely to congregate.

That’s all there is to it! A clean, flat, rectangular list of data is often what you need for effective Excel work. If you pass the data menu test then you’re well on your way.

PS. Excel 2003 introduced a new ‘List’ item on the Data menu – which can be helpful in managing your lists. Experiment and see what you think.

Lists of things (in Excel)

Published on 6 September, 2006 in Data Management and Excel. 0 Comments

To get started on something that might conceivably be of some use to any of you, I want to list the features of a good list.

You have probably already spotted this, but one of our fundamental skills as knowledge workers is creating and working with lists of things. This ought to be considered one of the basic skills – a transferrable skill – a building block in the skills that combine to enable a knowledge worker to do knowledge work.

It’s no surprise to find that, along with everyone else, accountants use lists all of the time: lists of department codes, lists of staff on the payroll, lists of capitalised assets, lists of debtors who still haven’t paid… the list is endless!

So, how hard can ‘listing’ be? Anyone can write out a list, and an accountant is surely as good as the next person at putting a list into a column of consecutive cells in Excel.

Incredibly, as simple as this sounds, poor technique can waste alot of your time. If you find that an update to a list is required, or simply that the list needs different presentation, then flexibility will be required. Now, if your customers are anything like mine, the ‘updates and changes’ scenario is as common as Wimbledon. For efficient progress, you need to be able to re-use your list without any further reorganisation – and definitely without re-entry of data.

The key to list efficiency is being able to pass…

The Data Menu Test

You probably haven’t heard of the Data Menu Test before, because I’ve just invented it to sum up my key message here. And what does it mean? It’s simple. Passing the Data Menu Test is done by ensuring you can use the commands under the word ‘Data’ in your Excel menu.

I’ll revist the data menu commands and running a practical Data Menu Test in a future post, but for now I’ve put the practicalities of passing the test into… a list:

  1. Use a single Excel sheet for a list. Not keeping all of your list in one place is simply bad. There’s no doubt that for some later requirements you will want to report some list-extracts on separate pages, but we’re talking about capture at this stage, not reporting. Sometimes when combining related lists from separate sheets into one, a new column is required to distinguish rows according to which sheet they came from. Using this new column, ensure that the originating sheet of each row can be identified. This way, no information is lost.
  2. Use only one row for each ‘record’. Often a report from some other system will ‘fold’ the information of one list-item onto multiple lines, but this is a formatting option and should not be a part of your list compilation.
  3. Use one column for each ‘field’. The meaning of data in a column, from the top of the list to the bottom, should be clear and consistent. This generally means that the type of information in each cell of a column is uniform. Certainly, you should not change the meaning of a column part way down. Use a separate column wherever necessary.
  4. Use a single header row. Lists are much easier to use when each column is given a title. A problem arises, however, when the column title is longer than will fit comfortably in a single cell. In these circumstances, beginners often use two cells for the header text, one on top of the other. This is a mistake. If necessary, widen the column, abbreviate the header text or format the header cell to ‘wrap text’.
  5. Leave no blank rows or columns in the list. All cells forming any part of the list should be contiguous.* In particular, do not leave a blank row between the header row and the remaining rows.
  6. Do not include any summary information. Lists are frequently constructed in order to derive some sort of summary information, such as a sum total. However, these should not contaminate your basic list or else any subsequent calculations will need to be elaborately constructed to avoid summing the sums.

* – The inverse should also be true – any cells that do not form part of the list should be separated from the list by one or more completely blank rows or columns.

Armed with this list, you’ll have grasped the key to the data menu and be on the way to huge gains in productivity. Banish for good those time-wasting, energy-draining spreadsheets of unstructured data!

Comments and feedback are welcome!