1 / 27

Management Paper – Commercial Spreadsheet Review

Management Paper – Commercial Spreadsheet Review. Simon Murphy simon.murphy@codematic.net Developer – Codematic Ltd. Spreadsheet background. Up to 200 Mb size Up to 1 Million formulas 1-10,000 unique formulas 5-10,000 lines of VBA £1M to 100’s of Billions in values

parkerf
Download Presentation

Management Paper – Commercial Spreadsheet Review

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Management Paper – Commercial Spreadsheet Review Simon Murphy simon.murphy@codematic.net Developer – Codematic Ltd

  2. Spreadsheet background • Up to 200 Mb size • Up to 1 Million formulas • 1-10,000 unique formulas • 5-10,000 lines of VBA • £1M to 100’s of Billions in values • Often linked to other technologies such as OLAP, ADO, COM or .net etc. • Finance, Banking and Sales and Marketing areas

  3. Agenda • Introduction • Scope of Review • Stages of Review • Spreadsheet in context • File Review • Workbook Review • Sheet Summary • Inter Sheet Interactions • Cell/Block level Relationships • Summary • Conclusion • Any Questions?

  4. Introduction - Spreadsheet Quote Program Manager to Steering Committee: “We can build this system in – a large consolidation software package –, it will take 2 developers 6 months and cost half a million. Or 1 developer can do it in Excel in 10 weeks for 30 thousand.”

  5. Introduction - Spreadsheet Definition • 2006 - Organisational • Spreadsheet – the product of one or more people following some sort of process to a deadline. • 2005 - Technical • Spreadsheet – powerful and flexible, single end user, analysis and presentation tool, optimised for speed of initial development.

  6. Scope of Review • The review is pre-sales (i.e. ‘free’) • The aim of the review is to estimate the time to repair, enhance, or migrate the spreadsheet. • Totally NOT any sort of audit. • Target time is 2-4 hours. • Explicit assumption the spreadsheet is fit for purpose currently. • Assumes no useful documentation.

  7. Spreadsheet in Context • What is it used for? • How important is it? • How connected is it? • Is network access required?

  8. File Review • Virus scan • Workbook file properties • Remove protection

  9. Workbook Review - 1 • Open with VBA disabled • Check any VBA code (and XLMs) for security issues

  10. Workbook Review – 2 • Review summary metrics

  11. Workbook Review - 3 • Run XLAnalyst for brief summary • High score may make review harder

  12. Workbook Review - 4 • Glance at unique formulas

  13. Sheet Summary • Once sentence summary (if possible) of what the sheet does. • Added to navigation sheet

  14. Inter Sheet Interactions • Data flows • Left hand are pure children/source sheets • Right hand side are unconnected (by basic formulas)

  15. Inter Sheet Interactions

  16. Inter Sheet Interactions

  17. Inter Sheet Interactions

  18. Cell/Block level Relationships • Review named ranges • (Free tool - Google for NameManager)

  19. Cell/Block level Relationships • Quick value type overview

  20. Cell/Block level Relationships • Review the actual formulas for shapes • Finally work though a few formulas

  21. Cell/Block level Relationships • Real world flows

  22. Cell/Block level Relationships • Real world flows

  23. Some Fun Formulas • =IF(AND(AND(AND(AND('s29'!AK50=0,'s29'!AK51=0,'s29'!AK54=0,'s29'!AK55=0)))),"N","") • =IF(AND($E$45="a",AF9>0),AK45,IF($E$45="M",AK45/12*$E$9/30,IF(AND($E$45="Q",AF9>0),IF(AK50=12,AK45/4,IF(AK50=11,AK45*3/11,IF(AK50=10,AK45*2/10,0))),AK45*0))) • =IF(AND(I89<>0,I88=0,SUM($U$88:U88)=0),1,IF(OR(ISERROR(I89),NOT(ISNUMBER(I89))),"Error",0)) • =+B49*(0.173076923076923) • =+E27*SUM(D5/G5) • =SUM('PayEx'!$U5*'PayEx (Annual) '!$J$5)*(100%+'% Increase in x'!CH7) • =G43*1000/G83

  24. Last one • =E111+E118+E119+E120+E121+E122+E123+E124+E125+E185+E186+E187+E188+E189+E190+E191+E216+E217+E218+E219+E220+E222+E223+E227+E233+E234+E240+E241+E246+E468+E469+E470+E471+E472+E475+E533+E534+E535+E536+E537+E540+E579+E580+E581-E257-E252

  25. Summary • Brief overview of one commercial review process • Aim to drill into a spreadsheet one level at a time • Target is to make changes to a live spreadsheet without introducing new errors

  26. Conclusion • Using a few basic tools and a structured approach it is possible to gain a reasonable understanding of a spreadsheet within commercially viable timescales.

  27. Questions? • simon.murphy@codematic.net • Spreadsheet consulting, reviewing, maintaining, rescuing, migrating, add-in development etc. • Websites • www.codematic.net • www.xlanalyst.co.uk

More Related