How a Simple Excel Spreadsheet Saved us from Chaos!

How a Simple Excel Spreadsheet Saved us from Chaos!

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.

[wpairbnb_usetemplate tid=”1″]