Search the web

Inputs are the numbers that drive your model.  This means all the numbers you put into your model, including, for example, the number 12, representing the number of months in a year. Why? although everyone knows there are 12 months in a year, if you see a formula with a 12 in it, can you be certain what that 12 means? It could be number of months in a year, or it could be number of bottles in a packing case, or……just about anything!

Use Range Names for common values

By using an input cell, which is labelled, it is always possible to check what the value 12 refers to.  With commonly used values such as this one, you may want to give a name to the input cell containing the value 12 (eg MonthsInYr).  That way, if you use the 'range name' in a formula, it is obvious what the formula is trying to do. Beware however, too many range names can make navigation very difficult.

Keep related inputs together

Another good reason for keeping all inputs in a single cell is that if you ever need to change that value, you only need to do it in one place. Whilst the number of months in a year is always 12, I have seen plenty of examples where a factor was used in dozens, hundreds or even thousands of cells, which should have been in an input cell.  A typical example of this would be a currency exchange rate. Others include yield rates, conversions from imperial to decimal units etc. Imagine reviewing or maintaining a spreadsheet where hundreds of cells contain a formula, which includes:

[Cell reference]/1.5

Wouldn't it be easier if that 1.5 was a reference to a cell on the input sheet, labelled "US$/£ exchange rate"?  Better still, your formulae would refer to that same cell, which had been named, for example, USD_Rate, so that the formulae would be:

[Cell reference]/USD_Rate

Not only can you instantly see what the formula does, if one of those two currencies suddenly dives on the currency markets, you only need to change one cell on the input sheet, instead of searching for all the occurrences throughout the whole model. If someone wants to see a sensitivity based on currency exchange rates, it is far easier to do so with a single input, than a factor hard-coded into many cells in the spreadsheet.

The number one

An allowable exception to this rule is to use the number 1 in formulae.  This is often necessary, for example to apply a growth factor, you would multiply by (1+x).  To calculate a reciprocal number you would use 1/x. Using 1 and 0 as logical values, representing true or false is also useful.

[Home] [Best practice] [Resources] [Links] [About Us]