Creation of Custom Reports as provided for in the Reports Menu

[Tizwoz]

Firstly, you are right that I too made an assumption, & I apologise for my misconception of your position. By bad!

I cannot see how else to record a Payment on Account other than in Spend Money from the invoice in question & input the actual amount paid against that invoice. Having done that on several occasions, on returning to the supplier account, unallocated sums have been erroneously allocated. Perhaps I am missing something? I know that automatic allocation used to be optional & I wish it were again, & I know I am not alone in this.

On the question of reporting - my Board of Trustees has requested I provide a Transaction Report on a specific event’s income & expenses which occurred during the year. I was able to provide an accurate P&L Statement using the Tracking Code, but I cannot see any way to isolate that specific event from any other income & expenses for a Transaction Report. I would be grateful for any ideas since the Custom Report does not function.

I do hope that the developers address the issue of reporting soon, but can you help in the meantime?

[Brucanna]

@Tizwoz

I cannot see how else to record a Payment on Account other than in Spend Money from the invoice in question & input the actual amount paid against that invoice.

If you are directly matching up an invoice and payment then “Spend Money from the Purchase Invoice in question” is one option. The other option is from the Cash Accounts tab, select - Spend Money - “bank” and for the Account line enter Accounts Payable + Supplier + Invoice.

@Tizwoz

Having done that on several occasions, on returning to the supplier account, unallocated sums have been erroneously allocated

For this to occur, means that you have entered a Spend Money transaction without any allocation to a specific Invoice. As mentioned above, for such payment data to be recorded prior to knowing the correct invoice allocation, then the payment is initially posted to an “Unallocated Payments” account, located under BS - Liabilities.

For example, if you have a payment for 500 but only 300 can be allocated to a specific invoice then the balancing 200 is posted to Unallocated Payments. This way the Supplier account remains in sync and the 500 is available for bank reconciliation purposes.

Once determined, the Unallocated Payment can either be re-assigned or transferred via Journal.

In my “experience” the simplicity approach for such accounting re-builds is to request from the Suppliers a transaction statement and then process that as your source for the Purchase Invoice documentation. Also, the statement “could” advise how payments received were allocated. Then marry up the clients scarps to the statements rather then use the scraps to create the accounts themselves.

@Tizwoz

I know that automatic allocation used to be optional

and it still is, nothing has changed. The location where the overpayments were stored has been modified but the underlining processing of the transaction remains unaltered.

[Tizwoz]

I must be missing something as I didn’t have an “Unallocated Payments” option, so I’ve created it in Special Accounts. I still feel my problem is not being addressed (unless I am misunderstanding your responses).

As I am working on three years’ legacy & incomplete accounts, I am inputting data as I come across it, including payments on account of various supplier invoices throughout the three years, assigning them to the right invoice(s). Of course, as I get through the data & come to the end, all will become clear as to when full payment of each invoice has actually been made, but I do not want automatic assignment of funds to invoices not yet paid in full until I find the actual payment relating to that invoice - & I cannot see an option to turn this function off, which I understand once did exist.

I’ve never known Payments on Account to be so allocated to partially unpaid invoices in other accounting software, rather than sitting on the supplier’s account as a PoA, & I found it frustrating. However, having now created an “Unallocated Payments” account, I will try that option for the unallocated portion in the interim, & reassign as data comes to light.

As to the reporting function, I am delighted with the new Tracking Code option, & am using that enthusiastically to create custom reports - however, this currently only applies to the P&L reports. Could this function be included with the other reports - for example, Transactions? This would allow customised Transaction reports to become a piece of cake. It would also be great if more than one Tracking Code could be used in each report - & that would negate your Customised Report function having to be used, so that all those having problems with it would most likely no longer need to use it. I’ve never got it to work as I cannot determine the precise Column Headers!

Also on my wish list would to be able to reformat your reports in terms of layout - & I appreciate that may be way down the line, but if multiple Tracking Codes could be implemented in ALL your reports, I would be a very happy bunny indeed!

Happy New Year to all at Manager.io.

[Tut]

@Tizwoz

I didn’t have an “Unallocated Payments” option, so I’ve created it in Special Accounts.

This is not an option. That was a reference made by @Brucanna in his post of 8 days ago to an account you would create. There is, by the way, no reason to use Special Accounts for that. It can be an ordinary liability account.

@Tizwoz

I cannot see an option to turn this function [automatic payments] off, which I understand once did exist.

This option never existed. The structure of how it worked was once different and somewhat more complex, but it has never been optional.

@Tizwoz

I am delighted with the new Tracking Code option

This is not new, but has been in the program for at least three years.

@Tizwoz

if multiple Tracking Codes could be implemented in ALL your reports,

This isn’t going to happen, and couldn’t happen in a number of the reports because it would make no sense. What you have been writing about from the beginning will be handled when job costing comes on line. That is second on the Roadmap. Tracking codes were never meant for project accounting, but for handling different company divisions. They’ve only been a clumsy workaround, and no effort is going to be put into enhancing capabilities of a workaround when a purpose-designed module is being worked on.

[Tizwoz]

Job Costing sounds to be exactly what I need, so I eagerly await implementation. Thank you.

[Brucanna]

With the “Unallocated Payments” account you can use an ordinary liabilities account where you can put all the unallocated portion of payments until they find a rightful home, but depending on the volume you have of these unallocated portions that account could be come messy unless you keep it well reconciled.

Alternatively, you could manage these unallocated portions on a per supplier basis by making the Unallocated Payments account a Custom Control account and then create under Special Accounts an account for each Supplier, then their unallocated portions could be grouped separately from other Suppliers.

Step 1 - Create Unallocated Payments account as a Custom Control account

Step 2 - Activate the Special Accounts tab and then create an account for each Supplier at that tab

Step 3 - Post unallocated portions to the respective Suppliers Unallocated Payments Special Accounts

Then in the end when all unallocated portions for a Supplier have been re-assigned, you can delete the respective Supplier Special Account, so that only Suppliers with unallocated portions remain listed.

[MicheleA]

Tizwoz, I have the same problem with the programme allocating customer receipts to various departments in proportion to the value of the respective services invoiced from each department. That is not how the receipt is always to be allocated. Just as you say, it should be FIRST invoice FIRST. but that doesn’t happen.
I have the same sort of discussion in another Forum - Job-Wide Profit & Loss.
The responses I have received from the developers mirror your concern about the ‘nonsense’ results produced in the Reports. Being a CPA myself and trying to do non for profit reporting using this nice software, the cash flows reported in the P&L have little reflection on the actual flow that I would manually assign to the respective departments.
I hope that the developers DO listen to us and assign WHOLE dollars to First invoiced item, then fill up the Invoice, onto the next item etc. FIFO inventory system for service entities.

What have you done to overcome that automation?

And are you frustrated that old invoices that ARE fully paid still appear as available for assignment of later receipts? That is a mistake - old paid invoices for a particular customer should be closed off by the programme for any future receipts.

[Tut]

@MicheleA

automation

As you have been told, you can control that by specifying invoice numbers.

[Tut]

@MicheleA

developers

None of the responses you have received to this or other topics is from the developer.

[Brucanna]

@MicheleA

Just as you say, it should be FIRST invoice FIRST.

And that is exactly what happens, its just that you are putting two services on to the ONE invoice and then refer to those services as though they are separate invoices. As explained elsewhere, call it services/inventory or tiddly winks - you can’t apportion a Receive Money to parts of a single invoice.

[MicheleA]

Hi Brucanna.

That fact is worth knowing for me.
Yesterday I even went back over 6mths of invoices and receipts, assigning tracking code to individual receipts. But, although i assumed that the tracking code would force that receipt to Dept A, it didnt!! Disappointing. Now i have receipts that are allocated by the program rather than as the customer intends.
The problem arises only when a customer has an opening credit balance and there is no way to assign a department tracking code to that. Then the customer pays for the mext invoice in full but the credit mucks up the subsequent receipt allocation.

Is there anyway that an opening credit balance can be assigned to a department?

Micehele

Exclusively Strata

[Brucanna]

@MicheleA

assigning tracking code to individual receipts.

I don’t know how many different ways it can be explained to you - you can’t assign tracking codes to Accounts Receivable payments (receipts). The tracking code field on the Receive Money screen is for Cash Sales - Income processing transactions, not for settlement payments of Sales Invoice transactions.

@MicheleA

i assumed that the tracking code would force that receipt to Dept A,

The tracking code for the income transaction was assigned by you on the Sales Invoice, you can’t re-assign a tracking code when the payment (receipt) for that Sales Invoice arrives as that creates a conflict.

EG: The Sales Invoice has an Item assigned to tracking code A. The payment (receipt) arrives a month later for that Sales invoice and during the Receive Money is assigned to tracking code B - where does the income get posted to ??? Does the Sales Invoice or the Receive Money control the tracking code ???

@MicheleA

Is there anyway that an opening credit balance can be assigned to a department?

NO, an opening credit balance is a Balance Sheet item and has absolutely nothing to do with income, tracking codes, P&L or cash basis accounting.

As advised previously, your basic solution is to issue two invoices with separated receipting and if you can’t issue two invoices then you can’t not have apportioned receipting’s.

It’s noted in your earlier post that you are a CPA, therefore you more then most Manager users would have a much greater understanding of the fact that Sales Invoice payments (receipts) are purely a Balance Sheet transaction - a transfer between Accounts Receivable and Bank/Cash accounts and as such any attempt to link such a pure Balance Sheet transaction to some form of P&L reporting seems incomprehensible.

Furthermore, being a CPA, you should also have a clear understanding that cash basis accounting can only report to the P&L what the source income and expense transaction documentation has determined after they have received a complimentary cash transaction. The cash transaction itself can’t impose if its income or expenses, only the source documentation can do that.

However, if I am wrong, could you please quote, via a return post, the applicable accounting standard or other recognised accounting authority supporting your position as I would like to add them to the reading list for when I next lecture accounting honour degree students.

[MicheleA]

Hi Brucanna,
Yes, I fully understand the link between Accounts Receivable in Bal
Sheet and Income in P&L.My entities are not for profit so the notion
of P&L never makes sense and with any accounting software package,
there is no relevance of ‘Income’ or ‘Profit’ per se. I have added
to the Chart of Accounts P&L so that the description is something
like; Net Surplus or Deficit for period. I would love to be able
to design CUSTOM REPORTS, have BUDGETED figures for each proposed
expense item - my woes would be few.The tracking code system is a good
tool for reporting ‘income/receipts’ and ‘expenses/payments’ in two
columns. This I really like.
However, the irony of your Cash Summary Report is that the INFLOWS ARE
INCORRECT AND MISLEADING as they are really the RECEIVABLE. Only
the P&L Stmt presented in CASH format reveals the actual cash that the
programme has allocated from each receipt. For the accrual to appear
here, readers will assume full receipt of those monies which is wrong.
Also, the Cash Summary does not allow a comparative column by
Departments using Tracking Codes like the P&L actually does. Don’t you
find that anomoly worth addressing Brucanna?? That is why I am
forced to use the P&L Stmt for reporting Receipts and Payments
Again, thanks for this interchange of ideas.I hope that you can add
BUDGETS and modify the Cash Summary report so that it presents
comparative Columns by Tracking Code (which you say is for cash
sales).
have a great dayMichele