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”.

EXIT INTERVIEWS – DON’T MISS THE OPPORTUNITY

It’s one thing if you are running a Hot Dog stand across the street from a college.  You realize that pretty much anyone working for you is not going to spend fifty years with you and retire with a gold watch.  Turnover is to be expected.  I’m going to assume as a controller you are involved in a fairly large organization.  Today, as you well know, employees (and you too) are performing a lot more tasks and expected to do much more than in past years when labor was so much cheaper.

I recently spent 13+ years as controller with a fairly large accounting staff.  One thing stood out.  There was a steep learning curve for every new employee on the staff and for me as well.  It took over three years on the job for me to start feeling comfortable with what I was doing.

When an employee decides to resign, you, as Controller, will probably not be the first one to know this (even if the employee tells you that).  Most likely the employee has become dissastified (even disgruntled) and has decided to seek another job.  Generally, by the time you find out,  it’s already well known on the grapevine at your office.   For that reason alone, you should try to keep an open door policy where your staff members feel comfortable coming to you with a problem.    Losing a  trained employee does impact you as the Controller.  Your boss (CFO, Owner, etc) is not going to be the one doing the training, coaching, mentoring  required to get the new employee up and running.  You are.

When an employee leaves, it requires a ‘root cause’ analysis.  Why after all the training, pay, benefits, bonuses, etc. is this employee unhappy enough to leave.  Do not let them get away without someone performing an EXIT Interview.  In some companies, HR may conduct that interview.  This is really unnerving to a lot of managers because they have no idea what the employee will say.  I would hope that HR would share this information with you, in total.  Let’s remember, some employees do have built in issues with authority figures.  I’ve run into a few  and eventually, they leave for supervisory positions if they can find one.

If you are allowed to conduct the EXIT interview, here are some of the questions I would ask:

  • What issues have led you to the decision to leave?
  • Were you offered this job or did you actively seek a new job?
  • Were you uncomfortable coming to me to ask for a raise?
  • How would you rate your training for the job you held?
  • How would you describe your working relationship with your co-workers?
  • Were you sufficiently challenged in your job or were you bored?
  • Were you overloaded with work or did you feel you were underutilized?
  • Did I provide you with enough feedback about how you were doing?
  • Did you have the resources you needed to do your job?

Painful as it might be, you need to gather as much information as you can to find that ‘root cause’ even if by chance, you might be part of the problem.

I wonder how many of you have been able to make a counteroffer and if you were able to, did you?  Unfortunately, countering with more money is a double-edged sword.  The employee is temporarily happy but they are going to wonder if you could pay them more, why did they have to threaten to quit to get a raise.  Has your trust diminished in that employee.  Will they threaten to leave again?  I’m not sure.  Every case is different.

You Should be Reading This………

Here’s a new book I’m adding to my Recommended Business Reading List:

SCALING UP EXCELLENCE by Robert I. Sutton & Huggy Rao.   When you get Tom Peters to write a recommendation quoted on your back cover…..well then this is a book worth reading.

THE NEW AMERICA FOR CONTROLLERS

The Wall Street Journal recently printed a front page story that also filled up almost an entire back page about the state of employment for men in America.  Sadly, more men remain out of the workforce than women and for much longer periods.  One recurring theme among formerly employed executives was the admission that they had not kept their skills up to date.  We are in the throes of a structural labor market shift.  Yes, the 2008 crash precipitated it but hidden behind that curtain is the fact that companies are still trying to employ technology to replace employee overhead costs.  That’s why middle management positions are under so much attack and I believe will continue to erode over time.

For those of you with designations like CPA or CMA, there are requirements for continuing education.  For a CPA in my state, it’s 40 hours per year with a required 8 hours minimum of Accounting & Auditing.

If you are not a licensed CPA or you are under no requirement to maintain a license requiring annual hours of CPE do not count yourself among the lucky.  And, if you are, I see so many CPA’s at seminars doing everything they can to ‘escape in place’, texting, playing solitaire on their I-pads, etc.  They exude an air of they are simply too busy (or too smart) to be there.

What attitudes and skills do you think are the most important as a Controller (especially those of you looking to move up) that you require to keep up in this rapidly changing technological world?  Here’s my list (in no particular order):

  • Understand Technology and what it can do for you and for your company
  • Technology is not your enemy because you do not understand it.
  • Not all Technology investment and spending is a waste of money

Recently, I visited a prospective client who had brought in a controller (they had gone through a succession of accounting people and controllers) to run their somewhat complex business.  He was definitely old school with stacks of paper on his desk bound up in rubber bands (lengthwise & crosswise).  The computer system was barely understood.  In effect, he was trying to take care of a number of employees in different roles without any knowledge on his part of the business.  He focused on  how much it was going to cost per hour for me to come in and assist them in some very specific areas where he lacked knowledge.  He couldn’t see the outcome for the cost.  I’ve never seen a good business save itself into a profit.

I have met many like him.  They are still depending on what they learned in business school to help them navigate waters that have shifted so diametrically over the last fifteen years or so as to be unrecognizable by someone coming out of college in the sixties or seventies.

The last thing you want to happen to you is to become the subject of C-Suite conversations about bringing in someone younger and more in tune to the current requirements of the business world.  I can hear you right now.  That’s age discrimination, they can’t do that.  Yes they can.  All they do is dangle a one, two or three year severance package in front of you.  In exchange you sign away your rights to bring suit against the company.  I’ve seen that scenario play out many times over with all sorts of personnel in sales and management.   However, I have also seen accountants five or ten years out of college who have no idea of what is going on in the world of accounting and finance.  They just keep their heads down and do no more or less than what they are told.  They are not on an upward sloping career path.

In closing, remember that when you improve your skills and knowledge, you improve the prospects for your company.  I always have an accounting or business book I am currently reading.  I look forward to CPE classes as an opportunity to learn more about my profession and the world it exists in.

Courses for CPA’s are always priced for members and non-members.  John Wiley and CCH are great sources for business books and writings as well as Amazon.  Read the Wall Street Journal often, every day if you can.  Join organizations like IOFM or IOMA.

I challenge you to invest in yourself and your future.  And remember, be relevant!

THE HP HORROR STORY – A LESSON ABOUT DUE DILIGENCE

According to today’s Wall Street Journal , in October 2011, HP (Hewlett Packard) purchased a U.K. company, Autonomy for $11 billion.  It doesn’t matter how big you are, that’s a lot of money.  Fast forward to 2012 (a year or so after the purchase according to WSJ) and HP took a write-down of $8.8 billion.  In my opinion, this is a staggering failure of due diligence.  The allegations  are that prior to the purchase, Autonomy had falsified its financial statements.  It is claimed that revenue was recognized for which there was no basis.  Purportedly, they claimed transactions for which there was no customer and other assorted falsifications.  I want to point that as stated in the WSJ, Autonomy has denied these allegations.

As Controllers, we are all subject to being involved in acquistions.  That can include new companies, buying out someone’s division, etc.  Even large capital investments like new software systems or a certain piece of equipment should be subjected to thorough due diligence/analysis prior to the purchase.

I know that many of us (including me) have a tendency to ‘fall in love’ with these acquistions.  We’re excited to see our companies grow and we recognize that new blood in our company may help revitalize it (and us).   The problem is that we are almost physiologically driven to ignore the bad news about an acquisition.

If you were a shareholder or any stakeholder, wouldn’t you want to know how this happened.  I think we already know that audited Financial Statements are no guarantee that they are not fraudulent.  One would hope that the materially distorted numbers were discovered, but as we have seen time and again, that simply is not the case.

Here are some of the questions I would raise if I was involved in the investigation:

1.  Did anyone look at their comparative financial statements for the last three to five years to see if their sales growth and profit made sense?

2.  Did anyone look at their sales reports to see how they tied out to their financials?

3.  How was the valuation of the company reached?  Obviously it was not worth anything related to the purchase price.  How could you take an $8.8 billion write down on a company you paid $11 billion for, and just a year ago?  That’s an 80% write down.

Controllers……………….you cannot let something like this happen to you.  In SME’s, we tend to be very proud of how quickly we can push through acquisitions versus our larger corporate colleagues.  But there is a downside to this.

So here is my advice based on past experience.

1)  I always liked to look at tax returns.  Very often they can raise questions you probably should have asked.

2) Assuming you are buying a business or division in your same industry, you should have some ‘instincts’ about what a reasonable set of financials should reflect.  Do the labor costs seem in line?  How much are they paying the officers?  Too little or too much?  If it’s not in your industry, resort to outside help…..someone who was familiar with the industry you are investing in.  If no one is available, there should be statistics/data out there that might assist you in your analysis.

3) With almost every acquisition I have been involved in, Year 1 after the purchase, expenses go up (sometimes dramatically) and sales go down.  Customers who may have felt some obligation to the ‘old’ company feel none towards the ‘new’ company.  Expenses go up because ‘old company’ decided to sell  two years ago and stopped investing and upgrading systems and assets.  Don’t forecast increased sales when you are putting together that ‘pro forma’ for the new company.  And don’t cut expenses because you think there will be economies of scale.  This advice generally holds true for most SME’s.  This is where that ‘love affair’ with your acquisition really kicks in.  Beware!

Finally – ‘TRUST BUT VERIFY’.  Your career may depend on it.

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