ERP Reporting remains a challenge today. Whilst the ERP system can manage the granularity of a business, delivering business insights does not appear to be the focus for software vendors. Since introducing Calxa Unplugged, we have suggested a number of ways of using a manual organisation combined with data imported from a spreadsheet. One of the most popular uses has been to produce management data to help with ERP reporting. Here’s the how and why of that task.
The why is actually quite simple, if a little surprising to those who have been working just in the small business accounting space. ERP systems are good for accounting and compliance and managing complex inventory or workflows. However, they often lack functionality in budgeting, cashflow forecasting or putting management reports in the hands of end-users.
Consultants to Help with ERP Reporting
ERP systems, whether it is NetSuite, SAP, Microsoft Business Central, MYOB Advanced, or something else, usually have underlying sophisticated reporting capabilities which can pull out any data that’s in the system. The problem is that you need a lot of skill to create those reports or modify them. Something beyond the talents and experience of many finance teams. That’s why there are many specialist consultants in the ERP space who are good at that task. Usually, they will set up the basic reports you need when implementing your system and will be more than willing to create more later but for a fee based on their hourly rate.
Calxa for ERP Reporting is a Welcome Alternative
Creating a manual organisation in Calxa will give you an option with some great benefits. Firstly, you will need to import your accounts, business units and the monthly actuals. However, once done, this gives you access to the full suite of reports Calxa has on offer. Your ERP reporting has just expanded to include 3-way forecasting in your kit. Not usually what an ERP system can produce. What’s more, you don’t need a degree in Computer Science to select, filter and run the report. To add further value, once you have done that once, you can schedule delivery month after month.
How to Set Up Calxa Unplugged for ERP Reporting
There are a few steps during the implementation. Once this is completed, your ERP reporting task simply becomes part of a monthly maintenance list.
Importing your Chart of Accounts
We’re dealing with accounting, so the Chart of Accounts is the backbone of our reporting system.
- Depending on how your ERP system is structured, you may need to strip out the nominal code section from the account code. Essentially, some ERP systems will have long GL codes combining [Company]-[Branch]-[Department]-[Project]-[Nominal code]. For this purpose, your ERP reporting set up just needs that last part. The rest will form your business unit setup.
- When importing the accounts, just bring in the bottom level, detail accounts. After that, you can then use Calxa’s Account Trees to create the desired reporting structure to group and summarise accounts for different reporting needs.
Importing your Business Units
Just to be clear, these are your branches, departments or projects, whatever you call them.
- Again, split them out of your account codes if you need to and import just the flat lists.
- You will be able to group and summarise them with Business Unit Trees to get the reporting flexibility to suit your business. However, if your interest is just in company-level reporting and cashflow forecasts, you can skip this step.
Importing Monthly Actuals
An important part of the implementation is considering the period you want to start with your ERP reporting.
- We recommend importing opening balances in the last month of the financial year before last.
- Then import monthly amounts for each month last financial year to facilitate prior year comparisons.
- Now, import for each month up to date and then you’ll be ready to start generating reports. The ongoing monthly task will then be to import that month’s movements, adding any new accounts or business units as you need.
Start with the Opening Balances
Add the opening balances in the month before you want to start your ERP reporting. If, for example, your financial year runs from April to March, and the current year is 2023-24, you will want to include 2022-23 numbers in your reports (at least). In this case, the opening balance month should be March 2022, the last month of the previous year. As a result, this will give you the right opening balances for your reporting period.
Importing Monthly Movements
Each month, you need to import the account movements by business unit for each account. Now while our default import assumes increases to any account are positive values, whether income, expense, asset or liability, that’s not the only method.
It is important to understand that the standard import takes positive values for income, for cost of sales and expense accounts; positive values or increases in asset, liability and equity accounts. The key is to export a sample import template that will give you a good idea of the layout.
So, what if you are starting with a trial balance from your ERP system? We have customers using some of the Sage products, for example, that do this. They take the Sage trial balance to a spreadsheet and add an extra column to combine the debits and credit columns. This method uses positive and negative values for the debits and credits – meaning you can easily verify that your import balances. On top of that, it also uses the closing balances rather than the monthly movements.
Using OData or Similar Query for Import
Preparing actuals for import can be a challenge from some of the smaller systems. However, many ERP products will have an option to create an OData or similar query. Essentially, this is used to extract the data you need in the right layout. On the other side, for other systems where you need to rely on some sort of report export, which typically includes headings, totals and other information, we have developed some Excel macros to reformat the data ready for import. Ask us if you need help with that.
Future plans for ERP Reporting in Calxa
Towards the end of 2023, our roadmap includes work on direct ERP integrations. This will simplify the process significantly. In the meantime, using Calxa Unplugged to set up a manual organisation with some imports is a great way of delivering ERP reporting. . Our most-requested ERP integrations are Microsoft Business Central, MYOB Advanced (Accumatica) and Netsuite. If there’s anything you think should be on that list, let us know!