One of the most common mistake in model development is the lack of clear distinction between inputs and calculations. Even worse are inputs hidden within formulas.
Let’s look at the following simple example which computes the present value of two distinct scenarios.
Without looking at each cell, one can only guess what are the inputs. It could well be any of the following:
| Used Formula | Inputs |
|---|---|
| NPER | Interest Rate, payment, present value and future value |
| PMT | Interest Rate, number of periods, present value and future value |
| PV | Interest Rate, number of periods, payment value and future value |
| FV | Interest Rate, number of periods, payment value and present value |
We also have no idea of what interest rate was used in the calculation.
Now let’s look at the same improved model which uses distinct format for formulas and inputs. The interest rate is taken out of the formula for greater clarity and a dashed border is added to all input cells.
Inadequate identification of inputs
Correct identification of inputs
Of course, you have to define a convention and be consistent in its use. It could also be different than this one, for example, blue fonts for inputs. However, the dashed borders has one significant advantage, you can even clearly identify inputs on documents printed in black and white.
Note that in order to maximize the visibility of the dashed borders, you should turn off Excel’s gridlines.
In Excel 2003, go to the Tools / Options menu and uncheck Show Gridlines of the General tab
In Excel 2007, in the Show / Hide section of the View Tab, uncheck Gridlines
In my next post, I will take a look at one Excel functionnality to help you identify inputs, “Go To”.
One Trackback
[...] a distinct format for inputs to help users quickly identify controllable elements. This is very often a missing item in models. [...]