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″]
Hi JOn
Great article, I am always trying to make the booking management process easier with excel. I did not see a download link to your excel template could you include it.
Hi, I just put up a link where you can download a copy. It’s here: https://lazylizard-my.sharepoint.com/:x:/g/personal/jc_lazylizard_net/EQE4SEuqo0pMpqSzFz4FprcBQXdN3EFcGOqWtzHEtuoJdQ?e=GftwJb
(If you don’t see anything when it opens – scroll up to the top of the page. For some reason this links seems to open it at the bottom of the page)!
Hello,
This sounds lovely. I am new to excel (just a couple classes). I am wondering if there is any more information you can share on the formulas. I just found your blog and this might be very helpful. I recently started to rent my home on Airbnb a couple months ago.
I would like to be organized from the beginning.
Thanks so much.
Hi, I just put up a link where you can download a copy. It’s here: https://lazylizard-my.sharepoint.com/:x:/g/personal/jc_lazylizard_net/EQE4SEuqo0pMpqSzFz4FprcBQXdN3EFcGOqWtzHEtuoJdQ?e=GftwJb
(If you don’t see anything when it opens – scroll up to the top of the page. For some reason this links seems to open it at the bottom of the page)!
Thank you for this!
So you have two rooms in your home that you rent in addition to living there, and you allow 1-3 people per room? That sounds like it might be both crowded and awkward at times. One thing I will agree with you on is that the AirBNB dashboard and associated payout pages, etc is in dire need of a makeover.
We have four spaces we rent on an extended stay basis (30+ days) and also have our own website, so we HAVE to manage what’s going on in a spreadsheet. I also have a dry erase board in my office wall with all this information in case we get leads from elsewhere and I need to know in an instant whether I have something to offer or not. I came here looking to improve upon my spreadsheet! 🙂
Andy
charlotteforless.com
Great idea to have your own site and promote (and sell) it outside of Airbnb! Just curious – how are your 30+ day rentals going?
We’re just getting started. Thanks for the suggestions.
I created the spreadsheet and added a column next to ETA entitled “AAT” for Actual Arrival Time. If people seriously deviate from the expected ETA I want to remember it.
I also added three additional columns for guest names # 2, 3 and 4.
And I added a cell # column just for good luck.
That’s the great thing about Excel, you can customize it to your exact needs. I would recommend if you have a column for Actual Arrival Time (interesting!), you might want to put one next to it to show whether or not you consider their late arrival justified or not.
In our case, 90% of our guests flew into New York and when they were really late it was almost always the fault of the airline. So we never held that against them. Other people who drove but stayed in touch and kept us up to date with their ETA, we didn’t hold it against them either. But people who were completely silent (except those stuck at foreign airports from where they couldn’t call us), and very late – we did hold that against them.
You have to be somewhat flexible and understanding on this since people are traveling and things can happen on their way that they couldn’t have foreseen.
Hi
Im also a host, and I was wondering how I would sort everything out as Ive become more busy. One thing I want to ask is if you could show me the layout of the spreadsheet. Also, how do I quickly add information about my new guests. Is it possible for me to make it automatically connect from the airbnb website?
Hi Mina! Sorry for the late reply! I just put up a link, after seeing how many people have asked for it, where you can download a copy of the spreadsheet. It’s here: https://lazylizard-my.sharepoint.com/:x:/g/personal/jc_lazylizard_net/EQE4SEuqo0pMpqSzFz4FprcBQXdN3EFcGOqWtzHEtuoJdQ?e=GftwJb
(If you don’t see anything when it opens – scroll up to the top of the page. For some reason this links seems to open it at the bottom of the page)!
I ended up entering everything by hand which did become a chore and one I didn’t always keep up with well enough. But I don’t know of any way to have it done automatically. Would be nice though.
This was so thoughtful and helpful. Thank you for sharing!
You’re welcome! I’m so glad it was helpful for you!!
Thanks Jon,
This is the best piece of advice and explanation of how to organise the layout of my spread sheet to cope with the day to day demands of retaining and organising all the information to successfully maintain an Airbnb small business. Could you post or email me a copy of the spreadsheet?
Do you use a spread sheet to manage the finances associated with your Airbnb business?
Hi David, sorry for the late reply! I just put up a link, after seeing how many people have asked for it, where you can download a copy of the spreadsheet. It’s here: https://lazylizard-my.sharepoint.com/:x:/g/personal/jc_lazylizard_net/EQE4SEuqo0pMpqSzFz4FprcBQXdN3EFcGOqWtzHEtuoJdQ?e=Ms1s4F
(If you don’t see anything when it opens – scroll up to the top of the page. For some reason this links seems to open it at the bottom of the page)!
No, I didn’t manage the finances in Excel – other than using Excel to get a quick graphical overview of how we were doing each month and each year. I used Quicken for the actual financial management and income reporting.
Considering Air BnB for the first time and we are also a big fan of the Excel spread sheet. Amazed how few don’t value it. Thank you for sharing such valuable information.
Hi Marti, I’m glad this was helpful for you! And yes, this would have been much harder without the spreadsheet. It was the only way we could have a quick overview of what was going on in any given week or month. It just isn’t possible to get this kind of view from Airbnb.
Hi – This spreadsheet looks like exactly what I’ve been trying to find! Can you share a link to it or send it to me? Thank you!
Hi Tom, sorry for the late reply! I just put up a link, after seeing how many people have asked for it, where you can download a copy of the spreadsheet. It’s here: https://lazylizard-my.sharepoint.com/:x:/g/personal/jc_lazylizard_net/EQE4SEuqo0pMpqSzFz4FprcBQXdN3EFcGOqWtzHEtuoJdQ?e=Ms1s4F
(If you don’t see anything when it opens – scroll up to the top of the page. For some reason this links seems to open it at the bottom of the page)!
You can use this to extract historic price and reservation data for all your listings for every day of every year and put it in to an Excel spreadsheet https://quantum-key.net/AppPages/251-AirbnbBasher—Historic-Reporter.htm. It takes about 5 seconds per year of data per listing. It’s really useful for setting your prices fro the coming year and predicting busy periods