RE: Office Tenant - Rental Status Sheet
Hi
I am looking to create a worksheet which works out how much rent is still outstanding for a particular client in an office building. The worksheet should act as a statement of account / rent ledger showing the client the dates they were invoiced,the amount they were invoiced, how much they paid, their outstanding amount for each payment (if any) and then their current account balance after each new invoice is added.
For example if they were invoiced for £360 pounds, but only paid £300, the outstanding amount column will list how much is still to pay.
When the client is invoiced the same amount for the next month, i know the client owes £60 from the last invoice, but i would like this reflected on the worksheet. the balance should then change to reflect if the client has covered the outstanding from last month or only partially covered it or the client has only paid the current months rent and has ignored the outstanding.After each new entry, the balance should change to reflect, how much the client owes.
Please see my current table below
I currently have a table with six columns:
____________________________________________________________________________________________
Invoice Date - Rental Period - Amount Invoiced - Amount Received - Amount Outstanding - Balance
__________________________________________________________________________________________
Below is more info regarding each column
Invoice Date = This will be entered manually
Rental Period = this is selected from dropdown list
Amount Invoiced = Index Match formula is used to get this date from main client table
Amount Received = Entered manually
Amount Outstanding =Amount Invoiced - Amount Received
Balance = ???
__________________________________________________________________________________________
I am finding it difficult to work out the balance after each entry. For example
Invoice Date - Rental Period - Amount Invoiced - Amount Received - Amount Outstanding - Balance
22/06/16 01 to 31st July - £360 - £300 - £60 -
22/07/16 01 to 31st Aug - £360 - £360 -
I am currently learning excel and know some of the basics, i would be grateful for any help that can provided regarding this problem.
If you require any further information, i will happily provide it.
Kind Regards
Luke
Hi
I am looking to create a worksheet which works out how much rent is still outstanding for a particular client in an office building. The worksheet should act as a statement of account / rent ledger showing the client the dates they were invoiced,the amount they were invoiced, how much they paid, their outstanding amount for each payment (if any) and then their current account balance after each new invoice is added.
For example if they were invoiced for £360 pounds, but only paid £300, the outstanding amount column will list how much is still to pay.
When the client is invoiced the same amount for the next month, i know the client owes £60 from the last invoice, but i would like this reflected on the worksheet. the balance should then change to reflect if the client has covered the outstanding from last month or only partially covered it or the client has only paid the current months rent and has ignored the outstanding.After each new entry, the balance should change to reflect, how much the client owes.
Please see my current table below
I currently have a table with six columns:
____________________________________________________________________________________________
Company A
Invoice Date - Rental Period - Amount Invoiced - Amount Received - Amount Outstanding - Balance
__________________________________________________________________________________________
Below is more info regarding each column
Invoice Date = This will be entered manually
Rental Period = this is selected from dropdown list
Amount Invoiced = Index Match formula is used to get this date from main client table
Amount Received = Entered manually
Amount Outstanding =Amount Invoiced - Amount Received
Balance = ???
__________________________________________________________________________________________
I am finding it difficult to work out the balance after each entry. For example
Company A
Invoice Date - Rental Period - Amount Invoiced - Amount Received - Amount Outstanding - Balance
22/06/16 01 to 31st July - £360 - £300 - £60 -
22/07/16 01 to 31st Aug - £360 - £360 -
I am currently learning excel and know some of the basics, i would be grateful for any help that can provided regarding this problem.
If you require any further information, i will happily provide it.
Kind Regards
Luke
