A Time to Excel, Part II

Doug Carner Comments
Posted in Articles, Technology

In part I of this article (Inside Self-Storage, July 2003), we looked at the following questions:

We answered the first two questions by studying a ficititious facility. What we learned was that, by giving away 8.62 percent of its rental income in monthly discounts, the facility was only gaining a 4.32 percent increase in its length of tenant occupancy. Until we did our math analysis, we could only guess at the true value of offering rental discounts. Several of you sent me the results from your storage facilities. Most of you were quite surprised by your findings.

Now let’s analyze another popular promotion, the first-month $1 move-in special. As before, we will start with an exported report from our management software and keep only three columns: street rent rates (column A), actual rent being paid (column B) and the tenant’s move-in date (column C). (If you want a refresher on how to do this in Microsoft Excel, refer back to the July issue.) Here is a short sample output representing our small facility:

Is a $1 first-month move-in special more profitable than offering rental discounts?

Let’s assume the tenants currently paying a discounted rent had instead been given their first month’s rent for a $1. As before, we will place our formulas in the empty “D” column and explain their meaning in column “E.” For consistency with part I of this article, the listed answers will presume today is 6/1/03.

Let’s start by highlighting cell D1 and typing =SUM(IF(C1:C9999=””,0,IF(A1:A9999>B1 :B9999,A1:A9999*DAYS360(C1:C9999, TODAY()),0))/30).

Hold down the SHIFT and CTRL keys while you press ENTER. This last step instructs Excel that you are using data sets. Curly brackets are automatically applied to the ends of the formula as confirmation. Move to cell E1 and fill in what this new number represents: “Gross Potential Rent on Discounted Units.”

Now move to cell D2 and type =SUM (IF(C1:C9999=””,0,IF(A1:A9999>B1:B9999 ,A1:A9999-1,0))). Again hold down the SHIFT and CTRL keys while pressing ENTER. In cell E2, type “Net Loss With $1 First- Month Move-In Discount.” Finally, move to cell D3 and type =100*D2/D1. This time, just press ENTER, move to cell E3, and type “Percent of Lost Revenue on Units With Discounted Rent.”

For our example, the value in cell D3 is 19.12 percent. This is an extreme loss in income just to achieve a 4.32 percent increase in our length of occupancy. It is even worse than the 8.62 percent loss that resulted from rental discounts. As before, try these formulas with your site data, as it may produce very different results.

Does offering automatic rent collection make financial sense?

Do you mail a tenant statement prior to rent payment being due? If so, your tenant will review the merits of his monthly rent expense before sending each payment. He may decide to vacate the unit and save his money. But what if you could diminish this risk and reduce postage expenses for you and your tenant?

Automatic rent collection eliminates the need to pre-inform your tenants of each rental payment. The tenant’s signatureon- file allows you to automatically debit rental funds from his checking or savings account (the banking term for this is ACH). Without monthly invoice reminders, most tenants will stay longer. I have found 60 percent of tenants will choose automatic ACH debiting, and 70 percent of those will remain renters for one additional month.

Your ACH-merchant bank fees are set by the management software you use, and those rates can vary dramatically. Some software companies charge as low as 20 cents per transaction while others tack on profit-sharing and raise your fee to 1.3 percent of every transaction. Again, we will use our oversimplified sample site to perform some calculations.

First, we need to know how many tenants we have at our site. Let’s start at cell D4 and type =SUM(IF(C1:C9999=””,0,1)). Hold down the SHIFT and CTRL keys while you press ENTER. Now move to cell E4 and type “Occupied Units.”

Now let’s calculate the average retail-rack (nondiscounted) rate for our site. In cell D5, type =SUM(IF(C1:C9999=””,0,A1:A9999))/ D4. Hold down the SHIFT and CTRL keys while pressing ENTER. Now move to cell E5 and type “Average Rack Rate of Occupied Units.”

Here comes our formula to calculate the average length of occupancy for all tenants who pay the street rate. In cell D6, type=SUM(IF(C1:C9999=””,0,IF(A1:A9999=B1:B9999,DAYS360(C 1:C9999,”06/01/03”),0)))/(30*(SUM(IF(B1:B9999>0,IF(A1:A9999= B1:B9999,1,0)),0))). Hold down the SHIFT and CTRL keys and press ENTER. Move to cell E6 and type “Months of Occupancy For Nondiscounted Units.”

Finally, let’s combine all the above values to determine our monthly gain from implementing ACH rent collection. We will assume the merchant fee is 20 cents per transaction and remove those fees from our total. As noted above, 60 percent of our tenants will choose this ACH service, and 70 percent of those people will stay for an extra month. In cell D7, type =.6*.7*D4*D5/D6-.6*D4*0.20. Press ENTER and move to cell E7 to type “Net Monthly Increase From ACH Rent Collection.” To turn these these dollars back into a percentage, go to cell D8 and type =100*D7/D4/D5. Press ENTER, move to cell E8, and type “Percent Income Increase From Automatic Rent Collection.” Your spreadsheet should now look like this:

From our table, we see that offering automatic rent collection is increasing our income by nearly 8 percent without giving anything away. This increase far exceeds the gain we achieved when we tried rental discounts and the $1 move-in special. This savings has encouraged hundreds of storage facilities to switch to ACH. The added income can pay for your door alarms, e-commerce, a kiosk station or video surveillance. In turn, these items will make your facility more valuable. Talk to your current vendor about which of these products/ services it offers.

On a personal note, I would like to thank the hundreds of readers who have sent me their suggestions and questions over the last two years. This is my final TechTalk column. I will, however, continue to reply to all reader inquiries.

Doug Carner is on the Western-region board of directors for the Self Storage Association. He is also the vice president of QuikStor Security & Software, a California-based company specializing in access control, management software, digital video surveillance and corporate products for the self-storage industry. For more information, call 800.321.1987; e-mail doug@quikstor.com; visit www.quikstor.com.

comments powered by Disqus