Please could you explain how spreadsheets are used for valuations?
Many of those reading this article will work for firms which use microcomputers in general practice surveying, whether for valuation, investment, property management or estate agency. Generally speaking the profession has been slow to accept microcomputers and the benefits they can offer, and particularly so far as valuations are concerned. But for the uninitiated the main benefits of a “micro” over and above manual methods in a general practice surveying firm can be summarised as:
large storage capacity;
fast number-crunching;
fast search-and-sort capabilities;
high quality and diverse output.
But it is often argued that for much valuation work computers are largely unnecessary. More often than not the valuer can produce useful results on a programmable calculator. This may be the case for a few calculations, where quality hard copy is not required, but the attraction of the larger memory capacity of micros, the availability of “near letter quality” (ie suitable for immediate inclusion in reports) and graphics output from appropriate printers are among the advantages that micros can offer.
It should be apparent that the tasks outlined in Figure 1 offer potential for computerisation and in the same way “application software packages”, as they are known, can be written specifically or adapted to any or all of these tasks. This is the theme which will be expanded on in the rest of this article.
Spreadsheets and software
One particularly useful software package is the spreadsheet — something that in recent years has been increasingly used for a wide variety of purposes. Spreadsheets are a “subset” of the general term software which in regard to an applications package refers to a set of pre-written programmes which give a computer instructions to carry out a particular application.
The various categories of software are shown in Figure 2 and one should note the distinction made between “off-the-shelf standalone” packages such as a spreadsheet, and “off-the-shelf application-specific” packages.
The latter are a type of package produced by a software house with a specific market and task in mind, for example a residual/appraisal package for valuers. Standalone packages such as spreadsheets, however, are more flexible in their use because they can be altered or “customised” by a user to carry out a wide range of tasks.
Conventionally a spreadsheet was and still is used in the accountancy profession to perform financial calculations and record transactions. It is in essence a large ledger sheet.
A computerised spreadsheet package is simply an electronic version of this which the user can alter or “customise” to perform various applications.
The first commercial spreadsheet was the VISICALC package produced in 1979 and emanating from the private research carried out by Dan Bricklin in 1978. Indeed it was this package (a mnemonic for “visible calculator”) that gave the APPLE II micro its immense popularity. The enormous expansion of the use of such packages, of which there are now 70 different types marketed, is illustrated by the fact that in 1984 it was estimated that 1 in 15 clerical workers in the USA used a spreadsheet in their daily work.
Spreadsheets and application-specific packages
Software, then, can be used for a variety of valuation applications. Spreadsheets should, however, be distinguished from application-specific packages written for a single purpose. The main advantages a spreadsheet has over such a package can be summarised as:
greater flexibility;
more powerful;
ability to carry out a wide variety of tasks;
capacity to integrate with graphics, database, and word processing;
relatively cheap.
In addition, setting up and using a spreadsheet provides a good way of actually learning the rationale that lies behind the various valuation techniques available. Clearly this is of great benefit to the student.
How does a spreadsheet work?
Any data that can be represented in a row and column format can be entered in a spreadsheet. The size of spreadsheet varies from package to package but can be as large as 9999 rows by 999 columns although available memory space in the micro may be a limiting factor.
In order to view a part of such a large spreadsheet at any time a “window” has to be placed over the spreadsheet and the computer display screen acts as this window, as shown in Figure 3. To move to other parts of the worksheet vertical and horizontal “scrolling” is used whereby the window moves over the spreadsheet so that every part of the spreadsheet is potentially accessible.
Within the spreadsheet the area at which any single row and column interests is called a “cell” and each cell will have a unique location within the spreadsheet. These cells can contain text, values or formula(e), and most spreadsheets already have a range of mathematical operators (+, -, x etc) and functions (NPV, PV etc) available.
In addition there will be a “status area” within the window indicating “cursor” location and the contents of the current cell. The “cursor” is a block of light one cell wide and one row in height which highlights the current cell. A “command” area will also appear within the display from which the user will be able to select a variety of commands which instruct the spreadsheet and the micro to carry out various tasks or operations.
Spreadsheets in valuation
Before describing how a spreadsheet may be used in a valuation application it is important to appreciate that a number of logical steps should be followed to build up any valuation spreadsheet “template”, namely:
(1) What is the nature of the valuation problem?
(2) Will using a spreadsheet help?
(3) What data do you need?
(4) Create the spreadsheet’s logic using formula(e) etc.
(5) Arrange data for ease of use.
(6) Insert the test data.
(7) Calculate results on this “dummy run”.
(8) Stop and evaluate and check for errors.
(9) Carry out your “what if?” calculations, by changing the value of variables in the spreadsheet.
(10) The spreadsheet template is saved, as a “worksheet”, and can be reused.
For example, in a residual or development appraisal valuation, a suitable layout might be to have the first “block” within the worksheet as the area for inputting data; the main body would then be for the standard valuation/appraisal and finally, say, a sensitivity analysis table could be added as the third block or element within the worksheet. The printout shown on the right was derived using the LOTUS 1-2-3 spreadsheet package run on an IBM PC-XT micro with a near letter quality printer.
Further refinements could also be incorporated and there is a package currently marketed in which a crude form of risk analysis can be carried out to create a more probabilistic form of valuation.(1) A note of caution here: when computerising a residual/appraisal you are computerising a method which the Lands Tribunal has rejected in cases where comparables and agreements are available (see Clinker v Ash). But having said that it should be self-evident that the possibility for fast sensitivity analysis and rapid “what-if?” calculations become very much easier than with a programmable calculator.
In much the same way a spreadsheet template can be created for discounted cashflows, the benefits of which have been discussed in this column previously. Indeed building on a basic DCF model one could quite easily draw up a template for a single property performance analysis. There are, however, problems in setting up a portfolio performance analysis where a number of properties are valued simultaneously. This is probably best achieved using one of the application packages written for this purpose, a good example being that developed by Dr S Hargitay at Bristol Polytechnic.
Words of warning to spreadsheet users
Despite the ease of use of spreadsheet packages and their undoubted suitability for a range of valuation applications it should be emphasised that errors can and do creep in, particularly where the valuation model is large and complex.(2) This is because, unlike the best application-specific packages, spreadsheets offer no built-in error checks; it is up to the user to build them in. So one must develop basic ground rules over and above the steps listed earlier. The template should be designed to incorporate separate blocks or worksheet areas for inputs, calculations and results. It is also helpful to incorporate helpful statements in the template to document areas for subsequent users (eg an area labelled Data Entry for the data to be altered). Finally, if the format can bear it try to build in error check routines.
Given these basic rules the spreadsheet offers an immensely valuable and powerful tool to the valuer.
References
(1) For further details on such valuation models see Risk Uncertainty and Decision-Making in Property Development, by P Byrne and D Cadman (1985), Spon.
(2) “Lying in comfort with an untucked spreadsheet”, J Schofield, The Guardian, October 3 1985.
See also: “A Valuer’s Path through the Software Jungle” T J Dixon, Estates Times, October 11 1985.
