## Financial Modelling and Excel Resource Links

Also from Plum Solutions via Patrick, a list of financial modelling and Excel resource links. They include Pointy Haired Dilbert: "probably one of the most relevant and useful blogs I have come across for the heavy-duty Excel user. If you subscribe, barely a day goes by without a fascinating tip or Excel tidbit."

## How Not to Write Formulae II

This one is from my cartoons:

## How Not to Write Formulae

Back to the links Patrick sent me. In Five Excel Formulas to make you cringe, Plum Solutions list five practices that they regard as bad. These are: explicit addition or addition and division instead of SUM and AVERAGE, too-long formulae, too many nested IFs, and wiring constants into formulae. Some if not all of these demonstrate defects in Excel as a programming language. For example, nested IFs and long formulae would be much easier to understand if Excel would allow formulae to be written over multiple lines, so that one can indent subexpressions. Formulae would be easier to shorten if Excel made it easier to define functions and didn't force one to drop into VBA. This was the point of a prototype I built that tried to avoid the need to do so: Defining Excel Functions without Visual Basic: a compiler that converts Excel function definition sheets to VBA.

## The Pain of Documentation

In the spirit of my previous posting, I shall again mention my Excelsior spreadsheet generator to point up difficulties one has with Excel: this time, in documenting a spreadsheet. In Gliders, Hasslers, and the Toadsucker: Writing and Explaining a Structured Excel Life Game and How to Document a Spreadsheet: an Exercise with Cash-Flow and Loans, I show how I can write high-quality spreadsheet documentation as part of an Excelsior program. And in Documenting Spreadsheets with Pseudo-Code: an Exercise with Cash-Flow and Loans, I propose how to do something similar but not with Excelsior this time. The idea is to write in a high-level "pseudo-code", get that right, and only then translate into Excel. But the first two require a special program, and the third requires great development discipline. What they really show is that you should not use Excel in any project which needs its internal workings to be documented.

## The Pain of Resizing Tables

Since the previous entry refers to one piece of spreadsheet-replacement software, I reckon it's OK to mention my own Excelsior, which generates.spreadsheets from input files that describe spreadsheets as collections of tables, related by formulae. If a spreadsheet needs many tables all the same size and shape, you can make these all depend on the same constants in the input file. Changing the constants will resize all the tables. In How To Avoid Hours Of Tedious Spreadsheet Editing, I tell how I used Excelsior to resize the 60 20-by-40 tables in a 10,000-cell spreadsheet that modelled stocks of social housing, so that we could generate new spreadsheets with differently sized tables in just a few minutes. As with the previous blog entry, the point is that the need for other software demonstrates something Excel is bad at: having lots and lots of tables whose sizes need to be changed together. More generally, Excel just is not very good at producing "families" of spreadsheets, identical except for the shape and size of certain regions.

## The Pain of Three-Dimensional Tables

Yet another link from Patrick O'Beirne, this time to Cool Anago Demo in Thomas de Nooij's blog Thomas on Spreadsheets . Thomas is writing about Anago Assemble, which enables users to specify business calculations visually — "without programming" — by graphically "wiring" inputs, calculators, and outputs together. The point of it for "How NOT to Use Excel" is that Thomas justifies using Anago Assemble by mentioning something that's hard to do in Excel: create a spreadsheet that calculates Sales per product from Units Sold per Customer, Product and Month. This needs a three-dimensional table. But Excel doesn't have three-dimensional tables. OK, so you flatten the table. Simple? Thomas explains why it isn't.

## 10 worst Microsoft Excel practices

Patrick also pointed me at 10 worst Microsoft Excel practices .from the The Universe Divided blog by Michiel van der Blonk. Michiel identifies problems such as using too many worksheets — one for each month in a financial report, for example — which makes it much harder to analyse the data than if it were all in one sheet.

## The Devil's guide to spreadsheet creation

Patrick O'Beirne of Systems Modelling Ltd sent me a list of articles about how not to use Excel. The first that I'll mention is his own The Devil's guide to spreadsheet creation, twenty rules for writing bad spreadsheets. Some will resonate with every programmer:

Don't obtain test data; whatever the spreadsheet result is, is right.
and
Documentation is for wimps; specifications are for the timid.
And, of course:
Just do it. Jump in and do it. The users will have to accept whatever you produce anyway.