A Time to Excel

How does discounting my rental rates affect length of occupancy?
Is a $1 move-in special more profitable than giving rental discounts?
What percentage of my tenants pay a discounted rent?
When does offering automatic rent collection make financial sense?
How many hours a month does my manager work on site?

Today's management software has some amazing reporting power, but to answer all these questions, you will need a report no one else has previously created. Microsoft Excel can provide the answers--if you know how to ask the questions.

This is the part one of a two-part column. At first, the procedures noted may seem a bit technical, but the step-by-step guidance will get you to the finish line in minutes. Best of all, you can save the finished report and update the information any time you need current answers.

Let's Get Started

First, create an easy export feature for your management software. You can do this by using your mouse to click on your computer's START button and selecting Control Panel to open a window with choices. Select Printer and Faxes, then Add Printer. The drop-down port selector must be set to File, and the location should be set to Local. Choose Generic as the manufacturer, with the model being Generic/Text only. Click on the OK button to complete the printer setup.

From this day forward you can send any printable report, from any program, to the "Generic" printer. The output will be saved to a text file you choose. You do not need to do this step again. Have your management software send the master tenant list to the generic printer and save the output as "Master_List.txt."

Click START, Programs, Accessories, then choose Wordpad. In Wordpad, choose File, then Open. Change the file type to All Documents (*.*), and locate the file called "Master_List.txt." You want to remove anything that is not in row-column format. Delete any additional information in the very top or bottom rows, such as your site name, address and anything else not relating to tenant data. When you are done, select Save from the File menu and exit the program.

Now open Microsoft Excel and choose Open from the File menu. Change the file type to All Files (*.*), and locate "Master_List.txt." The program will ask you to highlight the columns of information you don't need. Keep only three columns: Street Rent Rates (column A), Actual Rent Being Paid (column B) and the Tenant's Move-In Date (column C). Here is a short sample output representing a very small facility:

Excel will letter the columns and number the rows automatically. The intersection of each row and column is a rectangular area called a cell. The program lets you place a value or formula at any cell location. By creating your own formulas, you can extract patterns and meanings to the data table you just created.

What percentage of my tenants pay discounted rent?

Our sample table has only a few lines, so we can easily see we have seven rented units. But if we had hundreds of units, we would need a formula to quickly provide the answer to this question. We will place our formulas in column D and explain their meaning in column E.

Highlight cell D1, type =COUNTIF (B1:B9999,">0"), and press ENTER. The cell will display the total number of tenants paying any rent. This total will ignore vacant or damaged units, those being used for the company, and any others where rent is not currently being paid. So you remember what cell D1 means, move to cell E1 and type "Rented Units."

Now highlight cell D2, type =100*D1/ COUNTIF(A1:A9999,">0") and press ENTER. The cell will display your occupancy as a percentage of all your units. Move to cell E2 and type "Percent Occupancy."

Looking at our table, it is easy to see four tenants are paying less than the scheduled rent. We are comparing the value in column B to see if it is smaller than the corresponding value in column A, but only for units paying rent. To write this question as an Excel formula, highlight cell D3 and type =SUM(IF(B1:B9999>0,IF(A1: A9999>B1:B9999,10)),0). Then hold down both the SHIFT and CTRL keys while pressing ENTER. This last step instructs the program to use data sets. Curly brackets are automatically applied to the ends of the formula as confirmation. Move to cell E3 and type a definition, "Tenants Who Pay a Discounted Rental Rate."

The answer in cell D3 divided by the answer in cell D1 is the ratio of how many tenants are paying below the scheduled rent. Highlight cell D4, type=100*D3/D1, and press ENTER to see this ratio listed as a percentage--57.14 percent in our example. Move to cell E4 and type "Percent of Tenants Paying a Discounted Rental Rate."

How does discounting my rental rates affect length of occupancy?

Now that you know the percentage of tenants who have special pricing, let's see if those who pay a discounted rent stay shorter or longer than those who pay the standard rate. Let's only consider tenants paying a discounted rent and list the answer as the average number of months. Highlight cell D5 and type=SUM(IF(C1:C9999"",IF(A1:A9999>B1:B9999,DAYS360 (C1:C9999,"6/1/03"),0),0))/(30*D3). I used June 1, 2003 as our date. Be sure to use the current date, and don't forget to hold down both the SHIFT and CTRL keys while you press ENTER to submit this formula. Move to cell E5 and type "Months of Average Occupancy for Discounted Tenants."

We can change our focus to look only at tenants paying the scheduled rent by highlighting cell D6 and typing =SUM(IF(C1:C9999"",IF(A1: A9999=B1:B9999,DAYS360(C1:C9999, "6/1/03"),0),0))/(30*(D1-D3)). Hold down both the SHIFT and CTRL keys while pressing ENTER, then move to cell E6 and type "Months of Average Occupancy for All Other Tenants."

We are looking for the difference between these two values. Highlight cell D7 and type =IF(D5>D6,100*((D5/D6)-1),"NO"). Press ENTER, move to cell E7 and type "Percent Longer Tenancy From Discounting Rental Rates."

How much money are you giving away in rent discounts?

Highlight cell D8 and type =100-100*SUM(IF(B1:B9999> 0,IF(A1:A9999>B1:B9999,B1:B9999,0),0)) /SUM(IF(B1:B9999>0, IF(A1:A9999>B1:B9999,A1:A9999,0),0)). Hold down both the SHIFT and CTRL keys while pressing ENTER. Then move to cell E8 and type, "Percent of Lost Revenue on Units With Discounted Rent." Your spreadsheet should now look like this:

From our table, we can see the tenants who receive a rental discount are staying 4.32 percent longer but generating 8.62 percent below street-priced revenue. This is a significant income loss for minimal gain. The small increase in the length of occupancy indicates renters are not very price-sensitive. This facility is nearly 90 percent occupied, and it is time to raise rents. Excel took the guesswork out of our rent management. Now that's power.

Next month, we will use Excel to compare the value of the $1 move-in special, evaluate the real costs of offering automatic rent collection, and show how to analyze employee work hours.

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 [email protected]; visit www.quikstor.com.