There are many issues that can present a challenge in modelling. Discussion of these can be found at some of the websites listed on our Links page, and there are also many books covering spreadsheet software packages, although these tend to focus on technical points rather than quality and business issues.
Some of the most frequently encountered issues are:
- Circular formulas
- Interest calculations
- Currency and inflation
- Mixed time periods
- Large models
- Visual BasicTM for ExcelTM
Circular formulas are hardly ever necessary, except perhaps in certain front-end-fee calculations. In nearly all other cases they should be eliminated, because they obscure the logic of the model and can easily lead to spurious results. It can be difficult to trace the chain of circularity; one useful approach is to break the chain temporarily by replacing the formula in a “circular” cell with a fixed input value. If a circular calculation is absolutely necessary, it is important to control it by a switch (allowing linear calculation), or by using Visual Basic to manage the iterations.
One area where circular formulas are often used is in calculating interest, based on the average of the opening and closing cash balances. This is not necessary, and it is much better to calculate an approximate closing balance before interest, and then calculate the interest on the average of this and the opening balance. For complete (but perhaps spurious) accuracy, it is even possible to “gross-up” the closing balance to include interest without causing circularity.
Currency and inflation
For economic and public-sector models, reports may be required in “real” rather than nominal (money of the day) values. However, the underlying calculations should always use nominal values and then convert these to real. A model which attempts to calculate directly in real terms is likely to give misleading results, because it fails to convert carried-forward balances to the correct brought-forward values for each period. Similarly, multi-currency models should use local currency as far as possible, converting to GBP or USD only for final reporting. The only exception to this is in certain high-inflation economies with FX rates fixed to USD.
Mixed time periods
Project finance models often require monthly or quarterly periods at the start of the model, followed by six-monthly or annual periods later, and this can make it difficult to use a single formula for the whole of each row. One possible solution is to use a calculated flag row indicating the number of months in each period, which can be used as a multiplier in, for example, interest calculations. An alternative approach, if the model is not too big, is to perform all the underlying calculations using the smallest time period, and then consolidate for reporting purposes.
It is tempting to use multiple files for large models, but this should be regarded as a last resort. Multiple-file models are prone to hidden circularities, and can easily become corrupted unless all files are open in memory when any structural changes are made. It is preferable to reduce the size of a large model by eliminating any unnecessary graphics and formatting, and by simplifying formulas. In particular, if there are large blocks of repeating formulas, these may be replaced by an array formula, by a reference to a named formula, or by a custom Visual Basic function.
Visual BasicTM for ExcelTM
Many spreadsheet users limit their use of Visual Basic to recording with the Macro Recorder, for instance to generate a standard printout. However, VB for Excel is actually a complete Microsoft Windows application development environment, and it can also be very valuable in managing complex calculations, for instance with custom functions. However, as in any programming language, it is essential to ensure that VB routines are properly designed (with minimal use of GoTo), carefully written (with explicit variable declarations, standard indentation and liberal comments) and thoroughly tested (covering all paths through the code).