In the modern business world, efficient financial management is a key success factor. Many companies are faced with the challenge of automating their interactive profit and loss statement (P&L). What is often not known is the enormous benefits that automation and interactivity can bring to financial management at company level and in each individual department. In this article, we use a typical initial situation to show how the introduction of a group-wide data model based on Microsoft technologies such as Dynamics NAV, Navision and Business Central solves these challenges.
Situation before - manual process:
In many companies, the month-end closing is still carried out manually. The controlling team pulls the P&L from the ERP system - often Microsoft Dynamics NAV or Navision - and filters it manually by cost center in order to evaluate the individual profit centers. The results are then entered into Excel spreadsheets and this process is repeated for each subsidiary, especially if different ERP systems are in use. The end result is a comprehensive Excel file with separate worksheets for each subsidiary, which is then sent to the management. It will look something like this, only much bigger, and with different worksheets:
Disadvantages of the manual process:
- Time consumingThe entire process can take several days.
- Susceptibility to errorsManual post-processing of bookings in previous months increases the potential for errors.
- Dependence on individualsThe process depends heavily on the availability of individual employees.
- Limited depth of detailOnly aggregated figures are visible, detailed postings are missing.
- Limited comparabilityIt is difficult to make consistent comparisons over several years.
Situation after the introduction of a Group-wide data model:
All key financial figures of the profit and loss statement of the group of companies are calculated automatically, can be filtered by profit center, and you can navigate in several levels up to the individual posting and back again.
Visually appealing reports in Power BI ensure comparability at all levels over several years, with filter options by profit center.
The management can now see all bookings in real time and no longer has to wait until the controller has done his work at the end of the month. In addition, with Excel one Live connection to the group-wide data model, so that each Excel file updates itself in 3 seconds. The functionalities of the ERP system are no longer required for the evaluation.
Budgets and role concept
A role model was also used to assign department heads responsible for cost centers to their data so that they too can now manage their department's figures in a targeted manner. In the next step, budget figures were even integrated, against which the actual figures are compared. This in turn was used to calculate a forecast, with which every department head now knows how much money they can or cannot spend in the year. Automatic alerts are sent if the budget is exceeded. These figures are discussed on a monthly basis, as they represent a central control element.
Advantages of the new approach:
- Real-time transparencyManagement can now view all bookings in real time without having to wait for the month-end closing.
- Automated updatingExcel files can be connected to the group-wide data model via a live connection and are updated within seconds.
- Comparability over the yearsPower BI enables the comparison of financial data over many years through its interactive reports and provides deep insights at all levels.
- Budget control and forecastingDepartment managers can control their figures and monitor budgets, while automatic alerts are triggered if budgets are exceeded.
- Efficient use of resourcesThe controlling team can concentrate on analyzing the data instead of spending time preparing it.
The path to implementation
Automation begins with ready-made data structures for ERP systems such as Microsoft Dynamics NAV, Navision and Business Central. We adapt these structures to the individual needs of our customers and use them to create a cross-client data model for the finance area. Using Microsoft technologies such as Power BI and SQL Server (alternatively Azure SQL), we implement fully automated reports that are developed in close cooperation with Controlling and IT. For smaller companies, the introduction of an initial version can be completed within 1-2 weeks.
Automating the profit and loss statement using Microsoft Dynamics NAV, Navision or Business Central is a decisive step for companies to optimize their financial management. The introduction of a group-wide data model not only makes processes more efficient, but also significantly improves transparency and decision-making.
Please visit our Trainingif you want to gain further new knowledge about Power BI and the world of Business Intelligence.
If you have any further questions on this topic or require support with your data analysis, please do not hesitate to contact us.
