Replacing Manual Labor with Code – Development of a New VBA-Based Calculation Tool for a Manufacturing Company
A VBA-based prototype calculation tool was developed for a swiss EMS manufacturing company to automate recurring manufacturing cost calculations. The solution reduces manual work, improves accuracy, and presents results in a clear format, helping speed up quotation preparation and decision-making.
Ryan Kreis, 2025
Art der Arbeit Bachelor Thesis
Auftraggebende Swiss EMS Manufacturer
Betreuende Dozierende Richards, Bradley
Views: 6
The company’s existing Excel-based tool has grown significantly over the years, becoming complex to use and difficult to maintain. It required repeated manual data entry, involved disconnected data sources, and lacked automation, all of which slowed quotation preparation and increased the risk of errors that could lead to unprofitable contracts or lost orders.
The prototype was implemented in Microsoft Excel with VBA, using a layered approach for maintainability and future expansion. It imports structured ERP export files, calculates material, machine, and personnel costs, updates currency rates automatically, and generates evaluation sheets. Development included testing with real production data to ensure that the implemented functions reflected the company’s workflows and met operational requirements.
Although the tool is still in prototype form, it already delivers various advantages. Calculation times are short, repetitive manual steps have been eliminated, and results are displayed in a structured, visual format that is easy to interpret. This improves the speed and reliability of quotations, helping the company respond more quickly to customer requests while basing decisions on consistent, accurate data. The clear cost breakdowns and automated high-cost component analysis also provide valuable insight for purchasing and production planning. By adopting a layered architecture, the prototype enables further development, such as compliance checks, inventory analysis, or extended reporting features. These enhancements, combined with proper training and a company-wide rollout, could significantly increase operational efficiency, reduce the likelihood of costly errors, and reinforce the company's competitive position in a demanding market.
Studiengang: Business Information Technology (Bachelor)
Keywords Excel, VBA, Calculation Tool, Manufaturing
Vertraulichkeit: vertraulich