So whats 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 and hard to share with other people, including yourself if you haven't looked at it for a while.
- quite rigid and inflexible: you have to decide at the outset the principle characteristics of the model, typically its time horizon and length of budget period, and its difficult to change.
- 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 compicated 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 practise 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, If the model is split up to reduce size, 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 harp on a lot about are partial periods and daycount, and 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 Excel and re-use it - it would be great if you could, and have say an spreadsheet that calculates loans that you could call like a function or subroutine - but you can only do this in Visual Basic (VBA), which is slow. It's quite amazing that, as the budgeting and financial appraisal tool of choice, Excel doesn’t seem to have anything specific to help, except for the IRR and NPV functions which are over 30 years old.
Analysts of old probably recall systems like 'IFPS' and 'EPS' in the 1980's that dealt with the programming of project cashflows. They were a great idea and unfortunately the spreadsheet killed them off - because for easy the stuff the spreadsheet wins hands-down. However, spreadsheets weren't really designed for the 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? Well, it doesn't seem to be here, and Excel is the budgeting system that most people use because it is tremendously quick, flexible, and productive. 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?
BF 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 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 its like an 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, you have 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 an a standard argument in our functions called Periods The second idea was how to deal with partial periods and this was a real pandora's box, 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 we 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 transparentt what you are trying to do, it'ss actually must faster in execution, and has the by product that its 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).