Sunday, March 29, 2015

Do you manage your vacation rental with Excel?


If you are like me with just one vacation rental property, you may have built a spreadsheet to manage inquires, bookings, rent and availability. You might be tired of all the manual work but feel like an expensive software product or cloud-based subscription service just isn't worth it. That's where I was. Then I took a hard look at the Google Apps platform and had an idea - what if I used Google App Script to write a program that automates the whole vacation rental management process?  The platform is perfect for it:

  • Google Docs for contracts and invoice letters
  • Google Forms for rental inquiries
  • Google Calendar for managing availability and schedules
  • Gmail to send reminders and alerts
  • Google Sheets to create a single dashboard 
  • Google Contacts to keep track of vacation rental customers
  • Google Drive to store everything including signed scanned contracts


I embarked on the project 3 years ago, and have been using earlier versions for years with our vacation rental, the Lodge at Palmer Lake. This week I finished the initial release of a version to make public. I call it 'TrackVacs', for tracking vacation rentals.

The cool thing? Google offers the entire platform for free. 

You can click here to set up TrackVacs for your vacation rental at no cost. Try it out, you will get a lot of time back. If you appreciate it and want to contribute, that would be fantastic.


What parts of vacation rental management can be automated?


A great deal. Here is how software can greatly simplify the whole life cycle of having someone rent your property:

  1. A few clicks sends a pre-written email to a prospective renter with an inquiry form.

  2. When the rental inquiry is received, the code then:
    • Validates the requested dates against other bookings and rules. If an issue is found, a window pops up with options to address it.
    • Creates a calendar event for the stay and to set aside time for cleaning if desired.
    • Calculates the rent based on seasonal rate structures, extra costs for more people and cleaning fees as applicable.
    • Creates a Contact for your new customer, complete with all their contact info.
    • Builds an invoice letter and rental contract with all of the details of the renter's group, a breakdown of fees and sales tax, check in/out dates and times, payment due dates and contract terms.
    • Provides immediate visibility via the dashboard to key elements of each booking, and quick 2-click access to the associated documents and calendar entries.
    • Sends email reminders to the right parties automatically as payment due dates approach, rent is overdue or deposits need to be returned. 
    • Offers filtering options in the dashboard to get simpler views as bookings grow. 


Want to know more?


I put together a user guide, check it here - Using TrackVacs to Manage Your Vacation Rental.


Want to try it?


It's free. To get started, check out this installation guide that describes the setup step by step with screenshots. 

Any feedback is very appreciated, let me know what you think and how to make it better!





TrackVacs User Guide



Managing a vacation rental with TrackVacs


TrackVacs is a software tool leveraging Google Apps that automates the management tasks associated with running a vacation rental management business. It is 100% cloud based, and 100% free. For more information, check out this post.

Using TrackVacs is easy - the whole point is to make managing your vacation rental much simpler and less time consuming. 


Installing TrackVacs


If you haven't yet installed TrackVacs, check out this post for help on doing that.


TrackVacs files and folders


After installation, within your Google Drive account screen you have a TrackVacs folder. Give it a double click and you will see: 




 
At the top, there are 3 folders:


  • Attachments - this is where you put files you want to include as email attachments in reminders that are sent to your rental customers. For example, directions to your property or welcome information for when they arrive. The format of the files must always be PDF. These attachments will be included in the 2nd half payment reminder sent to your renter prior to their arrival date. 

  • Contracts - this is where TrackVacs will put all of the contract documents it creates for each rental booking. You do not need to put anything in this folder.

  • Signed Copies -  this is the place to upload scanned copies of rental agreements that have been signed by your renters. See below under Signed Copy column for further explanation.

Below the 3 folders, you can see 3 files:

  • A master template for an invoice letter / rental contract - you can edit this template as you see fit for your business. The only parts of the document that cannot be modified are the text elements enclosed with <> brackets. For example <Check In Time>. Any text elements formatted like this are keys in the document that tell TrackVacs where to put the right text that corresponds to your vacation rental business and the particulars of each rental agreement, such as dates and prices.

  • The TrackVacs dashboard - this is the center point of the system, and where you will go to access everything moving forward.

  • An inquiry form - this is the form that is sent to prospective renters to kick off the vacation rental booking process.


Using TrackVacs to manage a vacation rental

In the section below, we will walk though the whole process of managing a vacation rental - from initial inquiry all the way through to the point of returning a deposit after your guest's departure.

Maybe you use VRBO, HomeAway, OwnerDirect or other means like your own website for marketing your vacation rental property. One way or the other, someone reaches out for more information. You might email back and forth and eventually they express interest in booking a stay. 

This is where TrackVacs comes in


Go to the dashboard, select TrackVacs and then 'Send link to rental inquiry form'.


This window will appear. Fill in your prospective renter's email, and make any changes you would like in the subject or body of the note. If you would like to be copied, check the BCC box, then hit the 'Send Inquiry Form' button.






Here is what your prospective renter will get


This form style and graphic can also be customized to your business, including use of your own image.



When the inquiry form is submitted


When your renter submits the inquiry form, you will get an email alert that will look like this:






Click on the link in the email for quick access to your TrackVacs dashboard. You will then see the new inquiry appear at the bottom of your dashboard:



At this stage, TrackVacs has handled all of the following:
  • Validated the requested dates against other bookings and rules. If an issue was found, it is flagged and a window is available with options to address it.
  • Created a calendar event for the stay and another to set aside time for cleaning if desired.
  • Calculated the rent based on seasonal rate structures, extra costs for more people and cleaning fees as applicable.
  • Created a Contact for your new customer, complete with all their contact info.
  • Built an invoice letter and rental contract with all of the details of the renter's group, a breakdown of fees and sales tax, check in/out dates and times, payment due dates and contract terms. 

Your calendar event will be created, but not marked 'Busy' until the customer commits. They are 'penciled in', and the event will be show in green:






At this point all the prep work for getting back to your prospective customer is already done for you.

Click on the link in the 'Contract' column to bring up the invoice letter/rental contract that was generated by TrackVacs for that new inquiry. 

Your contract will look like this:




Review the document and if all looks good, go to File, 'Download as', 'PDF document'. Save the PDF to your local computer and email it to your prospective renter.


When the customer commits


When you receive your renter's deposit and signed rental contract, manually change the Status column to 'Confirmed'. 

Your calendar event for this booking will then be automatically changed to 'Busy' and the visibility set to 'Public'. The color will be changed to red. See below under Status column for further explanation.







Also, if you choose, scan the signed hardcopy of the rental contract and upload a PDF version of it to TrackVacs. See below under Signed Copy column for further explanation.

As the payment due dates approach


TrackVacs will monitor the dates and automatically send email reminders to your renter about upcoming payment due dates. If you chose to use the feature of storing Signed Copies, the PDF scanned file of the signed rental contract will be attached to both the 1st and 2nd half payment reminders. When the 2nd half reminder is sent, it will also include any additional attachments you have put in the 'Attachments' folder, such as directions to your property or welcome information. See below under 1st Half / 2nd Half Payment columns for further explanation.


About payments


As payments come in, manually change the 1st/2nd Half Payment columns to 'Received'. Also update the 'Collected' column with the amount received. If the payments become overdue, TrackVacs will send you email reminders alerting you to the situation. If you chose to use the feature of storing Signed Copies, the PDF scanned file of the signed rental contract will be attached to the overdue payment alert. 


Upon departure


A day after guests have checked out, TrackVacs will send you an email reminder to return their reservation/damage deposit. This reminder will only be sent if the Status is 'Paid in Full'. If you chose to use the feature of storing Signed Copies, the PDF scanned file of the signed rental contract will be attached to the reminder to return the deposit. When completed, reflect that in the 'Deposit to Refund' column by changing the amount to zero.


About taxes


When you have paid the sales tax associated with that guest's stay, reflect that in the 'Tax to Pay' column by changing the amount to zero.


That's it.


That is the whole process, TrackVacs takes care of most of the heavy lifting for you.



Details about the TrackVacs dashboard


The columns to the left of the ID column


This is all information taken directly from the inquiry form submitted by a prospective renter.


The ID column


The ID field is assigned by TrackVacs. If you delete the ID in a row, TrackVacs will see that as a new row, and when you pick 'Process inquires' from the TrackVacs menu (more on that later), that row will be re-processed from scratch, replacing all fields right of the ID column with new data except the Signed Copy. The invoice letter/rental contract will be re-created, and a new calendar entry created.


# of People column


This is calculated based on the total count between adults and kids from the inquiry form.


Nights Stayed column



This is calculated based on arrival and departure dates, and rounded to the nearest whole number based on check in and check out times.


Status column


The status column uses cell validation rules in Google sheets to ensure that there is only 4 valid status entries. The Status is always set manually by you. It allows you to control how the TrackVacs system responds based on what you know about the renter. Here are the details on each:


  • Prospect - this is the initial status of any new inquiry. The calendar event is set to Private and Available, in other words it is 'penciled in' but does not show the period as Busy and Public, so if you publish the calendar on the web people can see that those dates are not yet locked in.

  • Confirmed - set this status when you know that the renter is confirmed for their stay. For example, after you receive their deposit. When you change a status to Confirmed, the calendar event is changed to Busy and Public, locking in those dates for your renter. If you elected to create cleaning events when you first filled out the TrackVacs Setup Form, changing a row's status to Confirmed will also tell the system to go ahead and create that cleaning event on the calendar. When you change a status to Confirmed, TrackVacs will pop up a window alerting you to the fact that the calendar has been updated.

  • Paid in Full - set this status when you have received payment in full. Reminders to return customer's deposits will only be sent to you for those rows that have been marked Paid in Full.

  • Cancelled - if a renter cancels on you, changing the status of that row to Cancelled will automatically delete the calendar event for that stay, as well as the cleaning event as applicable. A status of Cancelled also tells TrackVacs not to send reminders for upcoming payments due to the renter, as well as not to send you reminders of any payments that are overdue. When you change a status to Cancelled, TrackVacs will pop up a window alerting you to the fact that the calendar event(s) have been removed.


Contract column


This field in each row contains a link to the invoice letter / rental contract document that is specific to the booking for that row. These documents are all stored in the 'Contracts' folder under the 'TrackVacs' folder, but you should rarely need to go into that folder as this link provides ready access to the right document.


Signed Copy column


If you choose to use it, this field in each row contains a link to the scanned hardcopy of the invoice letter / rental contract that was signed by your renter. Instead of file cabinets full of hardcopies, this option allows you to store everything in the cloud, 2 clicks away for quick retrieval.


If you choose to use this feature, scan the signed hardcopy of the rental contract and upload a PDF version of it into the 'Signed Copies' folder. The naming convention allows TrackVacs to build an association between that signed contract and the particular row in the dashboard. Files should be named '<ID> Signed Copy.pdf' where <ID> is the ID number of that booking. For example, if the ID is 15, name the file '15 Signed Copy'. A job will run overnight that will look for new scanned signed PDF files in that folder. If a new one is found for a booking row that does not yet have a link to a signed copy, a new link will be created.



Calendar Event column


This field in each row contains a link to the calendar event associated with that booking. The link takes you to a full screen view of the schedule but does not allow you to make changes to the calendar. Alternatively, you can bring up a browser session and navigate to Google Calendar where you can make changes.



1st Half / 2nd Half Payment columns


The 1st Half / 2nd Half Payment columns use cell validation rules in Google sheets to ensure that there is only 4 valid options. The only option that you set manually is the 'Received' option. Set the field to 'Received' after you have received that payment. The other fields are all set automatically by TrackVacs. Here are the details on each:


  • Not Due - this is the initial status of any new booking, and remains in effect until either you indicate that payment has been Received, or TrackVacs sets it automatically based on if a reminder was sent out or later, even with a reminder the payment is still Over Due.

  • Received - set this status manually after you have received the payment.

  • Reminder Sent - This status is set automatically by TrackVacs for any row that was marked 'Not Due', but an upcoming payment is approaching and an email reminder was sent out. Only one payment reminder for each of the 1st / 2nd half payments will be sent automatically by TrackVacs. You will be BCC'd on these emails. If additional communication is necessary with the renter, it is left to you so that the TrackVacs system doesn't overwhelm or frustrate your customers. If you chose to use the feature of storing Signed Copies, the PDF scanned file of the signed rental contract will be attached to both the 1st and 2nd half payment reminders. When the 2nd half reminder is sent, it will also include any additional attachments you have put in the 'Attachments' folder, such as directions to your property or welcome information.

  • Over Due - This status is set automatically by TrackVacs for any row that was marked 'Reminder Sent', but you have not yet manually changed the status to 'Received' because the payment has not arrived.


Rent Owed column


This field in each row is calculated automatically. It reflects the total rent owed, including your cleaning fee if applicable as well as any charges related to extra people that you specify. It is calculated based on the number of nights stayed, together with the nightly or weekly rate for the appropriate season. If a given booking is longer than 7 nights, the weekly rate will be applied. For example, if a guest stays 10 nights and the weekly rate is $2000, the equivalent nightly rate is a little more than $285, so the total rent will be $2857.14 for the 10 nights, excluding cleaning fees or extra people charges.

Of course, if you ever want to override the calculated rent there is no problem doing that.



Rent + Tax Owed column


This field in each row is calculated automatically. TrackVacs sets up an equation in these cells that takes the Rent Owed and multiplies it by (1+ the sales tax rate you specified). That way, if you manually change the rent, data in this column remains accurate.



Collected column


The Collected field in each row is always entered manually by you.



Remaining column


This field in each row is calculated automatically. TrackVacs sets up an equation in these cells that takes the Rent + Tax Owed and subtracts Collected. That way, as you manually enter the Collected rent, data in this column remains accurate.



Deposit to Refund column


This field is initially set by TrackVacs based on your deposit policy. After you return a guest's deposit, manually change it to zero.


Tax to Pay column


This field is initially set by TrackVacs based on the tax rate you specified. It is calculated automatically. TrackVacs sets up an equation in these cells that takes the Rent Owed and multiplies it by the tax rate. That way, if you manually change the rent, data in this column remains accurateAfter you pay the sales tax, manually change it to zero.



The TrackVacs Menu


Send link to rental inquiry form


As described above, this menu item provides a quick and easy way to send a prospective renter the rental inquiry form.




Filter bookings by date



This collection of menu items provides various options for filtering the dashboard to a subset of data based on date.


  • Show all bookings - removes all filters, shows all data rows that exist
  • Show current year bookings - applies a filter to only show bookings within the current year
  • Show other year bookings - this menu option brings up a window that asks you for the year that you would like to focus on, and filters out all other bookings that take place in other years
  • Show all bookings to date - applies a filter to only show bookings that are in the past
  • Show projected future bookings - applies a filter to only show bookings from today forward

Filter visible columns



These 2 menu items provide a way to toggle between two views:

  • Compact view - this view provides a compact view of the dashboard that makes it easy to work with. It hides the following columns:
    • Renter email
    • Renter mailing address
    • Renter phone
    • Number of adults in renter's group
    • Number of kids in renter's group
    • List of guest names in renter's group
  • All column view - this view shows all of the columns above

Change rental business information



These 2 menu items provide a way to modify information that was initially specified in the TrackVacs Setup process:

  • Rates - brings up a window that allows you to modify the rate structure. New rates specified will replace the original rates, but will only be in effect for bookings from this point forward.


  • Other information - brings up a window that provides you access to all of the information originally specified in the TrackVacs Vacation Rental Setup form. By clicking on the link in the window, a new browser window will open with the Setup form. All existing data will still be in the form, allowing you to modify anything you like and resubmitting the form. New information specified will replace the original, but will only be in effect for bookings from this point forward.




Process inquiries



This menu option provides the ability to handle potential issues with incoming inquiries. If TrackVacs finds a problem with a new inquiry, the new row will be flagged for attention.

For example, in the screenshot below a new inquiry came in but the requested dates had already been marked tentative for another guest (the Status column for the prior guest is marked 'Prospect'). Instead of moving forward with processing the new inquiry and potentially causing scheduling conflicts, TrackVacs notes the issue and highlights it in yellow. In response, you can use the 'Process inquiries' menu item. Doing so will cause a window to appear that describes the problem and provides alternatives for dealing with straight from there. In this example of a date conflict, you can choose to:

  • Work with the renter to modify the dates of the new inquiry
  • Delete the row associated with the new inquiry
  • Accept the dates as is (maybe the 1st renter has backed out)
TrackVacs will then process the inquiry with this new direction. 




What sort of potential issues does TrackVacs monitor and protect against?


There are a number of checks made upon receiving a new rental inquiry:

  • Ensure that the departure date is not mistakenly put before the arrival date.
  • Ensure that the arrival date is at least 14 days from today. While it may be fine, if an inquiry is asking for arrival within the next 2 weeks, the situation is flagged for attention.
  • Ensure that the requested dates are not already booked
  • Ensure that the requested dates are not already penciled in for another guest (Status marked 'Prospect').

Questions or Comments? 

Please let me know how I can make TrackVacs better.



TrackVacs Installation Guide


TrackVacs is a software tool leveraging Google Apps that automates the management tasks associated with running a vacation rental management business. It is 100% cloud based, and 100% free. For more information, check out this post.

This guide will provide step by step instructions for installing TrackVacs.

Once you install TrackVacs, check out this post for help on using it.

Installing TrackVacs


Here are the 3 steps we will walk through:

  • Open a free Google account - TrackVacs uses the Google Apps platform
  • Copy the TrackVacs system files into your account
  • Configure TrackVacs for your specific vacation rental management business


Open a free Google account


First, open a free Google account by going here. The username is what will show up when your renters get an email from you, so pick something associated with your vacation rental. For example, my vacation rental is the Lodge at Palmer Lake, so a good choice would be 'DanaAtTheLodgeAtPalmerLake' @ gmail.com.



Copy the TrackVacs system files into your account


Now copy the TrackVacs system files into your new account by clicking here.

Here is what you will see. Hit the 'play' triangle button circled in red below:



You will be prompted for authorization, hit Continue.


Then next window is a continuation of the authorization, hit Accept.


Now you can close the TrackVacs setup new user script window, and go to your new Google Drive account screen. Double click on the TrackVacs folder in Google Drive and you will see: 



 
At the top, 3 folders were created:


  • Attachments - this is where you put files you want to include as email attachments in reminders that are sent to your rental customers. For example, directions to your property or welcome information for when they arrive.

  • Contracts - this is where the software will put all of the contract documents it creates for each rental booking.

  • Signed Copies -  this is the place to upload scanned copies of rental agreements that have been signed by your renters.

Below the 3 folders, you can see 3 files:

  • A master template for an invoice letter / rental contract - you can edit this template as you see fit for your business. The only parts of the document that cannot be modified are the text elements enclosed with <> brackets. For example <Check In Time>. Any text elements formatted like this are keys in the document that tell TrackVacs where to put the right text that corresponds to your vacation rental business and the particulars of each rental agreement, such as dates and prices.


  • The TrackVacs dashboard - this is the center point of the system, and where you will go to access everything moving forward.


  • An inquiry form - this is the form that is sent to prospective renters to kick off the vacation rental booking process.


Configure TrackVacs for your specific vacation rental management business


Now that installation is done, now we can tailor the system to your vacation rental. There are a few steps, but this is a one time task that will take less than 10 minutes. 

The first step is to authorize the software to perform all of the automation tasks. To do that, open the TrackVacs Dashboard, and in the menu, pick Tools, then Script editor:




This will bring up a new window in your browser. TrackVacs uses an advanced Google service for calendar automation, so you need to enable it. Do so by picking Resources, then Advanced Google services:



That brings up a window where you can see advanced Calendar API is already turned on, but Google requires that you enable it. Do so by clicking on 'Google Developers Console' at the bottom of this window (circled in red below):



The Google Developers Console looks like this. Pick 'Calendar API' (circled in red below).



Now enable the Calendar API by clicking the blue 'Enable API' button (circled in red below):



You can now close this window in your browser. Back in the script editor window, hit OK in the Advanced Google Services pop-up window to close it. The in the menu pick Run, then 'onOpen':



The script then asks for authorization to perform all the automation tasks. As above, pick Continue and Accept in these windows:

 

You can now close this script editor window in your browser. Returning to the TrackVacs dashboard, you will see a welcome screen:



Enter your rental rate structure for various times of the year. The software will make sure you account for a rate for any given date. If your rates change later, you can use the TrackVacs menu to make those changes. Once you fill in your rates like the example below, pick "I'm done, submit Rate Structure". Step 2 will then be shown, which is to provide the specifics about your vacation rental property and policies so TrackVacs can automate the process accordingly. Pick "Click here, fill in and submit the form".



This will bring up a new browser window like below. Fill in the fields and click Submit.




You will get acknowledgement that your form has been submitted. Go ahead and close that browser window and go back to your TrackVacs dashboard. You'll see a confirmation window that the setup process is complete:




Try it out


That's it. Of course your dashboard is initially empty, so the first thing you may want to do is to submit a test vacation rental inquiry so you see what TrackVacs can do for you. To do this, pull down the TrackVacs menu and pick 'Send link to rental inquiry form'. Email yourself, click on the link to the inquiry form in your email, and submit a test inquiry. 




Don't worry, you can delete the test entry later - just right click on the row number on the left in the spreadsheet, and click 'Delete row'.

When the inquiry comes in, TrackVacs will process it and provide all the details of the prospective booking, including links to the invoice letter / rental contract as well as the calendar event. All the manually work is done for you, but of course you can edit and change anything as necessary.

To get more detail on using TrackVacs, check out this post for a User Guide.

Questions or Comments? Let me know!