Output Electronic Tax Records Files (ETRF) in Navision / Business Central

Estimated reading time: 36 minutes

Unfortunately, Microsoft has not yet (2024) delivered a ready-to-use ETRF (Plectronic Dax Pecords File) output. But it's not rocket science! And yet many system houses are hopelessly out of their depth. Perhaps also due to a lack of accounting expertise. The ETRF setup itself is the same for all Navision versions from 3.70 onwards, and configurable data export is available from this version onwards. However, the Rapidstart module, which can be used to export and import any tables from Navision, is only available from Navision 2013 / Business Central. Note: In Navision from about version 2018, the RapidStart is called "Quickstart". With this tool, you can download my ready-made definition file from this page here and output your first GDPdU file from Navision Financials / Dynamics Attain or Microsoft Business Central BC365 in less than 4 minutes. Whereby the 4 minutes apply depending on the version 🙂 Either way, I will get you from zero to ETRF-capable in one day if your Navision / BC365 fulfills the basic requirements.

You will also find a complete set-up guide here, including information on how "secure" this data is with your tax offices...…

For Navision versions between 3.60, 3.70 (I think that's when the GdPDU export was available for the first time) and 2009R2 (until then there was no configurable data export for Navision) you have to do this setup manually or order it from me. But this is usually done in less than 2 hours, and is a one-time process.

Required files

The official instruction issued by tax offices as a (poorly) scanned PDF for creating GDPdU files from Navision. Functionally still valid for all versions from Navision 3.70, 2009 (r2), Business Central. With this document you can also do this setup yourself for a Classic Client Navision or for the earlier used TaxAudit software Transdata. Transdata was used for Navision-versions 1.30, 2.01 etc. to output the necessary files from there. Of course, a separate report could still be created for these versions, which simply generates the finished XML files without any setup. Please contact me if needed.

The ETRF data record definition file (ETRF.dtd) for importing into the data export of Navision or Business Central. Please add the identifier dtd (Document Type Definition) to the file name after the download.

The package definition for the RapidStart module of Business Central and Navision BEFORE Nav Dynamics 14!

In Nav 14, the data export has been partially changed, so you need this package there. This should also work in later Navision versions (Dynamics) up to about version 2021.

To be on the safe side, here is another version in between, for Navision 2015 and thus possibly also for subsequent versions.

Import of the Rapidstart module

1. Please import the RapidStart file into your configuration packages:
   

2. accept the packet. the packet contains the necessary definitions for RapidStart itself, and also the user data for the data export.

Now switch to the Navision data exports (also referred to as "GdPDU definitions" in some Navision/BC 365 versions)

You will now find 2 exports here:

The dtd file must still be read into both.

Then enter the desired export path, which might be created by Navision during this process if it does not already exist.

Output of GDPdU data for the tax office

Select desired output (Minimal or by FA default with a lot of detail data), then go to Data Set Definitions. Please notice the two different "Export", just to be on the safe side 🙂

Enter the desired period, And OK (CTRL+Enter)

Tip: If you need to output multiple clients / years, then rename the resulting folder right away, e.g. to Client-2017.
Tip: If Navision aborts the export with the error message that the file is too large, split the export, e.g. from 1.1.2017-30.6.2017 and 1.7.2017 to 31.12.2017. There is also a workaround from me with which this error no longer occurs, but this is usually not worthwhile, as this activity is performed too rarely.

There is also a more detailed tutorial on how to output GdPDU files under Navision/Business Central 365 here

Please also note that you must also provide data older than 10 years on request, if you have it!Besides DSGVO, one of the most important reasons for regular database cleansing..

Do you have an IDEA about what the IRS/CRA does with your ETRF data?

Basics

I know from experience with my customers that they are usually satisfied with the output (export) of the ETRF data in XML format for the tax office. Previously burned on CD, now sent to the auditor via USB stick or even email... out of sight, out of mind. And nobody actually thinks about what happens to this - very valuable! - Navision Financials, Dynamics, Attain or Microsoft Business Central BC365 data there (and hopefully only there!) in the tax office or at the auditor's office - or from any other accounting system such as SAP, Sage, etc. - or from any other accounting system.
First of all, the data you are disclosing here is highly sensitive! All customers (debtors), all suppliers (creditors), and, depending on the configuration, your entire article master... your competitors would probably be willing to pay money for this too. Currently, this data is read / transferred to mid-range / mainframe systems in the tax offices directly after arrival there (but an email or USB stick can be "lost" or forwarded / copied beforehand...). They are still reasonably secure there today... until a Russian hacker group gets hold of them. They are no geniuses either, but the data that can be accessed there is worth millions! And if the mainframe is not accessible: the mail server might be...
The evaluation of the exported ETRF files itself is done with the IDEA software on conventional Windows computers. We know Windows from the news headlines...

Audits back then...

Do you remember audits in the "last century"? The accountant or auditor had ancient, dusty ToBa lists (totals and balances lists) delivered from the farthest corner of the darkest cellar. He would flick through them, bored, and then point to an entry here, an entry there. In addition to fresh coffee, he also demanded the original receipts for the entries shown in their normal bookkeeping. These were usually purchase receipts, and mostly expense receipts, because this is where most of the "errors" (doesn't always have to be fraud) were to be found. Add to this 1-2 logbooks and his success rate was high enough. Experienced and good auditors very often pointed to exactly the receipts that were really problematic. And this is precisely where the problem lies with the tax authorities, as is the case practically everywhere in the economy:
1. There are too few good and experienced auditors
2. the ones who are really good and put their fingers on the problematic documents quickly and reliably were and are often whistled back by superiors or even politicians if the entrepreneur concerned played in the right golf club... A good employee goes through this once... maybe a second time... The third time he quits and founds his own tax consultancy. He knows how to do it...

As a result, over a long period of time, the tax offices are left with rather average employees. Like almost everywhere else. Just look at most politicians 🙂 Where else can you easily earn 15K euros a month as a trampoline jumper, co-author of children's books, kitchen assistant or without a real educational qualification?... OK, I'm digressing again. Back to the topic...

The Idea testing software

This is, from a very technical point of view, nothing so great. It reads the data record definitions of your Electronic Tax Records File and then reads the user data, which is available in simple CSV format, into corresponding tables defined by the record descriptions. These tables from your bookkeeping/accounting are "mapped" (the columns are standardized, translated), which means that after this import, all your accounting files created via the ETRF interface from Navision Financials, Dynamics Attain & Microsoft Business Central BC365 are uniformly readable for the IDEA software and the auditor/accountant working with it. Gone are the days when auditors had to familiarize themselves with dozens of accounting systems in order to gain online access to company data and bookkeeping/accounting. If you have an electronic archiving system, you even have to hand over the documents contained therein to the auditor on request in such a way that he can easily find the documents imported into IDEA (e.g. via a document number search) in this archive. IDEA is designed to handle a large number of data records in a large number of tables, so Tax Audit (the "ready for the user" add-on on Idea) has no problems not only managing hundreds of thousands or even millions of data records in a table (e.g. the G/L account postings), but also analyzing them according to various criteria. This is where the following check macros come into play...

Test macros for the tax audit via IDEA

These are the magic of IDEA (Interactive Data Extraction and Analysis). If you're wondering: Wow, a tax office in Germany has developed something this great? No. As written above: There aren't that many great (in the real sense of the word) employees in German tax offices. That may be different in the USA, where tax inspectors are sometimes lowered from a helicopter directly into a company headquarters wearing bulletproof vests. No. IDEA was created by the Canadian Financial Supervisory Authority around 1987and has since been used in tax and financial audits in more and more (industrial) nations around the world. Germany has been involved since 2002 with 14 thousand licenses. And this is where the concept of audit macros And this is where the concept of audit macros paid off, as not all countries can be compared with each other. Some predefined audit methods are already supplied for audits. The few resourceful tax auditors who can still survive in the gray offices create clever (you have to say that!) audit macros from time to time, which pick out exactly the invoice or exactly the payment that should be looked at more closely from the thousands or millions of data records that are flushed into the auditors' laptops when they access a data carrier. And believe me, these macros are quite something. And in stark contrast to the "old days", you no longer need a gifted, experienced auditor. Now, tens of thousands of auditors and accountants can bring critical documents to light at the touch of a button. Fun fact: Germany is the most lucrative market with over 40,000 licenses, and the majority of the internationally used audit macros also come from Germany! This is probably one of the reasons why the German IDEA provider Audicon (and all other local providers of Idea) was integrated into the now globally active parent company Caseware around 2022 .

Examples of automated checks of ETRF data with IDEA

Audit of asset accounting. Does this ring a bell or two in your accounting department?

• Depreciation start and order date for fixed assets to determine whether depreciation has started too late or too early. Finds all assets whose depreciation start date is unusual.
• Depreciation percentage, to determine excessive depreciation.
• Determination of excessive depreciation when applying the simplification rule for additions in the second half of the year
• Identification of high book losses, non-cash benefits to employees and related parties of the taxpayer. Finds assets with high book losses on disposal.
• Determination of assets for which the economic life has been changed.
• Determination of incorrectly recognized declining balance depreciation
• Determination of unusual business transactions above a certain amount with even amounts or with equal amounts.
• List of assets whose depreciation start date differs from the date of acquisition.
• Assets for which the depreciation method was changed incorrectly.
• Sort selected assets by estimated economic life in ascending order, maybe there are some outliers? A sinfully expensive laptop that was only in use for 1/2 year?
• Determination of repair and maintenance expenses that must be capitalized and exceed a certain value.

• Sales... We'll come back to that below!

• Determination of permanent debts for current accounts and the minimum amount of a current account debt.
• List of business transactions for which the document date does not fall within the assessment period to be audited.
• Determination of incorrectly recognized liabilities that have been due for a certain period of time.

Payroll accounting... I myself still know methods that were used by a previous employer... IDEA also knows a few of them 🙂

• Identification of employees for whom wage tax allowances were incorrectly taken into account in payroll accounting.
• Employees for whom car benefits in kind were subject to wage tax.
• List of all employees who have two or more marginal employment relationships with the taxpayer (employer) to be audited.
• Employees who are listed several times in payroll accounting. (Bank details, name, address, dates of birth)
• Determination of severance payments that were not or incorrectly subject to wage tax.
• Employees with incorrect age relief amounts
• Tax-free wages according to DBA
• Tax-free employer benefits for double housekeeping

Incoming invoices... more on this below...

• Incoming invoices with an input tax deduction in the pre-notification period (debit taxation).
• Incoming invoices with high input tax deduction amounts
• Conspicuous input tax deduction

These were simple processes that even an average tax auditor would have found quickly without Electronic Tax Records File (ETRF) or IDEA. But the IDEA and TAX Audit analyses go much further! This is where it gets very mathematical in places...

Special test macros in IDEA

• Balance limits... A cash register can/may never have a negative balance. If your goods movements have also been transferred: An item cannot have a negative balance. Not even "at some point"! This is the perfect opportunity for an estimate!
• Filter and extract: High or low values are displayed from millions of data records in no time at all. Unusual individual bookings, bookings on Sundays, Santas in July, bikinis in December... Just like you know it from Excel. Except that here your accounting or even merchandise management is filtered at lightning speed according to common search patterns.
• Totaling, counting amounts,counting duplicates & displaying... across any fields! How often does the amount 633.12 euros occur in all G/L entries? Which amount occurs most frequently? Does a bank account number or a telephone number occur for different customers or employees? This quickly reveals the "income tax card income tax class 5 from grandma" for employees or temporary staff.
• For example, have purchase invoices been paid more than once (by mistake, of course...)? Does a purchase invoice or a certain amount appear more than once in the items?
• Gap determination: Are individual document numbers missing in invoice numbers, for example?
• Pivot tables similar to Excel
• Benfords Law... this number analysis uncovers every "invented" series of numbers. It can be used to uncover manipulated or retrospectively recorded logbooks or fictitious KM statements at the touch of a button... and that in court. Judges generally have no knowledge of correlation and causality and the difference between them. In addition to Benford's Law, the Chi Square Test has also secured its place in the detection of self-generated ("invented") numerical patterns.
• Fuzzy search: In addition to the duplicate search already mentioned, IDEA also finds Maier, Meier and Mayer here alongside "Meyer". In connection with similar amounts, incoming invoices duplicated by image processing may be found here...
• Canceled bookings... Was there an original entry that fits exactly 100%??
• Calendar anomalies... Which transactions were recorded at the weekend? Or were there hospitality expenses shortly after the managing director's birthday? With an analysis software such as IDEA, this can also be created over years (" hospitality expenses between August 6 and 10 of each year") at the touch of a button. With a check macro, this can also be done automatically. The check macro then only asks for standard information ("G/L account number for entertainment costs, managing director's birthday, wife's birthday") and off you go. Or does the cash register show a high deposit every Friday? Perhaps the cash register was not managed in monetary terms (i.e. real stock, real movements), but only "mathematically" -> estimation and rejection of the cash accounting are the immediate consequence.
• Jumps... Normally constant developments, e.g. "Cash receipts" or "Expenditure on materials" or hospitality costs can simply be shown graphically. Jumps in this graph naturally tempt the auditor to take a closer look at the jump. This quickly reveals split postings, e.g. for fixed assets, which have been split as low-value assets and entered in the financial accounts for immediate depreciation.
• Text analysis... Postings with "Telephone costs" o the "Vehicle costs" G/L account were perhaps still common with B-network telephones in the trunk. Today, these are occasions to search for "Ambient private withdrawals" or "Untaxed wage payments".

Further correlations that Idea can already find from Electronic Tax Records Files (ETRF) and other data

• Pizza boxes => turnover... OK, that's easy.
• 30/70: In the food service industry, sales are often split 30% beverages and 70% food. Deviations from this can make you want more...
• Broken glasses, used toilet paper or dish soap, stolen ashtrays -> turnover... This is where it gets more exciting. By means of Idea or ETRF (data carrier transfers) and the resulting sharp increase in company audits, company patterns could and are created for industry-specific comparisons ("ranking"). for industry-specific comparisons ("ranking"). Hairdressers, bakers, butchers, electricians... Small, medium-sized and large restaurateurs in the countryside, the city, a pedestrian zone, near a soccer stadium. One broken glass for every thousand beers served, one stolen ashtray for every 2000 guests, one large pack of toilet paper for every 300 meals. These dependencies are also quickly identified: Get the 10 Metro and 6 SB Union bills from last year, add up the glasses and toilet paper (or record them, adding them up is what Idea does), and you have a new key figure. And you can also quickly subtract the official projects from the meters of cable purchased from the electrician according to statistical consumption... what's left is the rest that was wired in black at the weekend.
• The number of new tires at a car rental company or cab service, liters of disinfectant solution for the on-board toilet at a bus company quickly bring "illegal driving" to light.
• Emptying of the porta-potties on construction sites indicates black employment.
• Used adult diapers should match the customer and sales statistics in care services. And when it comes to the service kilometers billed
• the "cash on the hand" portion of the nursing staff's wages, which are far too low anyway, also stands out. In addition, there was a much too high
• advertising cost item in the accounts, which was paid directly to a Polish company, conveniently located in the same street as the accounts department of the audited company.
• Sales on weekdays or in months. If a Christmas tree retailer records high revenues in May, you could ask about this. The "problem" does not always have to be on the side of the audited company! But perhaps this is an incentive for a cross-check, so that you take a closer look at this particular customer... IDEA also supports the auditor here, because once data has been entered into Idea, it is usually not deleted so quickly. If a North Korean hacker ever notices this...
• Cross-checks are then possible at the touch of a button years later, once the ETRF data has been read in from both companies: Does every outgoing invoice from company A to company B exist in exactly the same way in company B? In this way, VAT fraud can be uncovered by any novice auditor or tax auditor (tax office auditor).
• Time series comparison: If purchases rise or have risen, sales should also rise shortly afterwards (depending on the sector).
• Correlation to other data sets. For example, the auditor can use Website watcher to monitor the homepages of interesting companies and find "documented" construction progress... and then compare this with the G/L accounts of the construction projects.
• Private websites of managing directors or company owners... Do aviation pictures suddenly appear on the Facebook profile, and during these periods are there conspicuous service costs from an advertising company that also provides aviation training? Are there high construction bills for company buildings without this being proudly pointed out on the website? And then perhaps a private website of the company owner, which proudly advertises the "Completely renovated finca in Spain" for vacation bookings?

This also makes it much easier for tax authorities to find cheating in cost accounting when higher amounts are paid to an LLP (Canada) or an LLC (America) or unusually high consultancy fees are paid to Eastern Europe. Classic letterbox companies and sham transactions for tax avoidance. If, on the other hand, you have your IT consulting and programming for your Navision Financials Dynamics Attain or Microsoft Business Central BC365 provided by me legally, there is no danger here!

You will find regular courses on what is possible with Idea and your ETRF data from Navision Financials Dynamics Attain or Microsoft Business Central BC365 here . The TAXpro YouTube channel is also well worth watching on this topic..

And what does IDEA do for you?

You might think so: Just trouble. But that's not (necessarily) the case. You can use TaxAudit (now TAP or Alessa or GoBD), the add-on for Idea specially prepared for an easy start, to perform the most common standard audits with your own accounting data. It doesn't matter whether you keep your accounts in Navision, Business Central 365, SAP, Lexware or Sage. In the end, the ETRF export results in standardized, highly (but not too highly! This was a problem with SAP for a long time) compressed data records that contain all the truths about your, perhaps also creative, accounting.
With IDEA you ensure a little equality of arms. With TaxAudit you don't get all the audit macros that are devised in the tax offices of the world (!!) - but you do get a pretty good package.
And you can do even more with it. You can subject your own business data to the same audit, regardless of where and how it was collected. Expense reports of your employees, cash withdrawals from your cash register, redirection of supplier payments to private accounts... mileage reports for your truck or car fleet, water or electricity bills for your rental apartments abroad: Maybe you also have an employee who wants to turn a few figures in his direction... with IDEA you can find such anomalies in the same way and just as easily as your auditor / tax auditor. But for classic pivot evaluations, take a look at my BI edition for Navision / Business Central & Excelfirst, maybe that's also quite cool for your purposes! In the end, Idea and its attachments such as the check macros do nothing else than you could do with Excel or directly with Navision Financials / Dynamics Attain or Microsoft Business Central BC365, with the 2024 BC365 even directly in the item pages. But tax audit programs like idea make it child's play, and thanks to their specialization in large amounts of data (Idea: up to 2 billion data records per table!), they do it in no time at all.

ICS & process documentation

Germany is doing everything it can to stifle its drives, its companies, with uncompetitive taxes and excessive bureaucracy. Process documentation with the obligation to have Internal Control Systems (ICS) is another example of this. However, not every company is able or even willing to o escape this stranglehold by emigrating. On the one hand, exit taxation ("exit tax") or disentanglement is becoming an increasingly strict deterrent to leaving Germany at all. More importantly: Other countries learn from Germany, and some of these laws, rules and regulations also come from abroad. Even with taxes and administration, milk and honey do not flow everywhere, even if some "emigration consultants" like to suggest this. However, you can create a kind of "equality of arms" by acquiring tools similar to those used by auditors. Tax audit software such as Idea with common audit macros is a good way to test your own collection of figures ("bookkeeping") for the usual pitfalls before the auditor does. And don't forget: Perhaps you also have a black sheep in your company that is working to its own advantage? You can also uncover these manipulations with these tools. And don't forget: As a managing director/shareholder/company owner, you should have at least a basic knowledge of business management and accounting. I will be happy to support you with a pleasant basic training session under palm trees!

Process documentation

Practically every company (even very small ones!) is obliged by the Pegulations for the proper management and storage of Books, records and documents in electronic form and for Data access (RBD) require at least rudimentary procedural documentation. Ultimately, this is not rocket science. Process documentation is a written (electronic, e.g. as a Word document is also sufficient) record of various processes in your company. Examples:

• How are paper incoming invoices and (usually different) electronic (e.g. incoming invoices by e-mail) invoices processed?
• How can you ensure that incoming invoices are not lost? Here, a documented electronic invoice receipt can perhaps save you any further queries.
• How are outgoing invoices created and sent, perhaps with Zugpferd or XRechnung?Here, for example, a short procedure including screenshots for posting orders or creating/posting collective invoices in Navision Financials or Dynamics Attain or Microsoft Business Central BC365 may be sufficient.
• How is a payment run carried out?
• Who has access rights or access options to the transfer files?
• How are incoming and outgoing invoices stored? In paper form? According to which rules are they sorted and filed? In electronic form? How is filing security guaranteed?

Or, more factually summarized:

Section 6001 (Internal Revenue Code (IRC))

In Germany, procedural documentation is required by law in accordance with Section 146 (1) of the German Fiscal Code (AO). Characteristics include

1. Obligation to keep records: Companies must create and retain procedural documentation. This documentation describes processes such as bookkeeping and the handling of receipts.
2. Content of the procedural documentation:
   • General description: An overview of the IT systems, software and hardware used.
   • Organization of bookkeeping: How are receipts recorded, processed and archived?
   • Access controls: Who has access to the systems and data?
   • Change logs: How are changes to the systems and processes documented?
   • Security measures: What security measures protect the data?
3. Updating and review: The procedural documentation should be checked regularly (e.g. annually) and updated if necessary
4. Tax relevance: The procedural documentation is important for proper accounting and the correct preparation of tax returns. It may be checked by the tax office during a tax audit.

However, procedural documentation goes beyond these basics. As the company owner/managing director, you are ultimately also responsible for your operational security, so that your company does not suddenly become "inoperable" as a result of a fire, theft or cyber attack, for example. Of course, you don't have to carry the magnetic tapes to the backup server yourself. But you must have an overview (or at least be able to get one) of how basic IT processes work in your company. Here again, there is no way around procedural documentation. Depending on the IT structure (in-house or cloud , for example), there are also points such as

• Carrying out and checking data backups (programs, data carriers, cycles, readability check...)
• Securing the company network against unauthorized access (firewall, passwords, etc.)
• How are adjustments made to the IT or software (test systems, staging...)

As you can see, procedural documentation does not primarily serve the tax authorities. The primary purpose of procedural documentation is to make your company more transparent - for you too! Whether this should have been cast in laws and regulations (Deneral Sata Protection RegulationODPR, and then in the way that German thoroughness demands, remains to be seen. But... These laws are readily copied from abroad!

Process documentation is a whole separate topic, so perhaps I'll write a separate article about it. For you, this should have stuck:
Process documentation is a matter for the boss! You don't have to create it yourself, but you must have it and be able to produce it if required. If an auditor (or, in the worst case, a tax auditor) notices, for example, that a cash balance repeatedly changes by a round or similar amount between Sunday and Monday, or that bookings are occasionally made to a duplicate bank account on Saturdays, then they can ask about it:
Who has access to your company premises or company network at the weekend (remote workers, home office). Who made these bookings? In the case of the former, your procedural documentation comes into play (who has keys or chip cards for the door opener? When are they valid?) For the latter, your logs and access rights come into play. If Hinz and Kunz can log on to any computer with standardized passwords and supervisor rights, then this is primarily your problem... But secondly, thanks to the GDPR, it is also a serious problem in your administration, and you have to justify yourself for it. It is also possible that a previously undetected fraud by your employees will be charged to you as tax evasion.

ICS (internal control systems)

Congratulations if you've made it this far. As you can see, accounting and everything surrounding it can be quite dry. Nevertheless, you should at least familiarize yourself with the basics of your own bookkeeping, if you are not a sole proprietorship. But then you won't be reading this article anyway.

Interne Controlsystems ICS... what could that be? Well, in a first approximation, these are all the processes carried out regularly to detect "crooked dogs" in your company. Even stocktaking and the analysis of inventory discrepancies are part of this. A WMS with automatic zero-crossing checks can save you (and your employees) a lot of work here. I will publish a separate article on this, WMS with Navision Financials Dynamics Attain or Microsoft Business Central BC365. A regular review of account statements, avoiding double payments, an automated dunning system and receivables management: all of these are already quite "natural" internal control systems (ICS), which you hopefully and probably already use without any external pressure. Or, ICS summarized very objectively:

1. Purpose of the ICS:
   • The ICS monitors and controls compliance with legal regulations.
   • It ensures the effectiveness and efficiency of business activities.
2. Elements of the ICS:
   • Process analysis: Risk-oriented analysis of company processes.
   • Controls: Definition of control measures to safeguard the processes.
3. Benefits of the ICS:
   • Transparency: Clear structures and control mechanisms.
   • Risk minimization: Identification of and response to risks.
   • Regulatory requirements: Compliance with legal requirements.
4. Waiver of ICS:
   • Can lead to reputational damage, financial losses and wrong decisions.
   • Companies should implement and monitor a functioning ICS.

A well-designed ICS offers more transparency and minimizes risks in the company.

Idea, or more precisely its TAP (Tax Audit Professional) add-ons provide you with many more options, and with Alessa you can also automate reporting - and we are back to procedural documentation.

Alternatives to Idea

In addition to being fast, flexible and quite smart, Idea is one thing above all: the tax inspector's favorite! No, joke, that was already apparent much further up 🙂 Idea is quite expensive. Idea itself, the data driver or analysis engine, so to speak, costs a whopping 2,000 euros (I think 1,995 euros) a year - every year! But Idea itself is only the basis, you have to create every import and every analysis yourself! Therefore, you can hardly avoid a macro add-on such as TAP (Tax Audit Professional) for another 2,920 euros - per year! You might get a few discounts, but it won't be really cheap. The analyses of Idea and TAP involve some money, sometimes a lot of money... and Caseware pays handsomely for this. But there are also alternatives to Idea, and practically all of them are considerably cheaper. E.G:

Do it yourself.

Seriously! You can also generate the vast majority of Idea analyses with Excel and a few formulas and pivot tables. But every month anew. If you are not familiar with sophisticated VBA programming in Excel, you only need a few hours for the first quick evaluation, which TAP displays directly after the data import (oldest date, most recent date, movements on Monday/Tuesday/Wednesday...weekend, largest amount, smallest amount...) just like that... every week or every month.

Program it yourself (or have it programmed).

Of course, you can also create these evaluations directly in Navision Financials or Business Central. However, you should allow about 1 day for each evaluation macro. But then - once created - they are permanently free of charge and available at the touch of a button. This can even be a very attractive alternative, contact me for this!

A hybrid of do-it-yourself and programming

Using processed (enriched) data exports from Navision to Excel, you can get cross totals, min/max values, monthly and weekly day evaluations out of Excel in seconds instead of hours.

ACL Analytics by Diligent

Audit Command Language, corresponding to the macro language of Idea. Used, for example, by the Austrian and Danish tax authorities.

AIS TaxAudit from/for Idea: This product no longer exists, see above. These products are now only available from Caseware, e.g. as TAP (Tax Audit Professional), see above.

ARBUTUS Analyzer

A bit of a successor to ACL Analytics. The original core developer of ACL Analytics has gone into business for himself.

ACL Comfort – DATEV Data Assurance Package

Also a further development of ACL, but with ACL as the basis (like the TAP essays on IDEA). Primarily for DATEV members, because DATEV also wants a slice of the electronic tax audit pie. Considered simpler than IDEA and yet very powerful.

DataSniper

TopCAAts by Reinvent Data Limited

As suggested above, this is a collection of macros for using Excel as a data driver/evaluation engine for tax audits, tax audits in accordance with ETRF, GDPR and General Accounting Principles. This is also the greatest limitation: TopCAATs is limited to the performance of Excel, which means, for example, a maximum of 1 million lines in evaluation tables. A new version is probably planned that will integrate the data externally and thus remove this limitation, but to my knowledge the (approximately) 2010 version is currently still available - but this is also available as a demo version.

SAS Visual Analytics

A cloud-based solution. Due to the nature of global access, it is more suitable for internal audits. The loading times for moving millions of data records from your own system to the cloud should not be underestimated. Once the records are there ("in the cloud"), they are very fast and do not require any hardware of their own. Employees in worldwide locations can easily upload their analysis data, and evaluations can be carried out at the central company headquarters or in Bali or the Philippines. I wouldn't entrust my entire Navision or BC365 company data to a cloud...…

Active Data For Eccel/Office & SQL

Also a further development of IDEA, if you will. The former chief developer of IDEA has thus developed an instrument suitable for "everyone". Definitely worth considering for smaller amounts of data!

Teammate Analytics

Is also based on Excel and supplements Excel with some of the check functions of IDEA and ACL. Good for a quick overview with a limited amount of data.

Picalo

An open source development to map some of the IDEA check/analysis functions. This project currently seems dead.

My assistance

For every alternative and also the original (AIS TaxAudit or today Caseware IDEA with TAP or General Accounting Principles), there are also the training costs. In addition to the annual costs, there is also your working time, the constant new training and the data output. I offer you monthly, annual or on-demand analyses with IDEA TAP and take this work off your hands. I can detect problems or even attempted fraud/fraud in your company for you. Long before an auditor or tax auditor does. As a result, your expenses for my services can often pay for themselves or even generate a profit. For example, by recovering double payments, uncovering internal fraud (fraud analysis) and saving time during a tax audit. Of course I use IDEA for this. If you want to become the market leader, work with the market leaders! Contact me for an offer.

...And IDEA itself?

If you've made it this far, then you've already noticed that my heart naturally beats for my wife and Navision... and also a little for IDEA. Even if you want to condemn IDEA as a tax audit program: From a technical point of view, it's awesome and was basically the inspiration for all the programs mentioned above. See also my two recommendations below!

Which solution is the right one for you?

I can only really recommend 2 solutions: "My support" and IDEA TAP or GdBD. Of course, you can freely use the examples mentioned above, but then my support ends with the Electronic Tax Records Files (ETRF) data output from Navision.

My assistance

If you need me to set up or output your Electronic Tax Records Files (ETRF) data anyway, you shouldn't even bother familiarizing yourself with IDEA. If "pivot tables" in Excel still seem more like witchcraft to you: Even then, you're better off reaching for my help. For the annual license costs of IDEA of around 5000 euros, I can list your ETRF data output and a first and second analysis of your data with the most important deviations "from the norm" and, if necessary, help you to correct them. Contact me today, before it's too late - before your auditor wants you to explain harmless deviations from the norm and you're now sitting in ice-cold water.

IDEA itself

The German authorities use IDEA with audit macros, some of which are also supplied with TAP (Tax Audit Professional) or can be recreated by me - possibly also with ideas from you, because you have noticed certain fraud patterns in your company. If you want to prepare for a tax audit, use the same weapons as the IRS/CRA/HRMC!

That is why I cannot (and do not want to) offer any further advice on the other alternatives. Of course, you can also take a look at these (DataSniper, ACL, TopCAATs...) and try them out - but I'm focusing purely on the professional tools used by German tax auditors and accountants so as not to get bogged down.