Free Hotel Rate Plan Comparison Excel Template for 2026

How to use the template

1. Set the Dashboard assumptions

Begin with the yellow cells on the Dashboard. Enter the default occupancy tax rate, service charge rate, target net room revenue, rate parity tolerance, competitive rate tolerance, default OTA commission rate, and minimum advance purchase days.

These are editable planning assumptions. Confirm tax rates, service charges, commissions, and operating targets for the property before relying on the calculated results.

2. Enter each rate plan combination

On the Rate Plans worksheet, use one row for each rate plan, room type, booking channel, and stay date combination. Record the rate plan ID, source reference, plan name, category, room type, channel, stay date, booking window, and Base BAR.

Select a discount type of None, Percent, or Fixed Amount, then enter the applicable discount value. Add plan terms such as minimum and maximum stay, advance purchase days, refundability, cancellation policy, payment timing, breakfast, parking, other inclusions, and status.

3. Confirm costs and calculated amounts

Review occupancy tax, service charge, commission, fixed channel cost, and package inclusion cost for each row. The formulas calculate:

• Pre-tax selling rate after the entered discount
• Estimated tax and service charge
• Estimated guest total
• Estimated distribution cost
• Estimated net room revenue
• Discount to BAR and variance from the net revenue target

Estimated net room revenue deducts the entered commission, fixed channel cost, and package inclusion cost from the pre-tax selling rate. It is a planning calculation rather than an accounting result.

4. Record market shop observations

Enter manually collected observations on the Market Shop worksheet. Include the shop date, stay date, length of stay, guest count, hotel and competitor room types, booking channels, pre-tax rates, refundability, breakfast inclusion, and a source URL or reference.

For a useful comparison, match like-for-like terms wherever possible. The workbook marks records as Not Like-for-Like when refundability or breakfast inclusion differs.

5. Review the Dashboard and exceptions

Check active plan counts, average public rate, average estimated net revenue, average discount to BAR, plans below target, parity alerts, the lowest current competitor rate, and the hotel rate versus the market average. Investigate records marked Review, Aging, Stale, Terms Differ, or anything other than OK, then save a dated copy after a formal review.

What does the hotel rate plan comparison calculate?

The Rate Plans worksheet begins with Base BAR and applies either a percent discount, a fixed-amount discount, or no discount. This produces the pre-tax selling rate. Entered occupancy tax and service charge rates are then used to estimate tax, service charge, and the guest total.

Distribution cost combines commission on the pre-tax selling rate with the entered fixed channel cost. Package inclusion cost is deducted separately. The resulting net revenue estimate can be compared with the target entered on the Dashboard. The workbook also calculates the percentage discount to BAR.

This structure helps revenue teams distinguish the guest-facing price from the amount remaining after the specific distribution and package costs entered in the row.

How does the workbook compare hotel and competitor rates?

The Market Shop worksheet compares the hotel's pre-tax rate with a competitor's pre-tax rate for an observed stay date. It calculates the dollar difference and percentage difference, then checks whether refundability and breakfast inclusion match.

If those terms differ, the comparison is labeled Not Like-for-Like and the parity result becomes Terms Differ. Comparable observations are assessed against the Dashboard's rate parity tolerance. Their market position is labeled Below Market, Near Market, or Above Market using the competitive rate tolerance.

Shop age is also calculated from the observation date. A record is Current at seven days or less, Aging from eight through fourteen days, and Stale after fourteen days. This gives operators a practical way to separate current observations from older shops.

Which hotel rate plan details can be compared?

The workbook supports categories including Best Available Rate, Member, Advance Purchase, Package, Corporate, Government, Senior, AAA, OTA Promotion, Group, and Other. Booking channel choices include Direct Website, Voice / Call Center, Brand.com, GDS, OTA, Walk-In, Group Sales, and Other.

Operational terms can be recorded alongside price. These include booking dates, stay date, minimum and maximum stay, advance purchase days, refundability, cancellation policy, payment timing, breakfast, parking, other inclusions, and plan status. This makes it possible to review both price and booking conditions instead of comparing rates in isolation.

What should operators review on the Dashboard?

The Dashboard summarizes active rate plans and calculates average public rate, average estimated net revenue, average discount to BAR, plans below the revenue target, parity alerts, the lowest current competitor rate, and the difference between the hotel rate and market average.

Separate summaries group active plan results by rate category and booking channel. One chart references rate category information, while the competitor chart uses a fixed source area linked to the first five current sample records. Operators should confirm that the chart source reflects the comparison data they intend to present.

Use the displayed KPIs as a review queue. A below-target count may point to discounting, commission, fixed channel costs, or package costs that need closer examination.