These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 12, 2016

Property Management Tools: Using Excel VBA to Automate Rental Property Accounting

Business Background

One of my businesses owns and manages residential rental real estate. Generally, the tenants deposit their monthly rent payments directly into a special “deposit account” I have set up at the bank. In the past, I have manually checked the deposit account to verify the dates and amounts of monthly rent payments. Then I have manually transferred this information into a basic Excel file and calculated relevant late fees as necessary. Finally, I have taken time to personally contact tenants as necessary regarding past due amounts, upcoming payments, etc.

Problem

Manually looking up tenant deposits, calculating rent and late fee charges, and personally contacting tenants is tedious, time consuming, and, while necessary, adds relatively little value to the business. In our technologically capable, but extraordinarily hectic environment it is essential to leverage the abilities of technology to reduce the burden of these types of repetitive, low value-add tasks. Time saved from automating this lookup, recording, and contacting process can be better spent finding new deals, evaluating opportunities, and developing business strategy.

Solution: System Overview

This tool automates the previously manual rental property accounting process in three ways:
  1. The Update Register tool automatically updates the three primary components of the Property Account Register:
    1. Monthly Rent Charges—the code automatically calculates monthly rent charges due based on the difference between the current system time and past records already recorded.
    2. Rent Deposits/Payments—by automatically controlling Internet Explorer, the sub procedure logs into the online deposit/payment account, accesses the account history, and captures payments not yet recorded on the register.
    3. Late Fees—with all the charge and deposit data entered into the register, an algorithm is then able to calculate contractual late fees based on account balances, deposits made, and payment due dates.
  2. The Email Statement code emails an auto-generated account statement as a PDF file attachment to an auto-generated email message. To accomplish this, the algorithm opens an Excel template, personalizes it for the tenant, and adds the relevant charges, credits, and account balance based on the date range specified by the user. Then, a personalized email is generated using a text file template that is auto-filled programmatically. Finally, a PDF copy of the Excel statement is attached and the outgoing message is sent.
  3. The Text Statement procedure sends an auto-generated text message to the tenant’s mobile phone. This personalized message includes historical deposit information, current balance credits or amounts due, and customized messages depending on whether the account is current or delinquent.

Occasionally, tenants need reminders to keep the importance of paying their rent on time at the top of their minds. Beyond automating time consuming manual operations, this tool offers the additional benefit of communicating regularly with tenants to help them keep their rent obligations as a top priority.

No comments:

Post a Comment

Blog Archive