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.
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:
Post a screen shot of the Edit screen for your inventory item Cooling Gel 100ml. Show all information.
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.
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.
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.
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.
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.