Collaborate on Billing Statement in Excel for Logistics with Ease Using airSlate SignNow

Billing statement in excel for Logistics

foreign [Music] Highway your One-Stop shop for all your Excel needs in today's video I want to share with you a Supply Chain management file that I created in Excel that you can use to manage your supply chains as the name implies the file is basically a combination of previous videos that I created each their own part and here I'm actually grouping them to show you what you can do rather easily if you use all these tools if you're new to this channel my name is elad and I've been using Excel and Google Sheets for more than 10 years and I am constantly uploading content about those two softwares if you find that interesting please hit that subscribe button below so you don't miss anything out now that we're finished with the intro let's talk about the file itself so the file itself consists of a few functionalities inbound delivery report production outbound delivery there are sheets transaction log inventory bill of material parameters and dashboard and exposed build material which is a simulation sheet you'll notice on top I have a ribbon customized ribbon for the supply chain management which basically mimics the same thing so you can access this from any sheet if you want to learn how to easily create this ribbon just check out this video above I created about the office ribbon X which I very much recommend so the whole concept of this file is that you have user forms where you basically do something and everything is written in a transaction log so every action inbound outbound delivery or production will populate this sheet and that way we can use it for other sheets to track what's going on and you see you have a home button that takes you here the transaction log button takes you here and you can also access it from here which is very user friendly so let's start with the parameters sheet so in the parameters sheet this is where you set up the supplier names your raw materials unit of measure customers your products and your warehouses and of course you can add and all the drop down lists will include whatever you have here it's always looking for the last entry per column so you just have to add your input wherever you want and Everything Will Change the Define Bill of material this is where you set up your bill of material and I support up to five levels here meaning you could have for example the dyno toy it has this level then you can explode the head number one I think I put an example of that there you go we see arm so arm number one explodes into finger and nail so whenever you want to explode the dyno toy it will explode two arms and every time it exposes two arms it will explode 10 fingers and 10 nails as expected so this is where you set it up your bill of materials so these are both important before you and start things let's just start with the inbound delivery and you can see I can access it from here or I can go back to the home page and um click on the button I'll later show exactly how to do everything so the inbound delivery this is a user form a very basic one but very very friendly very useful if you're not familiar in how to use the user form please check out this video about user forms where I really break down how to do it I won't repeat it here but what I do have is the date always showing the date of today drop down list of suppliers that you saw before and the parameters selection of warehouses where I defaulted to raw materials because I'm assuming in the inbound delivery you we are receiving raw materials product so I have a product list what I received in unit or kgs and the quantity so I received five heads and I received doll heads yeah don't get don't get crazy just doll heads and I got 20 legs once I click on the submittent button I'm going to get this message inbound delivery added and I can check the transaction log all the way down I have these three lines the date the the supplier or customer the item the unit of measure the quantity which which warehouse and the inbound delivery which is the transaction type now let's say I want to um produce something so um let's say I have two customers Toys Inc I want to produce 900 units of Dino toy so I get this pop-up message not enough raw materials to process production okay so in the code itself I have a part that checks if I have enough quantity in the inventory of the raw materials okay so let's so I don't have enough so let's produce one so once I click on submit what actually happens I don't even have enough for one so let's take an action hero okay and I get this pop-up message I don't know why I don't see it production order added and now you see what happens when you produce an action hero you have a plus one for the finished goods Warehouse and you have negative for the raw materials that required for the action hero based on the bill of material that you defined over here so to produce an action hero I need this and this and this so that you have basically a plus to the finished goods and a minus to the raw materials that's how you produce the production the outbound delivery you select the customer and again the default is finished goods unless you you Import and Export and do nothing with the product so you could just use the same warehouse and you select for example an action hero I want to report an outbound delivery so I get this message outbound delivery added and I can see it here in the transaction log now it's from the finished good so I had the plus production the minus uh from the same warehouse so I am um balanced so those are the three um main processes that I have here and um what you can also look at is the explode sorry the dashboard the dashboard is just a simple functionality that you can use it has some pivot tables over here and pivot charts attached to them with slicers I've done these type of things a lot of time and share with with you on many um videos I'll attach an example if you want to see how to build this very easily I have all the back end back-end data over here all the pivots tables are over here they're all connected to the same slicers and just adding some pivot charts Justin is an example of what you can do um let's just look at the explode bill of materials this is a simulation so you could use you could take a look for example the dyno toy um so it shows you if it's possible or not too much so I get this message that I can't because I don't have 52. so um you can use that for uh uh simulating once you once you sorry forgot you need to click on this button and then you get the simulation that's why we got the pop-up message for the Nano toy because we're out of nail X we're out of we're out of stock for that item I just forgot you have to click on this button and that will explode um the bill of material so for if I just want to receive nail X I can also key in of course so let's get sorry should be unit let's get 50 units and now when I click explode although you saw already updated but now everything looks okay so um these are all the functionalities now if you're interested to learn how to build these things stick around and let's Deep dive into the VBA codes let's start with the easier part which is all of the um moving Between the Sheets so moving Between the Sheets each of these is a very simple code I will show you so each of them you can see calls a function called select cheat and the relevant sheet and sheets so it's power parsing a sheet a specific worksheet and this is the function for the select sheet so I'm calling unhigh cheats which is a function to you know Loop through all of the worksheets in the workbook and just unhide them using WD which is the parameter for the worksheet visible equals true so um I'm calling that just to make sure I don't have anything hidden because if it's in this won't work I'm going to activate that worksheet that I call that I want to select and what I want to do now is hide everything apart from that worksheet so again a loop working on all the worksheets and with a simple if if the name is not equal to the name then I will hide it that's it that's the that's the very simple part of selecting the sheets now let's show you the uh the user forms I mean I've talked about the user forms I want to see if there's something specifically um say different than the ones that I had before [Music] not here let's check production okay I'll show you also the the um the function to explode the bill of material that you see over here so it's actually quite simple it's a double Loop um but you know what I actually covered this in another video that I'll post right here it walks you through how to explode multiple build materials so to make this work you need to build user forms that have uh functionality of pasting values in a other sheet explode bill of materials multi-level parameters with some navigational buttons and that's about it hope you enjoyed the video if you did please hit that like button subscribe leave a comment and I'll see you next time take care now