Business Functions Library for Excel

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:

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 :For us, it means we can build a model that we were, frankly unable to build previously. It also means that we can sometimes use one model where previously we might use 2 or 3, and this has a real demonstrable savings in manpower. Over 15 years on, there doesn't seem to be a better way of building appraisal or business planning models that we have found.

Business Functions Ltd, London, UK Website Design: Webpure