Pushing Excel - Application Design

Description

Excel Application Design. A much needed subject - There are not many books on design of Excel applications so this is a first. The book takes accountancy as it's underlying theme and so tackles the problem from an accountant's point of view. 2A. General 3 2A.1 Excel can be used as: 6 2A.1a Be Careful What You Wish For! 6 2A.2 Applications 8 Facit Calculator (circa 1944) 8 2A.2a Development goes in cycles 13 2A.3 UI Design Objectives 14 2A.4 Change Control 15 2B. Structural Design Framework 16 2B.1 Framework 18 2B.2 Implement process and design methods 20 2B.3 Objectives 22 2B.4 User needs 24 2B.5 Key variables standing data 25 2B.6 Modules & steps (minimize number) 27 2B.7 Example of Design specs 31 2C Interface - Menus + Other User Interfaces 32 2C.1 Intuitive 32 2C.2 Logical 32 2C.3 Dropdowns List Technique 33 2C.4 Output – Reports 34 2C.5 Presentation and Format 35 2C.6 Navigation 37 2C.6a Aids to Navigation 39 2D Internal control system 44 2D.1 Dangerous current trends 44 2D.2 A 2020 operational paradox 45 2D.3 Totals 47 2D.3a Example: Total for a SUMIFS() Summary 47 2D.3b Example in action: Balance should be zero 49 2D.4 Agreement - Source & Destination 50 2D.4a Round-tripping 50 2D.4b Common key 50 2D.5 Reviews 51 2D.5a Review Summary 51 2D.5b Review Comparison to Expected Results 512D.5c Review Trends 51 2D.5d Review Accuracy of Calculations 51 2D.5e Review - Audit Area 51 2E. Documentation 55 2E.1Objectives 56 2E.1a Example 1 - Objectives 56 2E.1b Example 2 - Objectives 57 2E.2 Application Structure (Architecture) diagram 58 2E.2a Example 3 - Structure diagram 58 2E.2a Example 4 - Structure diagram 59 2E.3 Explanation 59 2E.3a Example 5 - Explanations 59 2E.4Specific challenges & solution – explanations 60 2E.4a Example 6- Specific challenges & solution – explanations 60 2E.5 Functions used (explanations in a separate sheet) 61 2E.5a Example 7 - Functions used 61 2F. Opening Macro 62 2F.1 Example opening macro code: 62 2G. Things to Avoid and Rules to follow 63 2G.1 Do’s 63 2G.2 Don’t 65 12H Typical use – 66 12H.1 Sub-ledger 66 12H.1a M/End analysis 66 12H.1b Rolling Recons 66 12H.1c Worksheet = Page Technique 66Pushing Excel