Although I would never admit it publicly, it’s the slightly messy tasks that spreadsheet jockeying throws up that I enjoy the most; almost like a guilty pleasure.

Take the experience I had last week as an example. We’d been running one of our subsidiaries Pet Emporium (funnily enough selling pet products) for 18 months or so with no financial controller worthy of the name and with most of the purchase and sales ledger staff banished to the (lack of) “service centre”.

In theory, how wonderful it is to have costs controlled through centres of excellence, but any spreadsheet jockey is at least nine-tenths control freak. We don’t like things that we can’t see or touch and if I’m sitting at my desk at Blaminio HQ I don’t like the thought of somebody processing my invoices miles away in Newcastle. When a so-called colleague produces a supplier statement that doesn’t reconcile you really want to explain your displeasure up close and personal as you eyeball the wally.

Anyway, you can imagine the state of the balance sheet of the subsidiary in question. Like a garden in the wrong part of town, the balance sheet had become tangled, overgrown and in dire need of some pruning and the abandoned fridges and broken prams removing.

There were suspense accounts everywhere and clearing each one of these down was going to be my priority. The problem was that the subsidiary had been reporting as just breaking even for the year, but the balance sheet was in such a terrible state that nobody could really rely on anything reported in the profit and loss account.

The problem was one of pure reconciliation. No matter how far any of us goes in our finance careers, we may never again achieve the level of satisfaction of that first correctly produced bank rec.

The finance system at our Pet Emporium allows the production of PDFs that I could download in a rather painful fashion into Excel, but doing so meant I ended up with three columns, one for debits, one for credits and a rolling balance column. The issue with the suspense account is that I was looking for equal and opposite entries to contra out to leave the problem, un-contra’d entries that would require further investigation.

Fine in principle, but these contra-ing entries weren’t exactly side by side on the ledger printouts that I’d downloaded into Excel. So, I was left with a scattered set of entries in a number format that didn’t help me.

The solution was simple in the end. I added two further columns. One column used an IF function to generate a positive or negative figure for debits or credits, which solved the problem of having the data in two columns and both as positive numbers. The formula I used just said that if there’s nothing in the debit column produce a negative figure for what’s in the credit column or just return what’s in the debit column if there’s a figure there =IF(B2=“ ”,-C2,B2). So, I now had one nice neat column filled with negative credits and positive debits.

This left me with the problem of ticking off the equal and opposite contra entries. So, in the next column I used another IF function returning all of the values I’d just created in my first additional column back to positives. This allowed me to sort the data in my newest column in ascending order and lined up all of the contra-ing entries quite neatly for me to tick off back in column 4.

So, my scattered contra-ing entries were now neatly grouped and ticked back allowing me to concentrate on the few rogue values. Not perhaps my most elegant solution, and certainly not one to please the purist spreadsheet jockey in me, but sometimes even the best artists have to work with what they’re given.

*Barry Cells is talking to Peter Charles who does not necessarily agree with any of his views on shared service centres, Newcastle or Barry’s choice of guilty pleasures.

peter@petercharles.comwww.petercharles.com.

Related tags: spreadsheet jockey, balance sheet, reconciliation,