Share

Spreading the load

Spreading the load
Blogs

Spreading the load

Finance | John Tate | 27 Apr 2008

John Tate reviews the chequered history of spreadsheets. 

I first became a serious spreadsheet user in 1987 when I joined a fashion business as finance director. The previous FD left the week before I joined and left me a very limited set of instructions on my new role. One of my jobs was to prepare the monthly management accounts. Simple you may think except the finance system I inherited was based on a Lotus spreadsheet that my predecessor had handcrafted.

The first worksheet was set up to allow me to enter the cashbook sum-maries, purchase and sales ledger balances, closing accruals and prepayments, depre-ciation and fixed asset adjustments etc. A whole series of macros then produced profit and loss reports by retail outlet/department and the balance sheet. I joined on 1 October and needed to produce the first set of results for the bank by the 7th.

After working 14 hours a day and one weekend I produced the accounts. The system was tricky to use but did the job. It took me about a day to update the spreadsheet each month and a further day to produce all the reports. A further half day or so was spent checking the information as I was always paranoid that I had missed something. No one else could use the system and I had to take holidays around the reporting cycle of the business. Reports to the bank were often late.

I decided to replace Lotus with a pre-written accounting package. Pegasus Senior was installed. The month end work reduced to about half a day of my time and I trained one of the team to take this over. Accounts came out on around the second working day of each month. I also inherited a cashflow forecasting spreadsheet.

The basis of this was quite simple. We imported the bulk of our garments from overseas. Goods came in on letters of credit and we had a number of sources of funding to guarantee each transaction. Bills of exchange were issued when goods cleared customs, typically but not always on 90 day terms. Payment for the goods hit our bank account when these were presented. The majority of goods were sold to other fashion retailers and these debts were factored in. I decided to build a spread-sheet to keep track of this.

The early version of the model, which took me a couple of hours to build, was a great success and saved me hours of manual work. However, in the following few months the model became more and more complex. Volumes of imports and the number of suppliers rose sharply. Additional funders were added. Shipment delays at Heathrow meant I had to update the master sheet on an almost daily basis. The initial model was poorly designed and each change meant adjusting a dozen or more worksheets with the new dates and figures.

By the fourth month it was taking me many hours to update each week. Then I made a mistake. A significant sales invoice to a customer was missed due to an error in the spreadsheet and for a couple of months this was not noticed. Thankfully I did some double checking of our billing and spotted the error but it could easily have been missed and never invoiced. Missing billing this one invoice would have wiped out nearly half of our profit for the year.

So I rebuilt the model, this time with many more checks in place and learnt an important lesson. Simple spreadsheets are fine. Complex spreadsheets need designing with great care from the outset and take time and effort to manage, support, document and test. Audit your systems, rank your spreadsheets by complexity and risk, and consider replacing really complex models with off the shelf packages where they exist. Charities must take this issue seriously or risk significant financial damage. Don’t wait until it is too late.

There is a body called the European Spreadsheet Risks Interest group (EuSpRIG) that focuses on issues we face when using this technology. Horror stories, which may ring a bell with heavyweight spreadsheet users, can be found on its website. Visit www.eusprig.org/stories.htm

Comments

[Cancel] | Reply to:

Close »

Community Standards

The civilsociety.co.uk community and comments board is intended as a platform for informed and civilised debate.

We hope to encourage a broad range of views, however, there are standards that we expect commentators to uphold. We reserve the right to delete or amend any comments that do not adhere to these standards.

We welcome:

  • Robust but respectful debate
  • Strongly held opinions
  • Intelligent relevant discussion
  • The sharing of relevant experiences
  • New participants

We will not publish:

  • Rude, threatening, offensive, obscene or abusive language, or links to such material
  • Links to commercial organisations or spam postings. The comments board is not an advertising platform
  • The posting of contact details for yourself or others
  • Comments intended for malicious purpose or mindless abuse
  • Comments purporting to be from another person or organisation under false pretences
  • Gratuitous criticism, commentary or self-promotion
  • Any material which breaches copyright or privacy laws, or could be considered libellous
  • The use of the comments board for the pursuit or extension of personal disputes

Be aware:

  • Views expressed on the comments board are left at users’ discretion and are in no way views held or supported by Civil Society Media
  • Comments left by others may not be accurate, do not rely on them as fact
  • You may be misunderstood - sarcasm and humour can easily be taken out of context, try to be clear

Please:

  • Enjoy the opportunity to express your opinion and respect the right of others to express theirs
  • Confine your remarks to issues rather than personalities

Together we can keep our community a polite, respectful and intelligent platform for discussion.

John Tate

John Tate is a qualified accountant and has over 20 years working in the IT industry. He is also a columnist for Charity Finance, IT advisor to CFG and a lecturer at Cass.

Ian Allsop (60) John Tate (53) David Davison (49) Robert Ashton (41) Tania Mason (24) Andrew Hind CB (17) Gordon Hunter (17) Daniel Phelan (14) Vibeka Mair (12) David Philpott (10)
David Ainsworth (10) Gareth Jones (8) Celina Ribeiro (8) Niki May Young (8) Rui Domingues (8) Andrew Chaggar (5) James Brooke Turner (4) Sir Stuart Etherington (4) Kate Sayer (3) Jeremy Swain (3) Garreth Spillane (3) Alistair Gibbons (3) Ian Clark (3) Claris D'cruz (2) Stephen Lloyd (2) Richard Maitland (2) Adrian Beney (2) Iain Pritchard (2) Pauline Broomhead (2) Daniel Fletcher (2) Martin Brookes (2) Tesse Akpeki (2) Nick Brooks (2) Stephen Hammersley (2) June O'Sullivan (2) Peter Holbrook (2) Belinda Pratten (2) Simon Steeden (2) Jonathan Bruck (2) Dan Gregory (2) Mark Astarita (1) Don Bawtree (1) Sir Stephen Bubb (1) Victoria Cook (1) Lindsay Gray (1) Rachel Holmes (1) Nick Ivey (1) Iona Joy (1) John Kelly (1) Michael King (1) Heather Lamont (1) Lucy McLynn (1) Chris Oulton (1) Julian Rathbone (1) Socrates Socratous (1) Richard Weaver (1) Karl Wilding (1) Richard Williams (1) Roger Chester (1) Matthew Bowcock (1) Joe Saxton (1) Reuben Turner (1) Martin Farrell (1) Paul Gibson (1) Becky Slack (1) Jonathon Grapsas (1) Andrew Scadding (1) Simon Hebditch (1) Su Sayer (1) Debra Allcock Tyler (1) Martin Birch (1) Mark Hallam (1) Jonathan Lewis (1) Sara Llewellin (1) John Low (1) Dame Mary Marsh (1) Ruth Murphy (1) Colin Nee (1) Julia Unwin (1) Kate Rogers (1) Malcolm Hayday (1) Filippo Addarii (1) Kimberley Scharf (1) Jakes Ferguson (1) Jessica Sklair (1) Joe Turner (1) John May (1) Julian Blake (1) Rosie Chapman (1) Andy Williamson (1) Malcolm Hurlston (1) Andrew Samuel (1) Chester Mojay-Sinclare (1) Paul Amadi (1) Kirsty Weakley (1) Luke Fletcher (1) Dan Corry (1) Peter Mitchell (1) Billy Dove (1) Andrew Ketteringham (1) Jackie Turpin (1) Lynne Robb (1) Jonathan Crown (1) Paul Emery (1) Ruchir Shah (1) Pesh Framjee (1) Sukhvinder Kaur-Stubbs (1) Moira Protani (1) Vicki Prout (1) Michael O'Toole (1) Dawn Austwick (1) Lisa Clavering (1) Paul Farmer (1) Neelam Makhijani (1) Logan Anderson (1) Andy Rich (1) Sharon Martin (1) Asheem Singh (1) Leigh Daynes (1) Abdurahman Sharif (1) Lynne McMahon (1) Richard Caulfield (1)
Less +++ More +++

Look before you leap when it comes to cloud technology

7 Jul 2014

If you are going to invest in cloud technology, John Tate has some words of advice – take time first...

How combining international aid and social media can transform Africa

18 Jun 2014

Social media is impacting life in Africa at breakneck speed, and it is not just the usual suspects Facebook...

Seven fun charity apps that engage with supporters

12 Jun 2014

Charities are doing exciting things with mobile apps. Kirsty Weakely takes a look at some that are great...

The Big Society Network saga needs further digging

25 Jul 2014

Richard Caulfield was suspicious about the independence of Big Society Network way back in 2010.

Society Diary: Bigot bingo and idle thumbs

25 Jul 2014

Our weekly round-up of outlandish and interesting information collected from the corners of the charity...

Film of the week: 100 days since 223 Nigerian schoolgirls were abducted by Boko Haram, by ActionAid

25 Jul 2014

To mark 100 days since 223 Nigerian schoolgirls were abducted by Boko Haram, ActionAid has carried out...