Introduction

A major logistics operation relied on an Excel-based model for estimating freight rates and calculating truck requirements. This resulted in: manual, error-prone, and inconsistent freight estimations; time-intensive process for procurement and planning; and limited ability to perform scenario analysis or dynamic forecasting

• Replace the legacy Excel-based model with a dynamic, interactive tool.

• Automate freight rate estimation and truck requirement calculations using predefined algorithms.

• Provide access control-based functionality for different user levels.

• Enable scenario analysis with variable inputs for better decision-making.

• Ensuring data integration from multiple sources (SAP, Excel, UI input).

• Balancing automation with flexibility for dynamic user-driven scenarios.

• Maintaining scalability and security while supporting 15–20 concurrent users.

• Freight Estimation Engine: Automated freight rate calculations across truck sizes, incorporating scenario analysis.

• Truck Requirement Module: Automated calculation of truck quantities and sizes per terminal, based on algorithms.

• ETL Connectors: Integrated data from SAP, Excel, and UI inputs for seamless processing.

• Real-Time UI & Dashboards: Role-based access, data visualization, and exportable reports (Excel, PPT, PDF).

• Scalable Architecture: NodeJS and MySQL-powered backend for performance, with security and load testing.

Technological Framework

Why these technologies?

AngularJS/ReactJS delivers interactive scenario analysis dashboards that business users can operate without technical expertise, while NodeJS and MySQL provide the performance needed to support 15–20 concurrent users with sub-second response times. 

Why this setup? 

Selenium test automation ensures the tool performs reliably under concurrent load, while ETL connectors for SAP and Excel eliminate manual data entry — providing a single, accurate data source for all freight estimations.

Why this setup? 

Direct SAP and Excel ETL connectors ensure freight estimation data is always sourced from authoritative operational systems — eliminating the inconsistencies that plagued the legacy Excel-based manual process

Takeaway

Zapcom replaced a manual Excel-based freight estimation process with a web-based tool automating freight rate calculations, truck requirement planning, and scenario analysis for a major logistics operation.