A practical guide / Hotel revenue dashboards
Can AI build a live hotel revenue dashboard from your spreadsheets?
Yes. AI can help build a dashboard that imports your hotel’s PMS and ledger exports, calculates agreed revenue measures and refreshes on a schedule. Your first win can be one view of revenue, occupancy, average daily rate and the latest forecast, fed from the files your team already produces.
Define what live means for your hotel. A dashboard refreshed after the daily export is current to that export’s cut-off. Show the data time on screen and agree the refresh routine before expanding the view or connecting more systems.
Brief your revenue dashboardBuild one revenue view with a visible data time.
Define the three room revenue measures plainly
Occupancy is the share of available room nights sold. Average daily rate, or ADR, is room revenue divided by room nights sold. Revenue per available room, or RevPAR, is room revenue divided by available room nights. The guides linked below explain these measures and how OPERA presents its daily figures.
More on this step
Use the same dates and room revenue basis for all three. Write down how your hotel treats tax, free rooms and rooms out of use. Agree those rules with finance and the revenue team so the dashboard matches the reports they use.
Keep a metric sheet beside the build brief. For each measure, list its name, formula, source fields, exclusions and owner. If the hotel changes a rule, update that sheet and record the date. Users can then understand why a figure has changed.
Read the source: Oracle: hotel property management reporting guide; Oracle OPERA: occupancy and RevPAR formulas
Create a repeat import from the files you have
Ask for a PMS export with trading date, property, room nights available, room nights sold and room revenue. PMS means property management system. Add ledger revenue totals for the same dates. Keep both source files so finance can explain any timing gap between them.
More on this step
One Microsoft route uses Power Query to combine exports from a SharePoint folder, then loads them into Power BI. Microsoft documents the folder connector and refresh needs. Ask the builder to check licences, sign-in details and any gateway needed to reach your source.
Give each import a file ID, cut-off and status. Decide whether a new file replaces a daily snapshot or adds new transactions. Put that rule in the build. It should stop a repeated export from adding the same trading revenue twice.
Read the source: Microsoft Learn: SharePoint folder connector; Microsoft Learn: Power BI data refresh
Move the forecast process out of separate Excel copies
Keep the forecast in a central set of records. Include stay date, property, room nights, expected rate, revenue, version and sign-off date. Load the latest approved version into the dashboard. Keep older versions so the team can see how the outlook changed.
More on this step
At first, staff can still upload a controlled spreadsheet. Moving forecasting out of Excel means giving inputs, versions and calculations a shared home. Ask AI to help build the import, input form and views around that agreed process.
Show actuals to date, the remaining forecast and the full-month outlook apart. Label bookings already held as on-the-books values if the revenue team needs them. The morning meeting can then see what has happened and what the hotel expects next.
Worked example
A 40-room hotel gets its first daily view
Example with made-up details: Harbour Gate Hotel has 40 available rooms for one night, sells 30 and records £3,600 room revenue on the agreed basis. Occupancy is 75%, ADR is £120 and RevPAR is £90.
At 07:00 the PMS places the prior day’s export in the agreed folder. A scheduled import refreshes the dashboard after that file arrives. The screen shows the trading date and last successful refresh, alongside the three measures and the ledger check status.
If the file arrives late, the dashboard keeps the previous data date visible and flags the missing export. The team can see which figures are available before discussing the forecast.
Illustrative example.
Export
Collect the PMS and ledger files.
Refresh
Apply agreed measures and import rules.
Discuss
View revenue and the approved forecast together.
Write an acceptance checklist for the dashboard
Use these headings for the first build checklist: source files; metric rules; import method; refresh time; missing-file warning; ledger check; forecast versions; user access; data time on screen; support owner.
Test a completed day and a closed month. Match room revenue totals to the approved source reports. Load a corrected export for the same date and check that it replaces the old one. Ask the revenue manager to change a forecast input and show which version appears.
Microsoft says source access and setup affect data refresh. Show a clear status when an update fails. Name a person to contact and agree which report the team should use while they restore the export. Keep the last successful data date on screen.
Read the source: Microsoft Learn: Power BI data refresh
A relevant public story
Warren built a reporting dashboard around his hotel role
Warren Lavender works as an executive at a five-star luxury hotel chain. After coaching with ChatGPT and Claude, he built his own dashboard for spreadsheet work and reports to hotel leaders. He kept developing tools for his role and exploring them with colleagues.
His experience shows how a first dashboard can grow from the reporting knowledge a hotel leader already has.
Read the source: Remote Humans: warren public story
Testimonial · 0:24
"I’ve learned more than I have playing around with it in the last couple of months."
Warren Lavender · Luxury hospitality
Set access around the revenue question
Use daily totals where they answer the dashboard’s questions. The ICO’s AI guidance covers security and personal data. Approve any guest-level fields and how long to keep them before adding them to the build.
Finance and the revenue team approve the metric rules and forecast inputs. Give users access to the properties and reports they manage. Keep the ledger check available from the dashboard. A user can then follow a total back to the source report.
Read the source: ICO: AI and data protection
Try a practical task
Brief the first revenue dashboard build
Use this build prompt with your metric sheet and sample export columns.
Try this in your AI app
ChatGPT
Claude
Gemini
CodexUsing Gemini? The button copies the prompt. Paste it into the new chat.
Design a hotel revenue dashboard from these PMS columns [columns] and ledger export [columns]. Use our metric sheet [sheet]. Show daily occupancy, ADR, RevPAR, room revenue and the current approved forecast.
Set out the import fields, daily snapshot replacement rule and refresh steps. Show trading cut-off, last successful update and missing-file status. Add a check against ledger revenue. Label actual, on-the-books and forecast values clearly.
Propose a central forecast store with versions and a way to enter new inputs. Return the screen layout, field map and checks using this closed period [sample]. Our approved access groups are [groups].
Questions about this task
Does a daily export make the dashboard live?
It makes the view current to the latest successful export and refresh. Display that time clearly. More frequent updates depend on the source system and the connection you build.
Do we need to replace every spreadsheet first?
Begin with consistent exports and a central reporting model. Move forecast inputs and sign-offs into that shared structure as the first dashboard proves useful.