Hospitality: Using the Direct Capitalization and DCF Sheets
Jen McGee
The Direct Capitalization and Discounted Cash Flow (DCF) sheets function together. The data in these sheets is collected as a function of completing the revenue and expense sheets. The Direct Cap and DCF tables are generated automatically and do not require any additional setup. However, you can modify and edit various cash flow-related elements to customize your analysis.
Please note that the platform has been updated since the formation of this video, the process and steps are the same, however the product may look slightly different.
How-to Guide
Discounted Cash Flow
Step 1:Access the Discounted Cash Flow (DCF) sheet.
Step 2: View all rows utilized for the Assumptions in this sheet.
Select the drop-down next to Assumptions, and select Show.
This will show you the collection of all the assumptions being utilized, tracking them here and utilizing them in the math going forward. In other words, for each projection for a particular year, the formulas rely on your estimates and then show the conclusions.
Pro-Tip: You don’t have to show these rows in your report; it’s there for transparency.
Set up your DCF sheet
These settings can be adjusted and updated as needed. The sheet will update accordingly, and you will not need to re-enter any previously entered data.
Pro-tip: You must reset the tables in the Present Value Assumptionsby selecting Click Here to Show All Rows and then selecting it again to Hide All Rows.
Step 1: Decide your holding period
Use this drop-down to select your desired period.
Step 2: Decide the year for Prospective Upon Completion and Prospective Upon Stabilization.
Use the drop-down menu to select the desired year.
These two options will alter the math in specific places and ensure that it calculates growth starting from the first stabilized year and from an unstabilized period. It also adjusts the Present Value Assumptions table to start at the selected Stabilized Year.
As-Is Comment
This cell will automatically populate and fill in based on the information selected for stabilization and the information in the table.
Prospective Upon Completion Comment
This cell will automatically populate and fill in based on the information selected for stabilization and the information in the table.
Prospective Upon Stabilization Comment
This cell will automatically populate and fill in based on the information selected for stabilization and the information in the table.
Discounted Cash Flow
The cash flow table shows both the As Is and the Stabilized periods (co-terminus).
The As-Is cash flow contains all of the periods in the Hold.
Stabilized cash flow refers only to the stabilized periods in the Hold.
In the example above, the stabilization year is set for year three.
Present Value Assumptions
Step 1: Input your Terminal Capitalization Rate
Step 2: Enter the sensitivity analysis spreads
Step 3: Add the Discount Rate
Step 4: Add the Reversionary Sales Cost
Step 5: Make the appropriate selection for NOI to Capitalize
Note: The default setting is 12 Months Following Resale.
Step 6: Select if you want to Adjust NOI for Gross up.
Step 7: Select if you would like to Adjust Gross Resale.
Direct Cap
Step 1: Access the Direct Capitalization sheet
Select HDCAP from the bottom or left-side menu.
The Direct Capitalization sheet directly follows the DCF sheet, as it utilizes the year you identified to be stable to determine which NOI to capitalize into perpetuity. In our example, we have the stabilized year set at year three. The totals will match the totals in the DCF sheet based on Year Three.
Discounted Cash Flow Matrices
The tables below the Present Value Assumptions reflect the matrix for each of the scenarios.
HDCF2 and HDCF3
Should you decide not to use a co-terminus method or you want to change the presentation to exclude non-stable years, you have the option to use additional DCF tables in your report to present the corresponding start year for the Upon Completion and Upon Stabilization value scenarios.
Step 1: Review the years set for Upon Completion and Upon Stabilization on the HDCF sheet.
The same dropdowns on the HDCF_2 and HDCF_3 sheets will mirror the selections made on the HDCF sheet
Step 2: Review the Discounted Cash Flow 2 and 3 tables
Select HDCF_2 or HDCF_3 from the bottom menu
The HDCF2 and HDCF3 tables will update based on the year set for Upon Completion and Upon Stabilization.
For example, in the HDCF2 table, the Year 1 column will actually be Year 2 of the table on HDCF, as the Upon Completion field is set to 2:
HDCF_2
HDCF
This will allow you to present separate DCF tables for your Upon Completion and Upon Stabilization scenarios.
Once you have completed all the necessary information in the Direct Capitalization and Discounted Cash Flow sheets, you can move on to the Values sheet.
Additional Questions? Valcre Support is ready to help with all your Valcre product questions! You can connect to us via phone, email, or chat!
Comments
0 comments