Airbnb host - use case

xenium

hi,

One of the duller tasks of being an Airbnb host is reconciling payments. Airbnb have the habit of not spotting payment errors, and for the 2 years I bothered to check, I recovered around £800 per year in payments Airbnb had failed to make. They quietly made those payments once the error was pointed out to them.

It's a time-consuming task involving the following 3 data sources:

(a) bank statement info

(b) payment notifications from Airbnb which may link a single payment (payment date, amount) to multiple bookings (booking ref, amount accrued to booking)

(c) list of bookings (booking ref, total booking value)

The tasks are:

1. check that the payment amount of (a) matches (b)
   this is a basic 1-1 reconcilition

2. check that (b) matches (c)
   for bookings more than a month long this can be a complex reconciliation (need to sum the accrued amounts from (b) to a single figure per booking)

Currently I use a spreadsheet formula to perform this check.

Questions:

i/ how would I implement this check in Wave ?
ii/ how can I import the data above to Wave ?

An integration to Airbnb would be great, but meanwhile using Wave connect or the API could work if the system allows all 3 data sources to be imported, in particular (b)

Many thanks

---

Tags requested: Airbnb, Wave Connect, Wave API, Reconciliation, Journal Transaction Import

xenium

Update: it looks like Xero isn't able to perform this recon either.

Still looking for a solution.

Thanks

MerlinAccounts_UK

Would it be feasible/practicable to set up a customer account called Air BnB, then raise an invoice for each booking made (not to be sent out obviously). As payments are then received from Air BnB you could allocate those moneys received against the relevant invoices, thus enabling you to maintain an ongoing record for all outstanding booking settlements within Wave?

I know this isn't anywhere near ideal but would help you to keep track within Wave of what you are owed.

ChelseaK

@xenium I think @MerlinAccounts_UK has a good idea. You could create an invoice for every Air BnB booking made. Like Andrew said, you wouldn't need to actually send the invoice. Then, when you get paid, you can record a full payment by manually marking the invoice as paid (under the 'Get Paid' section, you can select 'Record a Payment'). Or, you can record a Deposit against the invoice by creating a Customer Pre-payment account (under Liabilities and Credit Cards in your Chart of Accounts). More details on how to record deposits in Wave can be found in this Help Centre article: https://support.waveapps.com/hc/en-us/articles/208622916-How-to-handle-invoice-deposits-or-pre-payments

This would be an effective way to track what is owed to you by AirbNb!

xenium

Thanks for the suggestions.

There are 2 reconciliation tasks since Airbnb is an intermediary:

i) invoice vs airbnb: this can be done by loading bookings as invoices, and airbnb statement data as a bank account. This is a 1:M reconciliation, so without an automatch on booking reference, this is still laborious.

ii) airbnb vs bank. This is a simple 1:1 match. However there is no interbank recon feature.

If Wave are interested in offering this service to the 650,000 Airbnb hosts, or any other business that uses an intermediary provider, please feel free to contact me to discuss. We specialise in bespoke data reconciliation systems, visit www.autoreconuk.com

MerlinAccounts_UK

@ckeen_1244
Seems to me that all this post was about was to try and generate a plug for a service that isn't anything to do with use of Wave?

xenium

As an Airbnb host I want to find a way to resolve this issue.

As a reconciliation specialist I would be happy to help if there isn't an available solution, but that is not my motivation for posting.

MerlinAccounts_UK

In the spirit of the purpose of the Wave community can you not share on here your solution to the problem you consider to exist so that other Wave users that are AirBnB hosts can benefit?

xenium

Hi Andrew, thanks for the suggestion. Yes I am happy to share my current spreadsheet solution if this helps, though I am looking to deprecate it. However it looks like sharing it may be a useful exercise, both as a solution for others to copy should they wish, and also as detailed clarification of what I'm trying to achieve.

Going forward I'm looking for a solution using a book-keeping system such as Wave, so I can more easily handover my Airbnb to a book-keeper.

I will post a demo version shortly, watch this space...

xenium

Here is a spreadsheet demo:

https://docs.google.com/spreadsheets/d/1X4uwM9SMIpqJD8yZmcGAxUGmr81jXBRihpuriTJ_EjQ/

Can anyone help translate this into a book-keeping process?

The google doc accepts public comments so feel free to post any comments or questions on the document itself (select cell of interest then menu Insert --> Comment)

Feel free to copy and use with your own data (File --> Make a copy)

I'd be happy to release a v2 based on feedback, and ultimately to deprecate the spreadsheet in favour of a book-keeping system.

Thanks

xenium

Ok I think the following is a solution, I'll try to test this sometime within the next week and report back here, but meanwhile any comments welcome:

Let's call this the Z-Recon (if there's a standard book-keeping name for this please post)

The "Z" connects 4 points:

(1) Top left of the "Z" is a list of Airbnb bookings which are loaded as invoices. Unique reference is the Airbnb booking reference.

(4) Bottom right of the "Z" is the host's bank statement with deposits received from Airbnb

To link these we load the Airbnb transaction CSV file, twice as follows:
(2) The "Amount" column is loaded as a deposit to merchant bank "Airbnb". Include "Confirmation Code" in the load, as this is the Airbnb booking ref to match to (1).
(3) The "Paid Out" column is loaded as an invoice to supplier "Airbnb"

We can then reconcile (1) vs (2) and (3) vs (4)

Since Airbnb supply the csv so that (2)=(3), we can then apply a single journal entry (column total of csv file) to zero those off.

I think that's it. To make this work automatically, the book-keeping system would just need an auto-match rule option using a reference number (Airbnb booking ref in this case).

GoranB

Let me know if you have made progress on this.