Building a Business Plan or CashFlow Model

[john]

[Work In Progress]

I end up doing the same sorts of thing time and time again when developing a modest size business plan, cashflow model, or, as I am now, a real estate (property) model. Here's some of the things I always seem to end up doing. I'm going to talk alot about property, and tenants, but its applicable in other businesses:

• Color the Input Cells. I use pale blue. I think its best to color the main output or calculated cells too - I use pale yellow.
• Have a Global Parameters page. Everything should be range named on this page.
• Page to a schedule, one or more consolidation schedules. I always come back to this. There is a page to each main area, say rent, leasing costs, construction, financing etc. The same applies to non-property models. Then there is one (quite often just one) schedule that pulls the bottom lines of each of these schedules into a global consolidation.
• Hang on to the idea that Base is variable. Base is the length of each timeperiod and in a BF you can have as a global variable accessed by each function. Try to hang on to this principle, because it means you can then run your model quarterly or yearly without massive redevelopment. This will tend to mean you use BF's everywhere, but thats OK.
• Database is row-to-a-tenant. If you have lots of similar entities, like tenants, have a database sheet in the workbook. This is just a sheet with a row to a tenant, or row to rental stream, or row to customer, etc. If done column to a tenant, even page to a tenant, but row to a tenant works best.
  
  Column to a tenant is OK if you have a small number of tenants, small amount of data, and then you can just have the tenant data at the head of a projections column ie have the data and the calcs in the same place, then you just copy one block across for a new tenant.
  
  Sheet to a tenant is elegant, again you can have calcs and data in the same place, but the model gets very big.
  
  So, in general, the database is row to a tenant, just like Excel expects to you to have a database. Later, you might want to use MS query to link to an external database, and MS query likes row to a tenant, too. BF Std Ed has a database browse utility (beta) that you might find useful.
• Database contains just data. If you need to have calcs for each row in the database, have separate schedules that link of the database. Don't mix calcs with the database otherwise you will accidentally overwrite your calculation formulae.
• Use data validation. No harm done by as much data validation as you can be bothered to do. Don't use free-form combo-boxes linked to combo-boxes, because you will never know if you changed a cells data, or the combo box did. Visual elements like combo boxes are nice but potentially very confusing and disorientating. When they break, its very frustrating.
• Use BF array functions. Of course any formula in Excel can be an array formula, the problem is they are hard to understand. BF's array functions are draconian in their simplicity (because generally they just apply for a timebase), easy to understand (as easy as any BF is!), much faster, and they guarantee to you, the user, that the same formula has been used across a whole row or column.
  
  More than that, however, they are inadvertently some of the best formula protection you can get. You can't alter an array function by just editing it, you can't delete one cell in an array function region, and you can't insert columns. It's maddening, but its super protection until the day when everyone remembers that you just go Ctrl-Alt-Enter to edit an array function, and even then its still pretty good protection.
  
  The problem with Excel's sheet-level protection is that its an all or nothing solution and it is never implemented properly. I have heard that array functions slow you down. Well, I can tell you that BF's array functions are genuinely much faster, and when we get around to further optimization this is is going to get even better. The only problem can be that Excel takes long to insert or delete rows and columns when there are lots of array functions around, sometimes much longer, but once you know that Excel experiences considerable angst when doing insertions/deletes and you make allowances, its OK.
• Consider using custom number formats. Instead of putting '5' in one cell and 'years' in another, why not simply use a custom number format so that the number 5 in the cell is represented as '5 years'. Well the reason you might not do this is because Excel's number format syntax is a beast, but help is at hand. if you use our NumberFormats utility, the Custom option allows you to put in a custom suffix or prefix and you can preview what its going to look like. This technique saves you a cell (although make sure your suffix/prefix is not too long or you will overflow the cell), and it looks better, because the word spacing is nicer.
• Calculation Issues.
  When the Calc time gets to over a minute ot two, stop and think. This should be prefaced by 'turn automatic calculation off' (Excel/Tools/Options), because for a model of any size, auto-recalc is a pain. You can see how long a recalc takes by using CalculateFull (Alt-b-c) from the BF menu. If it gets to over a minute or two, you are never going to like this model. You can optimize by using array functions as described, and after that its time to consider whether the model should be split up, in which case consider using our CashFlow
  Administrator to do the linking (live cell links are not recommended).
  
  As you develop the model, you can save time by using our Calculate Workshet option (Alt-b-w). Oh, and my personal plea is don't use iterative recalculation (turn it off in Excel/Tools/Options) - as a technique it looks seductive but invariably ends in pain.

[To be continued...]

[rhoro]

Hi John,

can you pls elaborate on your suggestion that 'iteration' be turned off? In my several financial forecasting models it's the only way that my models will solve, since I do/will not use a fudge on the Balance Sheet.

rick.

[john]

Iteration can clearly be slow, but also you are trusting that natural convergence exists. If you really understand it, then great, maybe its just me, iteration does have its fans. I also have suspicions that Excel is not quite so stable when iteration is on, but its just a hunch. I can't say whether there might be another way around the BS fudge because I don't know what it is, all I can say is that I am doing a BS forecast at the moment, expecting at any time to be proved wrong and that a proper BS without iteration is impossible. We shall see...

[FinMod]

Yes John, you are right... Iterations would make excel unstable, especially when there are two or more circular references generated from a single cell.. The outputs shown may not be the outputs calculated.