Search the web

Keep a single formula across an entire row

Most financial models have time periods across the columns of a spreadsheet. In some time periods, events will occur that will require different treatment to most other time periods. The modeller's task is to design a formula that will cope with all the events, which may occur in any time period. For example, in a quarterly model, the financial year-end may trigger certain events.  Rather than having a different formula in every fourth column, the formula should evaluate whether a particular time period falls on a year-end. There are a number of ways of achieving this, including the use of 'flags' (see later) and the =If() function.

Why is this important?

There are several reasons; if the formula changes across a row, it may become invalid if some of the inputs are changed. If, for example the year-end date of the company were to change, the formulae that were designed to work out the year-end figures, would all be in the wrong columns.

Secondly, if the formula is the same all the way across the row, it is much easier to maintain, or update, as your model develops.  It may seem like unnecessary work to design the formulae this way, but believe me, any extra effort here will pay back manifold. 

Thirdly, when your model is reviewed (which it should be before anyone bases a decision on it) there will be fewer unique formulae for the model reviewer to check, saving time and money.  You should actually find it quicker to design formulae this way, than to copy and paste formulae to do a different job in various different time periods.

The first time period

One area which causes contention is the first time period, where an opening figure is picked up from an input cell. In order to keep the formula consistent, an if statement can be used to determine whether the formula is in the first time period, in which case the opening value will be used, otherwise the closing value from the prior period will be used. The formula will be of the form:

=If(this period=first time period,opening balance,prior period closing balance)

where the descriptions are cell references holding those values.

If you use different formulae in the first time period, be careful when updating or maintaining your model, not to copy the formula from the first time period across the rest of the row, or you will over-write all your cleverly crafted formulae in the rest of the row.

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