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

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

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

Hey - I'm one of those guys who make crap spreadsheets! Think about my feelings guys!!! ;)

But I'm learning.

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.

Dave,

Quote:

---

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" ;)

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!) :bug:

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 :devil: now.

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

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

Here is an article that addresses the topic.

The conclusion to the lengthy article:

Quote:

---

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.

---

I nominate myself for this type of error on a much larger spreadsheet (many years ago I hasten to add) :blush

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

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!) :bug:

---

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

Quote:

---

Originally Posted by brettdj

But its virtually unusable for anyone else - so he failed.

---

There is briliance in your bluntness, you know that! :rotlaugh:

Quote:

---

Originally Posted by kpuls

There is briliance in your bluntness, you know that! :rotlaugh:

---

By Australian standards I'm regarded as touchy-feeely. ROFL

And Malcolm - nice example.

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...:whistle:

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

Kind regards,
Dennis