Softwiro
Free Excel template

Hotel Guest Folio Template for Excel (Rooms, Reservations, Billing)

A free hotel guest folio template for Excel, made for a small hotel, bed and breakfast or guesthouse. You list your rooms once, add a row for each reservation, log every breakfast, minibar item, laundry load and deposit as it happens, then pick a reservation and the Guest Folio sheet shows the whole bill ready to print: the room charge for each night, the extras, tax, payments and the balance due. The same data feeds a Dashboard with arrivals, departures and balances, and a Monthly sheet with occupancy, ADR and RevPAR. It works in Excel, LibreOffice and Google Sheets and in any currency.

Excel file (.xlsx) · 338 KB · 10 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

Example from the sheet

Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
As of46342Change the date, tax rate and currency on Settings. Move each reservation's status as guests arrive and leave.
Rooms occupied tonight7Occupancy this month35.0%
Rooms vacant tonight3ADR this month128.16
Arrivals today1RevPAR this month44.86
Departures today1Room revenue this month13,457.00
Late arrivals or past check-out1Extras revenue this month1,410.00
Deposits held, not yet arrived1,498.00Balances due, stayed or staying2,816.56
Rows to fix (Check column)0
Room typeRooms in serviceNights soldOccupancyRoom revenueADR
Single21423.3%1,106.0079.00
Double33134.4%3,421.00110.35
Twin22236.7%2,548.00115.82
Family22643.3%3,874.00149.00
Suite11240.0%2,508.00209.00
All rooms1010535.0%13,457.00128.16
Largest balances dueGuestRoomCheck-outStatusBalance due
R-2040Ian Fraser30146343Checked in668.88
R-2042Kevin Doyle20246345Checked in554.12
R-2039Hana Sato10546343Checked in379.00
R-2044Mohamed Ali20346344Checked in359.68
R-2043Lena Fischer10446345Checked in261.88
R-2037Fiona Bell10346342Checked in261.88
R-2041Jenna Cruz10146344Checked in254.80
R-2020Benjamin Cole20246324Checked out35.00

Example from the “Dashboard” tab, with the numbers and formulas that are in the file.

What is inside

  • Dashboard: Rooms occupied and vacant tonight, arrivals and departures today, late arrivals, deposits held for guests who have not arrived, and the month's occupancy, ADR, RevPAR, room revenue and extras revenue. Below: results by room type, reservation counts by status, extras by item, the largest balances due, and four charts.
  • Reservations: One row per reservation: confirmation number, guest, phone or email, room from a dropdown, check-in, check-out, guests, status (Reserved, Checked in, Checked out, Cancelled, No-show), an optional agreed rate and a note. The sheet works out nights, room charges, extras, tax, folio total, paid and balance due, and the Check column turns red for a double booking, a repeated confirmation number, a room that is too small for the party or a check-out before check-in. Room for 300 reservations.
  • Charges: The folio charges log: date, reservation, item (breakfast, minibar, laundry, parking, extra bed, late check-out, airport transfer, no-show fee, discount), quantity and unit price. Each line shows the guest, amount, tax and line total, and is flagged when its date falls outside the stay. Room for 800 lines.
  • Payments: Every deposit, payment, final settlement and refund with its date, method and note. Refunds are typed as positive numbers and subtracted. Room for 600 payments.
  • Guest Folio: Pick a confirmation number in B4 and the sheet shows the guest, room, dates and rate, a summary (room charges, extras, discounts, tax, folio total, payments, balance due and whether the account is settled), then the night-by-night room charges, the extras and discounts, and the payments. It prints on one portrait page.
  • Monthly: For each month of the year: room nights available and sold, occupancy, room revenue, ADR, RevPAR, extras revenue and total revenue.
  • Rooms: Your rooms with type, nightly rate, maximum guests and status (Active or Out of order). Each room shows its nights sold and revenue this month and whether it is occupied, vacant or out of order tonight.
  • Settings: Property name, currency symbol, the As of date, your tax rate, whether room nights are taxable, and your lists of extra items (each marked taxable or not), payment methods and room types.

The file opens on a start tab (“Start here”) that explains how to use it.

What makes it different

Most free guest folio files are a single blank bill that you type over for every guest. This one is a small front-desk system in a workbook: the bill is built from your reservations, charges and payments, so a deposit or a minibar entry made on Tuesday is already on the folio you print on Friday. A double booking is flagged the moment you type it. The sample is a 10-room guesthouse through October and November 2026.

What goes on a hotel guest folio?

A guest folio is the running record of one stay: the room charge for each night, anything the guest buys during the stay, any discount, tax, and every payment, ending with the balance due. A deposit paid before arrival is a payment, so it reduces what the guest owes at check-out.

In this template the folio is built for you. In the sample, Jenna Cruz (reservation R-2041) stays in single room 101 for 4 nights at 79, which is 316.00 in room charges. She added parking for 2 nights, an airport transfer and breakfast for two, 89.00 in extras. With the sample's 8% tax typed on Settings (the airport transfer is marked not taxable) the tax is 28.80 and the folio total 433.80. She paid a 79.00 deposit and 100.00 on account, so the balance due is 254.80.

How do I stop double booking a room?

The Check column on Reservations compares each reservation with every other one for the same room. If their dates overlap and neither is cancelled or a no-show, both rows turn red and say Double booked. A guest leaving on the day another arrives is not a clash, because the night of check-out is not charged. The sample has no clashes, so nothing is flagged.

The same column flags a party larger than the room's maximum, a repeated confirmation number and a check-out date before check-in. Cancelled and No-show reservations are greyed out and do not block the room. This is a booking list, not a calendar grid, so before you promise a room to a new guest, enter the reservation and look at its Check cell.

How do I work out occupancy, ADR and RevPAR in Excel?

Occupancy is room nights sold divided by room nights available. ADR (average daily rate) is room revenue divided by room nights sold. RevPAR (revenue per available room) is room revenue divided by room nights available, which is the same as occupancy times ADR. Room revenue here means the room charges only, without tax or extras, and a stay that crosses a month end is split by night.

In the sample the 10 rooms offer 300 room nights in November 2026 and 105 are sold, so occupancy is 35.0%. Room revenue is 13,457.00, ADR is 128.16 and RevPAR is 44.86. October had 78 nights sold from 310 available: 25.2% occupancy, ADR 122.00 and RevPAR 30.70. Family rooms are the busiest type in November at 43.3% and single rooms the quietest at 23.3%. The Monthly sheet lists every month of the year and the Dashboard charts them.

How do I handle tax, deposits and discounts on a guest bill?

Tax is a number you type on Settings: the rate, a label for the folio, and whether room nights are taxable. Each extra item is marked taxable or not. The template does the arithmetic only. Rules differ by country, region and sometimes by property, so check your local rules for what applies and at what rate. The 8% in the sample is just a number to show the sums.

A deposit is recorded on Payments as type Deposit, a refund as type Refund, and the folio shows payments received less refunds. A discount is a charge with the item Discount and a positive amount, which the sheet subtracts. In the sample, Ian Fraser in the suite (R-2040) gets 25.00 off, and the cancelled reservation in room 105 (R-2049) shows a balance of -115.00 because its deposit has not been refunded yet. For a standalone bill to a company, there is a separate invoice template, and the cash book keeps the money coming in and out.

How to use it

  1. On Settings type your property name, currency symbol, the As of date (today) and your tax rate (type the rate that applies to you, or 0). Edit the extra items and payment methods.
  2. On Rooms list every room: number, type, nightly rate, maximum guests and whether it is Active or Out of order.
  3. On Reservations add one row per reservation: a confirmation number that is never repeated, guest, room (dropdown), check-in, check-out, guests and status. A rate agreed with the guest goes in Agreed rate. A double booking turns the Check cell red.
  4. Move the status along: Reserved, Checked in, Checked out. Use Cancelled or No-show when the stay does not happen.
  5. On Charges add every extra when it happens: date, reservation, item (breakfast, minibar, laundry, parking...), quantity and unit price. For a discount choose the item Discount and type the amount as a positive number.
  6. On Payments add every deposit, payment, final settlement and refund with its date and method. Type amounts as positive numbers.
  7. Open Guest Folio and pick the confirmation number in B4: the nightly room charges, extras, tax, payments and balance due appear, ready to print. Read Dashboard and Monthly for arrivals, balances, occupancy, ADR and RevPAR.

Before you start:

  • Capacity: 40 rooms, 300 reservations, 800 charge lines and 600 payments. The Guest Folio shows up to 31 nights, 25 charge lines and 15 payments (its totals come from the full records).
  • Nights = check-out date minus check-in date. The night of check-out is not charged, so a new guest can take the room on the day the previous guest leaves without a clash.
  • Room charges = nights x nightly rate. A rate typed in Agreed rate replaces the room rate. Cancelled and No-show reservations have no room charge; add a No-show fee on Charges if you charge one.
  • Tax is a typed rate on Settings. It is not looked up for you: check your local rules for what applies. Settings says whether room nights are taxable, and each extra item is marked Yes or No. Tax is rounded per night and per charge line.
  • Folio total = room charges + extras - discounts + tax. Balance due = folio total - payments (refunds reduce the payments). A negative balance means the guest is owed money.
  • Occupancy = room nights sold / (rooms in service x days). ADR = room revenue / room nights sold. RevPAR = room revenue / room nights available. Room revenue excludes tax and extras.
  • The report month on Dashboard is the month of the As of date. A stay that crosses a month end is split between the two months by night. Rooms in service is today's count of Active rooms, used for every month.
  • Use one currency in one workbook. The currency symbol on Settings is only a label.

Excel file (.xlsx) · 338 KB · 10 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Still running this on Excel?

This sheet suits a start and a limited amount of data. If your way of working grows, or you need more than one person working on it at the same time, Softwiro can build you a program that fits the way you work.

In this sheetIn Custom Software from Softwiro
One file that one person edits at a time, and copies reach you on WhatsApp.A program with a login for each user and permissions you define, and everyone sees the same data.
You type everything and check the numbers by hand.We design the screens around your work steps, so each person records only what concerns them.
Columns and formulas are fixed as the sheet was designed.We build what you need: reports, alerts and screens your way.
Backups and security are your own responsibility.The program runs on a secured server with regular backups.

See Custom Software from Softwiro · Ask for a demo

Frequently asked questions

Is this hotel guest folio template free?

Yes. Download it, use it for your property and change anything you like. Nothing to sign up for to open the file.

Does it work in Google Sheets and LibreOffice?

Yes. It uses ordinary formulas such as SUMIFS, COUNTIFS, INDEX and MATCH, so it works in Excel 2016 and later, LibreOffice and Google Sheets. In Google Sheets, upload the file to Drive and open it with Sheets.

How many rooms and reservations can it hold?

40 rooms, 300 reservations, 800 charge lines and 600 payments. The Guest Folio shows up to 31 nights, 25 charge lines and 15 payments, and its totals count everything. When the file is full, save a copy and start a new one.

How do I set the tax rate?

Type your rate in Settings, say whether room nights are taxable, and mark each extra item Yes or No. It is only a typed number, so check your local rules for what applies to your property.

Can I print a folio for one guest?

Yes. On Guest Folio pick the confirmation number in B4 and print the sheet. It fits one portrait page with the nightly room charges, extras, payments and the balance due.

Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html

More free templates for: Travel agencies

More free templates

All templates · Free tools