jkp-ads.com Newsletter for July 2015
As you are reading this I am probably somewhere nice and sunny with my feet up enjoying a great novel. I certainly plan to be :-)
Golden rules for developing spreadsheets
I have been developing spreadsheet models for over two decades now so I am vain enough to think I have some wisdom to share about this field of work. I have gathered some golden rules for you which you should keep in mind when you embark on a new journey to develop a spreadsheet model. I'm not implying these rules are complete. Nor do I think they must allways be followed. Go ahead and do cherry-picking: use what you like, ignore what you don't.
Rule 1. Develop for other users
It is highly likely that the model you are developing is not just for you!
Many spreadsheet builders start developing a model for themselves. This is the wrong mindset. On average, Spreadsheets are used by 12 different people. So keep this in mind the next time you start building a spreadsheet. Plan ahead!
Rule 2. Document
Studies have shown that only 33 percent of all spreadsheets have some form of manual (documentation).
At the very least, include a table of contents with your model in which each worksheet is listed. While you are at it, include a short description of the purpose of each worksheet. You can find a little tool on my website which makes this very easy. The tool is explained in an article I wrote some time ago: Building an Excel add-in. If you have a hard time describing what a worksheet is for, perhaps it needs reconsidering!
But this is the bare minimum; Proper documentation is serious work. Let me suggest some subjects to cover in your documentation:
- A list of Assumptions; if your model uses assumptions, now is a good time to list them somewhere. Include references to their source and to what parts of the model this affects.
- Workarounds; Sometimes one needs to implement a workaround to achieve something otherwise deemed impossible. Document such workarounds meticulously as it will become very hard for other developers to understand what your model does and how.
- Show the structure; If your model has a structure (and I hope it does!), try visualising this structure in a diagram. The diagram could show what your input and output sheets are for instance.
- Show the flow; If there is a certain logical order in which the user is supposed to work with your model (and again, I hope there is!), show that order in a diagram or at the very least, show explanatory text.
Rule 3. Structure your model
This may be an open door, but structure is very important. Many people advise to use separate worksheets for input, calculations and output. To put in other words: Separate data from logic and from presentation.
If you ask me, you don't have to be as strict as "Always put data on one tab and calculations on another". That does not always make sense. I advise you to be pragmatic about this. If you have a small model with just a few entries and a couple of formulas it is absolutely fine to have everything on one single worksheet.
But at the very least, make it absolutely clear to the user which cells are for data entry and which are calculated cells. Which leads to rule number 4:
Rule 4. Format your model consistently
Add some style to your model. And I literally mean Style. Excel has cell styles, so please use them. I wrote an elaborate article on using cell styles a long time ago. I advise you to consider these types of cells and style them accordingly:
- Input cells
Cells that are the main input to your model
- Parameter cells
Cells that contain constants for your model, such as boundaries. Consider putting these on a separate Settings tab.
- Output cells
Cells in an area that is meant for output, such as printing or presenting the results of a calculation on screen.
- Calculation cells
The cells where the actual calculation work is performed.
- Boundary cells
By shading otherwise empty cells you can easily make areas with differing functions stand out from other areas.
Rule 5. Add checks and balances
Studies have shown that a huge percentage of Excel models contain errors. As much as 95 %. Unfortunately to err is human so we need to prepare ourselves. Never assume your model is correct, check and double-check! It is impossible to give you an all-encompassing solution to this problem however, what your checks need to be will depend on your industry and your model. Some general points of consideration:
- Is the data complete (Have all input cells been filled)
- Is all data valid (use data validation, but also include check formulas because pasting can wreck your validation)
- If your model has a balance, does it balance?
- Use a separate check sheet which contains all checks of your model. Show the end result of the check sheet on every worksheet of your model
- Use conditional formatting to make the good stand out from the bad.
Rule 6. Arm yourself against errors
As I already said, 95 percent of all spreadsheet models contain errors. Here are some pointers that may help avoiding them:
- Avoid heavily nested and complex formulas. Have you ever heard about "the rule of thumb for spreadsheet formulas"? Well, the simplistic rule is "never develop a formula that is longer than your thumb". Try to avoid stuffing all logic and complexity of your model into one single mega-formula. So:
- Break your calculation into steps, each step in one cell (column, row). This makes it easier to spot where your calculation is yielding unexpected results (or even goes haywire)
- Use one single formula in a block of cells. If a block of cells contains a similar calculation, make sure the formula can be copied into all those cells.
- Do not edit a single cell within a block of cells to handle exceptions to a rule. If there are exceptions, work them into the formula.
- Use a separate column for decisions on exceptions, so you can quickly spot the exceptions.
- Avoid hard-coded numbers (and strings) in formulas as much as you possibly can. And yes, I think the column index number in a VLOOKUP function is a hard-coded number!
Excel VBA voor Financials 5th edition
This well received (Dutch) course will get a major overhaul and will be run again (5th time already!) on October 6th, 14th and 21st. Join us to learn all a financial needs to know to get started using VBA to automate tedious processes.
I am repeating myself, I know, but we're still in the planning stages of an "Advanced Excel VBA" course.
However, we are slowly working out all your great feedback into a real course outline which will appear next month I assure you! Keep a close eye on our site topexcelcass.com for more information.
Our survey to ask opinions on course content and whether or not you'd be interested in such a course is still available!