Easy Way to Track Room Revenue

There are lots of “packages” you can purchase to keep track of your revenues.  It is something that all innkeepers have to do and it’s not one of the more fun parts of the job.  It is important because you have to pay tax on these revenues AND it is vital to track how your business is performing.  It’s also important to reconcile this to your merchant statement every month.

Not many people like this task, but I have a method that is super EASY and FREE.  I keep an excel spreadsheet which I’m happy to send to anyone who fills out the form at the end of this post.  If you don’t have excel OR don’t know how to use it have no fear.  I have pre-formatted the spreadsheet and highlighted the rows you need to complete for each guest.

I track 4 things for each guest:  guest name, date of stay, rate and number of nights.  I have the tax rate built into the spreadsheet and a formula that calculates the total room nights, total tax and the grand total for each guest.  Every time I get a reservation I take a minute to open up my spreadsheet and add each guest.  I keep mine in Google Drive because that way I can update it right away no matter where I happen to be.

At the end of the month I reconcile my totals to my credit card statement.   This is a pain, but really important because mistakes happen and this is when you catch them.  You want to pay tax on the right amount.  Another important thing that you need to keep track of are refunds.  This can make your end of month more difficult to figure out if you don’t add them to the spreadsheet.  For example you may have a batch that you thought was supposed to be $500 but it was only $300 because you issued a refund.  These can be entered into the sheet as a minus.  That way you don’t pay taxes on rooms that you gave a refund to.  This doesn’t happen that often, but it does happen.

Take a look at my spreadsheet (a sample entry) and if you want a copy, complete the form below.  The blue boxes are the only ones that you have to enter with each reservation.

revenue worksheet2

I have a separate tab in the spreadsheet for each month to make it easy for taxes. At the end of the month I download my bank transactions directly into this spreadsheet and reconcile the difference between my room revues and the deposits. The difference is normally due to a 1-4 day lag between when cards are run and deposits are made.

If you would like a copy of my spreadsheet to try out please complete the form below.

{ 2 comments… read them below or add one }

Paula Fraz April 2, 2014 at 4:28 pm

was looking for something simple…since I am self teaching myself how to develop a spreadsheet as you are showing on this page…..your help and sharing is greatly appreciated……..

Reply

andrea April 8, 2014 at 11:45 pm

Paula,

I just emailed you the spreadsheet. I hope it helps.

Andrea

Reply

Leave a Comment

Previous post:

Next post: