Advanced Excel for Surveyors

Philip Bowcock, Natalie Bayfield

• Publisher: Estates Gazette
• Year: 2014
• ISBN:
  9781135322779
• Edition: 1
• Topic: Jura
• Subtopic: Eigentumsrecht

Advanced Excel for Surveyors is the companion to the highly successful Excel for Surveyors. This volume is intended to help both students and practitioners use Mircosoft Excel™ to solve some of the more complex problems that the surveyor may come across.It explores how Visual Basic and macros can simplify and speed up repetitive tasks, fulfilling one of the basic aims of computing: "If it is repetitive teach the machine to do this for you". The methodology of portfolio analysis is a relatively new discipline, which may be unfamiliar to many readers. The book provides an introduction to the principles and shows how Excel can help, readers may even find this of help when assessing their own personal investment portfolios. Further ideas for setting up databases; how to arrange for several surveyors to work on a single project; data analysis; and the use of charts in Reports are discussed together with further advice on security and protection.

Chapter 1
Introduction

Pythagoras observed that the Egyptians and Babylonians conducted each calculation in the form of a recipe which could be followed blindly. The recipes, which would have been passed down through the generations, always gave the correct answer and so nobody bothered to question them or explore the logic underlying the equations. What was important for these civilizations was that a calculation worked – why it worked was irrelevant.

Simon Singh

One might boldly suggest that in the property world bespoke valuation software could be considered a modern day analogy of these ancient ‘recipes’. The importance of bespoke software such as packages sold by Circle, Systemslink3 and Kell to name only a few developers is in the ease and consistency that they confer to users and their firms. The prevalence of these packages among property firms suggests this to be the case. It also suggests that there is a broad acceptance that they work. We have not examined any one package in detail, but it is safe to assume that these packages are tested many times by more than one person. By comparison, this is more that can be said for most appraisal models created by surveyors on Excel and others with wider responsibilities¹.

What is the point in trying to replicate models in Excel? There are two fundamental reasons why it is important to know why the models work. First, due diligence – the valuer might need to justify a particular approach to the problem. Second, without knowing how the models work it is easy to generate an error when adjusting for non-standard circumstances². However forward thinking software houses might be, their packages are still machines producing answers to questions posed by surveyors and not by software developers. There will always be a lead-time in creating these machines, unless, of course, the surveyor can create the machine himself. Fortunately surveyors do not have to be software developers. A working knowledge of Excel will normally do. However they will need to revise their valuation skills. We are concerned that many new recruits into surveying continue to be taught to use tables and valuation packages, without appreciating that constructing models from scratch is the most reliable way to build appraisal skills.

One other undeniable feature of Excel is its compatibility. It is becoming fashionable to communicate appraisals electronically instead of, or at least as well as, delivering the printed version. Some of the larger firms also build in some interactivity for their audience allowing clients to change yields and bank rates and watch the changes ripple through the model to the result. It is only the largest surveying firms that have all of the valuation packages, and can read any valuation in any medium. By setting up a model in Excel one can be confident that just about any firm or individual can be persuaded to accept it. Perhaps most importantly this is also true for the new generation of clients in funds and institutions who are less likely to have the bespoke programs.

We have recognized that worksheet skills in surveying are more widespread, and often quite complex. It is a marked contrast with only a few years ago when many surveyors did not even have a computer sitting on their desk. However, as we have mentioned, with increased complexity comes increased possibility for error. We do not recommend that you build an ‘all singing dancing’ model. Rather, use Excel to deal with a specific problem. Remember that it is safe practice to seek a second opinion on your work before passing it on to a client.

Multi-tenanted properties present some of the more difficult challenges to the construction of worksheets. What makes them especially difficult is the frequently irregular nature of the individual tenant cash-flows, unexpired terms, varying review patterns, rents and growth rates, and miscellaneous costs. These all make the search for a one-size-fits-all formula virtually impossible. The many different tools available in Excel to assist in this task means that there are almost as many different models as there are surveyors making them. Some surveyors pride themselves on the complexity of their worksheets. However the most complex are not necessarily the best. Most Excel textbooks discuss the merits of planning for a ‘good’ worksheet, and this book is no exception. There are some simple rules and guidelines that can be applied at the planning stage to make your worksheets simpler and easier to understand while doing the same job.

The most unreliable worksheets are templates that have been set up by one surveyor and then added to over the years by others. These worksheets often gain credence from longevity, but may contain subtle flaws. Any worksheet that earns itself the honour of being called a template must be offered to those who use it only with training or with a fully detailed labelling and description. Any user is entitled to question its structure.

There are some formatting tips which may be employed to help surveyors read and understand worksheets and templates quickly. We will discuss these later, and in addition we shall introduce some other Excel functions that can cut down on the technical complexity of the worksheet.¹

Although much of the material was initially produced on a Macintosh computer all examples and references are based on PC operations. We assume that Macintosh users will be familiar with the minor variations in procedures. Excel and other Office files are interchangeable between the two operating systems.

Notes

1 It is not possible to rely on the package being at fault to defend a negligence claim.

2 When the authors are teaching they note that the ability to put together more unusual models is the most common reason delegates cite for learning Excel.

1 By way of example, one of the authors was once presented with calculations of the value of a development project done first by a proprietary package and secondly by Excel worksheet. The answers were different, but the reason for this could not be found.

Chapter 2
Some of the new features in Excel

2.1 Introduction

In this section we set out some of the more useful modifications and additions to the latest versions of Excel. There are now several versions current in the different issues of Microsoft Office – Office 97, Office 2000, Office ME and Office XP, plus Macintosh versions 1998,2001 and 10.0 – and the following facilities are not found in all versions. Some facilities are just re-arrangements of menu items, but others are significant and useful additions. The general operation of Excel is as before – a tool which can be used by surveyors to perform operations which would be too time-consuming, difficult, or impossible by other means.

You should appreciate that Excel has many hundreds of features and facilities, and they are not all considered here – it would require a very large volume to cover every possibility, and many of these would not be relevant to the work of surveyors. For example they include several which involve imaginary numbers for which, so far, we are unaware of any purpose in surveying. Similarly, many of the financial functions are based on American practice which differs from that in this country.

If you upgrade to the latest version you can still open and save all your previous work in Version 97/98 format. You can even save in earlier versions right back to Version 2.2, although some formatting and other functions may be lost.

2.2 New features

2.2.1 Help

You should bear in mind that Excel is a very elaborate program with many facilities including many specialised operations. Although we discuss many of those which are of particular use to surveyors, there are inevitably other aspects and methods which may be useful in particular cases. The on-screen Help Menu ⇒ Microsoft Excel Help (or Function Key FI) is a valuable facility which is well worth exploring.

2.2.2 FILE MENU⇒PROPERTIES

This produces a window with five tabs of which ‘General’, ‘Statistics’ and ‘Contents’ give general information about the workbook. ‘Summary’ and ‘Custom’ provide locations where further information about the workbook, its contributors and destination can be saved.

2.2.3 More toolbars

Several new toolbars are now available as standard, of which the Pivot Table, Visual Basic and Web are possibly the most useful. As with other toolbars, the purpose is simply to make the relevant buttons conveniently available, and the more you open the less space there will be to see the worksheet. Call them and close them as necessary.

2.2.4 Custom views

You can create a custom view to define sets of particular display and print settings and save them as views with View Menu⇒Custom Views. You can switch to any of the views whenever you want to display or print the workbook in a different way. The stored settings include column widths, display options, window size and position on the screen, window splits or frozen panes, the sheet that is active, and the cells that are selected at the time the view is created. There is also the option to save hidden rows, hidden columns, filter settings, and print settings.

The custom view includes the entire workbook. If you hide a sheet before a view is added, Microsoft Excel hides the sheet each time you show that view.

Before you create a view set up the workbook as you want to view and print it. If you include print settings in a view, the view includes the defined print area or the entire worksheet if the sheet has no defined print area.

2.2.5 Number formats – fractions, phone numbers, etc

Several new number formats are now included, of which Fractions and Phone Numbers would appear to be the most useful in this country. (Others such as Zip codes and social security numbers are based on American practice.)

• (a) If a cell is defined as a fraction any fractional number will be converted to the form selected in the Custom type box. Fractions are defined using the ‘?’ character with up to three denominator digits, thus ???/??? will convert 123456 to 10/81. The number is of necessity rounded to the nearest fraction but is still preserved in decimal form for calculation purposes.
  Numbers can also be entered as fractions and will be converted to decimal form internally. Enterin...

Table of contents

• Cover
• Half Title
• Title
• Copyright
• Contents
• Foreword
• Preface
• 1 Introduction
• 2 Some of the new features in Excel
• 3 Managing large worksheet models and good practice
• 4 Databases
• 5 Sharing workbooks
• 6 Maps
• 7 Protecting your workbook and setting passwords
• 8 Visual Basic
• 9 Regression analysis
• 10 Linear programming
• 11 Simulation
• 12 Portfolio management
• Appendix A Bibliography
• Appendix B Add-Ins
• Appendix C A note on Excel for Macintosh