SAP Reporting in Excel
Delivering logistics efficiencies for leading international beverage manufacturer.
SAP data Analysis TO OPTIMISE SUPPLY CHAIN OPERATIONS USING EXCEL
stock distribution optimisation
We developed an optimisation algorithm with a university based computer scientist, then replicated it using VBA in Excel to complement the client's existing SAP software.
logisitical efficiency
The tool enabled the client to maximise order fulfilment between different warehouses while minimising stock movements and reducing wastage.
RAPID TURNAROUND
The client had estimated it would take around 18 months to make the necessary changes in SAP. We managed to deliver a solution that could operate using SAP exports in Excel within a matter of weeks.
The Client Challenge
One of the world’s leading soft drinks manufacturers urgently needed to increase the number of delivery vehicles they could process between two of their warehouses.
To facilitate this, they had to ensure there was optimal distribution of stock between their automated and traditional warehouses.
Their goal was to maximise the numbers of orders they could process while minimising the number of stock movements required between the warehouses.
The client wanted a tool that would process data exports from their SAP system, allocate orders for dispatch from each warehouse and specify stock movements to facilitate the dispatch.
Our Approach
After working closely with the client to understand the parameters and complexities of their existing systems and establish their goals and KPIs, we deployed a computer scientist.
We developed a weighted optimisation algorithm which ranked stock and prioritised orders based on data including current stock distribution, incoming stock, daily order configurations, minimum stock movements and BBE dates to ensure older stock was moved first.
Replicating the algorithm in VBA, we were able to develop a tool that allowed the client to process their SAP data in Excel. This enabled them to quickly process their data and identify issues such as shortfalls in stock to meet forthcoming orders.
It automated recommended order movements while allowing the client to override this manually to prioritise urgent orders when necessary. It also created outputs to advise the team whenever stock movements were required.
The Outcome
Our client was delighted with the automated SAP export analysis our tool provided.
They were able to increase the maximum vehicle throughput of vehicles from their warehouses from 180 to 250.
It also allowed them to optimise staffing levels at their conventional warehouse and ensure that the automated warehouse operated at maximum efficiency.
They saw wastage reduced as more efficient identification of BBE dates and batch numbers ensured that older stock was always shipped first.
By deploying the solution using Excel development was completed in a fraction of the time that the client expected it to take using SAP.