Variance Analysis in Business
The variance analysis has found its wide application in business, since its application allows managers to evaluate results of their work and understand what needs should be changed to improve efficiency.
Variable cost variances; direct material variances; direct material price variance; direct material usage variance; direct labour variances; labour rate variance; labour efficiency variance; variable production overhead variances; fixed production overhead variances; sales variances; sales price variance; sales volume variance, etc. This list can go on because variance analysis is used to control the enormous list of indicators.
Due to the variety of indicators, which are controlled by variance analysis, it usually integrates into a corporate ERP system (for instance SAP or Oracle). Integration of variance analysis into the ERP system makes it possible for the managers to obtain real-time variance analysis, hence the response rate to changes is significant competitive advantage.
Is there advantage or disadvantage in integration of Variance Analysis in ERP System?
The rate of business environment change grows constantly: new technologies emerge, which change markets and industries; world and local crisis happen; consumer preferences change, etc. Ongoing upgrade of data analysis model is required in order to manage business in such conditions.
For example, during the period of rapid growth companies are focused on revenue, and during the period of crisis on the cost price. This leads to the shift in management priorities, which means that new financial reporting requirements are imposed. For a CFO it only means to change an analysis model in corporate ERP system.
Writing the technical task, a struggle for priority in the task tracker, testing, completion based on the test results — that's what you must deal with whenever you make changes in the ERP system. Sometimes, it takes weeks and months, and by the time the reporting is ready, it's no longer relevant. At the same time, the cost of integrator services is quite high, which often leads to IT budget overrun or even makes the rework unaffordable.
Microsoft Excel Comes to the Rescue
To retrieve the data from the ERP system and create calculation formulas in the Excel workbook you do not need to write the technical task and involve expensive programmers. As a rule, employees of the company's financial department can handle this task. But despite this, the implementation of variance analysis in Excel is associated with significant difficulties.
Firstly, using Microsoft Excel does not automate the process of developing formulas for calculating variances, and you still have to spend time on their development. Before proceeding with the calculation, you need to select a variance analysis method, and, if necessary, determine the order of calculating variances, and then derive the calculation formulas for each variance. This is an obligatory stage, and the use of Excel or ERP system does not give advantages in this process.
Secondly, it takes a considerable amount of time to check the calculation formulas and correct errors working with Excel. Formulas for calculating variances may be very complex. Look at one of them below to understand the essence of the problem. This is the real formula for calculating the discount variance from the base price, which is used in one FMCG company. Be sure that at least once you will definitely make a mechanical error by changing links from variance to variance.
Thirdly, reusability of the variance formula in Excel with new data always requires additional time for testing and configuration. Microsoft Excel has a significant disadvantage compared to ERP systems: once you have configured the calculation of variance analysis in an Excel workbook, you cannot be sure of its reliability in the next use. The thing is that unlike ERP systems, Excel does not separate calculation logic and data — formulas and numbers are stored in the cells together. In practice, this means that the next time you use the book with calculation you need to change the links, then check the links and, if the addresses of the ranges have changed, you will have to enter these changes in the formulas.
Finally, the speed of implementing of variance analysis in Microsoft Excel depends on the professionalism of the performer. The performer must have excellent knowledge in the field of financial statement analysis, be a confident Excel user and have a good experience in the development of variance analysis. Therefore, not all ordinary employees can cope with this task.
ERP Reliability + Excel Flexibility + Formulas Automation = Fincontrollex® Variance Analysis Tool
The add-in Fincontrollex® Variance Analysis Tool completely automates variance analysis in Microsoft Excel. Using the add-in allows you to perform a variance analysis of any indicators in just a few minutes. At the same time, all you need is a variance base formula and data. The rest of the work is done for you by this add-in:
- — Prepare all the calculation formulas (or calculate the values) of the variances.
- — Create a detailed report on products and a consolidated report on the variances;
- — Build a waterfall chart*.
Fincontrollex® Variance Analysis Tool allows you to separate the variance formulas and data as it is implemented in ERP systems. This gives you the ability to edit the mathematical formula of the variance easily, update the data, use the variance analysis repeatedly on another computer without having to transfer or edit formulas for calculating variances in the Excel workbook. By adjusting the variance base formula once, you can be sure of the reliability of the calculation formulas when you use it next time.
Fincontrollex® Variance Analysis Tool allows to perform variance analysis by the users who do not have deep knowledge in the field of financial statement analysis. Because of automation, users do not have to create and edit formulas of calculation which eliminates the risk of making errors.
To perform variance analysis, the Fincontrollex® Variance Analysis Tool uses a universal method of calculation. Due to this, the area of application of the Fincontrollex® Variance Analysis Tool is unlimited, and you can use it to analyze any financial indicators, whether it is the revenue from sales or the NPV of an investment project.
Fincontrollex® Variance Analysis Tool has a convenient and intuitive interface, so you can easily create and customize variance formula of any complexity, change the order of variance calculations, configure grouping of data in the report by periods or by variances as well as add horizontal analysis to the report.
For users of Fincontrollex® Variance Analysis Tool, we constantly update the library of ready-made variance formulas. You do not have to waste time developing a new formula, but use a ready solution.
And finally, thanks to all the above advantages the application of the Fincontrollex® Variance Analysis Tool shortens the development and performance of variance analysis by few days. And in some cases, it even allows you to perform a variance analysis of indicators that could not be calculated previously because of the complexity of variance formulas. As a result, you can always assess the overall picture of the events quickly and understand what needs to be changed to improve business performance.
To ensure the effectiveness of the Fincontrollex® Variance Analysis Tool download a free trial version right now. Before installing, we recommend you to read the License Agreement, the Installation Instructions and get familiar with the Reference Guide.
As an example of a variance formula, you can download the test variance analysis with data (the instructions for setting up the analysis are provided in the
Limitations of the Trial Version
- — Validity period — 30 days
- — The variance analysis with formulas can be only performed in models with the number of variances not exceeding 4.
For proper functioning of the Add-in you need:
- — Windows 7 — 10
- — Microsoft Excel 2010 (or higher) for Windows
- — .Net Framework 4.5.2 (or higher).
The rest of the system requirements can be found on the Microsoft.
Attention! The add-in does not work on Mac computers and in earlier versions of Microsoft Excel for Windows.