> So what's wrong with the spreadsheet tool you are given to solve your financial problems with?
The type of Excel model used to project a budget or business plan is frequently:- quite complicated including for yourself if you haven't looked at it for a while. Its therefore hard to share with other people,
- quite rigid and inflexible: you have to decide at the outset the principal characteristics of the model, typically its time horizon and length of budget period, and its difficult to change. Often this means having a mixture of monthly, quarterly and even annual models that do a similar thing.
- hard to achieve consistent accuracy, especially when dealing with partial periods ie when a cost or revenue starts or changes part-way though a period. This often results in a very complicated looking formula.
- prepared in somewhat of a rush to meet a deadline, and therefore whatever needs to be done is done, regardless of what is good practice and documentable.
- quite large, often being the business plan for the whole company or division. The model may well have started small and grown to match different scenarios and complexities. In an effort to simplify things, the model might be split up to reduce size, but then the links get precarious and break.
There really isn't anything in the standard Excel function library to help you with this, because the functions are very general and don’t address the concept of the time period. Something we discuss a lot in the function documentation are partial periods and daycount. Excel's efforts to deal with these via the YEARFRAC() function and others are so general as to be not very useful. You can't just 'parcel up' logic in the resultant Excel formula and re-use it - it would be great if you could, and have say a spreadsheet that calculates loans that you could call like a function or subroutine - but you can't. You can only do something like this in Visual Basic (VBA), but it is very slow both in execution and accessing the spreadsheet inputs. It's quite amazing that, as the budgeting and financial appraisal tool of choice, Excel doesn’t seem to have anything specific to help the business analyst, except for the IRR and NPV functions which are over 30 years old.
Analysts of old probably recall systems like 'IFPS' (I used one called 'EPS') in the 1970's and 1980's that dealt with the programming of project cashflows that talked the language of cash projection. They were a great idea but unfortunately the spreadsheet killed them off - because the spreadsheet was much faster in development, flexible and more user-friendly. This came at a price. Spreadsheets weren't really designed for large scale, accurate, financial modelling. Wouldn't it be great to have a programming language where you could just combine all the incomes, work out the finance, do the tax , etc, all within our familiar spreadsheet? Well, it doesn't seem to be here. So we have sacrificed our data modelling principles for convenience. In this free form spreadsheet world, what steps can we take to restore some semblance of order?
Business Functions was born from the need to run loads of Excel models for the purposes of Real Estate and structuring financial deals, but the idea applies equally anywhere within business planning. The first type of function we developed was a rental function, because UK rents, although straightforward in principle, are a pain to model, with 5 yearly reviews, sometimes with respect to a moveable 'market', and then perhaps with breaks and voids. It turns out that the idea of a projections function that slots in the correct amount of cashflow in a timeperiod can be neatly applied to a growing sales line, a loan, or a capital cost curve - or even just a one-off payment. In a way it's like a financial language like IFPS within the spreadsheet.
Two key concepts had to be investigated during the development of BF. The first of these was cash and accruals. We often think of rent or interest occurring smoothly over time, when in fact the payments occur in lumps as they fall due. Which way should you model them? Well, really, you need to be able to model either way, preferably by a simple variable or switch, depending on whether you are looking at accrued income for the accounts department, or cashflow for the treasury people. That led to a standard parameter in our functions called Periods.
How to deal with partial periods became a real pandora's box in development, as it turns out that there are an almost infinite number of daycount conventions that can be applied to split a timeperiod. This led to the standard DayCount variable.
The second concept was that we realised we really weren't modelling on a cell by cell basis at all: we were calculating a whole rows values at once and slotting them in. And so most functions were given the capability of being 'Array' functions, where you just enter a single function across the whole line in a budget. It makes it very transparent what you are trying to do, it's actually much faster in execution, and has the by product that it's more difficult to accidentally delete the formula.
So, given the problem of an unmaintainable, innaccurate, inflexible spreadsheet, does BF do the job?
In our view, it does, providing :- the projections behaviour you want to model can be matched to a Business Function.
- That you are familiar with the way BF functions generally work (and they all work the same way).