Hotel Insurance Program & Claims Register: insurance register, BI sum insured calculator and claims log for hotels

Full description

For financial controllers, general managers, risk managers and asset managers who run a hotel's insurance program with a broker. The Policies tab lists each cover (property damage, business interruption, public and employer's liability, excess liability, cyber, motor, money, machinery breakdown, directors and officers, terrorism) with sum insured or limit, deductible, net and gross premium, rate per mille, premium per key and a renewal status from the days to expiry. Property_Values builds the buildings reinstatement value from floor area, rebuild cost, fees, debris removal and cost escalation, and shows how the average clause cuts an example claim when a sum insured is too low. BI_Calculator turns revenue less uninsured working expenses into insurable gross profit, projects it to the end of the indemnity period and sets the required sum insured, then tests every start month against your seasonal profile. The claims register tracks status, reserve, payment, recovery %, retained cost and ageing, and Claims_Analysis reports loss ratios on earned premium by policy year.

Save with a bundle: this workbook is also in the Hotel Finance & Investment Pro Bundle, 25 workbooks for $59 instead of $329. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.

What is inside

• Hotel_Insurance_Program_Claims_Register.xlsx: 10 tabs, 1,994 formulas, 2 native charts, 12 integrity checks, 22 drop-down and input rules, a Method tab with 16 calculation notes.
• Policies: 11 sample policies in 11 cover classes, 18 rows, renewal alerts and 3 years of premium by class.
• Property_Values: reinstatement value in 9 visible steps, 6 items at risk, cover ratio, average clause and settlement of an example loss.
• BI_Calculator: insurable gross profit, 14-step sum insured projection and a 12-month seasonality test.
• Claims: 100 sample claims (19 open) and room for 150, with 19 columns and 4 automatic flags.
• Claims_Analysis: loss ratio by policy year on earned premium, claims by class and by cause, open claims in 5 age bands.
• Dashboard: 11 KPI tiles, a check tile, 2 charts and a renewal list of the next 5 policies, printable on one page.
• README.txt and LICENSE-and-Terms.txt.

How it works

1. Set the rooms, the as-at date and the first month of your policy year.
2. List every policy with limits, deductibles, premiums, taxes and dates; renewal statuses update from the as-at date.
3. Enter floor area, rebuild cost and contents values, and last year's revenue and variable costs, to test your sums insured.
4. Log each claim when it is notified and update reserves and payments as the insurer reports.
5. Before renewal, read loss ratios, open claims and ageing on Claims_Analysis and the Dashboard.

Details

• Format: one .xlsx workbook, English.
• Software: Excel 2016 or later, or LibreOffice Calc 7 or later.
• No macros, no data tables, no pivot tables, no add-ins.
• Works in any currency.
• Delivery: instant download.

Good to know

• Sample data is illustrative and does not describe any real hotel or insurer.
• Not insurance, legal or financial advice. The policy wording governs every claim; review sums insured with your broker and a qualified valuer.
• Taxes on premium, compulsory covers and notice periods vary: verify local requirements.
• Single-user license.

FAQ

How does the average clause test work?

For each item the workbook divides the sum insured by the value at risk. When the ratio is below 100%, an example loss is paid in that proportion, before the deductible, so you can see what the hotel would bear.

How is the business interruption sum insured calculated?

Revenue less uninsured working expenses gives insurable gross profit. It is projected with your growth trend over the months to inception, the policy period and the indemnity period, then multiplied by indemnity months / 12.

What does the seasonality test add?

It sums your monthly profit profile over every possible loss window. If a closure starting before the high season loses more than the sum insured, the test shows it.

How are loss ratios calculated?

Incurred claims (paid plus reserve) divided by earned net premium for each policy year. The current year uses the share of the year that has passed at the as-at date.

Can I add more policies and claims?

Yes. There are 18 policy rows and 150 claim rows with formulas ready; new cover classes, causes and insurers are added on Settings.