Beacham - Pushing Excel - Application Design

Pushing Excel

Application Design

Pushing Excel VLOOKUP() by Cliff BeachamCopyright Clif Beacham 2019. Al rights reserved.

Printed in the United States of America.

Published by Cliff Beacham Inc.,

4621 E Colorado St, Long Beach, CA 90814.

This book may also be purchased for educational, business, or salespromotional use. Online editions are also available for most titles inthe series.

Editor: Cliff Beacham

Production Editor: Cliff BeachamCopyeditor: Cliff Beacham

Proofreader: Cliff Beacham

Cover Designer: Cliff Beacham

Interior Designer: Cliff BeachamIllustrators: Cliff Beacham

Printing History:

March 2019: First Edition.

Trademarks:

Many of

the

cliffcpa.com

designations used by manufacturers and sel ers to distinguish their products are claimed as

Pushing

Excel

Application Design

by

Cliff Beacham

CPA, MBA, MCDBA

This book examines the topics of Application Design, it discusses and pushes Excels boundaries by adapting it to various scenarios and how to extend the designed use of Excel features.

Published by Amazon Kindle

About the Author

Cliff Beacham has spent many years working with spreadsheets from the days of Visicalc, through Lotus 123 and final y Microsoft Excel.

His career as an accountant started with being at first a Chartered Accountant and then a CPA. In addition he holds an MCDBA (Microsoft Database Administrator) qualification. This indicates his emphasis on data and information. His book entitled SQL A simple introduction by Cliff is selling on Amazon.

However, he is mostly focused on solving accounting problems and uses Excel as his chosen tool.

In this book he shares his experience and knowledge (with many practical examples) it aims to change the way the reader sees Excel

he counters some of the negative views of spreadsheets and discusses the many advantages as Cliff is inclined to say at the end of the book

Is your life now changed?

Cliff claims that if a reader reads and understands this book, their knowledge and ability with Excel wil grow to the level of expert.

Pushing Excel Application DesignTable of Contents

2A. General

2A.1 Excel can be used as:

2A.1a Be Careful What You Wish For!

2A.2 Applications

Facit Calculator (circa 1944)

2A.2a Development goes in cycles

2A.3 UI Design Objectives

2A.4 Change Control

2B. Structural Design Framework

2B.1 Framework

2B.2 Implement process and design methods 20

2B.3 Objectives

2B.4 User needs

2B.5 Key variables standing data

2B.6 Modules & steps (minimize number) 27

2B.7 Example of Design specs

2C Interface - Menus + Other User Interfaces

2C.1 Intuitive

2C.2 Logical

2C.3 Dropdowns List Technique

2C.4 Output Reports

2C.5 Presentation and Format

2C.6 Navigation

2C.6a Aids to Navigation

2D Internal control system

2D.1 Dangerous current trends

2D.2 A 2020 operational paradox

2D.3 Totals

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

2D.4b Common key

2D.5 Reviews

2D.5a Review Summary

2D.5b Review Comparison to Expected Results 51

2D.5c Review Trends 51

2D.5d Review Accuracy of Calculations 51

2D.5e Review - Audit Area

2E. Documentation

2E.1 Objectives

2E.1a Example 1 - Objectives

2E.1b Example 2 - Objectives

2E.2 Application Structure (Architecture) diagram 58

2E.2a Example 3 - Structure diagram

2E.2a Example 4 - Structure diagram

2E.3 Explanation

2E.3a Example 5 - Explanations

2E.4 Specific 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

2F. Opening Macro

2F.1 Example opening macro code:

2G. Things to Avoid and Rules to fol ow

2G.1 Dos

2G.2 Dont

12H Typical use

12H.1 Sub-ledger

12H.1a M/End analysis

12H.1b Rolling Recons

12H.1c Worksheet = Page Technique

Pushing Excel

2A. General

If you do not have time to do it properly, how are you going to find the time to fix it? Note: I have said this before and will say it again and again!

In general, application design is common sense but there seems to be nothing common about sense! This is not rocket science it has been said 1000x.

If you do not invest the time at the beginning, you will regret it later

and the later you leave it, the more difficult and time-consuming it wil be to fix it. Often I recommend to my clients that we should start over

this time properly they do NOT like to hear that wanting a quick-fix

which is the approach they used in the beginning!

This chapter tackles the topic of design which can be considered a thought process rather than a methodology. It is quite dangerous to restrict our thinking by imposing any rigidity of methodology so please read this chapter in the spirit in which it was written. It is a bunch of things to think about but it is your application your creation and your design.

Creativity is required to develop applications and Excel does a wonderful job of providing some great tools without restricting the developer more than necessary for the type of application that can be developed.

So this is not a recipe to follow but rather a number of suggestions and points for a developer to consider when designing an Excel application.

There is/are

certain common threads and ideas that flow through most applications.

a databank of good practice

a workable approach a solution that has some universal applicability.

Application Design

We wil explore some of these ideas and hopeful y you wil be able to add your own to them to form your own individual style.

Firstly, do not expect a recipe for an application nor a methodology. It is a matter for judgment. The application does the job in the circumstances or it does not. It is easy to use or it is not. It is effective or not, efficient or not and it can be said to be a good application or not. In the end there are some suggestions I wil make and some caveats that I warn against but, in the end, it is up to the developer

who will do it their way.

Many people use a spreadsheet as a clever typewriter. They type the input into the output and mix input values with workings hard coding values in formulas. Then they have the gall to label the result as

Spreadsheet Chaos or Hel , conveniently ignoring the fact that it is of their own making; not Excels.

By now if you have read this far you know that Excel basical y consists of:

a data store (using XML)

a program that updates the data store

an interface

all of which comprises of a number of worksheets in a workbook.

Of course, there can be more than that (Charts, VBE, Add-ins etc but you get the idea).

There are three application tracks to developing: 1. Users the interface

2. Developers the calculations and business logic 3. Storage Data storage has to be optimally designed and managed.

Each of these tracks require careful consideration and none are complete by themselves. The integration of these layers is what makes an application efficient and effective.