The year is coming to an end soon, it’s time to get a small sample of a few mandates done by our developer and technical support team this year. A useful little summary for companies who want to have examples of concrete productivity gains and automations achieved with Excel VBA BI.
Operation management mandate example
- E-commerce is on the rise. Several companies have asked to automate product management including all features to automate their transactional websites.
Solution: A well-designed Excel utility, using automatic loads and formatting with Power Query. Isolated calculation constants and instructions programmed in Visual Basic (VBA)
Healthcare related mandate example
- In pandemic year, a personalized dashboard that shows precise statistics linked to performance indicators.
Solution: The Power Query add-in for downloading and formatting data from many different sources, while Power Pivot allows us to make precise computational metrics based on performance metrics, all in Microsoft Power BI.
The purpose: these interactive dashboards are made available to specific users.
Accounting related mandate example
- Implementation of the accounting standard IFRS17
The new IFRS17 standard affecting the insurance industry fundamentally changes accounting policies and financial reporting. It has important implications for processes and indirectly for taxes, products and investments.
Solution: Using Microsoft Power Query inside Microsoft Excel allows the design stage of the project to detail the different loads needed before automating the transformations later in the process Microsoft Excel, which is the perfect software to validate the results. In the next step, Excel can be used to validate the results from different sources in parallel. Finally, we draw up control reports and hamper the final results.
Construction related mandate example
- Automation of quotes (submission)
Several construction and engineering companies have asked us to automate quotes’ creation, each project has its own characteristics related to their specialties, but the method is similar. Whether it is our kitchen and bathroom renovation customers, our insulation expert, our roofing customers, a display booth manufacturer or the manufacturers of prefabricated houses and chalets: the structure of the solution is similar.
Solution: An Excel utility developed in Visual Basic for Application (VBA). All the utilities have a navigation system, a detailed entry sheet, a mask tab for the parameters (drop-down list, cost price, profit margin calculation, then a series of developed custom reports such as the quote submitted to the customer, annexes, work orders, purchase order and of course an export mechanism in PDF format.
Non-profit organizations (NPOs) related mandate example
- Accountability
Several non-profit organizations must periodically meet the requirements of different governments. Regularly, these are detailed statistics, cumulating the activities carried out for beneficiaries compared to historical data, evaluating the cost of services rendered versus the cost of administrative costs. These reports frequently include comment boxes.
Solution: Frequently, we automate one or more data loads for statistics and sometimes data from the accounting system. Using the VBA programming language, we create custom reports that will simply be sent to the government.
Conclusion: In this period of labor shortage that affects many businesses, Power add-ins, the Visual basic application programming language and Microsoft Power BI allow us to automate recurring tasks.