FAST 4.01-01 Use the INDEX function over the CHOOSE function
INDEX and CHOOSE are used for the selection amongst the options. Both functions solve the purpose. However, in FAST methodology we would advise modelers to choose amongst options by using INDEX function over CHOOSE function.
INDEX function has more advantage in comparison to CHOOSE function because INDEX is:
1. Easy to update if additional line items are added. Simply increase the array size.
2. In accordance with the FAST methodology of block calculation.
3. Easy to write the formula as INDEX takes array whereas for CHOOSE we need to point out each of cell addresses.
4. From our experience we have found that sometimes function CHOOSE (if used excessively) makes the file heavy, crashes Excel and sometimes model does not get full calculated.
The only advantage CHOOSE has over INDEX is that CHOOSE works when the options are scattered at various places. However, this advantage becomes redundant when following the FAST methodology where block calculations are promoted.
FAST 4.01-02 Do not use the NPV function – ever
We have seen modellers use NPV function quite often. However, we propose to minimize the use of NPV function in spreadsheet Financial Modelling OR use it very intelligently depending upon the type of model we have.
Generally we build financial models where the financial reporting is done on the End of Period (EoP) basis. In these type of models the NPV function should not be used because the NPV function discounts the Cash Flows for a period with the discount rate. This yields a wrong result because Cash Flow which are reported on the EoP should not be discounted in that period.
For the above scenario in particular, the XNVP function can be used instead of the NPV function. Both functions have limitations and cannot be used to cater for varying discount rates.
FAST 4.01-03 Do not use OFFSET or INDIRECT functions
Excel function selection should be ‘fit for purpose’, and the simplest, most direct implementation should be applied, for example using LOOKUP function variants, when INDEX or CHOOSE is fit for the task would be considered poor style.
OFFSET and INDIRECT functions should be avoided except in limited circumstances, as the logic inter-dependency is not direct when using Excel tracing arrows. (OFFSET for scenario picking in particular should be avoided, as INDEX is invariably a preferred choice.)
FAST 4.01-04 ROUND
Use of Excel rounding functions – particularly for purposes of making figures look better, ensuring tables appear to ‘add up’ better, or, somewhat ironically, in the interests of precisely matching reality (for example debt draws only available in certain increments) – should generally be avoided. If necessary, find the particular point that requires adjustment, that is do not simply throw ROUND(x, 2) on all formulas.
Please correct the typo (XNVP) in the statement “For the above scenario in particular, the XNVP function can be used instead of the ………………”
Sometimes 3D structures make more sense, even in behind-the-scenes calculations. When using 3D structures (multiple worksheets usually with the same layout but different values), CHOOSE is unavoidable unless one uses very long and complex arrays as 1st arguments to INDEX.
As for LOOKUP functions, one of the greatest advances in computing was databases, and one key strength of databases is using text to identify records. Many tables are indexed best by text rather than numbers, meaning referring to values in tables is often best accomplished using LOOKUP functions rather than INDEX.
Finally, NPV. Maybe those of us who’ve been around since Lotus 123 was the leading spreadsheet just know to use NPV(r,…)*(1+r) and do so automatically, but there’s a huge advantage to NPV which also bears on ROUND: cell formulas, especially long chains of discounted cashflow calculations, are subject to double precision rounding errors (akin to represeting 1/3 + 1/3 + 1/3 to any FINITE number of decimal places, the text representation of results will never add up to 1.0). NPV is also subject to rounding errors, but because all intermediate calculations are performed in FPU hardware and intermediate results stored in 80-bit FPU registers rather than 64-bit cell values, NPV avoids the off-by-a-bit results that are pretty much guaranteed by calculating net present values without any functions. Were the FAST authors ever trained in or have any practical experience with rounding errors due to finite precision in computer calculations? Any of them ever subscribe to any IEEE or SIAM publication?