Financial Due Diligence

Abstract

This chapter describes the process of conducting financial due diligence for real estate companies, portfolios and assets. It will provide practitioners with an overview of potential issues to be investigated and provides an approach to analysing these issues.

Starting with a description of goals and an approach to financial due diligence, these will be compared with commercial and property due diligence to establish an understanding of the differences and similarities with these neighbouring forms of due diligence. Subsequently, the scope of financial due diligence will be discussed. This discussion encompasses the common scope sections of a financial due diligence engagement: the quality of financials, the quality of earnings, the quality of net assets as well as the quality of cash flows. For each scope area, items of these broader scope sections, issues, analytics as well as practical challenges will be described.

As a result, common issues and analytical approaches to financial due diligence in the real estate sector will be illustrated.

Appendix

1.1 A.1 ICAEW “Twenty Principles for Good Spreadsheet Practice”

The following appendix summarises the “Twenty principles for good spreadsheet practice” according to the Institute of Chartered Accountants of England and Wales (ICAEW) (ICAEW 2015).

1.1.1 A.1.1 The Spreadsheet’s Business Environment

• 1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.

• 2. Adopt a standard for your organisation and stick to it.

• 3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.

• 4. Work collaboratively, share ownership, peer review.

1.1.2 A.1.2 Designing and Building Your Spreadsheet

• 5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.

• 6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.

• 7. Include an “About” or “Welcome” sheet to document the spreadsheet.

• 8. Design for longevity.

• 9. Focus on the required outputs.

• 10. Separate and clearly identify inputs, workings and outputs.

• 11. Be consistent in structure.

• 12. Be consistent in the use of formulae.

• 13. Keep formulae as short and simple as practicable.

• 14. Never embed in a formula anything that might change or need to be changed.

• 15. Perform a calculation once and then refer back to that calculation.

• 16. Avoid using advanced features where simpler features could achieve the same result.

1.1.3 A.1.3 Spreadsheet Risks and Controls

• 17. Have a system of backup and version control, which should be applied consistently within an organisation.

• 18. Rigorously test the workbook.

• 19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.

• 20. Protect parts of the workbook that are not supposed to be changed by users.

1.2 A.2 FAST Principles

This appendix summarises the FAST Principles. The numbering makes reference to the individual standards (FAST 2014).

1.2.1 A.2.1 Workbook Design

1.2.1.1 A.2.1.1 General Workbook Design Principles

FAST 1.01-01 :

Group or separate worksheets by type: Foundation, Workings, Presentation and Control

FAST 1.01-02 :

Maintain consistent column structure across all sheets

FAST 1.01-03 :

Maintain a consistent time ruler throughout the model

FAST-1.01-03.1 :

Except when multiple time resolutions are required

FAST 1.01-04 :

Ensure primary time rulers span time frames of secondary rulers

FAST 1.01-05 :

Proliferate links to maximise navigational efficiency

FAST 1.01-06 :

Mark exports with red font and imports with blue font

FAST 1.01-07 :

Calculate only once

FAST 1.01-08 :

Use normally positive convention on Workings sheets

FAST 1.01-09 :

Use in-flow/out-flow convention on Presentation sheets

FAST 1.01-10 :

Do not overuse macros

FAST 1.01-11 :

Never release a model with purposeful use of circularity

1.2.1.2 A.2.1.2 Sheet Organisation

FAST 1.02-01 :

Arrange sheets so that calculation order flows left to right

FAST-1.02-01.1 :

Except to group Input and Results sheets

FAST 1.02-02 :

Do not attempt to optimise calculation layout and user interface/presentation on the same worksheet

FAST 1.02-03 :

Separate flags and factors onto dedicated sheets

FAST 1.02-04 :

Separate Workings sheets into functional “chapters”

FAST 1.02-05 :

Minimise inter-linking between sheets

1.2.1.3 A.2.1.3 Multiple Workbook Models

FAST 1.03-01 :

Do not split a model across multiple workbooks

FAST-1.03-01.1 :

Except when more than one modeller must work concurrently

FAST-1.03-01.2 :

Except when different files should be sent to different recipients

FAST-1.03-01.3 :

Except when a single workbook would be too large and intimidating

FAST 1.03-02 :

Avoid direct (external file) links

FAST-1.03-02.1 :

Except when the logic flows back and forth between workbooks

FAST 1.03-03 :

Use import/export sheets for line items passed between workbooks

FAST 1.03-04 :

External file links should be named

1.2.2 A.2.2 Worksheet Design

1.2.2.1 A.2.2.1 Universal Design Layout Principles

FAST 2.01-01 :

Each column should have a single and consistent purpose

FAST 2.01-02 :

Series worksheets should be defined for a single time axis only

FAST-2.01-02.1 :

Except series inputs sheets to avoid too many sheets

FAST-2.01-02.2 :

Except where local exceptions warranted

FAST 2.01-03 :

Make only two columns matter

FAST 2.01-04 :

Calculation logic should generally flow from top to bottom and left to right

FAST 2.01-05 :

Mark intra-sheet counter-flows with gray shade

FAST 2.01-06 :

Limit counter-flows to opening balance positions

FAST 2.01-07 :

Present information horizontally

FAST-2.01-07.1 :

Except for short vertical series for scenario structuring

FAST-2.01-07.2:

Except where vertical layout is more clear for printing

FAST 2.01-08 :

Do not hide anything

FAST-2.01-08.1 :

Except for undefined time which should be hidden

1.2.2.2 A.2.2.2 Calculation Blocks

FAST 2.02-01 :

Construct all calculations in a separate calculation block

FAST-2.02-01.1 :

Except when the calculation block is a balance corkscrew

FAST-2.02-01.2 :

Except when cascading calculations are warranted

FAST-2.02-01.3 :

Except when the calculation is a trivial formula

FAST-2.02-01.4 :

Except when a 2D line item is deemed the more efficient and/or readable design solution

FAST 2.02-02 :

Build calculation blocks so they can be replicated

FAST 2.02-03 :

List common calculation block components in a consistent order

FAST 2.02-04 :

List precedents in the order they appear in a formula

FAST-2.02-04.1 :

Except when this violates a “pyramid” layout

FAST 2.02-05 :

Use corkscrew calculation blocks for balance accumulation

FAST 2.02-06 :

Use timing flag and factor components routinely

1.2.2.3 A.2.2.3 Header Design

FAST 2.03-01 :

The time axis is best placed on the worksheet only once in a freeze pane

FAST 2.03-02 :

Display a single end-of-period date in a freeze pane

FAST 2.03-03 :

Display the operative period flag

FAST 2.03-04 :

Include a column counter for cross-reference on F11 quick charts

FAST 2.03-05 :

Include master error checks and alert indicators in the freeze pane

FAST 2.03-06 :

Include definition of SMU on Presentation sheets

1.2.2.4 A.2.2.4 Input Sheets

FAST 2.04-01 :

Organise inputs both by structure and commercial area

FAST 2.04-02 :

Include a dedicated instruction/comments column on Input sheets

FAST 2.04-03 :

Create self-documenting Input sheets

1.2.2.5 A.2.2.5 Presentation Sheets

FAST 2.05-01 :

Use Presentation sheets to present the model’s results

FAST 2.05-02 :

A model must completely explain how it works without the need for other software applications to present the model outputs

FAST 2.05-03 :

Provide a description of the modelling standards and method used to build the model

FAST 2.05-04 :

Provide a description of the model’s flow

FAST 2.05-05 :

Provide keys to colour coding, abbreviation, Named ranges and functions

FAST 2.05-06 :

Selection of chart type should correspond to the nature of the data being presented

FAST 2.05-07 :

Charts should be formatted for ease of comprehension of the main messages being communicated

1.2.2.6 A.2.2.6 Control Sheets

FAST 2.06-01:

Provide a table of contents

FAST 2.06-02 :

Provide a list of model qualifications and weaknesses

1.2.3 A.2.3 The Line Item

1.2.3.1 A.2.3.1 Line Item Taxonomy

FAST 3.01-01 :

Provide clear indication for constants vs series

FAST 3.01-02 :

Treat line items as the smallest indivisible object in a model

FAST 3.01-03 :

Do not use a series structure to present constants

FAST 3.01-04 :

Do not use row totals in model logic

FAST 3.01-05 :

Include display totals on all flows

FAST 3.01-06 :

Do not include display totals on balances

FAST-3.01-06.1 :

Except when the line item includes a single balance

FAST 3.01-07 :

Place display totals on the left where they are visible

FAST 3.01-08 :

Make numbers look like what they are with smart format

1.2.3.2 A.2.3.2 Formula Design Fundamentals

FAST 3.02-01 :

Formulas must be consistent

FAST-3.02-01.1 :

Except when marked as temporary code

FAST 3.02-02 :

Mark temporary code clearly

FAST 3.02-03 :

Do not use partial range references

1.2.3.3 A.2.3.3 Formula Simplicity

FAST 3.03-01 :

Do not write a formula longer than your thumb

FAST 3.03-02 :

No formula should take more than 24 seconds to explain

FAST 3.03-03 :

Do not write multi-line formulas

FAST 3.03-04 :

Use a limited set of Excel functions

FAST 3.03-05 :

Use flags to limit use of IF function

FAST 3.03-06 :

Use INDEX (or even CHOOSE) over IF to pick values

FAST 3.03-07 :

Never use nested Ifs

FAST 3.03-08 :

Do not use Excel Names

FAST 3.03-08.1 :

Except for external links

FAST 3.03–08.2 :

Except as a substitute for cell references in macro-coding

FAST 3.03–08.3 :

Except where it is impractical to display locally an ingredient in a calculation block

FAST-3.03-08.4 :

Except when it is a poor design choice to display locally an ingredient in a calculation block, e.g. error check tolerance value

FAST-3.03-08.5 :

Except in data validation lists

FAST-3.03-08.6 :

Except for text based criteria in conditional formatting

FAST 3.03-09 :

Do not construct array formulas

FAST-3.03-09.1 :

Except when Excel’s Data Table feature is being used

FAST-3.03-09.2 :

Except when calculation cannot be achieved without arrays

FAST-3.03-09.3 :

Except when the logic bloat required to avoid arrays creates a solution that is more difficult to review than the array alternative

FAST 3.03-10 :

Do not use a space as an intersection operator

FAST 3.03-11 :

Beware circularity or #ERRORs protected on inactive branch of IF function

1.2.3.4 A.2.3.4 Formula Clarity

FAST 3.04-01 :

Do not write formulas with embedded constants

FAST-3.04-01.1 :

Except when constants are universal

FAST-3.04-01.2 :

Except when constants are deliberately embedded to avoid their manipulation

FAST 3.04-02 :

Include spaces between arguments in formulas

FAST 3.04-03 :

Do not use parentheses in formulas unnecessarily

FAST-3.04-03.1 :

Except when they may improve formula clarity

FAST 3.04-04 :

Use “−1 *” coefficient for all sign switches

FAST 3.04-05 :

Do not include current sheet references in formulas

FAST 3.04-06 :

Do not use elements that appear to be non-structural in model logic

FAST 3.04-07 :

Do not over-anchor

1.2.3.5 A.2.3.5 FAST Labelling Conventions

FAST 3.05-01 :

Provide a label for all line items

FAST 3.05-02 :

Invest time in drafting a good label

FAST 3.05-03 :

All line items must have a unique label

FAST 3.05-04 :

Include a unit designator on all line items

FAST-3.05-04.1 :

Except it can potentially be omitted when SMU applies

FAST 3.05-05 :

Choose a capitalisation convention and stick with it

FAST 3.05-06 :

Include the word “balance” in labels of balances

FAST 3.05-07 :

Include the word “cash” when Label is otherwise insufficiently clear

FAST 3.05-08 :

Unit designators must be clear and unambiguous

FAST 3.05-09 :

Include units in the label

FAST 3.05-10 :

Ensure alternative sign version of flows are clearly labelled

FAST 3.05-11 :

Ensure distinction between opening and closing balances clearly indicated

FAST 3.05-12 :

Maintain labelling consistency pedantically and precisely

FAST 3.05-13 :

Unit designators must be consistently applied throughout the model

1.2.3.6 A.2.3.6 Links

FAST 3.06-01 :

Row anchor all links

FAST-3.06-01.1 :

Except when setting up to replicate sections

FAST 3.06-02 :

Do not create daisy chains; do not link to links

1.2.3.7 A.2.3.7 Timing Flags and Partial Period Factors (PPFs)

FAST 3.07-01 :

Use timing flags

FAST 3.07-02 :

Only create a flag when it is required

FAST 3.07-03 :

Only use flags which are relevant to the logic they are being applied to

FAST 3.07-04 :

Include display totals on all flags and PPFs

1.3 A.3 Excel Features Used in Modelling

1.3.1 A.3.1 Excel Functions

FAST 4.01-01 :

Use the INDEX function over the CHOOSE function

FAST 4.01-02 :

Do not use the NPV function—ever

FAST 4.01-03 :

Do not use OFFSET or INDIRECT functions

FAST 4.01-04 :

ROUND

1.3.2 A.3.2 Formatting Features

FAST 4.02-01 :

Use well-defined format styles

FAST 4.02-02 :

Do not merge cells

1.3.3 A.3.3 Excel Names

FAST 4.03-01 :

Only use fully anchored references when defining Names

FAST 4.03-02 :

Only use Names scoped to the workbook level (and not to the worksheet level) in calculations