Pushing Excel - Application Design

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