Consulting

Results 1 to 16 of 16

Thread: Darwin awards for pathetic Spreadsheet Design

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

    Darwin awards for pathetic Spreadsheet Design

    In my job I get to look a lot of big evaluation models. I see many, many, many bad ones .... to the point where I open them up, say "$%#$%^" and anyone who overhears me knows what I am looking at. But in the last fortnight I have seen two complete disasters and I need to share/vent/lampoon before I endure any more pain.

    The contenders for Spreadsheet nightmares of November 2005 are:
    1. The Array Formula nightmare.

      Every single formula in a 165 worksheet was entered as an range entered array. Even a formula like A1+A2 would be entered as A1:S1+A2:S2 ...... meaning that no formulas could be entered in isolation, no columns could be inserted etc. I won't even begin to mention the 5500 range names, 113 macros, the 50 hidden sheets or the 30Mb file size.
    2. The INDIRECT VLOOKUP nightmare.

      INDIRECT is used to feed in an entire sheet as the lookup range - for around 1000 calculations. I wonder why that model was sent to me with calculation turned to manual.
    Other entries welcomed and encouraged

  2. #2
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Dave,

    Hm, this is the major drawback when developing have been made by non-developers.

    I've taken part of so called applications with no documentation and with filesizes of upto 80 MB with the comment "Please, can You see why this no longer work and solve it for us". In general I try to convince the customers to drop it and let me create new solutions.

    In my experience 100 % of all the files over 2-3 MB or sometimes even less have major errors in that they calculate wrong. I can live with poor design etc but never accept that a spreadsheet contain pure calculation errors. After all, it defeat the purpose why we use spreadsheets.

    Now to my question: Why do You review disasters like the one You do?

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  3. #3
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by XL-Dennis
    Dave,
    Hm, this is the major drawback when developing have been made by non-developers.

    In my experience 100 % of all the files over 2-3 MB or sometimes even less have major errors in that they calculate wrong. I can live with poor design etc but never accept that a spreadsheet contain pure calculation errors. After all, it defeat the purpose why we use spreadsheets.
    IMO Good spreadsheet design is all about transparency, consistency and text explanatons. Non developers can produce very good models - one of the functions of my coporate group is to set, maintain and teach these principles. And we actively discourage "complex" Excel model feaurues such as VBA, there really is no need for it in an evaluation model

    Quote Originally Posted by XL-Dennis
    Now to my question: Why do You review disasters like the one You do?

    Kind regards,
    Dennis
    Its part of my job All significant investing decisions are reviewed by the group I work in

    I bounced back the first model to the CFO of that business unit and I told them it had to be rebuilt from scratch

    No such luck with model #2. It came from an external party - we will strip out the gumph and rebuild it into a well laid model of less than 1 MB .. with calculation on.

    My capacity to be amazed at very smart people building very poor models is yet to be exhausted

    Cheers

    Dave

  4. #4
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Hey - I'm one of those guys who make crap spreadsheets! Think about my feelings guys!!!

    But I'm learning.
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,292
    Location
    Quote Originally Posted by Babydum
    Hey - I'm one of those guys who make crap spreadsheets! Think about my feelings guys!!!

    But I'm learning.
    Yeah, but at least you have the sense to ask us to bale you out before you completely blow it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Dave,

    IMO Good spreadsheet design is all about transparency, consistency and text explanatons. Non developers can produce very good models - one of the functions of my coporate group is to set, maintain and teach these principles. And we actively discourage "complex" Excel model feaurues such as VBA, there really is no need for it in an evaluation model
    I agree with Your general sentiments and I'm interested to learn more what evaluation models You're talking about. Bear in mind that I take a general broad view while You view it from Your desk so to speak

    I'm not sure that despite a general strong support, clear documentation, live support, courses and etc it always create good models.

    If the creators don't understand the underlying (business) processes the models are supposed to reflect then I do not necessarily see the output as good. It can be acceptable but not per se good.

    In addition, if the conditions are good and You discourage complex Excel-models etc why does these "disasters" even exist?

    Would the use of another tool assure a more consistent outcome of the modelling?

    Dave - I'm just curious about the circumstances etc as I see a new source for learning more about behaviour etc

    Kind regards,
    Dennis

    BTW, I hope You're well paid in order to "cleaning up"
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  7. #7
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by XL-Dennis
    In addition, if the conditions are good and You discourage complex Excel-models etc why does these "disasters" even exist?
    I'm going to guess...

    In the Excel world, there are so many tools available to do things. Couple that with the fact that, in my experience, so few people are really trained on how to use them correctly.

    The biggest single issue, though, is that so many people don't realize that what they have built is not easy to audit, maintain, or understand for someone else. Why? Because they're so deep into it, they know how they built it, and they've always done it that way. And possibly it also starts as a project for their use only.

    As an aside, I'll bet that the person who built an entire sheet of array entered formulas thinks that they're quite clever, actually! LOL! (Boy did it ever stump me the first time I ran into one of those!)
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #8
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    I just got done reviewing a business plan that a client was ready to take to his banker. He had listed his employees, their rates, and their expected hours in his payroll projection area. Unfortunately, he had used these numbers to calculate a weekly wage (a good number) then multiplied his weekly by 4 for a monthly number (bad) then multiplied that by 12 for an annual number (really bad). I reworked this and some similar problems for him and then discussed why he needed to ask for more capital.

    I think he thinks I am the now.

    Not quite the caliber of Dave's experience, but nonetheless frustrating.
    Brandtrock




  9. #9
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Unfortunately I can't for the life of me find this link, although I wish I'd bookmarked it at the time...

    About 2 weeks ago I read an article online which stated that according to some survey over 80% of spreadsheets contain mathmatical errors. This has huge implications for Oxley Sarbanes reporting. Oxley Sarbanes aside, just think of the implications of that...
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  10. #10
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Here is an article that addresses the topic.

    The conclusion to the lengthy article:
    Conclusion

    All in all, the research done to date in spreadsheet development presents a very disturbing picture. Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable in any organization. These error rates, furthermore, are completely consistent with error rates found in other human activities. With such high cell error rates, most large spreadsheets will have multiple errors, and even relatively small "scratch pad" spreadsheets will have a significant probability of error.

    Despite the evidence, individual developers and organizations appear to be in a state of denial. They do not regularly implement even fairly simple controls to reduce errors, much less such bitter pills as comprehensive code inspection. One corporate officer probably summarized the situation by saying that he agreed with the error rate numbers but felt that comprehensive code inspection is simply impractical. In other words, he was saying that the company should continue to base critical decisions on bad numbers.

    A major impediment to implementing adequate disciplines, of course, is that few spreadsheet developers have spreadsheeting in their job descriptions at all, and very few do spreadsheet development as their main task. In addition, because spreadsheet development is so dispersed, the implementation of policies has to be left to individual department managers. While organizations might identify critical spreadsheets and only impose hard disciplines on them (Panko, 1988), this would still mean that many corporate decisions would continue to be made on the basis of questionable analyses.
    Brandtrock




  11. #11
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I nominate myself for this type of error on a much larger spreadsheet (many years ago I hasten to add)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  12. #12
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by XL-Dennis
    If the creators don't understand the underlying (business) processes the models are supposed to reflect then I do not necessarily see the output as good. It can be acceptable but not per se good.
    Exactly right.

    When we present our in-house evaluation courses we make the point that the investment modeller isn't the junior person who sits in the back room and works for 24 hrs before project submission to get the job done.

    The modeller:
    • Is involved from day 1
    • Is recognised as a key member of the advocacy team
    • Has a very importnat role to play as the networker between all the functional groups (marketing, capital cost estimators, production planners, group tax, HSEC etc) which may not otherwise talk to each other
    • Must understand the business process from start to finish
    • Must make the investment proposal transparent
    The evaluation model is the end part of the submission process. The really important work is done in the problem framing (what is the issue), the strategy table (what major choices are we making) and then the range analysis. All three steps are major workshops involiving all team members and I say its really important as this is where the GIGO applies - great model is useless with either crap data or the incorrect investment being put forward (not as far fetched as it sounds)

    The next important step is the review stage conducted by an independent group of experts. But I'm drifting a little from the Excel part

    I actually started writing code in 2001 so that I could more readibly format and audit Excel models

    Cheers

    Dave

  13. #13
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by kpuls
    I'm going to guess...

    In the Excel world, there are so many tools available to do things. Couple that with the fact that, in my experience, so few people are really trained on how to use them correctly.

    The biggest single issue, though, is that so many people don't realize that what they have built is not easy to audit, maintain, or understand for someone else. Why? Because they're so deep into it, they know how they built it, and they've always done it that way. And possibly it also starts as a project for their use only.

    As an aside, I'll bet that the person who built an entire sheet of array entered formulas thinks that they're quite clever, actually! LOL! (Boy did it ever stump me the first time I ran into one of those!)
    Bingo 2

    That 165 sheet model that still causes me to swear had 50 hidden sheets because they were old redundant scenarios that weren't cleaned up or removed. And much of the visible logic wasn't necessary. Models need a revamp every now and then to clean out the non -core crud

    And the guy who built this knew a lot about Excel, very smart guy. But its virtually unusable for anyone else - so he failed.

    Cheers

    Dave

  14. #14
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by brettdj
    But its virtually unusable for anyone else - so he failed.
    There is briliance in your bluntness, you know that!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  15. #15
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by kpuls
    There is briliance in your bluntness, you know that!
    By Australian standards I'm regarded as touchy-feeely. ROFL

    And Malcolm - nice example.

  16. #16
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Dave,

    AlthoughI got some more questions I think I know some explanation that gives the background of the disasters You face although not fully explain them.

    Per se I believe that the mix of end-user and developer's software we see in Excel et al is part of the issue and I wonder what will happen when there will be 1.100000 rows in each sheet in the next coming version...

    Thanks Dave for taking Your time to give a broader and deeper insight of the processes You're involved in

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •