Creating New Techniques

I’ve been working on a new idea which could be interesting. I generally don’t get a chance to do this so it’s been fun to try to figure this one out. I’m trying to do this for budgeting purposes but I may not finish in time due to the complexity. If it is not done, we can always apply it next year. What I’m trying to do is assign random choices across time to see what happens under a certain set up. To clarify, each month some leases expire and either the lease gets renewed or the unit becomes vacant. Sometimes within the same month, a new lessee will move in. In some months, there are more move ins than there are expirations so some of the vacants become filled in with a new lease. So each month, the unit is either renewed, has a new move in or becomes vacant. The vacants become a pool of vacants from which we dip into when there are more move ins than expirations.

Apparently, the properties know what the renewal percentage they are able to reach so I start from there. I gather the expiration dates for all of the units and from there I can figure out how many units will be expiring each month and how many renewals there will be. The properties also seem to know how many move ins they will get which I’m just going to accept, just to get something going. So at this point, I have the number of expirations for each month and which units, the number of renewals each month, and the number of move ins.

Now, I’m not trying to figure out who will renew – that is a different problem which I may tackle in the far future. Right now, I’m just interested in what kind of revenue results will I get if I ran this simulation many times with random units being renewed, random move ins and random vacancy. What kind of range of revenue will the property attain or does it just oscillate wildly? I’m going to assume that we don’t know who will renew although that is probably the wrong assumption but it’s just to start off. So I have to figure out how to assign which expiring unit will renew, which gets a new lessee, and which becomes vacant. So if 20 units expire in a month and I know that 12 will renew because historically 60% will renew, I then have to figure out how to assign which of the 20 units will be renewed, get a new lease or become vacant.

So I have a table (table 1) that assigns a random number using RAND to all of the expiring units across the months and then another table (table 2) that ranks the random number within a month. So column C (just for the sake of an example) has the RAND function assigning a random number to all of the units. Column D represents January and all units that are expiring in January will have a formula referencing column C to pull in the random number. Column E will represent February and will pull in the random number from column C for those units expiring in February. Each month will pull in the random number only for those units that are expiring in that month. This is table 1 of random numbers. For table 2, let’s say it starts in column AA. That column uses a RANK function that references column D: it ranks all of the random numbers sitting in the January column. Column AB ranks the random numbers sitting in column E for February. From this I create table 2 of ranks. Now I can assign ranks 1 through 12 as renewals for January in the example of 20 expirations. And if you have 5 move ins, I can assign rank 13 through 17 as move ins.

So thus far, the expirations, renewals and move ins have been figured out. The harder problem, for me at least, was figuring out how to handle the vacant units because they may eventually become a move in. I’m close to figuring it out. Right now I have a row of expiration counts for each month, a row of renewal counts and a row of move in counts. To get a row of vacant counts for each month, I take expiration – renewal – move ins. If the number is positive for the month, then some of the expiring units become vacant and gets added to the pool. If the number is negative, then we have more coming in than expiring so we need to draw from the pool of vacants.

I found I couldn’t just set up a table of vacants assigned across the months much like I did in table 1 and 2 because I needed to be able to pull in vacants from prior months for cases when move ins were greater than the expiration, so I used a column of random numbers rather than a table of random numbers.

So far, I have a column that assigns a random number to all of the units that become vacant and another column to rank those random numbers. In the example of January with 20 expirations, 12 renewals and 5 move ins, I can assign the remaining 3 as vacant. That’s the easy part. But say we have 10 move ins instead. Now we have 7 more moving in than are expiring so I have to pull from some of the vacants to use as move ins. In the row of renewal counts, there will be a negative 7 to denote more are moving in than are expiring (reducing the vacant pool by 7). So vacants ranked from 1 to 7 will become move ins for January.

Okay, easy enough but let’s move on to February. Let’s pretend that February’s renewal count is negative 3 (-3). So now I pull in ranks 8 through 10 as move ins. What I did was to use SUMIF to sum up the vacant count row only if the number was less than zero. Within the SUMIF function, I used a combination of absolute and relative references. So January contains SUMIF($D$2:D$2,”<“&0,$D$2:D$2). The February will look like SUMIF($D$2:E$2,”<“&0,$D$2:E$2). Actually the formula is more complicated than that; I’m showing you just a portion of it.

So what’s the problem? Well, that’s the topic for the next post because this one is already long.