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 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 a 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 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 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, 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 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 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 must 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 10 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