CRE DCR Anatomy - MMMUK1/LambdaForRealEstate GitHub Wiki

Quarterly Cashflow Worksheet ('QtrlyCF')

One of the key essential sheets is the Quarterly Worksheet. Why? Because it is usually the sources for key investment metrics such as XIRR based on quarterly dates and associated metrics such as Profit, Peak Equity, Equity Multiple, accumulative cash flow figures, and debt service coverage calculations. These answers, while explored in both Monthly and Annual worksheets, provide direct answers for the customer Executive Summary. From a HCI perspective, the QtrlyCF is summarised in a main sheet elsewhere: be that an Executive Summary Sheet and/or Input/Output worksheet.

The consensus anatomy seems to be run through a familiar order of groups of rows as follows:

1. Spacing
2. Time Horizon
3. Spacing
4. Quarterly Content

Within the Quarterly Content, the following sections are common:

1. • Income
2. • Subtotal
3. • Space
4. • OPEX (Operating Expenses)
5. • Subtotal
6. • NOI
7. • (Optional stat) tracking of ERV
8. • (Optional stat) tracking of Vacancy per quarter
9. • (Optional stat) tracking
10. • Space
11. • Debt facility
12. • Geared NOI
13. • Space
14. • Promote calculation
15. • Post-promote NOI
16. • Space
17. • Fund Management fees & Costs (including tax)
18. • Space
19. • Post FM Fees NOI

The 'Optional stats' are sometimes included in Quarterly cash flow. But strictly speaking they are layers of information that are more suited to executive summaries, or other focused output pages. For completeness, they are added above.

Annual Worksheet

There is no reason why the Annual worksheet cannot just summarise the respective Quarterly periods in the same line order.

While a con may be the fact that it may provide a bit too much detail for an Executive Summary purpose, the pros of keeping the Annual Worksheet mirroring the Quarterly Worksheet are many. Auditing, manual reviews, and simple human interaction by switching between Annual and Quarterly worksheet tabs becomes far easier for all stakeholders involved in the spreadsheet or indeed the print outs.

To briefly compare these two worksheets, we can come up with the following exercise:

Item	Annual CF	Qtrly CF
Frequency	Annual.	Quarterly
Space	3 rows	3 rows
Date format	Medium	Medium
Periods	5	20
Space	1 row	1 row
Content 1	Income	Income
Content 2	Subtotal	Subtotal
Content 3	OPEX	OPEX
Etc...