CHALLENGE! (for me, anyway) Track Rental Income from Master Deposit Sheet

**puzzledbyexcel** · 01-09-2013, 08:36 PM

Hi there,
I am an excel novice...I understand basics and some more complicated formulas, but I cannot figure out the best way to approach this.

I manage a 28 unit apartment complex. I have to send a spreadsheet to our bookkeeper when I make a deposit. The spreadsheet delineates date of deposit, tenant, unit, and how the payment is allocated.

What I would like to do is create a separate workbook which would track each tenant individually and keep a running total of their past due payments so I am able to see immediately if someone is late, or if (for example) they've missed a payment toward their last month's rent deposit; also, being able to supply tenants with a list of their payments and outstanding balances quickly would be so great!

I have explored both the "vlookup" and "if" functions, but get mired down in the details, macros are a complete mystery, and I don't even know if I'm on the right track? Can anyone help me or is this too ambitious for a newbie?

Thanks for any help! I've attached both a copy of the spreadsheet I send to the bookkeeper and what I would like the individual tenant sheet to look like (roughly...am open to any advice). Please note the individual tenant worksheet I created has some flaws and my formulas are not completely accurate...just trying to fumble my way through all of this.

Thanks in advance for any help!
Workbook6.xls

**RemoteMed** · 01-14-2013, 12:13 PM

Hi there,

This is an easy one. You can use criteria such as, IF, CountIf etc. Arrays would work well

Another thing you can do is options to highlight cells when payment is late etc.

I'd be keen to assist if you can elaborate more on what you need. And how exactly you want everything to work.

**Richard Buttrey** · 01-14-2013, 12:43 PM

The starting point for all these types of exercise are to design yourself a simple 2 dimensional database to capture all your data in a simple table. Don't worry at this stage about reporting it, that will come later. The mistake many people make is to design the report they want to see at the end of the day and then start fitting data into it.

In your case I suspect you need two databases. The first will be a record of tenants and all the 'fixed' information that relates. I say fixed although fields like Rent will no doubt change over time and, but it's fixed in the short term. So create a table with fields for:
Unit
Rent Start Date
Resident
Rent
Park
....and any other stuff like this.

The second database will be a Cashbook which will contain fields for
Date
Resident
Amount Received
Type of Receipt (this will be a data validation drop down containing a pick list of Rent, Late Fee, NSF Fee...and any other types of income you receive

Once you have these in place and have started capturing data the next step will be to work out how you want your reports presented. But one step at a time. I've written a few of these sorts of systems before and they can start to get complex if rents change or residents move to different units at a different rent. A lot also depends on the level of detail you need to report. It's usually fairly easy to report overall totals for things but when it comes to extracting tenants detailed statements showing balances at different time when rents have changed it can get complicated.

A halfway house solution whilst you build this might be to just have the 'Cashbook' database and extend the 'Type of Receipt' column (changing the name to say 'Record Type' and extend the drop down validation to include all the amounts due stuff like Rent, Park etc. The downside to this is you'd need to add a record every rental period for each resident, but it would simplify reporting in the interim.

I'm a firm believer in the incremental approach to building these things rather than a big bang approach where you try to cater for everything automatically from the outset. If you set out with simple databases you'll always be able to easily add to them.