Creation of Custom Reports as provided for in the Reports Menu

The layout of the Profit and loss Statement that you have does not allow for Extraordinary Profits or Abnormal Income. But I have devised a new sequence in teh Chart of Accounts so that these sources of income are printed later after ORDINARY OPERATIONS. the problem is in the aggregation or subtotal from these income sources. as there is no way of report ing that.

The addition of the TRACKING CODE to your standard P&L report has resolved one huge problem I had in writing an event-specific report - why not add the Tracking Code function to ALL your reports to enable custom reports without SQL query knowledge - which doesn’t seem to work anyway, unless it’s just that I am unable to determine your Table & Column Names.

I would also like to see a method to alter the layout of your reports. Can you suggest a third-party software package that will do precisely that for your customised version of SQLite? I have tried several open source SQL query programs, which successfully open your .manager format files, but so far without success in interrogating them. PLEASE HELP WITH THIS VITAL FUNCTION!

PS: I am the Treasurer of & use your program for a charity’s accounts, & I love it - apart from the above, & the awful way it automatically allocates available funds to any outstanding invoices, even if they’re not paid yet - please make this an option to be disabled, as it was before!!!

Thank you for all your hard work. Happy New Year.

1 Like

This should not be a problem. Funds are allocated only when received/paid against the customer’s/supplier’s subaccount in Accounts receivable/payable. If the funds were designated for a specific invoice, that should have been selected when the transaction was entered. If not, why are the funds being received/paid? If they were not for an invoice, they should have been posted to some other account. If they were, money is fungible, so what difference does it make? If the funds are for some special purpose other than an invoice, the transaction should be posted to an advance payment/deposit account, either asset or liability, depending on which way the transaction is flowing.

When you enter the Receive Money you have two options (1) allocate to specific Invoices or (2) don’t allocate to specific invoices (oldest gets paid first, then next oldest etc). The following example demonstrates both

Firstly, please do not assume we are all brainless & do not know how to use your software.

You have obviously never had experience of Incomplete Accounts - this is where the client has provided little or no information, or disparate information going back years, where accounts have to be collated from various sources of information & data inputted correctly only when it comes to light, verifying its validity against what is known & applying it accordingly. Hence, aged invoices are inputted & payment applied WHEN the information confirming it is found.

Another scenario your software does not cater for is Payment on Account, where partial payment is applied to an invoice or invoices, which can then be reconciled against bank statements. Your automatic allocation of funds is a complete nonsense & is wholly unnecessary - when I allocate funds to an AR or AP invoice, that is a true reflection of the actual movement of money on any given date, which is what any accounting package should do - show where the money ACTUALLY is, not where you think it should be - ask any Accountant!

I am surprised that you have concentrated on this matter alone as it was a secondary issue in my post. You have not addressed the primary matter of your dire reporting functions. I was requesting that the Tracking Code be applied to all of your reports to make them more configurable (which worked well on a P&L Report), & asking if you can suggest a third-party solution to this.

As I said, I can access your modified SQLite database with various reporting programs, but not interrogate it to ascertain the precise Table & Column Names - but I strongly suspect that even if I were to guess these correctly, your Custom Report function would still not work!

I feel this is a matter that should be addressed urgently as people have been complaining about it for quite some time, as I know you are aware, & it is a vital accounting function to be able to correctly report financial affairs for any given period in any particular circumstance.

Although I have reservations about these two issues, I would add that your software is otherwise excellent & very pleasant to use. Addressing the reporting function & adding the ability to prevent automatic allocation of funds would make it perfect!

2 Likes

Dear Sir

You have absolutely no knowledge of my experience so I would strongly suggest that you should desist from casting dispersions on what you don’t know about other forum contributors.

My response above was based on that part of your post where I could assist based on your expressed concerns. The other parts of your post (tracking codes) requires a response from the programme developers and that is not I.

Yes it does, in fact you have the total control over both Accounts Receivables & Account Payables payment allocations. You can build up any fragmented documentation and match specific invoices & payments (and reconcile bank statements) or as you state “show where the money ACTUALLY is”.

In the instance that a piece of payment data is recorded prior to knowing the correct invoice allocation, then that payment is initially posted to an “Unallocated Payments” account. This is the exact same process used by forensic accounting teams when doing fraud and missing millions type of investigations and hence the elimination of automatic allocations.

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?

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.

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.

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.

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.

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.

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

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

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.

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

1 Like

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.

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.

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

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

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.

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

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.

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 ???

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.

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