Most of the functions in the projections categories can
also be used as array functions.
Array functions are particularly suited to the Business
Functions approach of having a common formula applied
across a timebase.
The advantage of array functions over conventionally
copying a formula across all the cells in your cash flow
are:
- You know that the same function is
applied uniformly over the time horizon, and that one
"rogue" formula is not different to its
neighbours.
- Computationally it is more efficient
because the input and output code to the function is
only done once for the array of cells instead of once
for each cell.
Array formulae are not everyone"s cup of tea and you
can still use the conventional copy-across approach if
the following factors are important to you:
- Editing an array formula is a bit of
a pain because you have to press Ctrl-Shift-Enter all
the time to re-input the changes.
- The array formula concept is more
complex to grasp. Business Functions arrays however are
pretty simple, because its simply a function returning
an array of outputs, one for each time in your
timebase. However some uses of array formulae are
mind-bogglingly complicated and you may have taken the
understandable decision not to go near them.
- Inserting rows or columns into the
array formula"s area requires the array formula are
re-specified.
To use a Business Function as an array formula:
- Complete the function inputs as
normal for the first period or time your cash
flow.
- Instead of putting a single time or
date in the first argument, put in the range of dates
that correspond to your timebase.
- Enter the function as an array
formula by pressing Ctrl-Shift-Enter.
For Example:
Con(ListofTimes,Base,Start,Finish,AnnualRate)
ListofTimes could be entered as B2:J2 for
instance.
You"ll notice that Excel puts curly brackets {} around
the formula once entered like this:
Where a Base variable exists, it
is ignored - you can put whatever you like in for it. The
Base for each time period is established from the gaps
between the list of times itself, and the Base for the
last period is the same as for the previous period.
You can include other functions and operators, as part
of a larger array formula and still return an array. For
Example:
-
{=Con(E2:G2,12,2000,2100,1000)*2}
But be careful. The following example won"t work:
- {Con(E2:G2,12,2000,2100,Grow(E2:G2,
100, 2100, 5%))*2} confuses Excel (and us!) because the
annual rate is specified as an array itself.
This following example does actually work, because it"s
two one dimensional arrays multiplied together, but you
need be confident with array formulas to do this:
-
=Con(E2:G2,12,2000,2100,1000)*2*Grow(E2:G2, 100, 2100,
5%)
On the Utilities menu you"ll find some useful little
tool designed to help you work with array
functions.
If you put an array function with a range of times into
a single cell only, the first result from the output
array will be returned. This is quite useful because you
can test the formula on one cell before "arraying" it
across the range.
Business Functions has a simple rule with its array
functions:
- Only the first argument of a
function is ever "arrayable", so it"s usually Time (becoming Times), or TheDate (becoming TheDates).
Conclusion
- For maximum efficiency, best
practise and optimum structuring, use Array
Functions.
- When you"re building a model,
particularly a small one, use the conventional
copy-across method - ultimately its more
flexible.
- If you"re unsure about Array
Functions, start in small way or avoid them for
now.
|