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.

It’s not the minister who counts

1 Oct 2014

Writing for Charity Finance magazine, which went to press before Brooks Newmark resigned as minister for...

Blending youth and experience

10 Sep 2014

Finance leaders need help from the younger generation to understand modern technology, says John Tate....

Newmark needs to understand unpopular fundraising methods

9 Sep 2014

The new minister for civil society will have to decide what he thinks about chuggers and telephone fundraising,...

Free eNews

Join the discussion

Twitter button

@CivilSocietyIT