Eusprig 2007

The theme for this years conference is Enterprise Spreadsheet Management: A Necessary Evil

I would like to submit a management paper from a developers perspective for consideration. I have a few horror stories I can draw on, but I was wondering if any of you folks would like to contribute too?

I’m looking for funny/scary real life situations you have seen involving spreadsheet management (or mismanagement). Or any strong views you have based on experience in the trenches. Heres an example (which needs shortening):

At one company the whole monthly management reporting for 120 business units was driven by an Excel macro one of the consolidation team had recorded/cobbled together. This was a closely guarded treasure and outside interference was not welcome, even though this imposed a significant burden on its owner each period end, especially as it failed most months. As this was an 8 hour process that ran overnight, any failure meant all financial reporting was delayed at least a day. It also meant a long night at work for its owner, as well as a requirement to fit holidays (and sick time) around the reporting cycles. Such key man dependencies are completely standard, and very real. One unanticipated absence instantly caused a 24 hour delay as cover staff battled to understand the contorted logic. The usual failure on the first run put reporting back another 24 hours.

I’m thinking more around the management of the on-going use of a spreadsheet, rather than the initial development, but I do have some other questions:

  1. What is an average build time to life time (for me 3 months build, 1-3 year life, but wide variation)?

  2. Examples of impact of build quality on lifetime maintenance (me: one 2 hr job took 3 days because s/s was so bad)

  3. Examples of using wrong versions of spreadsheets (me: reported wrong year as ‘prior year’ for 4 months, due to wrong link)

  4. Examples of attempts to remove spreadsheets altogether (me : Oracle Financial Analyzer implementation that eventually died, nice idea wrong culture)

  5. Examples, with main benefits, of migrating from s/s to something else (me: moving to Essbase with all the power and security is my favourite, although Access or SQL Server are more common)

  6. And finally what do you think is the single most important issue facing enterprises with loads of spreadsheets scattered around their network? And how would you resolve it.

Don’t feel like you have to answer all (or any), don’t betray any confidences or break any NDAs. But if you can help put the commercial developers view across I would be most grateful. How grateful? Well you’ll get a mention in the acknowledgments section, or maybe as a reference. If you want to remain anonymous feel free to email me. I’ll put the paper up for comment as soon as its done. Its a 2,000 word limit so I may have to edit any wordy contributions. And of course the paper may not get accepted.

Oh, and we don’t have long, the deadline is the 28th Feb! (thats this Wednesday so contributions by end of Tuesday (latest of your time and UK time) please).

Cheers

Simon

 

Advertisements

9 Responses to “Eusprig 2007”

  1. Anom Says:

    This is a true story of a large listed company.

    It used close to 20 linked spreadsheets to prepare monthly budgets for the board. The users then saved a copy of all the spreadsheets for each month in a separate folder. Why? Because there was a circularity, and the only way to get the same answer as the board papers was to keep a copy and never recalculate it!

  2. Ross Says:

    I was contracted to design a reporting tool. The tool pulled data from an SPSS data source and produced various reports, charts and presentations which where e-mail to people around the world. I was retained to do a similar job for another reporting function, my first tool had worked (more or less) flawlessly for about 7 months. Sadly the 3 party who complied the data decided to change the format of the DB and not inform anyone our end. The results was a very embarrassed senior manager having to recalling a set of dashboards, and (more importantly for me!) a data analysis hiding on the second floor avoiding previously mentioned embarrassed and very angry manager!

    I think there are more checks in place now!

  3. Marcus Says:

    Worst use of s/s:
    Stock broker/analysts had 40 analysts who each on @ 10 stocks, each stock’s detailed was maintained in a separate s/s (i.e. @ 400 s/s). They then needed to identify which stocks met certain criteria. Example: Which companies have a market capitalisation > 500M with a P/E ratio of

  4. Marcus Says:

    Worst use of s/s:
    Stock broker/analysts had 40 analysts who each on @ 10 stocks, each stock’s detailed was maintained in a separate s/s (i.e. @ 400 s/s). They then needed to identify which stocks met certain criteria. Example: Which companies have a market capitalisation greater than 500M with a P/E ratio of less then X. Nightmare. They would have bee far better off with a relational solution.

    1. Average build time to life time
    Varies but probably in 1 to 6 mth range. Longer projects are affected not by developing the s/s per se, but the overhead of bureaucracy, data sourcing issues (esp.), external vendors etc. Life span is usually well over 3 years.
    One reporting solution for an investment bank is still in use today (developed in 2000-2001). About 5 months to build.
    Another model to help manage stock movements for a national food company lasted 3 years before being migrated from 1-2-3 to Excel for another 3 year life. Each took @ 2 months to build.

    4. Attempts to remove spreadsheets altogether
    Business Objects implementation at major bank. Regardless of the s/s report being replaced, users insisted that each B.O. web report have an ‘Export to Excel’ functionality. Users would then retrieve the report on the intranet and then immediately export to excel for further enrichment.

    5. Migrating from s/s to something else
    Migrated series of 110 reports from Access/Sybase environment to Hyperion Essbase (and then later to MSAS). Pros: speed of generating reports from (@ 14) cubes. More intuitive nature of generating new reports. Biggest pro: cleaning up the data! Biggest con: expense – not many companies would justify the expense.

    6 Single most important issue facing enterprises with loads of spreadsheets.
    No.1. Versionitis – multiple people managing multiple versions of the truth. Where’s SoX in all this?
    No.2. Data Control – insufficient mechanisms to ensure data integrity and security. (I rarely develop s/s models now which store data in the s/s, I nearly always use a RDBMS).
    No.3. Data Redundancy – Huge numbers of replicated s/s emailed around bloating email servers.
    No.4. Spreadsheet duplication: I deal with many Co’s who have 10’s of thousands of s/s. One audit 6 years ago at a utilities Co showed 25,000 s/s on the server, 80% of which had not been modified in the prior year. Meanwhile huge amounts goes into backing these things up and maintaining in a server environment. If they were paper based, the Co would have shredded or drowned in paper.

    Cheers – Marcus

  5. Simon Says:

    Thanks folks
    These are exactly what I am looking for!
    anyone else?
    cheers
    Simon

  6. Dennis Wallentin Says:

    Simon,

    I think most ‘horrible’ cases still are under NDAs (It’s common to have a 5 years NDAs in my part of the corner).

    In addition:
    Large workbooks —> Calc Errors built in with outdated data
    Lack of documentation of solutions that are used for critical business aspects —> On updates it takes more time then necessary and
    Links, Links and Links —> I’ve seen too many poor designed solution due to the existence of Links.
    “Don’t touch my baby”-syndrom is too many times a source to prevent to implement improved solutions.

    Inhouse created solutions are ‘nightmares’ for external Excel consultants.

    I got some ‘sunshine’ stories where Excel solutions are superior SAP-systems but that’s another aspect ;)

    Kind regards,
    Dennis

  7. Simon Says:

    Dennis
    Good point about the ‘sunshine’ stories, its easy to lose sight of the benefits when focusing on the risks.
    cheers
    Simon

  8. MikeC Says:

    Just an addition to Marcus’ comment “6.3”

    “No.3. Data Redundancy – Huge numbers of replicated s/s emailed around bloating email servers.”

    In addition to this, it’s very common for every single recipient to separately save their copy of the attachment onto the network, leading to even greater waste of resource.

    (I will try and put something on before you “close off” this subject, even though I know I’m late already!!)
    Cheers
    Mike

  9. Ed Health Says:

    Doctors do think that acupuncture does lead to an increase in the number of sperm count found in the body. They also said that this could even help improve very slight structural defects. So why doesn’t everyone jump on board this acupuncture band wagon? Why aren’t all lower sperm count men out there looking into this? This has something to do with the other methods that are out there.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: