1 / 27

Spreadsheet Good Practice: Is there any such thing?

This article explores the best practices for using spreadsheets, discussing topics such as balance sheet lending, cash flow lending, modeling, training, and scenario management.

rsiegel
Download Presentation

Spreadsheet Good Practice: Is there any such thing?

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. David Colver Eusprig 2004 SPREADSHEET GOOD PRACTICE: IS THERE ANY SUCH THING?

  2. BALANCE SHEET LENDING • BALANCE SHEET £m • Buildings 1000 Other 500 Bank debt (800)Other (100) • Shareholders’ funds 600 Bank Corporation Adequate guide as to whether to lend Project

  3. CASH FLOW LENDING • BALANCE SHEET £m • Buildings 0 Other 0 Bank debt 0 Other 0 • Shareholders’ funds 0 Investors Bank SPC Useless guide as to whether to lend

  4. Modelling Training Audit PFI Advisory GROWTH Oil and gasMining UtilitiesProperty PFIPPP 1980 1990 2000

  5. DESIGN OUTPUT FIRST • HOW • Mock up schedules to be generated by the model • Page setup; titles; spacing • Headings, but no numbers • WHY • Can share with colleagues / manager right away • Acts as informal specification • Visible evidence of progress • Can print model neatly at any (unexpected) point in its development Outputs: BS Output: P&L Output: Cash flow Output: etc

  6. BUT... • TOO MUCH • NOT ENOUGH • SCIENTISTSRESEARCHERS • No idea of answer until they find it • PROJECT FINANCE • Standard financial statements • Each model looks quite like its predecessor • SAFETYCRITICAL • Need formal specification and test plan

  7. LONG FORMULA • =IF(OR(COLUMN()=Input!$B$272+1, COLUMN()=Input!$B$272+2), 0, IF(Input!$B$23>0, -MAX(0, MIN(-OFFSET($A$497:$BR$497, 0, COLUMN()-2, 1, 1)-OFFSET($A$497:$BR$497, 0, COLUMN()-1, 1, 1), MAX(SUM(OFFSET($A$497:$BR$497, 0, COLUMN()-3, 1, 1):OFFSET($A$497:$BR$497, 0, COLUMN()-4, 1, 1)), (Input!$B$23 >=2)*SUM(OFFSET($A$497:$BR$497, 0, COLUMN()-3, 1, 1):OFFSET($A$497:$BR$497, 0, COLUMN()-6, 1, 1)), (Input!$B$23 >=3)*SUM(OFFSET(($A$497:$BR$497, 0, COLUMN()-3, 1, 1):OFFSET(($A$497:$BR$497, 0, COLUMN()-7, 1, 1))))), 0)) • Complexity score: 60

  8. SEPARATE WORKINGS • REASONING • Long formulae are bad • We must break them up • But, intermediate results will clutter up output • Conclusion: Separate the intermediate results from the output • RESULT • BYPRODUCT • Related items next to each other Workings Outputs: BS Output: P&L Output: Cash flow Output: etc

  9. SCENARIO MANAGEMENT • ONE WORKBOOK PER SCENARIO Base case Low case High case Disaster case

  10. MULTIPLE INPUT SHEETS Base case Base case Low case High case Workings Disaster case Outputs: BS Output: P&L Output: Cash flow Output: etc SCENARIO MANAGEMENT • ONE WORKBOOK PER SCENARIO

  11. PREFERRED ALTERNATIVE CONTRAST Revenue Inputs Costs Workings Financing Outputs: BS Cash flow Output: P&L Output: Cash flow P&L Output: etc Tax Assets BS

  12. UPDATING: ONE STEP

  13. UPDATING: TWO STEP

  14. WHAT’S MOVING?

  15. AUDIT TESTS • PROBLEM • Separating the workings from the outputs • • wages war on long formulae • • BUT, risks misreporting calculations. • SOLUTION • • tests anything we can think of: bs balances, subtotals add up • • typically 20-25% audit overhead. Inputs Workings Outputs: BS Audit Output: P&L Output: Cash flow Output: etc

  16. WHO COULD OBJECT? • PROFESSIONAL AUDITORS • Extra 25% of formulae that contribute nothing to the outputs • On more than half of occasions, professional auditors ask us • • either, to remove the audit sheet from the model • • or exclude it from their scope of work. • US • When they do check the audit tests, outside auditors sometimes find slips in the tests. • => Misplaced confidence.

  17. NAMES • DAILY • Standard naming convention allows consultants to pick up each others’ work • Search for relevant lines is through psychologically ordered name space, not through physically ordered cell space. • Formulae more readable, especially those that link to other sheets. • Automatic column matching eliminates a whole class of error • OTHER • Solution to problem should not be expressed in terms of its layout. • Possible to reuse bits of models

  18. WHO SAID...? • The quantity I introduced in equation 13 • = • the quantity I introduced on page 2, paragraph 2 • x • (what Maxwell was talking about on page 4 paragraph 12 of his treatise on Electricity and Magnetism of 1873)2

  19. NAMES UNPOPULAR • Many organisations use Operis method, less Names. • Some clients explicitly reject names:=> Latest Operis Analysis Kit has Names Deapply command. • REASONS 1: IGNORANCE • To use Names well, whole team needs to be taught Names well: • • create, apply • • alter, check, delete • • row/column matching • • global/local • REASONS 2: INVESTMENT • It takes a moment to define aname... • • valuable investment in enduring models • • not worth investment in small, transient ones • …and to check it

  20. UNITS • PROBLEM • Combining quantities which have incompatible units is a common source of error. • • E and £ and $ • • Thousands and millions • • Km and miles • SOLUTION • (Best would be for ss cells to have notion of units.) • Compromise: • • Get into base units asap • • Use formatting to remove excess digits • • Styles are useful here.

  21. WYSINWG • WHAT HAPPENSWHEN YOU COPY? • A cell contains 1200000, but is formatted to show 1.2 (ie millions). • When copied to • • Word, it comes across as 1.2 • • Access, it comes across as 1200000 • WHAT HAPPENS WHEN YOU TYPE? • You see 1.2 but you want to have 1.4. • You have to type 1400000 • We know that the underlying number is in units; the appearance in millions is only formatting. • But does the ss user?

  22. SPACES • At the last Eusprig meeting, we were urged: • Make formulae readable by putting spaces in them. • = Sheet!A5 * (B5 + B6) • How could anyone object to that?

  23. OPERATORS • OBVIOUS • + • - • * • / • INTERMEDIATE • % • ^ • & • LESS OBVIOUS • Range operators: • Between : • Union , • Intersection _ • Do you add spurious operators to your formulae?

  24. SUMMARY N J

  25. SIZE DRIVES STRUCTURE

  26. GOOD PRACTICE • Is there any at all that is not domain-specific? Maybe: • 1 Keep it simple: Avoid overmodelling, or modelling beyond the resolution of your data • 2 Have an idea of how you will know the finished article when you see it • 3 Avoid long formulae • 4 Where you can, double (or triple..) check results by calculating them different ways. • 5 Don't use the OFFSET or INDIRECT functions • 6 Do not introduce circular logic into a spreadsheet (Unless you can explain in sophisticated mathematical terms the function that describes how the results of the spreadsheet are linked to its inputs.)

  27. ANYTHING NEW? Eusprig 2002

More Related