Excel Models Excel Tips Hotel RE Education Real Estate Financial Modeling Value-Add

Tutorials For The Hotel Valuation Model (Updated 01.03.19)

Tutorials For The Hotel Valuation Model (Updated 01.03.19)

Welcome to the A.CRE Lodge Valuation Mannequin‘s Tutorial Web page. On this web page you will see all of the tutorials for the A.CRE Lodge Valuation mannequin in Excel. There’s a lot to study inside this mannequin and over time, I’d like to point out you each the right way to use it in addition to how I constructed it. Within the first seven tutorials, we’ll concentrate on find out how to use the lodge valuation mannequin by going by way of every tab, separately. I hope that is useful and please contact me with any requests, strategies, or in case you occur to seek out errors.

To obtain, go to the Lodge Valuation Mannequin in Excel Principal Web page

Tutorials for the Lodge Valuation Mannequin in Excel

Word: This workbook incorporates macros so upon opening, in case you are prompted with the next – ‘SECURITY WARNING Macros have been disabled.’ – please click on the ‘Allow Content material’ field. The mannequin won’t work correctly with out macros enabled.

Tutorial 1 – The Abstract Tab
Tutorial 2 – CFSummary Tab (Excessive Degree Money Movement Abstract)
Tutorial three – OpCashFlow Tab (Detailed Working Money Flows)
Tutorial Four – F&B Tab (Meals and Beverage Inputs and Evaluation)
Tutorial 5 – OthOppDepts Tab (Different Working Departments Inputs)
Tutorial 6 – OthExpenses Tab (The Remaining Expense Inputs)
Tutorial 7 – Penetration Tab (Penetration Evaluation)
Tutorial eight – Waterfall – IRR Hurdles Tab

Tab 1. Cowl

The duvet sheet is the place you will see that any related hyperlinks to movies or web sites which might be related or in help of the mannequin. Moreover, because the mannequin is up to date over time, model notes that doc the updates might be proven on this tab.


Tutorial 1 – The Abstract Tab

As you’ve gotten in all probability guessed, the Abstract tab is the place you can find all the excessive degree and essential info and metrics that must be proven up entrance for evaluate by you and any third social gathering. Along with displaying this info, this tab additionally incorporates many essential inputs for the mannequin. As is the case in all of our fashions and on each sheet on this specific mannequin, any cells that include blue textual content or numbers are drivers that you must alter and replace in line with your particular circumstances. On this sheet, all the blue inputs are situated within the bins on the left hand aspect. Please see the video under for for an summary of this tab:

Abstract Tab Video


Tutorial 2 – CFSummary Tab (Excessive Degree Money Move Abstract)

The CFSummary tab provides the consumer a excessive degree assessment of your complete funding money move, from acquisition to disposition. Rows 60 and 62 present unlevered and levered money flows, respectively, and on the backside of the sheet you’ll be able to evaluate the next annual return and danger metrics by yr: free and clear return, money on money return, debt service protection ratio, and the debt yield.

Money Stream Abstract Worksheet Video


Tutorial three – OpCashFlow Tab (Detailed Working Money Flows)

This web page offers an in depth overview of the working money flows by line merchandise. There are a number of distinctive attributes in lodge proformas which might be totally different than the standard actual property proforma. I’ve created a companion publish utilizing this worksheet that gives an summary and goes over these variations generally element, which could be discovered [here].

This worksheet additionally incorporates a couple of necessary inputs within the header.

  • In row 10, you’ll set the occupancy fee for annually
  • Cell C11, you’ll set the ADR in yr 1
  • and in row 12, you wil set the ADR Progress Price for annually.

Within the picture under, you’ll be able to see these sections inside the purple field with the inputs in blue textual content. Click on on the picture for a better view.

Working Money Movement Worksheet Video


Tutorial Four – F&B Tab (Meals and Beverage Inputs and Evaluate)

The video under will stroll you thru this tab and it’s also possible to learn the information under the video:

F&B Tab Video

On this worksheet you will have the power to element 5 particular F&B choices. Cells F6:J6 is the place you’ll insert the names of the F&B providing. What’s presently plugged in now’s the next: Room Service, Bar, Restaurant, Banquet Catering, and [Other].

Under that in Cell F8, you’ll insert the typical visitor per room. That is wanted as a result of we calculate meal income and bills based mostly on a per individual calculation and we have to perceive how many individuals are occupying a room on common to be able to do that.

F&B Income Inputs

Cells D10:Okay60 – What Proportion of the Occupied Rooms Use the F&B Choices

The subsequent part under asks ‘What Proportion of the Occupied Rooms Use the F&B Choices?’ (Cells D62:Okay88). Right here you could have the power to venture room utilization of the F&B by meal.

Easy vs. By Yr

Easy

To offer a constant and easy % of occupied rooms that use the F&B by meal, be sure Cell E13, Easy, is clicked. From right here you’ll be able to fill in what % of the occupied rooms use the F&B choices for the maintain interval.

By Yr

If there’s an anticipation of a change in F&B utilization over time, then click on Cell E14, ‘By Yr’. Right here you possibly can alter F&B utilization by occupied rooms per meal for annually. That is notably useful in lots of instances reminiscent of a restaurant repositioning or improving one of many F&B choices to encourage progress.

Cells D62:Okay88 – Variety of Non-Lodge Friends That Use the F&B Per Yr

On this part you possibly can undertaking the quantity of non lodge friends make the most of the F&B choices. As with the occupied room inputs above, this part supplies you with a Easy choice by clicking on Cell E65 the place you assume the inputs are static via the funding, and the By Yr choice, Cell E66, the place you even have the power to research projected progress in use.

Cells D90:Okay98 – Common Examine Measurement per Individual

On this part, you merely enter the typical verify measurement per individual for yr 1 and you may develop every F&B class by a sure proportion annually.

F&B Expense Inputs

On this part, there are a couple of choices to undertaking the correlated F&B bills. Clicking Cell E105 and having the Easy choice lively, lets you use one enter for all of the bills for the precise F&B providing that may be a proportion of income for every F&B merchandise. See Cells F114:J114.

Clicking Cell E106, Detailed Line Gadgets, opens up a brand new part and permits the consumer to interrupt out particular prices by line merchandise. As with the Easy state of affairs, for every line merchandise, you’ll need to enter the price as a proportion of income.

Management Prices Yearly?

Clicking Sure, Cell E127, will will let you management the prices of every F&B providing going ahead. Please notice that the primary yr is already offered from the inputs above. That is so you’ll be able to work on detailed value gadgets and it’ll copy over right here. Then, you possibly can management prices in combination for every F&B merchandise. If you choose No, Cell E126, then the prices will stay the identical proportion of income over the entire time period.

Particular person F&B Providing Income Assessment

Cell N5 is a drop down menu that assist you to choose one of many F&B choices and evaluation them intimately within the desk to the appropriate, Cells P4:AA17. Selecting an F&B providing will alter the desk in accordance with the respective inputs for the choice chosen and you may evaluation for additional evaluation.

Complete F&B Income, Complete F&B Bills, and Expense/Income Sections

These are the tables under the Particular person F&B Providing Income Assessment part. These present all of the F&B income and bills in isolation from the remainder of the proforma so you possibly can shortly see the money circulate as you make alterations.


Tutorial 5 – OthOppDepts Tab (Different Working Departments Inputs)

This tab covers different revenue facilities outdoors of F&B and lodge rooms.

Different Working Division Income

Right here you’ve the power so as to add 5 separate departments in cells G8:G12. Upon downloading the mannequin, we’ve solely enter three: Area Rental,  Spa, and Present Store. To the left of the division inputs (Cells D8:E12) is the place you’ll choose the methodology you need to use to undertaking income sooner or later for every division. By clicking annual progress fee, you’ll be able to put in your yr 1 income projection in column H and apply an annual progress price in column I. Choosing Customized Progress Fee will allow you to manually  put within the income for annually inside the Income Money Circulate field. The full income from every division is summed up in row 13.

Different Working Division Bills

As with the income part, within the expense part you even have the power to do a Easy enter or Customized Yearly inputs for bills as a % of income.

Different Working Departments Worksheet Video


Tutorial 6 – OthExpenses Tab (The Remaining Expense Inputs)

This tab offers the inputs for Room Bills, Undistributed Bills, Franchise Charges, Administration Charge, Fastened Bills, and Cap Ex and FF&E Reserve.

Room Bills

On this part, you’ll put the price per occupied room in to Cell H7. you possibly can both have a static progress fee by ensuring Static, mixed Cells E7:E8, is clicked and placing within the annual progress fee in Cell I7 or you possibly can choose mixed Cells D7:D8 and put in a special progress price for annually in row eight.

Undistributed Bills

There are 5 separate Undistributed Expense gadgets which are pretty widespread which were positioned in Cells G14:G54: G&A, Gross sales and Advertising, I/T, Restore and Upkeep, and Utilities. If you need / produce other bills, you possibly can change these things to totally different classes if want be. Credit score Card charges have been additionally enter into the present model (1.zero) of this mannequin though they’re an expense that’s extra generally attributable to the working departments accordingly. The up to date mannequin could have this changed and/or eliminated.

For projecting these bills within the mannequin, you’ve got 4 totally different choices to select from in Four out of the 5 line gadgets. The second line merchandise, the place bank card charges are inserted (Row 24) is static and you may solely develop the expense out utilizing one progress price.

Let’s use Rows 14:15, G&A to explain how the expense projection optionality works. With cells D14 and E14 you’ll be able to choose to calculate bills as both a % of income or manually placing in yr in bills and utilizing an annual progress assumption. In cells D15 and E15 you possibly can select your progress projections as a Customized Enter for annually or one static enter to use to the full maintain interval.

So the choices are as follows:

  1. % of Income (D14) and Customized Enter (D15) – with this feature in cells Okay15:U15, you manually put in a % of the income and the worth is proven in row 14 above.
  2. % of Income (D14) and Static (E15) – with this feature, you set within the expense as a % of income in cell H14 for your complete maintain interval.
  3. Annual Progress (E14) and Customized Enter (D15) – In Cell H15, enter your yr 1 expense and in cells L15:U15 enter the expansion price for annually.
  4. Annual Progress (E14) and Static (E15) – In Cell H15, enter your yr 1 expense and in mixed cells I14:I15 enter the expansion fee for annually.

Once more, this is identical for all the opposite Undistributed Bills apart from the part with the Credit score Card charges, row 24.

Franchise Charges

This part consists of all the standard gadgets that could be included in a franchise charge such because the preliminary charge, royalties, advertising charges, reservation charges, and a fifth choice for different charges which may not have been included.

For the preliminary charge, you set within the quantity (Cell H65) and you may choose what yr that quantity must be paid (Cell I65). This performance is in right here in case you’ll reflag or could also be there’s another purpose you will have to pay a one time payment. If this isn’t wanted, you’ll be able to merely put a zero in Cell H65.

All the opposite charges in listed here are base don a % of income and have the power to be projected with a Static enter or Customized for annually. This works the identical as described within the different sections.

Administration Payment

The Administration Payment part, beginning in Row 80, offers an choice to enter a base charge as a % of Gross Income. You’ve gotten the standard choice of a static proportion over the maintain or you are able to do a customized enter for annually.

For the motivation payment, you’ve three choices for projecting the prices as follows:

  1. From Line Merchandise in Proforma (Cell D87) and Static (Cell E88) – utilizing this feature, in Cell H88,  you possibly can choose from three choices to base the motivation payment on: Complete Income, Complete Departmental Revenue, and Gross Working Revenue; and put within the % of the road merchandise to be paid to administration in Cell I87.
  2. From Line Merchandise in Proforma (Cell D87) and Customized (Cell D88) – choose from the three choices in cell H88 as described above and put within the % of the road merchandise to be paid to administration in annually in cells Okay88:U88.
  3. Customized (Cell E88) – with this feature you’ll be able to manually put in your incentive charges for annually in cells Okay87:U87.

Fastened Bills

This part is for taxes and insurance coverage. For taxes, put within the assessed worth, mill price, and annual progress projection in cells E94:E96 and for insurance coverage you possibly can put within the quantity in yr 1 and the annual progress projection in cells E:100:E101.

Cap Ex and FF&E

For the Cap Ex and FF&E reserve, it’s based mostly on a % of gross income and you may choose the choice to the left for a static enter or customized inputs for annually.

Row 110 help you put in a customized quantity in any yr for any anticipated main cap ex tasks.

Different Bills Worksheet Video


Tutorial 7 – Penetration Tab (Penetration Evaluation)

A essential element to assessing any lodge is to match the lodge to the native market comp set. There are few assets on the market that present this knowledge with the preferred group being STR (pronounced Star).

Aggressive Set

On this part, you’ll put within the room rely for as much as 10 lodge comps. As with the rest of this part, you’ll enter historic knowledge wanting 5 years again. Upon completion you possibly can look to row 19 for the full out there room nights of the comp set in annually. Row 21 and down exhibits the topic property with inputs from the Abstract tab. This exhibits what % of obtainable stock is our topic property. On this case, it’s 17.1% of the aggressive stock with the property’s room rely as a part of the denominator.

Market vs. Property Occupancy

On this part, you’ll enter each the historic and projected aggressive set occupancy charges. Row 32 is derived by dividing the topic property’s occupancy price by the aggressive set’s. If this quantity is under 100%, then the topic property is underneath performing in reserving room nights in comparison with the competitors, above 100% and the topic property is outperforming the comp set. In our instance, we begin at 78.7% and finish at 102.2%. This could possibly be an investor trying to reposition the property. The $8MM in Cap Ex in yr 2 that we noticed on the OthExpenses tab is probably one other clue as to this investor’s intentions.

Market vs. Property ADR

In row 35 you set within the historic and projected ADR of the comp set and in row 32 you’ll be able to analyze the ADR penetration of the topic property by yr.

Market vs. Property RevPAR

On this part, there are not any inputs wanted because the mannequin has already calculated all of the pertinent knowledge. Row 46 exhibits the RevPAR penetration.

Penetration Worksheet Video


Tutorial eight – Waterfall – IRR Hurdles Tab

This tab accommodates an ordinary Four-tier IRR hurdle waterfall mannequin to distribute proceeds between the overall associate and restricted companion. You’ve got the power to regulate the fairness contribution cut up in addition to the tier 1 proceeds cut up, and promote and hurdle price in every tier. All of the assumptions might be altered utilizing the blue cells within the Promote Construction field in cells B4:C24. Please watch the video under that may stroll you thru the right way to use it.

Waterfall Video

Concerning the Writer: Michael Belasco has over eight years of actual property and development expertise. He at present works for a worldwide actual property funding, improvement, and asset administration agency in San Francisco managing giant scale improvement tasks within the metropolis. Michael has each an MBA and Grasp in Actual Property with a focus in Actual Property Finance from Cornell College.

About the author

Admin