Hotel financial Excel model which generates the 3 financial statements as well as a valuation (incl. sensitivity analysis).
The model structure is as follows:
Timing: main conventions used in the model + 5 scenarios that can be used in order to evaluate the business plan and valuation.
Inputs (in yellow whatever can be amended as an assumption): detailed inputs for rooms, food and beverage and various other auxiliary revenue generating activities as well as their costs (includes detailed payroll) and the respective assumptions on balance sheet assumptions (fixed assets, working capital, debt and equity) and cash flow assumptions, includes as well tax and VAT assumptions and macro assumptions.
Calculations: this were all calculations are performed. Room revenues in 6 different categories along with their respective costs, Food & Beverage Revenues and Costs and other Revenue Generating Activities (Pool Bar, SPA, Weddings etc…), Admin Expenses. On the Balance Sheet Calculations we have detailed fixed assets assumptions along with capex and depreciation with 5 different categories, working capital assumptions, taxes (including income tax losses and VAT), debt obligations split as long term and short term, and finally Equity assumptions including minimum cash balance and dividends.
WACC (Weighted Average Cost of Capital): this tab presents a high level weighted average cost of capital used for the specific sector (this might differ depending on the country where the operations are carried over)
Financial Statements: everything is aggregated here into Income Statement, Balance Sheet and Cash Flow Statements with the relevant checks. No macros are needed everything is calculated in real time based on the assumptions used.
Valuation: the valuation of the company is calculated based the Free Cash Flows to the Firm and additionally a sensitivity analysis has been performed based on the WACC and the Growth Rate to Perpetuity. The central values of the sensitivity can be changed manually (yellow cells).
Summary: All the graphical representations of the most important KPIs of the Hotel Company are presented here.
Checks: A dedicated worksheet that makes sure that everything is working as it should!
Important Notice: Yellow indicates inputs and assumptions that the user is able to change, Blue cells are used for called up cells, and white cells with black characters indicates calculation cells.
The model is available as PDF Demo Version and as a Full Version in MS Excel.