Originally published: 22/08/2022 09:17
Last version published: 04/01/2024 08:46
Publication number: ELQ-57949-2
View all versions & Certificate
Last version published: 04/01/2024 08:46
Publication number: ELQ-57949-2
View all versions & Certificate
Hotel Real Estate Investment Analysis Template in Excel
Plan out all the assumptions for constructing/developing or acquiring a hotel, operating, and exit. Up to 10 year analysis. Includes three statement model.
hotel acquisitionreal estate modelunderwriting spreadsheethotel constructionthree statement modeldcf analysisirrrun a hotel
Description
The hospitality industry requires a combination of modules from a few lines of thinking. You will have staffing assumptions, there will be a daily room rate with seasonality (similar to short-term rental logic), and then you have a lot of debt/equity structures that are similar to multifamily/mixed use real estate modeling.
Tabs of this Template:
Global Control - Enter assumptions that define timing (launch year) and end month of forecast, exit cap, if the facility is sold or not at the end month, taxes, begin month of bookings, if debt is going to be used or not, and all sanity checks across the entire financial model.
Development & Acquisition - Define initial investment requirements. There are six sections that allow the user to enter costs such as purchase price, closing costs, construction / development or renovation costs, and what have you. 22 slots for each of the six sections. I know it is probably overkill, but better more than less slots.
Also, on this tab you can define any GP fees, such as acquisition / renovation fees, or any other fees as a percentage of each section. Also, the on-going management fee is defined here. That rate will apply to gross bookings.
Depreciation-Dev & Acquisition - Define useful life of each initial cost section and if none, then you can enter zero. Note, these depreciation costs automatically start kicking in on the income statement when the bookings being (defined month on the Global Control tab).
Revenue Assumptions - Up to twenty room types, each configurable by count, occupancy, improvement in occupancy, stabilized occupancy, and a base rate. User can enter variance from that base rate in each month of the year (seasonality) and that will adjust the nightly rate accordingly. For occupancy, a single rate is entered for each month, and then a pecentage improvement can be entered in each subsequent year. There is a global occupancy limiter to ensure no rates get over the defined rate.
Staff - Over 20 slots to define the type of staff, count, monthly salary/cost, payroll taxes/benefits and annual growth rate.
Other Costs - Fixed cost assumptions regaurding regular operation of the hotel. This would be things like insurance, utilities, general site maintenance, legal, accounting, and any other costs related to running the hotel.
Cap Table - The minimum equity required for the project to keep cash about zero will auto-fill. The user can then define investment amounts from a pool of outside investors and inside investors as well as their distrubtion rates. This is just the initial joint venture structure, but a second waterfall hurdle deal structure is also used and is separate from this schedule. This enables a simple split of contributions/distributions and a more sophisticated style depending on what is needed. This tab also displays IRR (per monthly cash flows of each individual investor and in aggregate per pool).
Debt Schedule - Depending on the deal financing type, this model allows for either the use of an interest-only loan that can accrue interest or apply it each month for a defined period or no interest-only loan and any debt required will flow to the p+i loan. The assumptions are very easy to toggle depending on what is needed. There is an option for a final REFI loan as well at a defined month. The value is based on an LTV + cap rate against trailing 12-month NOI at the refi month.
Benefits - A schedule that displays payroll taxes and benefits for staff based on what was entered on the staff tab.
IS- M/A - Standard formal monthly/annual income statement.
BS- M/A - Standard formal monthly/annual balanse sheet.
CF- M/A - Standard formal monthly/annual cash flow statement.
Distributions - DCF Analysis on a monthly basis. Shows deal level and investor group / owner group per the cap table. Includes IRR/NPV.
Visualizations - 10 visualizations that display financial preformance and key metrics of the deal over the forecasting period.
Executive Summary - Annual summary of all major financial line items, driving down to cash flow.
Monthly and Annual Detail - Shows how the assumptions all come together in order to calculate resulting net operating income, costs, debt, and expenses.
Optional CF Waterfall - A traditional cash flow waterfall that uses IRR hurdles to define how cash is split between the LP and GP. User can define contribution rates, hurdle rates, and split rates at each tier. Note, any acquisition / construction / management fees paid to the GP will go towards equity requried for the deal and all of that will be paid as income to the GP. This is displayed accordingly for the GP.
Annual GP&LP Summary - A final annual summary of the contributions and distributions to the GP/LP based on the waterfall structure. Includes separate fees for acquisition and management.
Note, this template is also included in my real estate financial model bundle: https://www.eloquens.com/tool/vJdYH9R7/finance/real-estate/the-complete-real-estate-bundle
This template is also included in two bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Real Estate: https://www.eloquens.com/tool/vJdYH9R7/finance/real-estate/the-complete-real-estate-bundle
The hospitality industry requires a combination of modules from a few lines of thinking. You will have staffing assumptions, there will be a daily room rate with seasonality (similar to short-term rental logic), and then you have a lot of debt/equity structures that are similar to multifamily/mixed use real estate modeling.
Tabs of this Template:
Global Control - Enter assumptions that define timing (launch year) and end month of forecast, exit cap, if the facility is sold or not at the end month, taxes, begin month of bookings, if debt is going to be used or not, and all sanity checks across the entire financial model.
Development & Acquisition - Define initial investment requirements. There are six sections that allow the user to enter costs such as purchase price, closing costs, construction / development or renovation costs, and what have you. 22 slots for each of the six sections. I know it is probably overkill, but better more than less slots.
Also, on this tab you can define any GP fees, such as acquisition / renovation fees, or any other fees as a percentage of each section. Also, the on-going management fee is defined here. That rate will apply to gross bookings.
Depreciation-Dev & Acquisition - Define useful life of each initial cost section and if none, then you can enter zero. Note, these depreciation costs automatically start kicking in on the income statement when the bookings being (defined month on the Global Control tab).
Revenue Assumptions - Up to twenty room types, each configurable by count, occupancy, improvement in occupancy, stabilized occupancy, and a base rate. User can enter variance from that base rate in each month of the year (seasonality) and that will adjust the nightly rate accordingly. For occupancy, a single rate is entered for each month, and then a pecentage improvement can be entered in each subsequent year. There is a global occupancy limiter to ensure no rates get over the defined rate.
Staff - Over 20 slots to define the type of staff, count, monthly salary/cost, payroll taxes/benefits and annual growth rate.
Other Costs - Fixed cost assumptions regaurding regular operation of the hotel. This would be things like insurance, utilities, general site maintenance, legal, accounting, and any other costs related to running the hotel.
Cap Table - The minimum equity required for the project to keep cash about zero will auto-fill. The user can then define investment amounts from a pool of outside investors and inside investors as well as their distrubtion rates. This is just the initial joint venture structure, but a second waterfall hurdle deal structure is also used and is separate from this schedule. This enables a simple split of contributions/distributions and a more sophisticated style depending on what is needed. This tab also displays IRR (per monthly cash flows of each individual investor and in aggregate per pool).
Debt Schedule - Depending on the deal financing type, this model allows for either the use of an interest-only loan that can accrue interest or apply it each month for a defined period or no interest-only loan and any debt required will flow to the p+i loan. The assumptions are very easy to toggle depending on what is needed. There is an option for a final REFI loan as well at a defined month. The value is based on an LTV + cap rate against trailing 12-month NOI at the refi month.
Benefits - A schedule that displays payroll taxes and benefits for staff based on what was entered on the staff tab.
IS- M/A - Standard formal monthly/annual income statement.
BS- M/A - Standard formal monthly/annual balanse sheet.
CF- M/A - Standard formal monthly/annual cash flow statement.
Distributions - DCF Analysis on a monthly basis. Shows deal level and investor group / owner group per the cap table. Includes IRR/NPV.
Visualizations - 10 visualizations that display financial preformance and key metrics of the deal over the forecasting period.
Executive Summary - Annual summary of all major financial line items, driving down to cash flow.
Monthly and Annual Detail - Shows how the assumptions all come together in order to calculate resulting net operating income, costs, debt, and expenses.
Optional CF Waterfall - A traditional cash flow waterfall that uses IRR hurdles to define how cash is split between the LP and GP. User can define contribution rates, hurdle rates, and split rates at each tier. Note, any acquisition / construction / management fees paid to the GP will go towards equity requried for the deal and all of that will be paid as income to the GP. This is displayed accordingly for the GP.
Annual GP&LP Summary - A final annual summary of the contributions and distributions to the GP/LP based on the waterfall structure. Includes separate fees for acquisition and management.
Note, this template is also included in my real estate financial model bundle: https://www.eloquens.com/tool/vJdYH9R7/finance/real-estate/the-complete-real-estate-bundle
This template is also included in two bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Real Estate: https://www.eloquens.com/tool/vJdYH9R7/finance/real-estate/the-complete-real-estate-bundle
This Best Practice includes
1 Excel model and 1 Tutorial Video
Further information
A template designed to output up to a 10 year financial forecast for hotels.
Hotel acquisition and/or renovation or construction/development.
