Webandmacros logo
Contact | Cookies

VBA Excel Macros - Optimal Sequence of Production.


Occasionally people in charge of production have the problem of choosing the optimal sequence of one or several lines of products. By sequence of manufacture we mean the order in which the different products will be made, therefore we will look for the best sequence fulfilling other objectives, like the non- existence of die times of manufactures, the reduction of time of change and adjustment of machines, the cancellation of delays . we consider the own- restrictions of each factory as well as the speed of machines, the capacity of human and material resources.

Obtaining the optimal sequence of production provides a decrease of process time, it also avoids possible delays when delivering the order.
Obtaining the optimal sequence of production provides a decrease of process time, it also avoids possible delays when delivering the order.

There are different techniques and methodologies to confront this problem, to find the optimal sequence which allows us making the suggested aim, also the methodologies can be the accomplishment of mathematical models by means of the application of heuristic methods cleared by the theory of the sequences.

The main advantage of using these methods is the optimal resolution of the problem, whereas the main disadvantage is the time you waste to the accomplishment of the intermediate calculations, the problem is we do not have this time in our daily work, leaving at one side the use of these techniques and following our own experience when taking decisions. By means of the macros vba in Excel we will be able to obtain the optimal sequence of production in just few seconds. Doing this, the Excel spreadsheet will solve the complex calculations allowing us taking the best solution.

Let's see the following example:

The company "Happy Candies S.A "dedicates to the manufacture and distribution of sweets around the world, they have a wide scale of different products: colouring candies, different sizes , different flavours. They use a machine that requires an specific process and time to make the candies.

At the present time the clients of the company are continuously complaining about the delay of their orders, so, the manager speaks with the boss production, discovering that there are excessive dead times. Immediately he asks for a daily report where it has to be specified the optimal sequence of the manufacture.

The boss production knows the tools to solve the problem, he has two options: first make a daily calculations in a manual way, spending too mucho time on this. The second option is using the Excel sheet and in just few seconds the problem will be clear.

VBA Excel Macros - Calculation and resolution of the optimal sequence of production.

Initial data

We have the following dates:

Time of operation : time of processing the product in the machine in order to finish the order of the client.

Time of delivering : Is the date in which the client ask for the order.

Date of reception : Is the date when we have the root material or the product half finished, ready to be processed by the machine.

Methodology.

Application of the theory of the sequence in one machine with the following theories:

  • SOT : sequence of fabrication wasting the minimum time to make the product.

  • LOT: Sequence of fabrication wasting the maximum time to make the product.

  • FIFO: Sequence of fabrication taking into account the incoming root material to process the order.

  • EDD : Sequence of fabrication depending on the date when the order has been asked by the client.

  • MOORE: Sequence of fabrication that optimises the global delays of the orders.

Excel macro - optimal sequence of production.

Vba Excel macro - optimal sequence of production.

Analisys and solution.

Every theorem gives us a sequence of production (depending on its configuration and on the quantity of orders to proceed) by means of comparing the different theorems we get the best optimal sequence of fabrication, as we can see in the following table

Excel macro - report optimal sequence of production.

Excel macro - report optimal sequence of production.

By means of the tool GANTT we show graphically the optimal sequence of production , obtaining in just few seconds the report that inform us about the reasons and the optimal sequence of production.


If you like, share it




 

www.webandmacros.net

www.webandmacros.net World webandmacros