Right, where do I start. I will try and make this simple, but I don't think I can, so please bear with me as this may be long winded!
I have just taken on an estate agent, who manages rental properties, charging commission on the rental income. They collect the rent on the landlord's behalf, deduct any commissions/cost of repairs and pay the balance to the landlord.
Upon payment, the estate agent issues the landlord with a monthly 'self bill' itemising the rental income and the deductions. Up to recently, they have done this on Word. As you can imagine, the larger the business has grown, their paperwork has spiralled. They are now registered for VAT and need something better, plus their auditor has been giving off at the state of their books, so they have asked me to set them up with something else.
The company cannot spend a lot of money on bespoke estate agency software, which from what I have seen is very expensive. On top of that, the owner does not want the member of staff to spend any more than 1 hour a week on the books. I have told him this is impossible as it takes time to keep proper books, especially when VAT is involved, but he doesn't want to know and will not listen to either me or the auditor.
The only software they had was an old copy of QB 2006, purchased ages ago and never used. I tried to set up the 'self bill' system within their QuickBooks 2006 and have found out the hard way that these systems are really not designed to work with this type of invoice.
The landlord is really a supplier in the case where he is owed money. It would then be easy if I could create in invoice for the tenant, collect the rent, and then create a bill for the landlord, deducting any commissions, linking the item categories so that the rent coming in is zeroed off once the bill is created (their auditor does not want to see the surplus rent coming in and out on the P&L, which is fine as they are disbursements). Only problem is, these systems do not allow you to print a bill, only an invoice, and the owner wants a piece of paper to give to the landlord.
He will not entertain the idea of keeping the another system for the landlord statements alongside QuickBooks, which will track the VAT and P&L, saying it will take up too much time.
The staff member is not trained in finance, so needs an basic system, which is difficult in these circumstances. After many hours working at this having come across various difficulties with the way the QB handles income & expense categories, liability accounts and bank accounts, the only way I could get it to work was to set up the landlord as a customer so I could print off a paper copy, renaming the sales receipt 'rental statement', showing the rent coming in, and the deductions taken off. The printout looked professional enough to please the boss.
Only thing was, using this method, the rent was then shown as coming in twice (remember the tenant has already been invoiced). The staff member was not confident with journals, and to be honest, I would not advise her to do this if she doesn't know books. So to get round this, the staff member prints the sales receipt, then goes back into it again, and reverses the figures, ie £500 rent is then changed to minus £500, and I have the rent and repairs categories linked to liability accounts so they do not show up in the P&L - the incoming rents come in from the tenants, and the rent and repairs and landlord payment goes back out. The commissions go in as income to show up on the P&L. It does mean an incorrect figure on the liability account due to the commissions not showing as coming out of the liability account to zero it off, but under the circumstances the auditor is okay with this and will prepare his own balance sheet seperately.
This system worked okay on QB 2006, the VAT was tracked, P&L okay, landlords were getting their statements okay, until the company was forced to upgrade to QB 2010.
The problem is, on some occasions, the estate agency spends more money on the property than is collected in rent, ie when there is a large repair therefore QB 2006 showed the rental statement in a minus figure, which was okay as the following month's rent usually cleared it. But QB 2010 will not recognise negative sales receipts! It throws up a warning to create a credit note! Which is in theory correct, but it really mucks things up for this company.
The poor girl dealing with this is under a lot of pressure as it is, she is making mistakes, which to be fair isn't really her fault when she has an a*hole boss only allowing her an hour a week and to modify this again and introduce a new admin process to work around this will be too much for her.
The bottom line is that none of the standard accounting packages are designed to deal with this type of billing. I have looked at Sage and VT with no headway. Every time I think I have it cracked, something new crops up. This company really need another system that has been designed with this type of company in mind, ie have a landlord that can be both a supplier and customer. Has anyone worked with this kind of billing before? I know mobile phone shops have a similar system in place when dealing with contracts, I used QB before years ago for a mobile shop, but the company was happy with a liability account printout. In this case the statements have to look like a professional statement. Does a system exist that can deal with this and is relatively cheap and easy for the novice and basic trained finance person to understand?
HELP! I am not getting anywhere with this, it's driving me to drink.
Whats the estate agents budget and how many landlords do they have on their books.
You can set up an accountant version of Arithmo. Each Landlord will be one client account, from which the estate agent generates invoice to landlord's tenants. The estate agents commision will be expensed in landlord's account and will be treated as income in the estate agents account.
The admin person can run all the landlords accounts from one system and it's really basic system.
To set up trial version, on behalf of your estate agent client, just click on link below and fill in form and we will open a trial ccount.
Dalbir ps: this is not a one hour a week job, in effect the admin person is doing half the bookkeeping on behalf of the landlords. All the best with managing this client and their expectations.
Hi, They may pay about £250 per year, maybe a bit more at a push, and have about 80 landlords on their books so far, plus a few block managements (apartments complexes)
Just wondering, does it have the capacity to print out a statement for the landlords on one document? ie if the estate agent organises a repair, pays the supplier it will go through the system as going out of the estate agency accounts, but this will obviously need to be charged back to the landlord, ie taken off the rent collected.
Basically, the system would need to automatically generate a statement like this for the landlord once the rent is collected, and repairs paid/commissions deducted.
Nice to see I'm not the only one with this problem!!
We have a property agency as well and it bends my mind trying to make it work properly and cost effectively. I wasn't doing all that confusing reversing journal stuff though, I started off setting up landlords as accounts etc, but now just run it all through a 'client bank account' and the two are meant to cancel each other out (rent in v commission and rent out), it doesn't though and we have resorted to spreadsheets to try and resolve it all and then put bulk totals through the system. My guy used to use word to send out statements to the landlords, but then bought a proper landlords package which is separate to the accounts where he inputs the rents in and out etc. It was surprisingly cheap, but more than £250!! It doesn't help in the slightest with preparing the accounts though!!
Sorry that's not much help, but I'm happy to share my vodka with you....
__________________
Jenny
Responses are my opinion based on the information provided. All information should be thoroughly checked before being relied on.
Thought I'd sussed a work around in VT+, and it still might be possible, with a bit more work.
Working on a dummy company, I created tenant customer accounts, and landlord customer accounts.
Created two services in the setup>invoice menu, 1 for Rents received, and on for commission deducted.
I invoiced tenant as normal and paid into the current bank account.
I then created a new credit note template (I called it landlord template), using the new template, I raised a credit note to the landlords account, entered the rent received in the 1st product line, then Commision deducted in the second but I entered the value as a minus. So far so good, it produces an "invoice" for the correct amount to pay to the landlord (rent less comm). This can then be paid from the current account against the landlords "invoice", in the customer accounts.
Here's the rub No matter what I do with the template, it always says Credit Note at the top, when previewed or printed. I have changed every conceivable text box property, and can't seem to change it. If it can be changed it may be a (work around) solution to your problem. I shall percivier (don't like to be defeated) but it will be over the weekend, as I am a bit busy for next two days. Even if it isn't what you want it will give me some practice at changing VT templates
Bill
If anyone know more about VT templates let me know
-- Edited by Wella on Wednesday 13th of July 2011 06:24:34 PM
Hi Bill, thanks for that, I didn't think of VT, I may still have a copy somewhere that a client had used. I will check this out myself and see, although it is looking increasingly like a bespoke package.
It is a nightmare alright trying to sort, especially when dealing with untrained staff who aren't afforded a lot of time to do the task. The problem with bespoke software designed for the industry is that they tend to throw everything in, website linking, marketing, the works which puts the price out of the league for the smaller agency. Some of the programs I have seen are thousands of pounds per year. All I am looking for is a small package just for dealing with rentals. It wouldn't matter if they were kept separately, the auditor doesn't want the dispursements in the P&L accounts anyway, so if I could find a small rental management package, she could use that just for the rentals, and QB for the rest and I could journal in the commissions income into QB at the end of the VAT quarter.
Just of of interest BudgetB, how does your client handle the VAT on landlord repairs? This may be better suited to another forum, but just thought I would ask you first anyway just in case you have a simple answer.
I would have treated repairs these as simple disbursements as I would have classed them as meeting the HMRC disbursement regulations, but am now a bit concerned after speaking to a colleague who had an issue with disbursements.
Basically, a few years ago he worked with a small not for profit company that ran a volunteer car scheme. They took bookings for people needing to get to hospital and organised a volunteer driver to collect them. the volunteer driver was then paid expenses of 40p per mile, 20p per mile was collected from person travelling and the company paid the volunteer the other 20p which they raised through fundraising. During a VAT inspection, the HMRC insisted that the 40p per mile volunteer payment was not just a volunteer expense to be disbursed, but was a charge that was subject to VAT. I thought this awfully unfair considering that the organisation was set up by cancer survivors primarily to take people to their cancer treatments in a hospital over 60 miles away. These people are usually unemployed or off work on long term sick etc and would be more affected by the price increase but the HMRC insisted that they had to pay VAT, and hit the company with a fine of backdated payments for 3 years!
From what I seen of the disbursement regulations, if you pay a bill (ie a repair on a property) on behalf of another party, and pass on that exact amount, no vat is due. VAT is only due if you charge commission on the figure paid, or an admin fee for providing the service. Up to now, the agency has just been paying the repairs, and charging the exact amount through to the landlord. The pay VAT on their commissions collected from the landlords which is usually a percentage of the rental income. If they had to disburse these repairs and make them subject to VAT as well, it would be quite expensive for a small landlord who may well take their business elsewhere...
I have spoke to 4 other professionals about this, one tax accountant said to charge VAT (although when I questioned him about the disbursement regulations, he seemed quite cagey, and answered that he "was erring on the side of caution in case they didn't meet regulations"), the estate agency auditor sait that they "he couldn't see why they should charge VAT" not a definite answer, one other accountant didn't know and another said he wouldn't charge VAT to see if theiy got off with it on inspection!
I emailed the VAT office for a definite answer, but just got referred back to the HMRC website.
-- Edited by mushroom on Wednesday 13th of July 2011 06:36:24 PM
Hi, They may pay about £250 per year, maybe a bit more at a push, and have about 80 landlords on their books so far, plus a few block managements (apartments complexes)
Just wondering, does it have the capacity to print out a statement for the landlords on one document? ie if the estate agent organises a repair, pays the supplier it will go through the system as going out of the estate agency accounts, but this will obviously need to be charged back to the landlord, ie taken off the rent collected.
Basically, the system would need to automatically generate a statement like this for the landlord once the rent is collected, and repairs paid/commissions deducted.
Thanks
Hi Julie,
At £250 for 80 landlords seperate accounts systems, not a feasible option.
I can't beleive the estate agent wants to hold his records together with a piece of string. Does he cut corners in the services he provides???
I suppose, you can follow Bill's idea of using credit notes and use word to rejig as statements but no way will the admin person (I feel so sorry for her) do this task in one hour per week for 80 landlords.
My client isn't VAT registered. I will find out the name of the rentals software he uses today and let you know, it was certainly more than £250 but it wasn't a small fortune so may be worth looking into. I hope he's paying his admin person a small fortune because she must be one stressed person!
I look forward to seeing what Bill can come up with, could be a solution for all of us!
__________________
Jenny
Responses are my opinion based on the information provided. All information should be thoroughly checked before being relied on.
I don't know how she copes with the pressure, and she isn't getting paid a terrible lot. She may well end up off with stress, and the thing is they will probably pay more in the long run as I'm sure the auditor will have to spend a lot of time correcting errors. He may learn the hard way when he gets handed another large bill. I have already told him giving his staff more time at their hourly rate rather then paying the auditor at his will be better for him but I may as well talk to myself.
Unfortunately I have seen people like this quite often, people outside of our profession generally do not understand how much work is required to keep a good set of books. It takes me back to a not-for-profit company I used to work in where a board of directors started nit-picking about my hours and querying my workload, I ended up being quite badly bullied and overworked in the end. I was ogininally taken on under a fair salary scale, with the directors at that time knowing how to evaluate a job properly, but unfortunately they moved on. The remaining Board members consisted of eldery retired people, and the younger ones were in lower paid jobs like part-time taxi driving, working in shops for mininum wage etc, and disliked the fact that they were my employers but I was earning more than them (they completely ignored the fact that the only reason they were company directors and therefore my employers was that they voluntered to work for nothing, they did not have to sit exams or pass any interviews). They hadn't a clue about finances or personnel issues, or generally any aspect of running a business. Even with the Auditor telling them at the AGM that I kept the best set of books she had ever seen, they kept on until I evenually resigned. They gave the books to a junior member of staff whose only financal experience was to study sage accounts and within 18 months they had to close down due to bad financial management.
I won't even get me started on David Cameron's Big Society - volunteering to 'help' their local communities by running businesses. In my experience of volunteer boards it tends to attract unqualified and inexperienced egos who want to sit on boards, be Directors and pretend they are important. It is a rare enough that you see a good board with the right mix of people long term.
They actually did me a favour in the end though as I probably wouldn't have taken the self-employment step otherwise, I had been working part-time with my business but was too comfortable with a decent weekly wage to take the plunge.
Thanks anyway Dalbir, and BudgetB, If you could pass on that software name, it would be great. I just queried another one this morning and they quoted me £4000 plus VAT, I don't think so!
I'll try the other VAT forum for an answer on dispursements.
No matter what I do with the template, it always says Credit Note at the top, when previewed or printed. I have changed every conceivable text box property, and can't seem to change it. If it can be changed it may be a (work around) solution to your problem. I shall percivier (don't like to be defeated) but it will be over the weekend, as I am a bit busy for next two days. Even if it isn't what you want it will give me some practice at changing VT templates 
