Motivation and Opportunity

Published on 17 November, 2006 in Other. 0 Comments

Before the end, I worked regular hours, earned regular pay, commuted far and was a often a little bit disatisfied. In short, I was employed.

Following redundancy I’ve been busy researching, discussing and planning (my excuses for few blog posts recently) and now have concluded:- I’m not going to seek further employment, instead I will be my own boss and earn my own crust!

Then and Now

This is, of course, both exciting and risky and deserved my serious consideration. Some of you may have considered similar decisions for yourselves and come to various conclusions. As I’ve described before, our environment often trains us to be risk-averse, to get as well paid a job as we can find and then conform to keep it. (The misfortune of some is that the perceived need to conform blinds them to the reality of some of the costs of doing so.)

Like a rocket escaping the gravity of earth, breaking out of the employee mould requires something of a leap. This, I think, requires motivation and opportunity, and the presence or absence of these are probably the main factors why different people choose different courses.

Anyway, back to me. My basic plan is to establish a small software company, target a niche market and trade primarily over the internet – a type of business sometimes referred to as a micro ISV. I’ll write more in the future about my first product and my progress in making it ready for release, suffice for now to say that it is specifically for management accountants and that I would have saved many hours each month, when employed, had I written it before.

It remains to be seen how the new hours, pay and satisfaction stack up against what I’ve been used to. The only thing I know for certain is that I’m not going to commute far!

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.

The End

Published on 5 October, 2006 in Other. 2 Comments

I’ve just been made redundant.

That was a surprise. In fact it’s a shock to think forward to Monday when, for the first time ever, I have no plan, no agenda. It feels like there’s a chasm ahead.

It’s interesting to feel that, because my head is hyperactive with thoughts of what I might do next. I’m itching for some time free of other people’s demands to think properly through plans of making my own employment, which I’ve hoped to do since I was at school. Thoughts and feelings don’t always concur.

I was always quite impressed with the American way of managing careers that I observed when living over in California at the turn of the century. In that time and place it seemed as though nobody was tied down. People would give up one career with abandon and re-launch themselves into something completely different, apparently just for the fun of it and with no fear of losing the plot. They were determined not to drive their careers into dead ends: ruts were for climbing out of, even if it meant going back to school and getting another degree.

(Actually, the idea of getting degrees from “school”, rather than a university, didn’t really help my esteem of degrees over in the US of A, but I’ll put that down to being an outsider who just didn’t understand …)

Over here in England, we’re much more conservative about our careers. Give up a perfectly good job? Do something completely different in which one has no training or experience? How reckless! No, young man, the thing to do is keep your head down, prove your worth by the fact that you’ve stuck to your (accidentally chosen) career through thick and thin.

So, anyway, I’m considering being a little bit American at the moment. I’ll let you know if anything comes of it.

Endearing traits

Published on 23 September, 2006 in Management Accounting. 0 Comments

As if by magic, members of a profession somehow pick up traits in common with their peers.

You wouldn’t be surprised to come across a doctor with illegible handwriting, nor a university lecturer in open-toed sandals. And unfortunately for all of us, the term “builder’s bottom” was coined for a very good reason.

So what is the professional trait of the accountant?

Prompted by a comment from a colleague in another department today my vote goes to the practice of having a really big calculator and carrying it around with you to meetings. It doesn’t matter if the only functions on the calculator are + – x รท and %. It doesn’t matter that you’re not going to be doing any sums. The only really important things are that it is big and it’s tucked under your arm as you enter the meeting room.

If you haven’t yet made it as an accountant, the answer is simple!

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!