WHY BUILDING AN EXCEL SPREADSHEET IS LIKE BUILDING A SKYSCRAPER!

Excel spreadsheets are everywhere.  From business uses to personal uses .  Coaches using a spreadsheet to track their Little League’s scores and stats to massive ones used in economic modeling.  And then there are the business uses that can affect employee compensation or the decision to buy or invest or not.  One of the biggest and more recent  ‘horrors’ is the story of the ‘London Whale’s’ spreadsheet that is alleged to have led to the career end for a JP Morgan London trader named ‘Bruno Iksil’.  His trades allegedly leading to  a multi-billion dollar loss for JP Morgan Chase.

Quoting from the blog ‘Revolutions’,  the author refers to another blogger, James Kwak, a University law professor and co-founder of the ‘Baseline Scenario’ blog where he refers to the the use of a ‘Value at Risk’ (VaR) model that underpinned the hedging strategy “”operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another”” and that it should be automated but never was”.

It’s a complicated story but one that has received widespread and embarrasing publicity for Jamie Dimon, the head of JP Morgan Chase as he tried to explain how one trader could have exposed the bank to so much risk.  But for us, the real story is the SPREADSHEET story and my ‘skyscraper’ premise.

If you walk into a twenty story building, I doubt you wonder if the building will stand erect until you leave it.  None of us do.  But, do you know if the foundation was poured to standards, strengthened correctly and the materials used were not sub-standard.  It’s really impossible for you to know unless you were involved in some way in its construction.  But everything in that building’s walls and foundation were built one upon another.  In a similar parallel, a colleague of mine once reminded me that an airplane was built of millions of moving parts all supplied by the lowest bidder!

But what has all this have to do with spreadsheets?  I’ve seen statistics quoted that as many as 75% of all spreadsheets contain significant calculation or assumption errors.  The spreadsheet I’ve always been most comfortable with is one I built, cell protected, and could generate an answer for me that I could recognize as being reasonable.  Beyond that, they scare me to death.  So here are some of my ‘Best Practices’ that Controllers should consider:

  •  Just because you, or someone else, loves building spreadsheets is not a good enough reason to build it
  • Avoid whenever possible using spreadsheets to actually run a part of a your business, like recording sales, or managing inventory, accounts receivable, order entry, etc.
  • Learn how to use Excel’s spreadsheet auditing tools. I can’t emphasize this enough.
  • Find a way to test a spreadsheet.  Perhaps manually figuring the answer and then seeing if you get the same result from your spreadsheet.
  • Look at your inventory of spreadsheets.  Identify which ones are critical.  (used to calculate bonuses, compensations, commissions to be paid and ones that lead to decisions are an example)
  • Critical spreadsheets should be ‘tested’ for accuracy.  Have they always had the formulas protected?
  • Think of a spreadsheet like a ‘house of cards’ (no, not the Netflix show, but I do love it) where only one minor shift in balance can bringing it all crashing down.  The thing is, in a ‘House of Cards’ you can see the cards falling.  You can’t in a spreadsheet with severe defects.  As you add layers of complexity, test, test, test.
  • Even if you’ve been out of college for only ten years, that course you took for one semester, did not teach you all you need to know.  Be a student of spreadsheets.  (See Below)

Once, while working as a Senior Financial Analyst for a publicly held corporation, and working a lot in the acquisitions area, I was given a spreadsheet to work with that had been designed by someone earlier, some years before.  No one knew who the author was and there was absolutely nothing documenting how the spreadsheet was put together or how it should work.    In the workbook, there were  numerous tabs all feeding up to the main sheet.  If you estimated capital expenditures over a period (and this was before we ever saw bonus depreciation) a depreciation sheet projected  how much depreciation expense the acquisition might incur for that year.  The model was based on a 10 year forward projection of net income and if return on investment exceeded your hurdle rate (cost of capital).  It was a nightmare because I was so uncomfortable with its reliability and struggled to see how entering one number might replicate through the entire spreadsheet.

There are tons of books out there on spreadsheeting.  Want to really learn how to use macros?  There’s a book for that.  Want to create a ‘breakeven analysis’ there’s a book for that.  Who is my ‘go to guy’?  Carlton Collins.  I first met him through an AICPA course.  He is one of the most knowledgable guys anywhere on Excel.  Just google him or, if you subscribe to The Journal of Accountancy, you’ll find him in the ‘Technology Q&A’ section.  He’s also served as an adviser to Microsoft on matters regarding Excel.  He’s a CPA and he understands very well your challenges.

Finally, the absolute worst thing you can find out is that the commission dollars paid to a salesforce and calculated by a spreadsheet you inherited has overpaid them by 2 or 3% (or worse) for the last five years and you didn’t catch it.  Probably some salesman complained he was shorted on his commission and when the accountant began checking into it, they found that actually that sales person plus 20 others have been overpaid.  Believe me, it can get much much worse than that.  When it comes to spreadsheets, as they say, ‘TRUST, BUT VERIFY”.

SDF Applications

Exceptional Apps.

Dennis Dean Smith

Cross From Your Ideas to Success

Crochet with Raymond

The mad adventures of a lesbi@n hooker and her siamese cat!

The Office Blend

ALL THINGS WORK LIFE

Law At Work

HR, employment, and health & safety advice in Jersey and Guernsey, Channel Islands

Live to Write - Write to Live

We live to write and write to live ... professional writers talk about the craft and business of writing

The Daily Post

The Art and Craft of Blogging

Tech

News and reviews from the world of gadgets, gear, apps and the web

TED Blog

The TED Blog shares interesting news about TED, TED Talks video, the TED Prize and more.

Matt on Not-WordPress

Stuff and things.

Philadelphia Estate and Tax Attorney Blog

Philadelphia tax, estate, corporate, small business attorney providing IRS tax representation, estate planning & administration, small business, probate, wills, trusts, tax planning

WordCamp Central

WordCamp is a conference that focuses on everything WordPress.

Leadership Freak

Empowering Leaders 300 Words at a Time

Late Blooming Entrepreneurs

Making it big in business after age 40

Small Food Business

The Soup to Nuts Resource for Artisan Food Entrepreuers

FUEL LINES

A voice for private company controllers