Production and Inventory Control by Batch in Excel
Originally published: 02/09/2020 08:43
Last version published: 04/02/2021 10:24
Publication number: ELQ-22492-7
View all versions & Certificate
certified

Production and Inventory Control by Batch in Excel

With this spreadsheet, you will have full Production and inventory control including batch division. Contain VBA code.

Description
With Production and Inventory Control by Batch, you will have full Production and inventory control including batch division. Select the components of the Production Order and perform the Production Entry.

Select the batches to be consumed in the system and keep the History and Traceability of all transactions carried out.

The System also allows you to return to production if necessary, and the respective amount will be returned to the Inventory!

The Consumption Report shows a list with the components consumed, batch, date and time of consumption separated by Production Order!

Create, control and print Production Orders in a practical and organized way. The spreadsheet simulates an MRP system - Material Requirements Planning, with entry of goods,control and use of materials. Choose the materials to be used in the Production Order, click on 'Register PO' and let the macro do the work for you.

A history with all Production Orders will be available to facilitate the creation of new orders with just one click.
At any minute you can check the 'PRODUCTION' panel that contains all the information and history of all the Production Orders created and finalized.



* Reports, Charts, Indicators, Dashboards and more!

Select the year and see reports prepared with data separated by month with the main indicators of your company, including: Total production, Production Value, Input and consumption of components and Finalized Orders.

Track monthly production with annual and monthly data available to you with just one click!

You can also select a Production Order, and view various details on the charts quickly and simply, including value added per production order, monthly production and returned components.

Two inventory control tables: Component inventory and Component inventory separated by batch, the tables include search engine! Easy, objective and practical!

Indicators with status of Production Orders.

Graphs with entry demonstration and monthly component exit are also available.
All graphs and indicators are dynamic and automatic, you only need to choose the desired year.



* Planning of Material Needs

Make the control by changing the 'Status' of the desired Production Order, select 'Separating Materials', enter the quantity and that quantity will be separated for this production and low on stock. At the end of production, inform the total produced and in case of leftovers, this balance returns to the stock.

Manage your production and inventory. Control the entry, separation and use of materials necessary for the production of your company. Track inventory reports and automatically generated indicators.



* Other features

Follow the main reports, graphs, indicators and Dashboards with essential information for management, monitoring and planning.
Production Order Printing: The form is ready and completely filled, just inform the number of the Production Order and print.

If you have any ideas or changes you want to make to the spreadsheet, get in touch!

----------------------------------

USER GUIDE:

1. REGISTRATION: In this area you will register the Components that will be used in the Production Orders. Fill in all fields carefully.

2. ENTRY: Check in the Components as your company receives them. Keep your inventory up to date. Inform the batch if it exists, if you do not want to include the batch for this component leave it blank!

3. PO REGISTRATION: Fill in the fields with the information that will be used in the Production Order and click on "Register PO" (Production Order), the information will be saved in a database but you can edit it later if you need to. Production orders are saved and can be viewed in a list in cell "D5".

4. PRODUCTION ENTRY: Perform the production entryindividually separated by PO, select the batch and the type of transaction. When adding a new entry, click Update batch. Consume: Production entry will consume the components of the PO; Return: Is anything wrong with the amount or selected the wrong batch? Just make the return and the balance of the selected components and the respective batchs will return to the stock. And the entire transaction history is maintained in the Consumption Report.

5. PRODUCTION: View details of all PO's. Stock Output: number of separate components for this PO, select Separating Materials, Waiting for Production or In Production and the quantity informed will be reserved for this PO. After finished, select Finished and fill in the Finish Date.

6. PRINT PO: The Production Order assists the team with information and guidance needed for production. The form is ready and set up for printing.

7. REPORTS: General: reports with important information divided monthly. Component Stock: here you will see the real situation of your stock, income, division goods and component exit. Use the searches to find the desired component or batch. Consumption Report: system transaction history.

8. DASHBOARDS: Visual presentations and control panels with the most important information. Here you will have access to graphs and indicators for analysis, management and decision making.

This Best Practice includes
1 Excel Spreadsheet

Acquire business license for $30.00

Add to cart

Add to bookmarks

Discuss

Further information

Create Production Orders, manage and control your stock with batch segregation, perform production entry selecting the batch to be consumed and the entire transaction history is maintained in the Consumption Report.

You like efficient tools to help you!

You don't like to use spreadsheets with VBA code.

Reviews

  • Be the first to review this Downloadable Best Practice

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Production And Inventory Control By Batch In Excel

The user community and author are here to help. Go ahead!

  • Kristaps Krankelis
    Hi,
    Can you asign a batch number to completed production order? And does the completed production order add the produced stock to entry tab and can you then reuse this as next component for futher manufactirung proceses?

    Thanks
    arrow_drop_uparrow_drop_downReply reply
    • Marcus Servatti
      Hey Kristaps Krankelis, i hope you doing fine.

      When you make a "production entry" by selecting consume at the Production tab, you need to select a batch, and then, only then this entry will consume the components and the batch selected.

      The Spreadsheet doens't have this function to reuse the produced amount as another component (but it could be done), you could just do it manually by adding another component.

      Send me a message and we could talk about developing it.
      arrow_drop_uparrow_drop_downReply reply
      • Marcus Servatti
        You could also "return" the amount consumed and change the batch (if you consumed it wrongly).
        arrow_drop_uparrow_drop_downReply reply
      • Phat Nguyen291
        hello i want to type the amount of consume component manually. Can you help to change this.
        Also i want to add residual product automatically when type in production entry tab. For example Product X : Componant A-> ProductX + Component B + Scrap . In which component B automatical add entry on Entry Tab for component in.
        arrow_drop_uparrow_drop_downReply reply
        • Marcus Servatti
          Hello, i hope you are doing fine!

          When you register a production order you can set the amount necessary for a component. When you add a production, there's a formula in column "O" if you won't use the previous data registered, you could just erase the formula and type it manually in this column.

          Let me know if it worked.

          Take care,
          Marcus Servatti
          arrow_drop_uparrow_drop_downReply reply
        • Mohammed AlAlShaikh
          Can you please share a video of this in action? Thanks
          arrow_drop_uparrow_drop_downReply reply
          • Marcus Servatti
            Hey! Thanks for the interest! Unfortunately i can't right now. I am trying to add demo version of it but since I am at my phone am having some problems. In the meantime if you want I could send it to your email.
            arrow_drop_uparrow_drop_downReply reply


        0.0 / 5 (0 votes)

        please wait...