A small hotel can produce a weekly revenue report in about twenty minutes. Export a reservations list from your property management system (PMS) covering last week, the next six weeks and the same dates last year. Let a spreadsheet calculate occupancy, ADR, RevPAR, pickup and pace against last year, then have ChatGPT or Claude write the one-page summary.
The order matters. Chat assistants read tables well but can slip on arithmetic across many rows, so the sums belong in the spreadsheet and the assistant's job is to compare, spot oddities and ask you questions. Rate decisions stay with you: the report ends with questions, options and a decision log, never an instruction.
The six numbers a weekly page needs, and nothing else
Most hotel reports are either a wall of PMS columns or a dashboard of thirty tiles. A weekly page for the owner needs six figures, each answering one question. Define them once at the top of the sheet so the assistant, and your front-desk team, read them the same way.
| Figure | How it is worked out | The question it answers |
|---|---|---|
| Occupancy | Rooms sold divided by rooms available | How full were we? |
| ADR (average daily rate) | Room revenue divided by rooms sold | What did we get for each room we sold? |
| RevPAR (revenue per available room) | Room revenue divided by rooms available, or occupancy multiplied by ADR | How did fullness and price work together? |
| On the books (OTB) | Room nights already reserved for a future date, cancellations removed | What is already reserved for the weeks ahead? |
| Pace | On the books now, set against what was on the books for the equivalent date at this point last year | Are future dates filling faster or slower than last year? |
| Pickup | Room nights added to the books over a set period, usually the last seven days | How quickly are bookings arriving right now? |
Pace is the figure most small hotels skip, and it is the one that changes decisions. It compares the books today with last year's books at the same point, not with last year's final result. A date that finished at 78 room nights last year says little on its own. If 70 of those were already booked at this stage, then 36 today is a real gap. If only 44 were, the gap is small and last year's late rush may simply repeat.
Line the dates up by weekday: the date 364 days earlier falls on the same day of the week, so Saturdays meet Saturdays. Holidays and events that moved between years will distort a comparison, and the assistant cannot know about them unless you say so. Add a notes line to the sheet each week, such as "school holidays started a week later this year" or "a conference moved from the 14th to the 21st".
Three definitions also need fixing once, because changing them mid-year breaks every comparison. First, what counts as an available room: if one room is out of order for a three-week refurbishment, either take it out of the available count or leave it in and note the loss, but do not switch between the two. Second, whether complimentary and house-use rooms count as sold. Third, which revenue you use: room revenue before tax, with a note on whether breakfast sits inside the rate. Write the three rules in a notes tab and paste them into the prompt.
What to export from the PMS, and what to keep out of the chat
Every PMS names its reports differently. Look for a reservations list you can download as CSV or Excel for a date range, and for any report called something like occupancy forecast, pickup or pace. If a pace report exists it saves effort; if not, one reservations export with the right columns is enough to rebuild it. If you are unsure what your system and your channel manager (the software that sends rates and availability to booking sites) can export, what software hotels use explains how the pieces fit.
The export needs these columns, one row per room booked:
- Booked on, the date the reservation was created. Without it you cannot rebuild pace or pickup.
- Arrival and departure dates. The departure date is the morning the guest leaves, so a stay from the 12th to the 14th occupies the nights of the 12th and 13th.
- Room revenue for the stay, before tax, with breakfast and extras treated the same way on every row.
- Status and, if the PMS offers it, cancelled on. Cancelled bookings stay in the file with their dates: they were on the books once, and last year's pace depends on knowing when they dropped off.
- Room type and channel (direct, one of the online travel agencies, phone, walk-in). Both are optional and both make the report better.
Leave out guest names, emails, phone numbers, addresses, card details and any reservation reference that links to a guest record. The report never needs them, and a spreadsheet with no personal data is one you can paste into an assistant without a second thought. If your export insists on names, delete those columns before the file goes anywhere. Revenue figures are still commercially sensitive, so use a work account and check the model-training switch in the privacy settings of whichever assistant you use: business plans of the major assistants do not train on your content by default, and consumer plans let you switch training off.
The window matters too. Export every reservation with a departure date from about thirteen months ago to three months ahead. Thirteen months back covers last week's comparison and last year's position at the same point; three months ahead covers the pace section. For a property of six to forty rooms that is a file of a few thousand rows at most, which any spreadsheet handles easily. If the PMS cannot export that range in one go, export two files and paste them under each other. Cleaning messy data covers dates that arrive as text and duplicated rows, the two faults most exports have.
Why the sheet does the sums and the assistant does the reading
Hand an assistant a 600-row export and ask for weekly ADR, and you will usually get an answer that looks right. Usually is not good enough for a figure you will price against. ChatGPT's pricing page lists data analysis and file uploads on every plan (limited on the free one), and Claude's lists file creation with code execution on every plan, so both can run code on a file. A spreadsheet still wins for this job because its formulas are visible and its totals can be ticked against your PMS.
The commonest slip is averaging averages. Take the daily ADRs from the example week further down: $95, $95, $99, $105, $140, $150 and $114. Their simple average is $114.00. The true weekly ADR is total room revenue ($9,120) divided by total rooms sold (76), which is $120.00. The simple average gives a quiet Monday the same weight as a sold-out Saturday and understates the rate by 5%. A chat assistant asked to "average the ADR column" makes exactly this slip, and so does a hurried person. Weighted figures always come from totals: add up the revenue, add up the rooms, then divide.
Pace is the other place where sums go wrong, because it needs the state of the books on a past date. The trick is to ask one question of every reservation: was it on the books on that day? It was if it had been created by then and had not yet been cancelled. Two helper columns and three formulas turn that into something any spreadsheet can run.
Reservations tab (named Res): A = booked on, B = arrival, C = departure,
D = room revenue for the stay, F = cancelled on (blank if never cancelled)
Helper columns, row 2 shown
G2 Live until =IF(F2="",99999,F2)
H2 Rate per night =IFERROR(D2/(C2-B2),0)
Nights tab, one row per stay date, with the stay date in A2
Rooms on the books today
=COUNTIFS(Res!B:B,"<="&A2, Res!C:C,">"&A2, Res!A:A,"<="&TODAY(), Res!G:G,">"&TODAY())
Same weekday last year, as the books stood this time last year
=COUNTIFS(Res!B:B,"<="&(A2-364), Res!C:C,">"&(A2-364), Res!A:A,"<="&(TODAY()-364), Res!G:G,">"&(TODAY()-364))
Room revenue on the books today
=SUMIFS(Res!H:H, Res!B:B,"<="&A2, Res!C:C,">"&A2, Res!A:A,"<="&TODAY(), Res!G:G,">"&TODAY())The first formula counts reservations that cover the night (arrived on or before it, leave after it), were created by today and are not cancelled as at today. The second asks the same question about the same weekday a year earlier, as at the same point a year earlier. Put the two side by side for each week ahead and you have pace. For pickup, run the first formula again with TODAY() replaced by TODAY()-7 in both places and subtract: the difference is what arrived in the last seven days, net of any cancellations in the same period.
You do not have to write these yourself. Paste your header row into the assistant, say what each column holds, and ask for formulas that match your column letters. Then test them on one date you can count by hand in the PMS calendar. If the sheet says 9 and the calendar says 10, find out why before anything else happens. The export must also include last year's reservations, or the same-time-last-year column will read zero.
If your PMS stores no cancellation date, last year's books will look fuller than they were, because cancelled stays never leave. Write "last-year pace overstated" on the page and begin a snapshot habit instead: every Monday, paste the values (not the formulas) of the on-the-books table into a Snapshots tab. A year from now the comparison will be exact, whatever the PMS keeps.
A worked week for an imaginary 16-room hotel
Every figure below is illustrative. The hotel has 16 rooms, so 112 room nights are available each week. The week ran from Monday 12 to Sunday 18 October, and the comparison week is the Monday-to-Sunday 364 days earlier. First, the daily rows the sheet produces:
| Day | Rooms sold (of 16) | Occupancy | Room revenue | ADR |
|---|---|---|---|---|
| Mon | 7 | 43.8% | $665 | $95 |
| Tue | 8 | 50.0% | $760 | $95 |
| Wed | 9 | 56.3% | $891 | $99 |
| Thu | 10 | 62.5% | $1,050 | $105 |
| Fri | 15 | 93.8% | $2,100 | $140 |
| Sat | 16 | 100.0% | $2,400 | $150 |
| Sun | 11 | 68.8% | $1,254 | $114 |
| Week | 76 of 112 | 67.9% | $9,120 | $120.00 |
The shape is typical of a small hotel: $95 on the quiet nights, $140 and $150 when it fills. The weekly ADR is pulled up by two nights, which is why it has to come from totals. Next, the comparison with the same week last year, again straight from the sheet:
| Measure | Last week | Same week last year | Change |
|---|---|---|---|
| Rooms sold | 76 | 70 | +6 |
| Occupancy | 67.9% | 62.5% | +5.4 points |
| ADR | $120.00 | $115.00 | +4.3% |
| RevPAR | $81.43 | $71.88 | +13.3% |
| Room revenue | $9,120 | $8,050 | +$1,070 |
RevPAR is up 13.3%. Occupancy rose 8.6% in relative terms (from 62.5% to 67.9%) and ADR rose 4.3%, so roughly two-thirds of the gain came from selling more rooms and one-third from a higher rate. That split matters for the next conversation: more rooms sold at a similar price is a different story from the same rooms sold for more. Now the part that looks ahead, with six weeks of pace and the books today beside the books at this point last year:
| Week starting | On the books now | Same time last year | Gap | Final result last year | ADR on the books now | ADR same time last year |
|---|---|---|---|---|---|---|
| 19 Oct | 58 | 52 | +6 | 71 | $118 | $112 |
| 26 Oct | 49 | 55 | −6 | 78 | $121 | $119 |
| 2 Nov | 36 | 44 | −8 | 66 | $109 | $113 |
| 9 Nov | 31 | 33 | −2 | 60 | $110 | $108 |
| 16 Nov | 22 | 21 | +1 | 55 | $104 | $104 |
| 23 Nov | 17 | 18 | −1 | 52 | $103 | $104 |
| Six weeks | 213 | 223 | −10 | 382 |
The six weeks together hold 213 room nights against 223 a year ago, a pace index of 95.5 (213 divided by 223, times 100). That single figure hides three different stories. The week of 19 October is six nights ahead with a rate on the books $6 higher, which is healthy. The week of 26 October is six behind but priced above last year, so either the rate is high for the demand or the bookings are arriving later. The week of 2 November is eight behind and cheaper too, weak on both counts. Pickup adds a fourth angle: 24 room nights were added across these weeks in the last seven days, against 31 at this point last year.
The standing prompt that produces the same page every Monday
Save the prompt below as the instructions of a Project in ChatGPT or Claude (custom GPTs are being retired, so a Project is the safer home; Claude's Free plan allows up to five projects). Each Monday you paste three things underneath: your notes line, the decision log, and the summary tables from the sheet. Change the room count and the number of weeks to match your property.
You write the weekly revenue page for a 16-room independent hotel.
I own the hotel and make every pricing decision.
Data rules
- Use only the figures in the data I paste. Do not use outside knowledge
about demand, events, weather or competitors.
- Do not recalculate totals. You may subtract one figure from another to
show a change, and you must show the two figures.
- If a figure is missing or looks wrong, say so under Data notes and do
not fill the gap.
- Treat my notes (out-of-order rooms, events, rate-plan changes) as facts.
Write these sections, in this order, in plain language:
1. Last week: occupancy, ADR and RevPAR against the same week last year,
with the change in each. Say whether occupancy or ADR contributed more
to the RevPAR change.
2. Pace: for each of the next six weeks, on the books now against the same
time last year; then the total and the pace index (now divided by then,
times 100). Name the two weeks furthest ahead and furthest behind.
3. Pickup: room nights added in the last seven days against the same seven
days last year.
4. Questions for me: three at most, each tied to a specific number.
Ask; do not guess the answer.
5. Options to weigh: up to three, each with its trade-off. Never give a
rate figure or a percentage change to a rate.
6. Last week's decisions: check my decision log against this week's
numbers and say whether they support or contradict each decision.
7. Data notes.
Under 350 words. Only those seven headings. No praise, no filler.Four rules in that prompt do most of the work. "Use only the figures in the data" stops the assistant explaining a soft week with weather it knows nothing about. "Do not recalculate totals" keeps the sums where you can check them. "Ask; do not guess" turns the assistant into a prompt for your own knowledge of the town, the competition and the calendar, which is the knowledge the report lacks. And "never give a rate figure" keeps the page from reading like an instruction, which matters more than it sounds, because a confident number on a page gets followed.
Section six is what makes the weekly routine compound. Because the decision log goes in with the data, next Monday's page can say whether the midweek offer you launched last week moved pickup. Without it, every report is a fresh start and no one learns whether the decisions worked.
What came back, and the four things I would fix
This is an illustrative reply for the week above, written the way these assistants tend to answer. Your own output will differ in wording, so judge it against the checks that follow rather than copying it.
Last week. Occupancy 67.9% (76 of 112 room nights), ADR $120.00, RevPAR $81.43. Same week last year: 62.5%, $115.00, $71.88. RevPAR is up 13.3%, driven mainly by higher rates.
Pace. Six weeks on the books: 213 room nights against 223 at this point last year, a pace index of 95.5. Furthest ahead: week of 19 October (+6). Furthest behind: week of 2 November (−8). ADR on the books averages $111 across the six weeks.
Pickup. 24 room nights added in the last seven days against 31 last year, which suggests demand is softening into November.
Questions for you. (1) The week of 26 October finished at 78 room nights last year, the strongest of the six. Is anything on that week this year? (2) The week of 2 November is eight nights behind and its rate on the books is $109 against $113. Has a rate or an offer changed? (3) Saturday sold out and Friday reached 15 of 16. How many days ahead did each fill?
Options to weigh. Hold current rates on 26 October until question 1 is answered. For 2 November, a midweek offer to past guests would add volume at the cost of rate. Cutting weekend rates by 10% is another option, but it would give up revenue on nights that already sell out.
Last week's decisions. None logged. Data notes. None.
Four things need correcting before this goes anywhere near a decision:
- "Driven mainly by higher rates" is wrong. Occupancy rose 8.6% in relative terms and ADR 4.3%, so rooms sold did about two-thirds of the work. Add both relative changes to the sheet so the assistant quotes them rather than judging.
- "ADR on the books averages $111" is an average of averages. The simple mean of the six weekly rates is $110.83; weighted by the rooms on the books it is $113.36. Put a weighted line in the sheet and let the assistant quote it.
- "Suggests demand is softening" claims more than the data does. The data says pickup is seven room nights behind last year's. Why is a question for you. Strike the sentence, and add "describe what the numbers show, never why" to the prompt if it keeps appearing.
- "Cutting weekend rates by 10%" breaks the no-rate-figures rule. Delete it. If it recurs, move the rule to the top of the prompt and add: "If you are tempted to name a rate, ask a question instead."
The reply also did something worth keeping. It treated 26 October as a question rather than a verdict, and it asked how many days ahead Saturday sold out. A night that fills three weeks early at $150 has probably been priced too low; one that fills the day before probably has not. The assistant cannot answer that, but the PMS can, and the question sends you to look.
One more habit costs ten seconds. After every reply, send: List every figure in your reply that does not appear in the data I pasted, and every calculation you did yourself. Anything on that list gets checked by hand or deleted.
When pace and rate disagree: four readings to test
The pairing of rooms on the books and rate on the books is the most useful comparison on the page. This table sets out what each combination might mean and what to examine before anyone touches a rate.
| Rooms versus last year | Rate versus last year | A reading worth testing | Look at this first |
|---|---|---|---|
| Ahead | Higher | Healthy, or one large booking inflating the week | Is a group or a long stay in there? Take it out and recheck the figures. |
| Ahead | Lower | Rooms are arriving because they are cheaper | Which rate plan or channel supplied the extra nights, and what does it cost you in commission? |
| Behind | Higher | The rate may be too high for the date, or bookings are arriving later this year | Pickup over the last seven days, and what the neighbours are charging for those dates. |
| Behind | Lower | Weak on both counts | Causes outside pricing: an event that did not repeat, a channel closed by mistake, a booking page fault. |
These are readings, not rulings. Last year may itself have been unusual, and the table assumes the same rooms were on sale both years. When the extra nights in the second row come from one booking site, how independent hotels win more direct bookings covers what to do about the mix. For the third row, a weekly competitor rate sheet shows whether the neighbours have moved.
Keeping the pricing decision with the owner
The assistant drafts, the sheet counts and you decide. It helps to build that division into the routine, with a decision log: one row per decision, written by you, pasted under next Monday's data. Here is a filled-in version for the example hotel (illustrative).
| Date | What the page showed | Decision | Review on | Result |
|---|---|---|---|---|
| Mon 19 Oct | Week of 2 Nov: 8 nights behind, rate on the books $109 against $113 | Keep the rate. Email past guests a midweek offer for 2 to 5 Nov, breakfast included. | Mon 26 Oct | Pickup for that week +5 against +3 last year at this point. Offer stays open. |
| Mon 19 Oct | Week of 26 Oct: 6 behind on rooms, rate $121 against $119 | Hold the rate. Ask the front desk what was on in the town last year. | Thu 22 Oct | A trade show that filled the week last year has moved. Expect the gap to stay. |
| Mon 19 Oct | Saturday sold out at $150 | Check booking dates for the last four sold-out Saturdays before changing anything. | Mon 26 Oct | Three of four filled more than two weeks ahead. Raise the Saturday floor by one step for dates still open. |
Drawing the line between what the assistant may and may not do keeps the page honest. It may restate the numbers, list options with their trade-offs, ask the questions you would ask yourself and draft the email once you have decided to send one. It may not change a rate in the PMS (a browser agent could, if it were handed the login, which is exactly why it never should be), quote a competitor's rate it was not given, choose a discount depth or decide to close a channel. If you later want software to move rates for you, whether AI dynamic pricing is worth it for a small hotel weighs the cost; the weekly page is still how you audit it, because a pricing tool that lifts ADR while pace falls shows up here first.
Smaller or bigger than sixteen rooms
The page scales, but its shape should change. A six-room guest house has 42 room nights a week, so a single booking moves occupancy by 2.4 points and a six-week pace table is mostly noise. Report four weeks ahead, split into weekends and midweek, and quote pickup in room nights rather than percentages. Two lines of narrative from the assistant are enough.
A forty-room hotel with a restaurant has the opposite problem: the average hides too much. Add a line per room type and a channel split, so a drop in ADR can be traced to the rate plan responsible, and add a second revenue line for breakfast and bar if they matter to the economics of a stay. In a peak fortnight, repeat only the pickup formula each morning (two minutes) and keep the full page weekly.
A Monday that takes twenty minutes
The routine is short because the sheet does the calculating and the assistant does the first read. Here is how the time splits once the formulas are in place (illustrative).
| Step | Minutes |
|---|---|
| Export reservations from the PMS and paste over last week's data | 6 |
| Tick the control totals against the PMS daily report | 3 |
| Paste notes, decision log and summary tables under the standing prompt | 2 |
| Read the reply, correct it and ask follow-up questions | 5 |
| Write this week's decisions in the log | 4 |
| Total | 20 |
Setting it up takes longer, perhaps two to three hours the first time, most of it spent getting the export columns and the formulas to agree with your PMS. Do it in stages. In week one, produce only last week's occupancy, ADR and RevPAR and check them against the PMS. Add the year-on-year comparison in week two, and pace once the hand test passes.
The money is small. The spreadsheet is free in Google Sheets or the Excel you already own. One prompt a week fits inside a free assistant plan for many users, and the paid individual plans list at $20 a month for ChatGPT Plus and Claude Pro ($17 a month billed annually). The real cost is the quarter of an hour you must protect each Monday, and the discipline to write the decisions down.
Checks that stop a wrong number steering a rate
A weekly report is only worth reading if the numbers are right. These checks take a few minutes and catch most problems.
- Tick last week against the PMS. Rooms sold and room revenue in the sheet should match the PMS's own daily or manager report to the room and to the dollar. If they do not, stop and find out why.
- Count one future date by hand. Pick a date four weeks out, count the reservations in the PMS calendar and compare with the sheet.
- Watch for books that fall between two Mondays. A drop of more than a few room nights is either a cancellation wave or a broken export. Check the PMS before blaming guests.
- Confirm the window. If last year's column reads zero or looks thin, the export started too late.
- Reread the notes line. Out-of-order rooms and moved holidays change the comparison, and the assistant only knows what you tell it.
- Read the reply for causes it cannot know. Weather, events and competitor moves are not in your data.
Here is a plausible failure, so you recognise one when it appears (illustrative). The sheet shows 35 room nights on the books for the week of 9 November, while the PMS calendar shows 31. Four rows carry the status "Tentative", which the formula has treated as booked. The fix is a status filter and a line in the notes tab saying which statuses count. Without the hand count, the week would look four nights healthier than it is, and a rate would be held that should have moved.
None of this replaces a revenue manager at a large hotel, and it is not meant to. It gives the owner of a small property the habit those teams have: the same six numbers, the same day, compared with the same point last year, with a written record of what was decided and whether it worked.
Questions small-hotel owners ask about the weekly report
Can ChatGPT read my PMS export directly?
Yes, if your plan allows file uploads, but give it the finished summary table rather than the raw reservations. Assistants can run code on a file, yet a spreadsheet gives you formulas you can inspect and totals you can tick against the PMS. Let the sheet calculate, and let the assistant read, compare and ask questions.
What if my PMS has no booked-on date?
Without it you cannot rebuild pace or pickup from an export. Look for a built-in pace or pickup report, or ask your PMS supplier where the date is stored. Meanwhile start a snapshot habit: every Monday, paste the values of your on-the-books table into a Snapshots tab, and next year's comparison will be exact.
Do I need last year's data for the report to be useful?
No. Without last year, compare against a target you set (occupancy and ADR by month) and against your own recent weekly average of room nights added. Pace and pickup still tell you whether bookings are arriving faster or slower than usual. Add the year-on-year view once thirteen months of history exist.
Is it safe to paste hotel revenue data into a chat assistant?
Aggregated figures with no guest details carry little personal-data risk, but revenue is commercially sensitive. Use a work account, switch off model training in the privacy settings if you are on a consumer plan (business plans do not train on your content by default), and keep names, emails and reservation references out of the file.
Further reads
- Vacation Rental Analytics: Occupancy and Rate Reports With AI — The same occupancy and nightly-rate reporting for a handful of rental units.
- Hotel Shift Handover Notes With ChatGPT: From Desk Logs to a Daily Report — Feed the front desk's daily notes into the same weekly routine.
- Hotel Breakfast Food Waste: Predict Buffet Demand With AI — Use the forecast occupancy line to plan breakfast covers.
- How to Get a Weekly Business Summary Emailed to You by AI — Have a summary reach your inbox without opening the sheet.
- Where Should a Small Hotel Start With AI? — Pick the next hotel job worth handing to AI after reporting.
- AI for Hospitality: Which Jobs to Delegate and Which Stay With Staff — Which hotel jobs to delegate to AI and which stay with staff.
- How to Price a Wedding Venue: Peak Dates, Midweek and Minimum Spend — A worked price grid for a 120-guest venue: four date tiers, food-and-drink minimums, a midweek discount test and three yearly scenarios modelled with AI.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: hotel-industry guides for the definitions of occupancy, ADR, RevPAR and booking pace (PriceLabs, Roommaster, Cloudbeds); ChatGPT and Claude pricing pages, checked October 2026. All worked figures are illustrative.