Container Space Optimization using Excel VBA

Various industries, supermarket and logistic agencies face lot of problems regarding container space optimization. Proper arrangement and placements of items, bins, goods in container followed by arrangement of the container in cargo vehicles for transportation is very difficult task. Because of improper filling of bins in container same space may accommodate lesser number of bins. Also due to presence of void spaces in container, same number of bins requires more number of containers. This leads to increase in transportation cost. Companies are interested in optimizing manufacturing process to reduce cost, improve quality and obtain high efficiency. Unfortunately, most of industry have to pay more cost due to improper packaging of material. Attempt to provide optimum feasible solution for space optimization has been done previously by using methods like Biased Random Key Genetic Algorithm (BRKGA), Largest Area First Fit (LAFF) and various operation research techniques. But due to improper arrangement of bins and unavailability of proper optimization techniques the space gets wasted and damage of goods occur in container. To utilize the maximum occupancy of the container in this project we are proposing the heuristic algorithm solution with weight which consider the dimension of the items and container like length, height, width, weight. This project aims to generate a system for optimizing a container space using user friendly Excel VBA. We are taking advantage of data processing ability of Excel VBA. The program generated system takes up all information regarding the size, orientation, number, types of bins and does computation using the fielded information. At the end system shows the order in which bins should be placed, its orientation, profit achieved and animation of entire arrangement. The proposed system can be used in any platform which needs space optimization like industries, supermarket and logistics agency. Keywords—Space optimization; heuristic algorithm; Excel VBA;


I. INTRODUCTION
Nowadays, various industries, supermarket and logistics agency face numerous problems regarding a container space optimization. Companies are interested in optimizing manufacturing process to reduce cost, improve quality and obtain high efficiency. Unfortunately, most computational methods for complex machining system require significant computational resources to evaluate each parameter of a multivariable subject function. No method currently results in the same level of efficiency for all process.
Like cargo loading, proper filling of bin and planning is most essential factor in transportation like railways, airlines, trucks and buses. It is the proper arrangement of goods, items, bins into the available space of container. Due to improper arrangement of bins and unavailability of proper optimization techniques the space gets wasted and damage of goods occur in container. The proper arrangement and placements of items, bins, goods in container and then arrangement of the container in cargo vehicles for transportation has become very difficult task nowadays. To utilize the maximum occupancy of the container the optimization algorithms, simple algorithms, LAFF (Largest Area First Fit), and LAFF algorithm with weight consideration are proposed. All algorithms, simple algorithm, LAFF and LAFF with weight consider the dimension of the items and container like base, height, width, weight (as only square and rectangular cubic boxes are taken into considerations). These algorithm help to plan and fit bins properly in a container and give the optimize solution for arrangement of bins.
II. LITERATURE REVIEW Lot of research work has been done in the area of space optimization and use of VBA to generate optimized solutions. Our work is based on the various literatures available.
Jae-Dong Hong, Yuanchang Xie and Ki-Young Jeong [1], had discussed an efficient VBA spreadsheet algorithm and model for system optimum traffic assignment which deals with finding optimal traffic assignment scheme. To obtain this scheme, a mathematical program (MP) should be formulated and solved. The process of formulating an MP requires identifying all possible paths connecting each originto-destination pair through the network. Since the number of those paths turns out to be the number of the decision variables, consequently, formulating and solving such an MP is difficult due to the large number of the decision variables and constraints. In this paper, the authors suggest a new and efficient way of formulating an MP and develop a Microsoft Excel model with Visual Basic for Applications (VBA) to find the optimal assignment scheme for the traffic assignment problem.
Wang Yan and Hu Hongliang [2], presented Hydropower Computation Using Visual Basic for Application Programming. Hydropower computation is essential to determine the operating conditions of hydroelectric station. Among the existing methods for hydropower computation, equal monthly hydropower output and dynamic programming are the most commonly used methods, but both of them are too complex in computation and hard to be finished manually. Taking the advantage of the data processing ability of Microsoft Excel and its attached Visual Basic for Application (VBA) program, the complex hydropower computation can be easily achieved. VBA not only embodies the function of Visual Basic, but also demonstrates its powerful function in solving problem
Jose Fernando Goncalves and Mauricio G.C Resende [3], presented a novel biased random-key genetic algorithm (BRKGA) for the 2D and 3D bin packing solutions. In this paper they addressed the 3D rectangular shaped boxes into the minimum number of three dimensional rectangular shaped bins. They conducted a study to optimize free space in bins by using genetic algorithms based on random key. It uses a maximal-space representation to manage the free spaces in the bins. The proposed algorithm uses a decoder based on a novel placement procedure within a multi-population genetic algorithm based on random keys.
Teodor Gabriel Crainic, Guido Perboli and Roberto Tade [4], worked on Extreme point-based heuristics for three dimensional bin packing. One of the main issues in addressing three-dimensional packing problems is finding an efficient and accurate definition of the points where to place the items inside the bins. The performance of exact and heuristic methods is actually strongly influenced by the choice of a placement rule. In this concept container space is optimized by using extreme point base rule.
Andrew Y. Kudowor [5] focus on the development of the foundation of an integrated information interface to a Geographic Information System (GIS) in a relatively new environment, which is designed specifically for applications dependent on transportation network modeling (TNM). The paper reports the development of the foundation of an interface to Arc Map intended for transportation network development and analysis using the integrated VBA development environment of Arc Map.

III. METHODOLOGY
The Space optimization deals with providing optimum feasible solution which might not be perfect but chosen from best available. The Bin packaging problem can be solved by various methods as follows, 1. Biased Random Key Genetic Algorithm (BRKGA) 2. Largest Area First Fit (LAFF) 3. Solver using Heuristic Algorithm (Applied) In this paper we are going to talk about Solver using Heuristic Algorithm.

A. Solver using Heuristic Algorithm (Applied)
Visualizing 3D container loading plans requires a high degree of effort. In practice, container loading planning is usually done using specialized software, and Excel may not be appealing. In this project we are proposing the Excel Solver solution which can be used by end user with no specialized training. Space optimization using Excel Solver has been designed for simplicity above all available techniques.
The field of Space Optimization research mostly focuses on heuristic algorithms. A variant of the Large Neighborhood Search is implemented within the Space Optimization Solver. An outline of the algorithm is given below.
Step 1 (Initialization): Sort the items with respect to their priority, size, and profit. Sort containers with respect to their size and cost.
Step 2 (Constructive step): Use the First-Fit-Decreasing heuristic to pack the items into the containers.
Step 3 (Perturbation): Randomly remove items from containers, and randomly empty a number of containers. Sort the containers in decreasing order of the volume packed into them.
Step 4 (Re-Optimization): Use a constructive heuristic to repack the removed items into the containers.
Step 5 (Solution update): If the new solution is better than the best known solution, update the best known solution. Otherwise, revert back to the best known solution. If the time limit is not exceeded, go to Step 3.
The solution provides the heuristic solution in customized manner. It provides the solution as per our input to First-Fit-Decreasing in setup menu sheet and gives the result based on "Volume, Weight or Maximum Dimension (Length, Width or Height)". For animation of loading we can propose, as per our loading ease, "Wall Building, Layer-Building, or Column-Building".
The approach is much better due to its simplicity and no need of using specialized software. Space optimization using Excel Solver is designed for simplicity above all available techniques. This method consider weight of boxes, the distribution of the weight in a container, the use of multiple containers, and the order of shipment of boxes which are the main drawback of other methods.
IV. COMPUTER SIMULATION OF EXCEL VBA There are various assumptions considered during Container Space Optimization: a. No safety distance is required between items in a container. b. All items and containers are assumed to be rectangular cubes. c. No items can be prepositioned in a container. d. In addition to the no-overlap constraint and the (optional) compatibility constraints, every item must be supported fully on the surface it is resting. A. Structure of the worksheets Container Space Optimization Solver adopts an incremental flow of information, with subsets of data being kept in separate worksheets, as depicted in Figure 1.

B. Container Space Optimization Solver Worksheets
Container Space Optimization Solver consists of various Sheets which are generated using button available in the preceding sheet. The various sheets available are -1. Start -This is the first sheet of the Container Space Optimization Solver. It consists of login details (for security purpose we are adding login feature to worksheet) which are feed in database. The system check the details with input from user. If login details are not correct then it will not allow performing any operation. After pressing login button it will run the "Login" macro which takes you to next sheet "Setup Menu". Each container type has a width, height, length, associated cost, and the number of containers available. The parameters related to Container defined within this worksheet are Container Type ID, Container/Truck Type, Width (x), Height (y), Length (z), Volume, Weight capacity, May be used(Yes/No), Cost, Number of containers. 5. Solution -For each container a set of columns detailing the items packed into it will be generated. Column A also contains the list of detected infeasibilities, below the items in the first container. The columns in this worksheet are Item type name, X coordinate, Y coordinate, Z coordinate, Orientation, Total volume and Net profit 6. Visualization-This worksheet is optional, and if generated, it contains rectangular cube shapes showing the containers and the items in the containers. The user can move the shapes around to see for better or better looking solution. The current design will not automatically write this change in solution into the solution worksheet.

C. Container Space Optimization Solver Functions
Along with the worksheets, Container Space Optimization Solver consist of various optional features which are generated using button available in the preceding sheets.

A. TARGETED AUDIENCE
As the present work is concern about the container space optimization hence our targeted audience are those personals/industry those are related to packaging and transportation. Large industries have dedicated packaging and transportation department, but in case of small scale industry it might not be possible to pay for optimized packaging and transportation. This may lead to improper filling of containers, thereby requiring more containers resulting in higher transportation cost and lowering profit. The proposed Excel-VBA based solution overcomes the aforementioned limitation without requiring any specialized training or costly dedicated tool. It has widespread application ranging from local shops, Super Markets, Transporters, Packaging industries, Ecommerce Companies and even in warehouses for optimize use of available space to place the material and find it whenever needed.

B. IMPLEMENTATION OF SOLVER
Container Space optimization using Excel Solver is prepared and implemented to validate the use of Solver in real world. For that purpose we considered shipment order of one super market. The list of item containing in a light commercial vehicle (ASHOK LEYLAND DOST) was analyzed using the solution obtained by the algorithm. Container Space optimization using Excel Solver was found impressive in utilization of cargo deck. The results were able to save the cost of shipment by fitting more no of item in the same space.
VI. CONCLUSION In this paper we addressed the container space optimization problem which arises due to improper arrangement and placements of items, bins, goods in container followed by arrangement of the container in cargo vehicles for transportation, resulting in increased transportation cost and ineffective space utilization. The objective of this work was to generate a system for optimization of space in container using the data processing ability of excel with visual basic application (VBA).
The proposed system allows optimization of container space by automatically calculating optimal quantities, utilization of void space to accommodate more number of container, suggesting solution for proper filling of bins in a container, detection of specific location of required object staked in warehouse, reduction in transportation cost, increase in loading volume space utilization from 84-87% to more than 90%,increase in customer satisfaction by reducing customer claims about delivery of waste space and shortens order cycle time from 102 days to 95 days by streamlining the process. Moreover supplier will gain more profit from increase in sale opportunities of about 3 to 6 % as customer orders will increase. The future work can be done on design of proper space optimization algorithms (heuristic algorithms) and CAD model for trucks and any other vehicle by considering the dimension of the container like weight, height so that maximum space can be used for further transportation.