The Pro Forma Assumptions section is where the user can input all income and expenses assumptions for the analysis. These values will be displayed on the Pro Forma report.
The first section of blue inputs in the Pro Forma Assumptions (Rooms Available, Rooms Sold, Occupancy, ADR, and RevPAR) are intended for reference only and will not be used for any calculations, however, the calendar year values and Annualized Period values will be displayed on the Operating Summary report to be used for comparison to the analysis period.
The first column of the Pro Forma Assumptions inputs contains the line item labels for all income and expenses. All cells with blue fill and blue text can be edited or removed, as needed. The labels entered in this column will become part of the bucketing list used for bucketing expenses on the Historical Data tab.
The first column to the right of the labels is the “Broker OM” column. The values entered in this column are purely meant to be used for comparison and for reference when determining pro forma adjustments to income and expenses. These values will not be used in any calculations of the model (unless selected for “Annualized Period”). The blue label in the top row can be edited as needed.
The following 3 columns display annual totals gathered from the Historical Data tab. The first column displays calendar year totals from either the previous calendar year, 2 calendar years prior, or 3 calendar years prior (selected from the dropdown). If “Prev. Cldr Yr” is selected, the annualized total of the calendar year prior to the year of the last month of the historical data will be displayed (for example, if the latest month of historical data is April 2023, the displayed values will be the annualized 2022 values). However, if the last month of the historical data is December, the previous calendar year will be considered that year (for example, if the last month of historical data is December 2022, the “Prev. Cldr Yr” selection will display annualized values for 2022).
The next column displays annualized trailing values for the selected number of months (1 through 12).
The “Annualized Period” columns display either the annualized trailing values or the “Broker OM” values. These will be used for projecting analysis year 1 (with the exception of Rooms Revenue, which will be based on the Occupancy & Revenue Assumptions). Note: The operating expenses can be based on a different trailing period than is used for the income, using an independent dropdown beneath the “Annualized Period” header of the expense section.
The Adjustment columns allow the user to override the projections for each income/expense item. Adjustment Type options may vary for depending on the given income or expense item. All income rows (excluding Rooms Revenue) have the following adjustment options: $/Year, $/Month, $/SF/Year, $/SF/Month, POR (per occupied room), PAR (per available room). If “No Adjustment” is selected or there is no option selected for Adjustment Type, the Annualized Period total for that line will be used as the projected value for analysis year 1.
In addition to the above-mentioned Adjustment Types, departmental expenses also have the following options: % of Sales, % of Total Sales, and % of F&B Sales (Food & Beverage Sales). The “% of Sales” Adjustment Type will calculate as a percentage of revenue of the applicable item. For example, if “% of Sales” is selected for Food Sales Expense, the expense will be calculated as a percentage of Food Revenue. The “% of Total Sales” Adjustment Type will calculate as a percentage of the total Rooms, Food, and Beverage Revenue.
Rather than having detailed operating expenses, the user has the option to project operating expenses at a fixed percentage of effective gross revenue using the OpEx Override. To use, select “Y” from the dropdown and enter the % of EGR to be projected for operating expenses in the cell to the right. Note: This override does not include Real Estate Taxes or Management Fee, which can be controlled separately.
The Analysis/Stabilized Year columns are used to display the total and per room annual values given the selections at the top of the section. Firstly, the Stabilized Occupancy % input determines at what point stabilization is met. Stabilized year 1 will begin in the first month when the average forward 12 months’ occupancy meets or exceeds the Stabilized Occupancy %. If the year type selected is “Stabilized”, a note will appear to explain how stabilization is defined and provided the month in which stabilization occurs. Note: The Analysis/Stabilized Year dropdown is only used for display purposes (on the Pro Forma Assumptions section of the Inputs tab and the Pro Forma report) and the selection will not affect returns.
The last three columns of the Pro Forma Assumptions contain growth inputs (annually compounding) for each income and expense line item (excluding Rooms Revenue and Real Estate Taxes). The growth rate entered in the Year 4 column will also be applied to every year after year 4. Note: These growth rates always correspond to analysis years, regardless of whether “Stabilized” or “Analysis” is selected as the pro forma display option.
Management Fee can be adjusted as either a percentage of total effective income or percentage of room revenue. The Minimum Monthly input will determine the lowest Management Fee expense per month throughout the analysis. The Min Monthly Growth % input increases the Minimum Monthly management fee on an annual basis (compounding).
