We are a function band: I’m using Quickfile to manage deposits that clients pay us in advance of their event, often in a years time. That works reasonably well, but I have no idea what our net worth is on any day.
I want to be able to set up a report that takes the current bank balance (1200) and deducts the advanced deposits (sales) that we’ve received for future gigs. [This calculation would assume that past gigs are settled in full and there are no creditors/debtors.
At the moment, I send out an invoice and client pays a deposit. I then use ‘log payment’ to allocate it to their invoice, which is recorded as ‘PAID IN PART’.
In a custom report, what would be the formula I need to extract the total of deposits paid in advance? I’m assuming the formula would be something like this:
Spare cash in bank = 1200 - total of cash deposits payed in advance
Do you use a separate nominal code for deposits, or just use ‘General Sales’ for everything?
General sales for everything at the moment
The thought was if you had a separate nominal code for the deposits, you could do a simply calculation using custom reports. However, I’ve discussed this with a colleague to look at a few possibilities, and the issue with that is it would take the value of the invoice rather than what’s been paid - if that makes sense?
The other issue with my thinking is once it’s all been paid off you would need to change the invoice for the nominals to be correct.
My colleague has suggested a holding account, which would mean your bank account wouldn’t necessarily reconcile, but it would be an easier way to manage this, effectively holding the deposits in a virtual bank account until it’s paid.
You would have something like this:
Invoice for the full value to 4000 General Sales, let’s say for £1,000 for simplicity
25% deposit paid, so £250 logged as money in on the deposit holding bank account, tagged to the invoice.
Remaining 75% paid to current account, tagged to invoice
And finally the deposit tagged as a transfer from the deposit holding account to the current account
That would certainly track the deposits and allow you do a calculation on them in an easier way, but it would require a little bit of time to keep this in order.
I think setting up a holding account would create too much work for me and just confuse the process of monitoring client accounts.
Surely, there must be a way that I can query the raw data to find out what income has been received between two dates?
For example, I use the ‘Purchase Reference’ on the client invoice to indicate the date of the gig, so Purchase Ref No: 170701 indicates their event is on 1st July 2017. If I "select all numbers greater than TODAY’s date (eg greater than 160610 from the purchase reference field then I’d pick up all the total deposits paid to date.
Can I do a query on the data?
Unfortunately you’re not able to query the data in that way.
The only other way it could be done would be to download a CSV by using the account back up tool, which would give you the reference, the amount paid and the amount outstanding. You could use filter tools in software such as Excel:
You can find out more about back ups here
You can also get this on individual invoices if you wish, by adding the reference column:
Then tick the invoices you wish to check (or select the ‘all’ box at the top), and go to ‘More Options’:
Then select ‘Export data’:
This will download upto 50 invoices as shown on the page, but the back up option will include all invoices
Isnt this called deferrals (opposite of accruals) where the date of payment is before the date of the contract to be invoiced and stands in a separate accounting period? Quickfile has a nominal ac for accruals but not for deferrals but I guess you could make one if you find out the appropriate code to place it in. Then you would assign your reciepts for the gigs in advance to the deferrals account and when the contract has been completed (youve played your gig) you would move the money to the right place as though you had just received it. This is quite easy and simple. Similar idea to a holding account but with a caveat of being relevant to the next accounting period yet to occur. Then you could view your profit/loss and balance sheet for specifically defined periods and it would show your balances for that given time?
Thanks for your suggestions @QFMathew @petalpatchcare
It was simple enough to download the data and then add formulae to calculate the net cash in bank account, so I’ll use that method, thanks. I’m not an accountant, and adding holding accounts would confuse me and create more work.
Presumably, one day, I could use Zapier to read the data and make changes in my Google spreadsheet?
We currently have 2 actions and 3 triggers configured on Zapier.
Zapier is great for triggering events in real time when something happens in QuickFile. Or conversely if you want to make something happen in QuickFile when some event occurs on another one of your apps.
If I understand this correctly what you’re trying to do is more of a reporting problem related to isolating those part-paid invoices and knocking it of your bank balance, I’m not sure Zapier will be a massive help.
Normally in accounting you’d use a balance sheet to determine a company’s NET worth, in your case I think it’s the cash position you’re seeking. Without some how tagging those deposits, I’m not sure how else they could be isolated.
Another option you’ve got is assigning a project tag to all part paid sales invoices and using the NET paid on the project overview screen as a possible way to get at that figure. Either way I don’t think there will be a unified report and it will likely involve comparing two separate reports, e.g. your bank balance and the project balance.