Production and Inventory Control by Batch in Excel DEMO
Originally published: 08/09/2020 12:12
Last version published: 06/02/2023 10:52
Publication number: ELQ-70707-7
View all versions & Certificate

Production and Inventory Control by Batch in Excel DEMO

This is a demo version of the Production and Inventory Control by Batch spreadsheet. Fell free to download and test.

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 NeedsMake 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 featuresFollow 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!Note: This is a demo spreadsheet. To buy this spreadsheet go to: 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

Marcus Servatti offers you this Best Practice for free!

download for free

Add to bookmarks


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.

Note: This is a demo spreadsheet. To buy this spreadsheet go to:

You like efficient tools to help you!

Note: This is a demo spreadsheet. To buy this spreadsheet go to:

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

Note: This is a demo spreadsheet. To buy this spreadsheet go to:

4.7 / 5 (13 votes)

please wait...