Generate Invoice from Excel Spreadsheet for Manufacturing Seamlessly

Generate invoice from excel spreadsheet for Manufacturing

welcome I am back with another free Excel template from ins honor comm in the previous videos we have seen inventory trackers for retail business as well as rental business this time we will be talking about the manufacturing business and this template can be used in scenarios where you manufacture your products based on raw materials that you purchase from your suppliers and then the manufactured products you will actually be selling them to your customers so if your business is dealing with such a scenario then this template can be helpful to calculate the current stock of raw materials that you have in your inventory as well as how many products you can make using those raw materials that you have this template could be helpful in making decisions around whether you will be able to fulfill a sales order from your customer and if you determine that there is not enough inventory of raw materials then you can place an order to your supplier to get more raw materials from them you can download this template for free from in Zahrah calm now let's go and look at the template to see how it functions this is the template where I have filled in some sample data so that I can illustrate how the template works we are in the home sheet where you can see there are a couple of links to articles which will be helpful if you are new to excel if you're new to using Excel templates from in Zara comm please check those two articles that will be very very helpful and then a list of instructions on how to use this template it's very simple so let's go through the the template quickly the product sheet is where you enter the list of your products so in this case I am assuming this is a juice or a smoothie making company where we create these products like the banana berry shake or apple banana shake and all these different products these products are made from raw materials which would be the fruits raw fruits and vegetables so let's go and look at the raw material sheet where we enter each raw material that we have in our and then starting inventory is how many of the eat raw material did you have when you started using this template so this is a one-time entry and in this case for example I have ten of each of the fruits and vegetables that I've listed here and then the reorder point is the quantity of each raw material at which you want to put a new order to your supplier to replenish the inventory for example if the number of apples I have in my inventory goes five or below then I want to place a new order to my supplier to get more apples so that's what the reorder point means we have calculated columns here and this one is especially the available now will tell you the number of units you currently have in stock and then to order will tell you about whether you should place an order or not because this is comparing the current stock versus reorder point and telling you whether you should order or not if you have to place an order those raw materials will actually be shown in red font to help you identify which one should be ordered and the purchases column is basically the how many units have been of raw materials have we purchased so far and this these calculations are taking this data from the order sheet and the be OMG that we will be looking at now so the Bo M sheet is the stands for the Bill of Materials here is where we will enter how the products can be made from the raw materials so let's take a simple example the banana berry shake is a product and it is one unit of banana very Shea can be made from one banana and five strawberries and ten blueberries together similarly apple banana shake can be made from two apples and two bananas so this is how basically it's the recipe for each product and this is also something that you would enter only once this is not something you have to do daily it's a one-time entry of how the product can be made from its raw materials the unit of measure is just for your reference it's not used in any calculation the last three columns are used in calculations so I'm not going to go into this because this is primarily just to help us calculate the inventory you can ignore this for when you're using the template but definitely do not edit it now the order sheet is where you will probably spend more time on an ongoing basis because every new purchase order or sale order should be entered here with all the details for example the purchase order p1 was placed on or order date of July 3rd and then this is for two products like apples and bananas and you expect those apples and bananas to reach your location or to reach your inventory on 4th this there are two dates mainly because just because you place an order to purchase your raw materials doesn't mean that you will get it on the same date so the more important thing important date here is the expected date because this is the date when the items reach your inventory so this date is used for all the inventory calculations and in this case we are ordering 50 apples and 20 bananas as part of order one sale s1 stands for the next order where we are selling that's why the order type is sale on 5th July we are selling it and it leaves our inventory on the same day and it is for five units of banana berry shake and so this means that we have created five units of banana berry shake and sold it which will tell the template to automatically deduct the inventory of all the raw materials needed to make banana berry shake so that's how the template figures out how the inventory should be calculated based on the purchase and sale orders you can enter as many order line items you want as many orders you want in this table and the notes column is just for any any comments or notes that you want to enter keep typing it if you want to add more columns you can just click and cell h7 and then type a new name and then you can extend and extend as many columns as you want at the top you have today's availability check so this is important because if you have a sale order for example and a customer wants a specific product you would want to know whether you have enough raw materials to make that product so for example apple banana shake how many do I have today so you have enough raw material to make 13 apple banana shakes so if I go to banana berry shake I only have I can only make three of those so this will be helpful to make decisions around whether you can fulfill the sale order as well as decision to purchase more raw materials necessary to make the product so this is how the inventory check or inventory availability can be useful to add more rows and more columns it's very simple just go to the last row off the table and then go to the next one and then type in for example any any order number and then this now becomes part of your table and you can choose whether it's a sale or a purchase order and then you can enter the dates when the order was placed that date when the inventory will reach or leave us because it's a sale order and then what are you selling as part of this order maybe we sold vegetable mix of two units for example so this is how you would enter a new row to the orders table if you want to delete a row click on the cell right click and then delete table rows so that's how you would delete a row so this is how simple the template is there are no hidden sheets or anything all the formulas are right there in the green colored cells that is where you will see all the formulas needed for this template please do not add any formulas and then the sales quantity here and the the product sheet is nothing but a formula that calculates how many units of each product have we sold so far and then that is shown in a conditional formatting gradient color indicating the darker green would be the maximum so in this case we have sold seven units of mango carrot so far and so that means that shows up as the best selling product and then anything which is darker red would indicate that it's the least selling product in this case there are a few which have not sold yet with zero units of sales so this is how you could easily track inventory for your manufacturing business where you manufacture products based on raw materials and you can keep track of inventory and estimate how many products you can make from the raw materials very very easily with this template if you have any questions please leave them in the comments and I'll be very very happy to respond thank you very much for watching this video