Items or services invoiced in 2010, but payed in 2011

[roelofwielinga]

Hi masters of code...
I am working on my tax-administration and for my results of 2010 I need an overview of:

Services invoiced in 2010, that are going to be payed in 2011.

So basically I have to go back in time (31.dec 2010) and print out a list of open- unpaid invoices.

What do I have to do

[Support]

>> I have to go back in time (31.dec 2010) and print out a list of open- unpaid invoices.

I'm not too sure I understand what you mean, but you can print the "Invoice List" report for invoices dated between 1/1/2011 and 31/12/2011 and that will show you all invoices raised in that period and whether they are paid or unpaid.

However, the invoice balances on this report will include all up-to-date data including payments made more recently. It is not possible for ClinicOffice to "go back in time" and print a report based on a dataset which no longer exists. Reports can only be based on the data that is currently in your database now - not how it was at a certain point in the past. Ideally, if you needed to know what invoices were still outstanding at a certain point in the past (i.e. at the end of 2011) then you needed to print the report at that time.

Alternatively, you could restore a backup of your database which was taken at the start January to a temporary location and then print out the report from that dataset.

Hope this helps!

[roelofwielinga]

Would I be able to manage it like this:

Print report of all the invoices in 2010
but "exclude" all the bills that have been payed in 2010 ?

So these results will show all the bills that have been written in 2010, but payed (or not jet payed) in 2011.

Right

[Support]

>> Print report of all the invoices in 2010
>> but "exclude" all the bills that have been payed in 2010 ?

It's the same issue - how do you know when an invoice was paid? For example :-
- An invoice for £100 is raised in November 2010
- A payment came in for £50 in December 2010 and was applied to that invoice
- A second payment came in for £200 in January 2011 and part of it was applied to that invoice to pay it off fully

When was the invoice paid? 2010 or 2011? The answer is both. I'm afraid that what you're asking for is far from simple. If you need to know the exact standing of your accounts at a certain point in time, then you either need to print off a report AT THAT TIME, or you will need to revert back to a backup of the database from that time to generate the report.

[roelofwielinga]

Hi,
Thanks for your suggestions. After I have reverted the backup of my database of 31-12-2010, I listed the outstanding invoices. But then I realized that this is applicable only when I have updated the payments in the database right before my backup. It could have been that I have marked the payments in my database as "payed", on lets say the 15th of january (because that was the day that I checked my bank-account).

Forgive my ignorance but,

I assume our tax-institute is interested in my real turnover in 2010. They don't want to know how many invoices I have written. It is not important to see if a bill is completely payed, or not. But more "how many money is in the cloud" at the end of the year.

Reports can only be based on the data that is currently in your database now - not how it was at a certain point in the past.

Don't all the payment have a time-stamp to it? One could lookup the time-stamp of these payments and see what payments came in at 2010 and minus this from the result of the invoices written in 2010. The result is the money that has not been payed jet.

Or am I wrong?

[edit: After fooling around a little bit, it seems that I can substract the grid report 'payment list (over 2010)' from the grid report 'invoices list (over 2010)'

Or does somebody else have an enlightening thought?

[Support]

>> I assume our tax-institute is interested in my real turnover in 2010
I'm afraid I don't know how your taxation system works in The Netherlands, but "turnover" can usually be calculated from the INVOICE LIST report or the PAYMENT LIST report depending on whether you're using ACCRUAL based accounting or CASH based accounting respectively.

>> Don't all the payment have a time-stamp to it?
>> One could lookup the time-stamp of these payments and see what payments
>> came in at 2010 and minus this from the result of the invoices written in 2010.
So what if a single payment for £100 comes in January 2012. £50 is for a previous treatment in December 2011 and the other £50 is for a treatment that was just done (in January 2012). Where does that £100 appear? On 2011 or 2012?

I think it might just be that you're over-complicating things. If you use ACCRUAL (i.e. invoice-based) accounting, then simply use the Invoice List report. If you use CASH (i.e. payment-based) accounting, then use the Payment List report.

More information can be found here :-
http://en.wikipedia.org/wiki/Comparison ... accounting

[jospore]

You can try a report template I recently got from the support team.

I am able to set the date for my financial year and get a summary for all patients, this means i can see which patients are in the negative ie owe the clinic money and which patients are in the positive ie patients with credits.

In addition I asked to have the patient code added.

I tried attaching this file but it doesnt allow me, perhaps support team can forward it to you

below is the copy of the email i got from them

Thanks for the email. Due to the nature of the Account Balances (open) it can only show the latest Account Balance as it pulls in the patient's entire balance rather than the individual invoices, payments, credit notes and refunds and then calculating those in the report.

There is however a report which may display the information you require. This is attached to this email. In order to import this into ClinicOffice please follow the steps below:

Copy the attached report off this email to your DESKTOP

Open ClinicOffice

Go to the Reports and Templates section

Click the Import button

Locate the report file (on your DESKTOP)

Click Open


When you run the report you will be able to search on the date. After performing your search you will note how the grid is being grouped by the patient. If you wish to see an overview of this information without the breakdown all you need to do is right click on the column header "Account" (this is above Date/Time) and select "Full Collapse". You will then have an overview of each of the patient's balance at the time you specified.

Please let us know if you have an question or problems with this report.

Jose

[Support]

Hi Jose - if you ZIP the file then you should be able to post it to the forum.

[jospore]

here you go, hope everyone interested can open this zip file
jose

[roelofwielinga]

Hi
I am getting 2 files out of this .zip-file:

layout0.record
report.record

Those extensions don't want to import in COv4. Even when I change .record into .COv4_report it still doesn't work.
Am I doing something wrong?

[Support]

The file is attached correctly and we're able to import it without a problem.

Please make sure that you simply right click on the link and click SAVE AS and then save it to your computer somewhere (e.g. your desktop).

This should save it as a ZIP file. Then you need to double-click on it and extract the "Patient Finances [Financial](1).COv4_Report" file - we recommend saving that to your desktop too. You can now import that file into ClinicOffice.

(NOTE: if you try to open the COv4_Report and extract it AGAIN then you will see the "layout0.record" and "report.record" files - perhaps that is what you're doing wrong?)

[roelofwielinga]

Hi again,

Of course I knew that there was going to be a time that I had to do this all once again. And that time is now .

Maybe my previous questions were unclear since I was talking about invoices.

My accountant likes to receive an overview of the gainings in 2011, that are going to be payed in 2012 (or have already been payed by now).

Could you please look into this matter again? Many thanks!

[William]

Hi Roelof

Does the report from jospore cover this as it does calculate the total balance that a patient has? If it does not, what needs to be altered on it in order for it to work?

[roelofwielinga]

Hi William,

Thanks for your answer! I've been looking into this report again, but it gives an unrealistic result. The sql-data is very long and too difficult for me to understand.

Maybe you can help me giving your thoughts in this humble idea:

If I would be able to enter a date into the "Account Balances (open)" report, it would show me the open account balances on that specified date?

I am very sorry that I keep bugging you with this matter

[mesologie]

I think I understand what Roelof wants and I think it's usefull. I have analyzed the SQL from the report of jospore, because that is a good start.
It would help if you can alter the report in such a way that you can select different dates for Invoices and Payments (personally I don't need the Refunds and Credits). So I think that if you can select all invoices of last year and payments of this year, the question of Roeland is answered (see my homemade screen print ).

Finance.jpg (17.12 KiB) Viewed 4287 times