Excel programming of a dynamic pricing tool for RAJA

Development of a dynamic pricing tool for Rajapack GmbH with Microsoft Excel & VBA

In August 2019, Rajapack GmbH, part of the international RAJA Group, commissioned eWorks to develop a dynamic pricing tool based on Microsoft Excel and VBA. An experienced Excel developer was therefore sought for the technical implementation, who could develop a customized solution for the price calculation. The goal was a flexible and automated solution for price calculation and simulation of product prices that met the requirements of the German market and could be extended to other sales regions in the future. This Excel order programming was implemented as an independent solution as part of a fixed price order.

In the first phase of the project, an Excel workbook (.xlsm) was created with a central price sheet template in the corporate design (CD), which served as the basis for any number of product calculations. The initial plan was to calculate several products in a single Excel file, with each region being considered separately. The original structure was specifically geared towards the German market. As requirements grew, it was modified to enable price calculations for other countries such as Switzerland. Instead of a central file, each product was given its own Excel folder.

Thanks to a uniform and generic structure, automated import from different data sources could be realized. Purchase prices, sales prices and market information from up to ten different Excel files are imported automatically. A flexible import configuration allows column names, spreadsheet names and import areas to be customized. In addition, a dynamic Excel menu (ribbon) enables intuitive operation even for first-time users. Individually programmed Excel functions automate processing steps, protect cell contents from unintentional changes and enable complex calculations, such as rounding prices according to internal company regulations. A special formula reset function allows values to be flexibly simulated and reset again. The latter was used in particular for price adjustments in order to be able to quickly discard test values and restore original calculations. This implementation in particular required in-depth knowledge of Excel VBA programming, as complex macro functions were integrated.

Due to growing requirements, the tool was further developed into an Excel add-in (.xlam). All functions were centralized in order to maintain and provide new features more efficiently in the future. Thanks to this architecture, existing price calculations remained backwards-compatible, meaning that valuable work was not lost. The programmed Excel add-in starts automatically when Excel is started, but is only activated when a suitable price sheet workbook is opened. An integrated admin area makes it possible to customize configurations directly in the Excel VBA add-in without having to roll out a new version. (Typical customizing includes, for example, the adaptation of column names for Excel imports / Excel exports, the definition of standard paths for Excel data sources and target directories for calculations). The import process has been generalized in such a way that data from up to ten different Excel sources can be imported into the price sheets and prepared automatically. Progress dialogs and validations (missing mandatory fields, invalid values, etc.) ensure transparency and data security.

In addition, an export function was developed to generate updated price lists and ERP-compatible data sources at the touch of a button. These functions support the sales department in the event of short-term price changes and facilitate data integration into external IT systems. A logging mechanism records all export errors and changes and ensures traceability for quality assurance. All changes to product prices are therefore recorded in the log file, including previous and new item prices. Typical errors such as missing mandatory fields or inconsistent data formats are also clearly identified and can be quickly rectified. The digital icing on the cake was the VBA programming of an Excel-based data source for the ERP import. Here, a defined template is automatically filled with calculated price data and imported directly into the ERP system. Progress dialogs provide information on the status of the export and support users with systematic data validation through targeted error messages.

Since September 2021, the RAJA pricing tool has been maintained as part of a support agreement. In doing so, eWorks reliably supports its client as an "extended workbench", just as an internal software development team from the IT department would do. Continuous further development is carried out by experienced Excel programmers and Excel specialists, who regularly implement new requirements and carry out optimizations. Since the start of the project, over 50 man-days have been spent on development and expansion in this way. Thanks to the professional support provided by the Excel macro developers, the repricing solution can also be used reliably in the long term.

The RAJA pricing tool demonstrates how flexibly Excel and VBA can be used for complex business requirements. Do you need individual Excel programming but don't want to hire an Excel freelancer? As an Excel macro service provider, we can support you with our experienced Excel developers. Request a non-binding quote now! Tip: Companies that prefer to calculate with an Excel programmer hourly rate benefit from transparent pricing models at eWorks. Or do you already have existing Excel tools? Then take advantage of our professional Excel support for your existing Excel solutions!