Welcome! We are a community of AirBnb hosts

This forum is dedicated to connecting hosts with other hosts. Sign up to get the latest updates and news just for AirBnb hosts! Note that we are not affiliated with Airbnb - we are just passionate hosts!

How a Simple Excel Spreadsheet Saved us from Chaos!


#1

When we first started hosting with Airbnb several years ago, things were simple. We rented just one room and we only booked about 50% of any particular month. And almost everyone stayed at least a week so our months were generally spent hosting 2-4 separate guests. Then we decided to put our other guestroom on Air and things got a little busier, but still manageable.

As Airbnb became more popular, we got busier. We raised our prices and the long 1-2 week stays morphed into 2-4 day stays. It seems at higher prices, people can’t afford to stay as long!

Now we’re booked 85%-90% of the time for both rooms and we have an average of 50-55 people staying with us each month.

The busier it has gotten and the more separate reservations we book a month, the more I began to realize the Airbnb website is totally inadequate for keeping things organized and putting the exact information we need at our fingertips. I realized we were using the Airbnb messaging system to keep track of everything. But that meant every time you need to know something about a guest, you have to go find their messages, then you have to read through a bunch of message to get to the information you need. There were other times when after a guest left, I might need to contact them again or look at their information, but I couldn’t remember their name and their message thread was so far down on the list it was impossible to find.

I also find the Airbnb Dashboard confusing. If you want to check if you have a guest checking in today – instead of starting with today – it may start with a guest or two who checked in several days ago and is still with you. You may have to scroll down a bit to even get to what’s going on today! I find that totally confusing. I wanted something I could look at that would tell me instantly – what’s going on today!

A couple of months ago I decided an Excel Spreadsheet might solve our problems. And it has… beautifully! It took some tweaking over time to get it just right. So I thought I’d share it with you in case it might help you. With that said, if you end up using something like this, you’ll definitely want to tailor it to your situation. Ours is perfectly tailored to ours, so I’ll tell you a little about our situation so you can see what I’m talking about and then better tailor it to your situation.

We live in the apartment with the two units we rent out. We have a home security system which people find a little complicated, so we feel like we have to be here to show people how to use it after they check in. So the main thing we need to know before a guest arrives is, what time of day they’ll be arriving – because we do have to be here. And sometimes we need to know this a few weeks in advance so we can make our plans around their schedule.

We would ask them what time they’d be arriving on the Airbnb message system, and sometimes they would answer and sometimes they wouldn’t. But using the Air message system to keep track of everything was turning into an organizational nightmare. Someone may have booked three months ago and they’re arriving tomorrow. I’d have to go into the Airbnb dashboard, and click on their message thread and sometimes have to read through a whole slew of messages before I could figure it out. And often I would find they had never told us, and I’d have to write them back again at the last minute about it. And I was losing track of which guests we knew about and which ones we didn’t.

Another problem was keeping track of things I might want to note in their review. Maybe they gave us some big problems during the reservations process, but when it came time to write their review, perhaps a couple of months later, I wouldn’t remember it. Or maybe they were a difficult guest, but by the time I got around to writing a review 7-10 days later it was hard to always remember which guest was whom and who did what when you have so many people checking in and out all the time.

After working with the spreadsheet for a bit, I realized we could also use the calculating power of Excel to help us keep a lot of key numbers close to our fingertips. So don’t forget about that either – it’s really helpful. For instance, we now know how many nights the average guest is staying with us. If that number goes significantly up or down, it could be telling us something. We also instantly know what our average price per night is (and it was much higher than we thought)! And we know which of our two rooms is getting more reservations.

Here’s how our spreadsheet looks and how it solved all those problems.

It’s 12 columns wide and the columns are:

Arrival Date: This column controls the spreadsheet. Everything is sorted by this column. You can enter the dates in any order, then use Excel’s sort function to easily sort from first date to last. So it’s never a problem to add a reservation that comes before some reservations you’ve already added.

ETA: If they’ve told us what time they’re arriving, I write it down here. If they haven’t, this box stays blank and it’s very easy to see who we still need to contact to get this information from.

Guest Name: Seems obvious, but it’s a big help! Also, I don’t just type their name, I copy it from Airbnb and paste it into Excel. It retains a link back to their profile page – so I can click here any time to see that page. This hasn’t been extremely helpful so far, but you never know.

From: In this column I put where they’re from. It’s good to know when you greet a guest. It gives you an idea how long they may have been traveling and how tired or hungry they may be. It may also give you something good to talk about. And it gives you a clue what language they might be speaking. Since we’re a multi-lingual household, sometimes we can speak to them in their language.

Nights: This is how many nights they’ll be staying. This has turned into our second most essential information to know – after the arrival date. You can also use Excel’s math features to do a lot of calculations with this column. On this one, we use the Average function to give us a quick average number of nights each guest stays. In September for instance, each guest stayed with us an average of 3.6 nights. For October, that has increased by one more night per guest to 4.7 – I think due to our lower prices. So it could be an indication that our prices might be a little too low. But if you prefer to get longer reservations, then this is also good to know and is an indication that lower prices might help in that regard.

Guests: This is the number of guests. We can host between 1-3 people per room. If it’s three people they have to pay $15 a night more. In the past we were sometimes surprised when three people walked in and we thought it was supposed to be two. And if we’re having three people, we have to setup an airbed and extra sheets and towels in their room. Thanks to this column, that information is always right at our fingertips and we’ve never been surprised again!

Room #: We’ve assigned a room number to each room. One room, we call room 10 and the other 11. We use the Excel Average function to calculate the average room number here. In October so far, we have 11 separate reservations. If the average “room number” is 10.5 it means both rooms have an equal number of reservations. If it’s 10.6, then room 11 has more reservations and if it’s 10.3 it means room 10 has more reservations. Right now it’s at 10.545, so room 11 has one more reservation than room 10. We think the closer these are to 10.5 it means both rooms are healthy and getting about the same number of reservations. Keep in mind though, it has nothing to do with room nights, both rooms might be rented by the same number of guests, while one room has more nights booked. But it gives us an idea if both rooms are getting pretty equal treatment on Airbnb as well as if our pricing and reviews are good for both rooms.

Earnings: In this column, we put the total amount we’ll receive for each reservation. At the bottom of this column we’ll total it up for each month. Then we’ll divide by the total number of room nights to get an Average Price per Night. I also created other “sheets” for each year’s income – like “2016 Income” and “2015 Income”. I have one for each year going back as far as we’ve been hosting. On each yearly sheet, I have a graph that is linked to the main sheet’s monthly earnings total column – so I can instantly see how we’re doing this month compared to previous months and previous years.

Alarm Code: We assign each guest an alarm and lock code. I have an automatic random number generator that I use so we’ll actually use unique codes each time (it’s harder than you might think to try to make these up on your own! They all start looking pretty similar after a while). So, I create the code right when the reservation is made and enter it into the spreadsheet. I won’t need it until the day the guest arrives, but it’s there and ready to go.

This brings up another advantage. Since I do this on Microsoft Office365, I can open this file on any device. When I need to delete and add alarm/lock codes, I just take my tablet out to the door and read off everything I need from this file. I used to have to write it down and take a piece of paper out there, so this is a lot more convenient. And uses a lot less paper!

Inbox Link: This has turned out to be an important column. I simply copy the URL to the Airbnb message page where I’ve had a conversation with each guest. Now we can instantly get back to our conversation with any guest even if it’s buried under 50 new conversations on the Airbnb messaging home page, simply by clicking on the link. This has been invaluable.

Special Comments: Here I just write whatever comments and notes I think might be helpful for us to know when the guest arrives. For instance, under one reservation I just wrote, “Coming with his wife Nina.” So when they walk in, I’ll know both of their names – without having to go back and find the conversation in the Airbnb inbox. On another guest, I’ve written, “Arriving JFK 6:00 AM. I told her she can drop her bags off at 10:00 AM”. That way if she asks later if she can show up at 7:00 a.m. I’ll know that I already told her she couldn’t.

Reviews: Here is where I write anything I might want to remember when I write their review but know I’ll forget by the time they leave. If the guest is fine, I just leave this blank. When I see that it’s blank for a particular guest, then I know that guest was fine and I’ll write something nice for them. But if they did something particularly annoying (or particularly wonderful) before or during their stay, I’ll make a note of it here.

One guest recently annoyed the crap out of me because they didn’t keep our directions on how to get here from the airport. So they kept calling us – through a friend who wasn’t with them – to ask for directions every step of the way! I made a note of it here, but ended up not mentioning it in my review. When they arrived, they were so nice and they had been traveling in the US for just a few days and didn’t have an American SIM card so they had no internet connection most of the time. So I forgave them since they were so nice, and had a perfectly valid reason for not being able to access my instructions (although they could have printed them – but they were so nice, so I didn’t care by that point). The nice thing is – I can make notes of things like that here and then decide later whether to use it – depending on how their entire stay went.

And that’s it! I hope this helps. Don’t forget to use the power of Excel (or whatever program you use) to make calculations to give you key metrics that will help you on pricing and detecting problems with your reviews.


#2

@JonYork thank you for sharing your system, very cool!


#3

Wow, what a thorough post! I only have one listing so it’s quite simple but I could see how this would be useful if one person is managing multiple listings. Thanks for sharing!


#4

Really enjoyed your post thank you @JonYork. I only have one [busy] listing but my life outside Airbnb is so crazy that your system will be a great help to me…just need to find the time to set it up! :wink:


#5

Wow, impressed! I’m tempted to make a version of this and track a few other things too, like how many guests I enjoyed hosting or how I feel about hosting. After a few good ones I love hosting so much, but after a few ‘meh’ ones I hate it a bit… so I don’t really know what my average opinion is. My guests share my space so I think it could be a good idea to track my emotional reaction to avoid sudden burnout that could be sneaking up on me.

A while back I’d wake up in the middle of the night in a panic thinking I’d forgotten about an arrival and a departure and a bag drop, major stress going on. After that I changed my settings to only allowing longer stays, and that’s really helped.


#6

@eyeborg,

That’s a great idea to track your emotional reaction to each guest!! I never thought of that, but that would be very interesting to see!


#7

So we added two Emotional Reaction columns. One for my emotional reaction and one for my wife’s. For September and so far in October, we’ve both agreed everybody scored a 10. That was kind of our gut feel too - that we enjoy almost all of our guest. But it will be interesting to see over time how often we get the ones we don’t like.


#8

Great idea and thanks for sharing. We had something similar for our various pricings on other platforms so the price was in sync. Ie, so the guest pays a similar figure whether they book with AirBnB, Stayz or direct. A simple formula helped calculate.

Am preparing a new sheet now using some of your headings.

Don’t know if you are a superhost but I award you this very special Air Host Forum Badge in appreciation: :trophy:


#9

I have created a similar spreadsheet with google docs so I can access when I’m not near the computer and it’s always the must updated version.

I also keep notes on every guest and ask for their private email. Sometimes they share it, other times they don’t. If they share their email I keep it on my spreadsheet.

I keep a tally of number of nights and total for the year.


#10

I also use a spreadsheet with google sheets - that way it is at my fingertips no matter where I am. I can pull it up on my phone in the middle of a meeting/sports event/etc and have every answer I need.
I also use different google docs to keep track of common answers (I have too many to just use the “saved message” - it would clutter it up), pricing for different dates, different conferences that occur in my city, all listing text in one document so I don’t have to open different sections of a listing to reiterate to a guest when they ask a question, etc, etc, etc.
This works especially well if you share hosting duties with someone that is not at your location or have a manager for when you are away.


#11

Instead of a spreadsheet I ended up with a database and it has become my best employee!! Not only does it give me all my quarterly occupancy tax calculations on the fly, it also has given me the ability to communicate individually with my guests. With the push of a botton I’m able to generate a welcome letter, a parking permit, a TV and basic house rules schedule etc. all personalized with their names etc. What it comes down to; Hotels have fancy management systems and I needed one too, so I created my own.
I ask each of my guests for their email address and birthday and thus my system also generates birthday reminders and does the rest.


#12

The link to the message is a great idea.
Suggest to look into Pivot Tables too as a great tool to summarise Revenue, RevPar etc per period


#13

Staywme, that’s sounds like a great setup. What kind of database do you use? Is it web-based?


#14

A database… love that. How do you create that?


#15

I’ll be at the AirBnB Open and I’ll have my laptop with me and I can show you rather than writting a boring book here. The “only” thing it doesn’t do for me is bring me my coffee in the morning. Other than that … you might be impressed.
Take care Evelyn. And … I’m sorry to hear about the New York decision this morning. Does that affect you as well?
Best
Peter


#16

this is great information. Thank you for sharing!


#17

Just log on for the first time and this is the very first post that I read… and love it! I have 2 separate rooms listed + whole house.

We have been overwhelmed by the number of bookings and it is chaos

This model will really help. Thanks for taking the trouble to share this. Much appreciated

Hans


#18

@Staywme would love to know as well. I know it would be tedious to write up, but any description would be helpful.


#19

@JonYork, thank you for a thoughtful discussion and for all the help with this tool. You certainly deserve SuperHost status in my book!

Question: Are there similar Excel sheet tips for keeping AirBnb bookkeeping records? I know AirBnb does a version that is downloadable, but does anyone have any modifications that they find helpful? (Should I post this as a separate thread?)


#20

What a wonderfully simple idea! I’m amazed at your fortitude moving that many people through in one month…I’d havde to develop multiple personality disorder to interact with that crowd!:scream:


Altcoin Fantasy - Crypto Fantasy Trading and Simulation Game - Win Bitcoin and Altcoins!