This article is a 6 minute read. Don’t have that much time? The article is summarised here in the Too Long; Didn’t Read [TL;DR] paragraph.

TL;DR

Goal Seek is a trial and error solution to complex calculations. DA UK has proprietary software that outperforms Excel for the average user by automating the iterative calculations required for Residual Land Value calculations. A software license also offers cost savings compared to hiring consultants to build specialist Excel spreadsheets.

This article is the first in a series set out to highlight the differences between DA UK's proprietary Development Appraisal software, and a Microsoft Excel appraisal document.

It’s important to explain why this series is being written. Excel is the most prominent appraisal tool used in business - I have seen, reviewed and created dozens of appraisal spreadsheets. Each appraisal was bespoke with a specific spreadsheet created for each client. They were costly for clients, but they had no alternative. Other ‘off the shelf packages’ didn’t fit their needs, and they didn’t have the skills in-house to set up their spreadsheet so they hired a consultant to carry out the work.

Proprietary software is designed for a very specific purpose. In our case, the creation of bespoke, user friendly development appraisals. Excel, while versatile, requires significant manual setup and skill to replicate the feature found within proprietary software. This setup work will involve, at the very least an understanding of numerous formulae. It likely requires the ability to combine formulae, work with array formulae and quite possibly set up some Macros - either by Excel’s built in recording tool or by writing some of your own Visual Basic code.

While you, the reader, might be ok with this, it exceeds the skills of the average Excel user, and likely the skills of the end user of the template.

Goal Seek is Excel’s name for it’s iteration function - more commonly known as ‘trial and error’. The user needs to establish a figure that can’t easily be calculated by a formula. Two good examples for this in development appraisals are Residual Land Value [RLV] and Debt borrowing amounts.

When trying to establish an unknown, such as an RLV, it’s important to have the rest of the required data. In it’s simplest form, you’re trying to find the land value which delivers a set level of profit, based on your predicted costs and revenues. With these figures it’s simple enough to calculate the RLV - deduct costs from revenues to give your combined land and profit figure; then deduct your target profit from the total profit to give your RLV.

If every project was this simple we wouldn’t need a trial and error approach… However, when you add in multiple revenue streams, numerous costs, varying programme dates for each and then factor in finance costs over the project, you have too many data points to make this simple calculation work.

Instead, you need to use trial and error - take an initial guess for the land value, enter it into your spreadsheet and compare the calculated profit to your target profit. Now adjust your guess accordingly and test until you hit your desired target profit.

Excel’s Goal Seek performs this repeatedly if you provide it with 3 inputs; the cell location of your profit, your target profit, and the land value cell.

Excel will repeatedly change the land value, recalculate the spreadsheet and check the profit value until it equals your target. This solution, and effort required in selecting cells and entering values to set up the function, works well when used occasionally.

A user would soon tire of having to perform the same actions every time they updated something in their appraisal - this is where proprietary software can shine.

When a DA UK user adds a residual item to their appraisal, they set their target profit percentage and the software automatically recalculates the land value each time a value is changed. There’s no set up required, the software, as it’s specific for development appraisals, knows that a residual value always needs to be recalculated upon a value change. All of the trial and error is done behind the scenes and the updated figure is displayed in an instant.

One area in particular where DA UK users benefit, is when performing a sensitivity analysis. The approach of an average Excel user would likely be to copy the spreadsheet numerous times, changing their variables each time and then setting up and running a Goal Seek. A more advanced user, could automate the Goal Seek through a macro. A super user, using Visual Basic for Applications [VBA], could automate the Sensitivity Analysis, and the residual calculation - but that’s not a simple process and would take planning, time and a fee that far exceeds several years of a DA UK license. I can speak from experience having previously coded a sensitivity Analysis in VBA.

DA UK's software performs everything required for the Residual Land Value when carrying out a Sensitivity Analysis [SA]. A final point to consider is that most SA’s return the affect of a variable change on the project profit. When calculating a RLV within your appraisal, your target profit may not change, but your RLV will so it’s important that the SA returns the results as the residual land value as our software does, instead of overall profit.