Report of general ledger transactions - format issue

When generate the report of general ledger transactions, found that for some fields which are long, the texts messed up in other fields, the result is that the report, when exported into an excel, became difficult to apply filter to search.

Moreover, for easier searching in excel for an exported report, can the fields like the date, reference number etc be split into separate columns instead of the present presentation? It would be much helpful if a column for “balance” displaying the ending balance of each transaction as well, like most of the accounting software does.

Although we can easy search the field on the web based report, we need to export the ledgers to excel to auditor, so an improved version of the exported excel general ledger would be much appreciated.

Why is a lengthy field difficult to search? The search function in the spreadsheet works the same way on lengthy fields as on short ones. The search string is either found or it is not.

If you want that type of display and are fond of exporting, drill down on account balances from the Summary page and export those results.

This seems like a step backwards in time and capability. Why not just print the report? Why give the auditor a file that can be edited? Mistakes can be introduced that way.

Why not give your auditor a copy of your business file and get him/her to download the desktop version of Manager and examine away to his/her heart’s content?

Maybe the auditor doesn’t have the time or motivation to learn another program. :confused:

This is totally outdated and not practical. Auditors don’t work on paper nowadays (At leats I don’t!). An excel file is easier to manage and analyse. :slight_smile:

I suppose that depends on who the auditor works for—the business or the tax authority—and where they work.

How does the auditor know that the Excel file is a true representation of the accounts and not a dodgy version?

The lengthy field is in fact can be searched as other shorter field. The problem is that if the fields are lengthy, some parts will be split and messed into other columns, see below highlighted in yellow. If you filter the field of “Debit”, some non-numerical text will appear which is in fact belongs to the field of “General Ledger Transactions”.

We can export the transactions by using the Summary sheet and click on the account balances then copy the transactions, but the drawback is that we need to click and copy the transactions of each balance one by one but cannot export the ledger in one go.

As for why we prefer pass an exported excel to auditor is that we do not prefer to export the entire manager file to them as there is lots of business data, and having an excel with easy to filter the account names can facilitate view of the transactions during the year.

@Hugo, I think what you are seeing has nothing to do with Manager. I can export the General Ledger Transactions report with much longer descriptions with no trouble.

When you export a report from Manager, the result is a tab-separated file (TSV). I think you have something in the content of your descriptions that is being interpreted by your spreadsheet as a cell separator. To confirm this, do two things:

  1. Post a screen shot of the Edit screen for your inventory item Cooling Gel 100ml. Show all information.
  2. Rather than exporting the report, use the Copy to Clipboard button. Copy the report and paste it into the spreadsheet. You will likely need to adjust the width of the first column, but the result will be much better formatted. See if the problem persists.

See screen shot posted below for inventory item and invoice, and I found that the root of the problem is that in the sales invoice, there is 3 lines for the product description, and I press “enter” to split into 3 times. So the excel recognise this as separator and present the excel file as messed columns. If this is the case, is there any way to solve?

As for the suggestion of copy to clipboard, it works and the result is better formatted, although the account name is not shown as a separate column like the one generated using export function, so it is more difficult to search.

%5D5FP%7BE%60KJ1D6A%7DL%7D~%244DM4C

Yes. Separate lines with the HTML <br> tag. See the Guide: Use HTML code in fields | Manager. For example:

Cooling Gel 100ml <br> Batch: 17463 <br> Expiration date: 01/11/2020

will appear as:

Cooling Gel 100ml
Batch: 17463
Expiration date: 01/11/2020

2 Likes

Tried and work now. Thanks! :slightly_smiling_face:

@Joe91:

Without intention to divert into a different topic, auditors MUST by practice requirement perform tests on the quality and integrity of every piece of information provided by client such a reconciliation with affected balances of related accounts. These test must be done either the document is provided on paper, pdf, or excel spreadsheet. In case, for most of us is easier to do those tests and use the information if it is in a spreadsheet format.

I think @Joe91’s point was that a separate spreadsheet would, in many locations, be rejected by an auditor. It is not related to the actual accounting system in use and does not come from an independent source (such as a supplier’s invoice). It can easily be constructed to include whatever numbers someone wants to put into it. Therefore, many auditors would consider it useless for tracing and verifying an actual transaction.

That is also what I was referring to earlier when I mentioned that whether an auditor worked on paper depended on who they are working for. It is one thing for an internal auditor to confirm that accounting results are as expected. It would be something else for a government auditor to approve the results of your accounting without seeing actual source material and corresponding records directly from your accounting program.

Based on this assumption than how does an auditor know that the numbers that I put real inside Manager are real?

The auditor must check that all the accounting principles are respected and that there are no anomalies… he is not a tax inspector.

Apart from this all the notebooks of the big fours are locked and crypted so the auditors cannot install anything. They want the documents in pdf and excel sent through TLS certified email.

That is why an auditor might want the original source documentation for confirmation, not a spreadsheet you constructed yourself.

That might not be true. The term auditor can mean different things in different contexts. A business might have internal auditors that check the work of accounting clerks. At the end of the year, the business might hire external auditors to certify financial statements for release to the public, the government, and investors, according to local regulations. A tax authority might send an auditor to examine the accounts of a company for compliance with tax law.

1 Like

Original documents are invoices, contacts and bank statements not your registrations.

Yes, @Davide, I agree about what original documents are. But I am completely lost as to what you are referring to when you mention “registrations.” I don’t know what you are referring to, and I never mentioned registrations.

As far as I understood, this discussion was about whether to give auditors spreadsheets you constructed yourself (which could be faked or erroneous) or a data trail that included original source documents and corresponding data and reports directly from the accounting system you are actually using.

For registrations I mean what you insert inside Manager. The objective of auditing by law is to declare procedural conformity and the truthfulness of the data resulting from the records.

Indeed it depends on what the auditor is commissioned to audit.
If supplied with the Manager data file they can audit the accounting system, the assumptions it implies and spot check it’s consistency with other primary data such as declared tax, bank statements, business costs & sales.
If supplied with a manually generated report the auditors can check the manual report for internal consistency and again spot check with other primary data sources.

The later approach significantly limits the scope of the audit as the data the auditor sees is consciously edited / limited by the party being audited. Whether it still meets the audit obligations would vary depending on what the requirements are.

1 Like