• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocating Recovered Charges

D

Denise

New Member
Hello,

I work in a real estate management corporation. We receive deposits from tenants when they move into one of our units. When they move out, we calculate any CAM charges or damage fees. I am trying to prepare a report that shows if we have a $500 security deposit, and have $100 in utility fees and $200 in damages, they would have a secuirty forfeiture of $300 and a refund of $200. I am having a hard time figuring out a formula that will put the amounts in the right "buckets". It sounds simple in theory, but my main issue is that if I have a security deposit of $800, and damages of $900, I can't get it show me the $800 in the recovered column. I have strung together IF formulas and nested several different formulas and I just can't get it. I am willing to email the spreadsheet to anyone that help. Thanks for any suggestions.
 
Hi Denise,

If I understand your question, you are trying to show in the "recovered" or "forfeiture" column the minimum of the damage amount or the initial security deposit. If so, you could use the MIN() formula to get the desired result.


For example, for the dataset below

[pre]
Code: 
#   A           B       C       D               E
1   Deposit	Arrears	Damages	Forfeiture	Balance
2   500	        100	200	300	        200
3   800	        0	900	800	        -100
[/pre]
Values in Column D can be calculated as =MIN(B2+C2, A2)

Values in Column E can be calculated as =A2-D2


This ensures that Column D shows the maximum of the deposit that will be forfeited, while Column E shows what the tenant owes you (negative amounts) or you owe the tenant (positive amounts).


Hope this helps.


Cheers,

Sajan.
 
Thanks for the post and the idea. Unfortunately, that only solves half of my dilemma. Below is what I am trying to accomplish with some figures for some illustration:


A B C D E F

Sec Dep Arrears Damages RcvdArrears RcvDamages Forfeiture

500 100 200 100 200 300

100 125 300 100 0 100

1000 200 950 200 800 1000


**(sorry, I can't seem to get this to stay in proper columns when I post )


Values are entered into columns A, B and C, and I can get the formula for column F... it is formulas for D and E that I am having difficulty with because of the variables. In one instance I have a security deposit of $100 and damages of $800. I can't get only $100 to show in the recovered column. Obviously this is a very simple version of what i am trying to do. The report has 8 different charge areas and therefore 8 different recovered columns as well. Again, any help is greatly appreciated.
 
Hi Denise,

Anything between the backtick char (located below the ~ char) will be displayed as is.


Are you wanting to show that when the deposit was $100, but the damages are for $800, the recovered amount is still only $100? That can be shown using MIN(100,800). (i.e. MIN(deposit, damages).


Perhaps I am not understanding what you are trying to do. If so, could you include your example, with the backtick character, showing how you would manually calculate the values?


-Sajan.
 
Hi Denise ,


If you email your workbook to one person , the solution will depend on the availability and efforts of only that one person. Uploading your file , and posting its access link here will make it available to everyone in this forum , and you are likely to get a solution quicker ; you may also get several and possibly better ways of doing the same thing.


The way to upload is :


http://chandoo.org/forums/topic/posting-a-sample-workbook


If you still want to email your file , you can send it to me here : narayank1026@gmail.com


Narayan
 
Hi Denise,


Can you show me what would be ideal result for the following case and how you are calculating values for column D, E, F (I mean mathematical formulas)! Take following data from your last post as example and explain the process:

[pre]
Code: 
Sec~Dep Arrears Damages Rcvd~Arrears Rcv~Damages Forfeiture
500     100     200     100          200         300
[/pre]
Regards,

Faseeh
 
I have never used dropbox before so I hope this link works. I still can't figure out to write a post that keeps my data in proper columns.


https://www.dropbox.com/s/9up2w163t1vwerw/Move%20Out%20Report%20for%20help.xls


Thank you for any help / suggestions you can provide me.
 
Faseeh, Between each of the "Rcvd" columns I have hidden columns that start with what would be column C as my beginning credit which is the total of the security deposit. Then after the Rcvd Arrears Column I have a subtotal that shows me the balance of the availble security less rcvd arrears. Below are the formulas I am using, taking into account "hidden columns"


Code: 
Sec~Dep    [code]Arrears    [code]Damages   Rcvd~Arrears
Rcv~Damages[/code] Forfeiture[/code]

500 100 200 100 200 300


For Rcvd Arrears I used =IF(D1>0,D1-b1,0)

Three would then a hidden column between rcvd arrears and rcvd damages that gives me a subtotal of the new available balance.

The forumula for rcvd damages is =IF(F1>0,F1-C1,0)

For Forfeiture column I used =e1+g1

My problem is that if the charges are higher than the secuirty deposit, I can't get the Rcvd columns to only pick up the amount available until it is zero, which would then translate into the tenant owing us funds back.

I hope I have explained this clearly, but I did add the file in my last post above.

I tried the MIN formula but that doesn't work either because it shows a negative amount if the charges exceed the available credit, and I need it to pick up the amount that is only being recovered.
 
Hi, Denise!


Give a look at this update file:

https://dl.dropbox.com/u/60558749/Allocating%20Recovered%20Charges%20-%20Move%20Out%20Report%20for%20help%20%28for%20Denise%20at%20chandoo.org%29.xls


I've just modified formulas in rows 10 and 11 (yellow highlithed), the case that wasn't working properly. Only added a MIN formula to fill buckets up to remaining amount available.


Check it and advise if any issue.


Regards!
 
I got it... and it was with the help of the MIN formula within an IF function. Thank you all for your help, I appreciate it!
 
SirJB7... yes, that is exactly what I had come up with too. Consider yourself hugged for even taking the time to look at this and respond... Thanks Sir!!!
 
Hi, Denise!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

PS: store huges as a deposit for further refund ;)

PS2: just download again the file, slight update
 
You must log in or register to reply here.
Back
Top