Spreadsheet Hell!

Discussion in 'Accounts & Finance' started by rlellie14, Jan 5, 2018.

Thread Status:
Not open for further replies.
  1. rlellie14

    rlellie14 UKBF Newcomer Free Member

    21 0
    Sorry, bit of a long one this...
    I started a new job as FD recently in a manufacturing/assembly business in the arts and crafts sector. Nice business and lovely people but my predecessor was a spreadsheet wizard who has developed an incredibly complicated series of spreadsheets to run the business, each with many links to many other complex sheets. Eg. the management accounts spreadsheets has approx. 30 tabs, some tabs have 3,500 rows, some columns extending out to DF and beyond. I did an exercise today to see where the YTD Forecast Sales figure came from and the source data was 14 (FOURTEEN) cells away (via 4 different spreadsheets and 6 different tabs). I'd put a diagram of it on here but can't seem to upload pics. You're lucky, it would give you a headache.
    3 people have access to the spreadsheets so you can imagine the risks. Every time something doesn't add up people pour over it for hours trying to find the error.
    My solutions seem to be:
    1) Try to get my head round it. Given that a senior member of the team has spent 4 years there and still doesn't fully understand it I don't think I have much chance of that in the near future.
    2) Get outside help/consultants to try to make sense of it. But that's a full time job and I can't justify that.
    3) Rip it up and start again. But I need to keep produce meaningful info with comparatives in the meantime.
    Option 3 is probably best for me and I just say to the board 'tough, you're not getting any info for the next 2/3 months while I straighten out this nonsense'.
    Does anyone have any other suggestions? I know I'm FD but even though I have a lot of experience I've never come across this level of unfathomable complexity! (even in all the companies I visited as an auditor many years ago).
    Posted: Jan 5, 2018 By: rlellie14 Member since: Oct 24, 2011
  2. Scalloway

    Scalloway UKBF Legend Free Member

    17,071 3,654
    I would certainly consider starting again from scratch. There is nothing worse than trying to understand a poorly documented spreadsheet.
    Posted: Jan 5, 2018 By: Scalloway Member since: Jun 6, 2010
  3. STDFR33

    STDFR33 UKBF Big Shot Free Member

    4,690 1,263
    I’d start again.
    But I’d start again from at least one of my predecessors periods.

    Then I’d reconcile my figures against there’s and identify any discrepancies.

    You could wventually work out what is happening within the spreadsheet to move forward. But if something breaks, you aren’t in a position to identify it and notice the error(s) unless it is obvious.

    Edit: my predecessor was also an excel wiz. When I came to doing my bit of the stat accounts, I found a £750k understatement on the previous years leasing costs. The reason? A complex formula, but not looking up the correct original cell and not dragged down to all cells.
    Simple is underated!
    Posted: Jan 5, 2018 By: STDFR33 Member since: Aug 7, 2016
  4. soundengineeruk

    soundengineeruk UKBF Regular Free Member

    375 66
    Sounds like option 3 is probably the right answer. If I was given a complex piece of source code that would take as much time to read it than recreate it; I would always recreate it to something that is simple and readable.

    The problem with using spreadsheets is that it only takes 1 thing to change, then boom! the information is all over the place.

    From initial post this kind of data would probably be best suited to be stored in a database rather than spreadsheets. Use spreadsheets to pull data from the raw database rather than have the raw data stored in the spreadsheet.
    Posted: Jan 7, 2018 By: soundengineeruk Member since: Jul 25, 2012
  5. GlennMiddleton

    GlennMiddleton UKBF Contributor Free Member

    46 4
    I am not a fan of spreadsheets, they are an aid but shouldn't be used to run a business. Accounting packages should be the epicentre of the financial control within the business.
    Posted: Jan 7, 2018 By: GlennMiddleton Member since: Jan 6, 2018
  6. Chris Ashdown

    Chris Ashdown UKBF Legend Free Member

    12,194 2,542
    Buy the software probably Sage asap

    Hire two or more data import staff on a short term contract and get them to restart importing all this current years records

    Fumble on with the old system until you can change over
    Posted: Jan 7, 2018 By: Chris Ashdown Member since: Dec 7, 2003
  7. MyAccountantOnline

    MyAccountantOnline UKBF Legend Free Member

    13,474 2,537
    I share that view.

    Many years ago I took the role of Financial Controller of a large country estate which had relied on spreadsheets. When my predecessor left it quickly become apparent that the only person who had the slightest clue of what the spreadsheets meant was the former Financial Controller but everyone was too embarrassed to admit they didnt really understand them and the monthly sheets being churned out were of almost no use to anyone.
    Posted: Jan 8, 2018 By: MyAccountantOnline Member since: Sep 24, 2008
  8. UK Contractor Accountant

    UK Contractor Accountant UKBF Legend Full Member - Verified Business

    5,335 966
  9. Alan

    Alan UKBF Legend Full Member - Verified Business

    6,772 1,873
    This - otherwise you will have to live with it until it drives you mad

    Posted: Jan 8, 2018 By: Alan Member since: Aug 16, 2011
  10. Nord20

    Nord20 UKBF Regular Free Member

    526 117
    I agree with everyone else - option 3 is really your only option, but maybe you can run with the existing spreadsheets whilst you develop an alternative? I can't say either way, but not understanding how it works is not always a barrier to using it, if there is reasonable confidence in the results it churns out.

    I would stop to think about the best solution going forward that doesn't just re-create the problems you're facing now (so don't replace it with a spreadsheet of your own creation that your successor is going to have to ditch because they can't understand it).

    Accounting packages are literally designed for this stuff, use one!
    Posted: Jan 8, 2018 By: Nord20 Member since: Mar 8, 2011
  11. Daugela

    Daugela UKBF Contributor Free Member

    82 11
    I agree with the above, a new file would make everything more transparent.
    Posted: Mar 10, 2018 By: Daugela Member since: Jul 15, 2017
  12. Financial-Modeller

    Financial-Modeller UKBF Ace Full Member

    1,014 394
    I have been confronted with this many, many times and its always better to start again and do it properly, and I have done so, successfully for clients many, many times. This is partially because a workbook was usually built by somebody for a specific purpose, and has been extended by different people for different purposes, with differing levels of knowledge of what has been done prior, and what they are trying to achieve, so that the final result is unwieldy, impossible to audit, and no longer fit for purpose.

    My approach would be:
    1. sort out your actual current and future requirements as FD, which may be quite different from the requirements initially, and will likely be populated in a different way to the existing version.
    2. specify what you actually need based on those requirements so that you have full transparency on what you'll end up with, and its cost. As part of any build, include auditing capability, KPIs etc to quickly alert users to input errors, typos etc.
    3. build it, with the opportunity to for you to test it in your user environment and make changes if required, as almost everybody decides that they want extra functionality during the build once they see something that looks great in front of them.
    4. handover to you or your designated staff, with automated reporting where possible to efficiently handle monthly reporting etc on an ongoing basis.

    I guess you have sorted something out by now but let me know if you would like to discuss.
    Posted: Mar 13, 2018 By: Financial-Modeller Member since: Jul 3, 2012
Thread Status:
Not open for further replies.