Production management of metallic constructions made with an Excel book. It is a management, to carry out the entrance of the orders of the clients, and to shell those orders, in the necessary technical specifications to produce them. Also they are managed, the entrances of raw material, the state of the stock and statistics of consumption and yield are made. The book is composed of 17 sheets that are made visible or invisible, depending on what the user chooses, in the initial menu and that interact with each other, with interlaced formulas.
The Excel book was originally made by the client himself, but it had certain shortcomings and flaws. Our job was to stabilize it, expand it with more sheets, generate more macros and programming. Next we explain in more detail, its composition.
The data entry is made in the Workshop Sheet. Once the data has been entered, a button is pressed that activates a macro. This macro deposits the data in the clients’ order form. In the Order Form, you can also enter orders manually. But it is better to use the Workshop Sheet, because in addition to entering the order, there are buttons to print the order and the sheets that have to go to the factory. In this spreadsheet, there is also a stock alert table, with a macro that updates the information.
There is a sheet with predefined models, which can be mentioned from the Workshop sheet, when entering the order. This facilitates data entry.
Another sheet is used for Material Receipt, i.e. orders to vendors. Besides, in the same sheet, there is a table with the last price Kg of each raw material.
The Stock Summary sheet, shows the state of the raw materials, indicating the actual stock, the stock on order, the pending receipts, the reserved material, and the quantities to establish the alerts, when they reach minimums. Traffic lights have also been included for the stock alerts. Within this spreadsheet, there is also a table of stock alerts, which is the one that feeds the alerts of the Workshop Sheet.
All the raw material is expressed in Kg, that is to say, an order reflects measurements in cm or meters, but these measurements must be converted into Kg, using the specific weight of each material. In this way, we can establish a summary sheet to show the Kg. consumed of each material.
On another sheet, we calculate the yield of each material, on the difference between the purchase price and the sale price, for each order and for each material.
Apart from all this management, our client asked us to integrate in the same book, but in separate sheets, the purchase of sporadic and unusual material, the sale of this material, and its stock control. We show an image of each of these three sheets.
Finally, in the book, there are sheets to establish the printing of the manufacturing sheets, which we do not show, and through macros, the data to be printed is established. There are some more sheets that serve to complement the data, like the auxiliary sheet that we show below, and that contains among other data, the data shown in the drop-down fields.
Application made with Excel. To use this application you need to install the MS Excel program. The application is made in Spanish language. Note: The data inserted in the images are fictitious.
With the intention of improving the quality of the manufactured products, another application is being developed based on the concepts of this spreadsheet, but which goes deeper in identifying each raw material and its path in the different production processes, so that the traceability of the raw materials and the finished product can be obtained.
