J
Jonathan Douek
Hi,
I am making a potential real estate investment model and I would like
help with a specific formula.
I am having trouble showing the increase in operating expense
escalations (from my expense table cf) for new leases (difference
between current to base year -> the year the tenant entered office to
the following years escalations). In the end, I want to show the total
amount ($) of the escalations for the tenants entering vacant space
and space being renewed (which will be the same formula(.
The info needed for this is: the" actual expense" which is lets say
for Q1 is the operating expense for that Q *(sf of vacant space in
that Q / total net square feet). Then I have the "base expense" -
which is the "actual expense" of Q2 minus "base expense" of Q1.
I can do it for one tenant but the problem is adding all the
escalations from the different tenants... what is the formula?
My data is set up like this: (T=table)
T1: I have one separate table which shows horizontally the total
vacant sf for each Q (no use explaining the sources of that data) and
the sf being renewed underneath also for each Q. Total 20 Q's.
T2: in another separate table I have an expense table with total
operating costs for each Q.
T3: in the table I need help with (also divided into 20 Q's), I'll
explain what I did to achieve escalations for one single tenant:
I made 2 rows, one named "base" and the other "actual expense".
The "actual expense" for Q1 is: 3q of T2 * ((T1)Q1 of vacant space sf
(F4)/ nsf). Now as this row is dragged there is an escalation
according to the locked value on T1 on Q1.
The "base row" Q1 is the same as Q1 of the "actual expense". In Q2 of
this row its: Q2 of "actual expense" minus Q1 of "base row" (locked
F4). So I can see the escalation for the vacancy at Q1 but the problem
is that I have multiple vacancies throughout the 20Q timeline which I
also need to calculate in the escalation row.
I will have to do the same thing for the sf being renewed and also sub
totals for both.
If this is too hard to understand from my expl, what function/formula
do you think can help?
I am making a potential real estate investment model and I would like
help with a specific formula.
I am having trouble showing the increase in operating expense
escalations (from my expense table cf) for new leases (difference
between current to base year -> the year the tenant entered office to
the following years escalations). In the end, I want to show the total
amount ($) of the escalations for the tenants entering vacant space
and space being renewed (which will be the same formula(.
The info needed for this is: the" actual expense" which is lets say
for Q1 is the operating expense for that Q *(sf of vacant space in
that Q / total net square feet). Then I have the "base expense" -
which is the "actual expense" of Q2 minus "base expense" of Q1.
I can do it for one tenant but the problem is adding all the
escalations from the different tenants... what is the formula?
My data is set up like this: (T=table)
T1: I have one separate table which shows horizontally the total
vacant sf for each Q (no use explaining the sources of that data) and
the sf being renewed underneath also for each Q. Total 20 Q's.
T2: in another separate table I have an expense table with total
operating costs for each Q.
T3: in the table I need help with (also divided into 20 Q's), I'll
explain what I did to achieve escalations for one single tenant:
I made 2 rows, one named "base" and the other "actual expense".
The "actual expense" for Q1 is: 3q of T2 * ((T1)Q1 of vacant space sf
(F4)/ nsf). Now as this row is dragged there is an escalation
according to the locked value on T1 on Q1.
The "base row" Q1 is the same as Q1 of the "actual expense". In Q2 of
this row its: Q2 of "actual expense" minus Q1 of "base row" (locked
F4). So I can see the escalation for the vacancy at Q1 but the problem
is that I have multiple vacancies throughout the 20Q timeline which I
also need to calculate in the escalation row.
I will have to do the same thing for the sf being renewed and also sub
totals for both.
If this is too hard to understand from my expl, what function/formula
do you think can help?