> -
- Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- Understand CELL REFERENCING, whether by absolute, relative or range naming. Recognise that, where you have the time and where its appropriate, that range naming has great benefits in terms of clarity and auditability. There are some people who are against range naming because they are concerned that its harder to see where the data has come from, but in our opinion the improved clarity in formulae outweighs this. Your formulae must be readable.
- When projecting time-dependent streams like cashflows, revenues and costs, use REPEATING FORMULAE over the full extent of the timebase. Avoid one-off "kludges" in particular timeperiods. The understandability of the model will improve and you will be also be able to make the most effective use of Business Functions Time and Base variables.
- Find out what Projections functions do. There are several categories of these and they are one of the "big ideas" of Business Functions. They enable you to use a single formula over the whole timebase of your model without resorting to single cell "kludges" that are hard to debug.
- When using a projections function in cash mode, use DayCount and Periods specifically rather than use the defaults. This is because these variables are crucial to your cash payment calculations. This is more a point for those of you interested in accounting-like precision, but it looks better, for example, if your rent or interest payment actually occurs in the correct month!
- Always make your models timebase flexible. Do this by always using Business Functions to project cash flows and using Base to reference a single timeperiod length specified in your spreadsheet inputs. It"s such a good habit to get into and makes your models much more widely useable: for example, the accouting department might want the model on a monthly basis, top management might want quarterly or even yearly. BF enables you to have such versatile models.
- Be aware of the various methods of specifying Time and Base. In the simplest form of input, Time is a date and Base is a timeperiod length in months. However, you can also specify both as dates, where Base then marks the end of the timeperiod, giving very fine control over the timeperiod length. Also, when specifying dates, remember you can use Business Functions decimal year format as well (eg 2000.5 = 1st July 2000).
- Note that where a timeperiod is specified as two dates, BF will "include the first, exclude the last". So the period 1st January-1st July can be though of running from 1 minute past midnight on 1st January to 1 minute past midnight on 1st July, and therefore exclude any revenue or cost accruing on 1st July.
- Understand the difference between cash and accruals, in advance and in arrears. Note that, in arrears, BF always pays the day after the accruals period ends, so periods of {-1.01,-7.01}, which refer to accruals periods 1st January-30th June and 1st July-31s December actually pay out on 1st July and 1 January.
Golden Rules | |
| Recommendations | |
| You probably need to consult the rest of the help file to see where we are coming from on some of these, but we thought we"d introduce some of our own "Golden Rules", even at this early stage: Excel Generally Business Functions in Particular |