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

Who's Who: John Tate