Container Space Optimization using Excel VBA

Container Space Optimization using Excel VBA

Manoj Suresh Paghrut

Dept. of Mechanical Engineering, SSGMCE,Shegaon.

Shubham Vikram Girhe

Dept. of Mechanical Engineering, SSGMCE,Shegaon.

Anuja Sham Ghodki

Dept. of Mechanical Engineering, SSGMCE,Shegaon.

Asmita Santosh Rawankar Dept. of Mechanical Engineering, SSGMCE,Shegaon.

Poonam Dnyaneshwar Tikar Dept. of Mechanical Engineering, SSGMCE,Shegaon.

Rohan Mukeshkumar Tiwari Dept. of Mechanical Engineering, SSGMCE,Shegaon

Abstract 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.

KeywordsSpace optimization; heuristic algorithm; Excel VBA; bin-packing problem

1. 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 multi- variable 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.

2. 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 origin- to-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

   with complex computation, visualizing, and secondary data processing.

   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 nvironment of Arc Map.

3. 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.

      1. 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.

4. COMPUTER SIMULATION OF EXCEL VBA There are various assumptions considered during

   Container Space Optimization:

   1. No safety distance is required between items in a container.

   2. All items and containers are assumed to be rectangular cubes.

   3. No items can be prepositioned in a container.

   4. In addition to the no-overlap constraint and the (optional) compatibility constraints, every item must be supported fully on the surface it is resting.

      1. 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.

         Figure 1: The flow of information between the worksheets.

      2. 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.

         2. Setup Menu -This worksheet is first input to the workbook. The parameters defined within the worksheet are described below.

            1. Number of types of items: Each item type has a width, height, length, associated profit, and the number of items available. The allowed rotations for the item type, and if the item must be, may be, or cannot be packed may be input later. Here only number of items are placed, due to constraint of Excel solver items value must be less than 100.

            2. Number of types of containers: Each container type has a width, height, length, associated cost, and the number of containers available. The user may also input if the container type must be used or cannot be used. Here only number of container are placed, due to constraint of Excel solver containers value must be less than 15.

            3. Item/Container labels: If set to Yes, writes the type of the item/ Container into the rectangle representing it in the visualization worksheet.

            4. First Fit Decreasing based on: An algorithmic parameter defining the order of items being packed within initial the constructive heuristic. Different settings may result in alternative solutions, especially for low CPU time allowances. The default setting is Volume. In some instances, Weight or Max {width, height, or length} may result in a better solution.

            5. CPU time limit (seconds): The amount of time after which the algorithm will end. As a general rule: the longer time allowed the better results will be obtained. Half this time will be spent to ensure that the organization of items within each container is sensible.

         3. Items -This worksheet consist of data related to items. Each item type has a width, height, length, associated profit, and the number of items available. The allowed rotations for the item type, and if the item must be, may be, or cannot be packed may be input here. The parameters related to items defined within this worksheet are Item Type ID, Name, Colour/Image file, Width (x), Height (y), Length (z), Volume, Rotations, Weight, Heavy item(Yes/No), Fragile item(Yes/No), Must be packed(Yes/No), Profit and Number of items.

         4. Container -This worksheet consist of data related to items. 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.

      3. 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. The various sheets available are

   1. Reset the Workbook

   2. Setup Items Worksheet

   3. Setup item-item compatibility worksheet

   4. Setup Containers Worksheet

   5. Setup container-item compatibility worksheet

   6. Setup Solution Worksheet

   7. Optional – Setup Vsualization Worksheet

   8. Engage Container Space Optimization Solver

   9. Feasibility Check

   The details for each of the function is as follows

   1. Reset the workbook: This is a quick way of deleting the data worksheets and resetting the Setup Menu worksheet.

   2. Setup Items Worksheet: Setup the Item sheet, if already exists deletes the existed.

   3. Setup item-item compatibility worksheet: Two item types are compatible if they can be packed into the same container. If this worksheet is not generated then the solver will assume that all item types are compatible with each other. If generated then, the user may choose incompatible item types.

   4. Setup Containers Worksheet: Setup the Container sheet, if already exists deletes the existed.

   5. Setup container-item compatibility worksheet: A container type is compatible with an item type if the item type can be packed into the container type (e.g. frozen foods and refrigerated / non-refrigerated trucks).

   6. Setup Solution Worksheet: Setup the Solution sheet, if already exists deletes the existed.

   7. Setup Visualization Worksheet: Setup the Visualization sheet, if already exists deletes the existed.

   8. Engage Container Space Optimization Solver: Run the code to Find Solution.

   9. Feasibility Check: This function is supplied for checking the feasibility of the data and the solution after manual alterations.

5. EXPERIMENTAL INVESTIGATION

   1. 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, E- commerce Companies and even in warehouses for optimize use of available space to place the material and find it whenever needed.

   2. 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.

6. 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.

ACKNOWLEDGMENT

We would like to express our deepest appreciation to all those who provided us the possibility to complete this report. It would not have been possible without the help and guidance of various people throughout the making of this work. A special gratitude we give to our guide, Prof. Chinmay Patil, whose contribution in stimulating suggestions and encouragement, helped us to coordinate especially in writing this paper. We are highly indebted to him for his guidance and constant supervision as well as for providing necessary information from time to time. We owe our deep gratitude to Dr S. P. Trikal,

HOD Dept. of Mechanical Engineering, and Dr. S. B. Somani, Principal, Shri Sant Gajanan Maharaj College of Engineering, Shegaon for their encouragement and more over for their timely support and guidance. We express our sincere esteems to our colleagues who helped willingly in completion of this system.

REFERENCES

1. Jae-Dong Hong, Yuanchang Xie and Ki-Young Jeong, An Efficient VBA Spreadsheet Algorithm and Model for system Optimum Traffic Assignment, International Journal of Applied Industrial Engineering, 1(2), 36-52, 2012.

2. Wang Yan and Hu Hongliang, Hydropower Computation Using Visual Basic for Application Programming, International Conference on Applied Physics and Industrial Engineering (2012).

3. Jose Fernando Goncalves and Mauricio G.C Resende, A Novel Multi- Population Biased Random-key Genetic Algorithm (BRKGA) for the 2D and 3D bin packing solutions, AT& T lab research technical report (2012).

4. Teodor Gabriel Crainic, Guido Perboli and Roberto Tade, Extreme Point-Based Heuristics for Three Dimensional Bin packing, Interuniversity Research Centre on Enterprise Networks, Logistics and Transportation (2007).

5. Andrew .Y. Kudowor, VBA integrated interface for transportation network dependent applications (2001).

6. R. Firdous and J.F. Devlin, BEARKIMPE-2 a VBA Excel program for characterizing granular iron in treatability studies, Computers and Geosciences Volume 63 (2014).

7. Gunes Erdogan, An open source spreadsheet solver for vehicle routing problems, Computers and Operation Research Volume 84 (2017).

AUTHORS PROFILE

Manoj Suresh Paghrut

Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj College of Engineering, Shegaon (M.H.)

Shubham Vikram Girhe

Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj College of Engineering, Shegaon (M.H.)

Anuja Sham Ghodki

Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj College of Engineering, Shegaon (M.H.)

Asmita Santosh Rawankar

Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj College of Engineering, Shegaon (M.H.)

Poonam Dnyaneshwar Tikar Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj

College of Engineering, Shegaon (M.H.)

Rohan Mukeshkumar Tiwari Bachelor of Mechanical Engineering, (2016-2020) Shri Sant Gajanan Maharaj College of Engineering, Shegaon (M.H.)