Create a Bill in Excel for Planning Effortlessly

Bill in excel for Planning

hello everyone welcome to excel Highway your One-Stop shop for all your Excel needs in today's video I want to share with you a very nice file that I created if you want to analyze your um supply chain in regards to MPS which is Master production schedule and MRP material requirements planning so I've built this simple file for you that you can use to do both which are the both processes are backbone and supply chain and and eventually what you can get is an understanding in the dashboard of your Gap to your net demand meaning if you can fulfill your demands and what is the cost of whatever solution you found and of course everything here is going to be based on your parameters your business your analysis so the the file has a few different sheets and I've changed the colors so you understand the difference between there's the in put sheets which are suppliers bone build material inventory data demand data there are the let's say the engine or the U algorithm of this file which is the MPS bomb explosion and MRP and there's the dashboard which is what I'm showing you and this is where you can change basically um your SE supplier selection and the date and you can see the impact so here for this this demo I've just created some information in the toy World um and you'll see that throughout this uh video so let's start let's start with the suppliers so the suppliers sheet has four different tables there's the Finish good supplier table where you define the name of your supplier the country and the production lead time okay so three parameters um and of course you can just add another supplier give them a name I don't know like uh Canada and the production lead time let's say four okay whenever you add a country make sure to add that country over here we'll get to that in a minute okay so I'm just going to take that out same thing for raw material supplier name of the raw material supply the country and the production lead time and what I've did what I did is I've created uh list okay you see the finished good supplier and the raw material supplier that's going to be used for um in data validation to select that supplier so make sure that you put the right supplier in the right location okay here there's um this is going to be used as a drop down list for the transportation possibilities and here for each country you need to set up the um um well you can sort it by country so we need to if you need to add a line for the sea and air lead time and delivery cost okay this is just dummy data of course all through the um the file but just so you understand obviously uh air transportation is usually times five or whatever the cost of sea transportation and but of course the lead time changes and you'll notice that the lead time uh is always in months here so production lead time and transportation lead time in months so if you have a new country you need to add it here and add a CN Air U lead time and delivery cost as it it's needed for the dashboard and the last table in supplier is where you define which supplier can produce which product and what is the production cost and you'll see that there is also finished good in raw material here so fa toys for example they can produce three different products our toys bz as in Brazil can produce also three products and also our toys us so they all three can produce everything of course you can Define that some cannot as I did in the raw materials so you'll notice in the raw materials I have one supplier that can produce five parts one supplier that can produce six and one supplier that can produce four so this limits your flexibility which is very common um okay so this is the supplier part you need to set this up you know when for just to start get things started and usually you shouldn't change that too often maybe just when you change suppliers boom or Bill of material is where you set up the relationship between the products and the Bill of material or the components so you can see in my case I have um three uh finished good products uh in the toy World fireman superhero and dog dolls and I have the pill of material a head a body a hand leg accessories and motor okay and here I just write the number per uh per product so a fireman doll needs one head one body two hands two legs eight accessories whatever that means and five Motor Parts I just wanted to make it a little bit interesting so for example the dog does not have accessories or mortar and just to make some changes cuz otherwise if it's just body parts it would be very um unanimous but of course this will match your world if it's you know building tables computers um I don't know making a pizza whatever so you'd have different components for different products and this is where you play with it so if you have more uh build of components you can just add it add them over here to the right like uh I don't know um eyes so once you do that this will have an impact on the um uh the rest of the file uh it is limited to I think about 10 Columns of course um you can add more it just means that you need to change a bit the formulas but it's not very it's not very complicated uh products is unlimited you can add as much as you want okay so you could have 20 uh 100 again you just have to change a bit the formulas but that's not that's even easier inventory data this is where you need to uh key in basically your onand inventory and whatever is in transit the onand is supposed to be captured in the date of today so you have a column for product a column for date a column for quantity and a formula column that basically uh groups everything to the to a monthly bucket because you'll see everything here is in a monthly bucket so for example fireman doll the date of yesterday has 50 this is supposed to reflect the onhand quantity and everything you see April May June July um that's supposed to reflect future uh inbound deliveries and you can see I can put whatever order I want it doesn't have to be um based on the dates okay so if if this could be empty by the way you could have no inventory which is okay but if you have inventory and usually do then you need to key that in so so that has an impact on the um um you know net demand demand data this is where you key in this is just for finished good product the date and I have usually like to uh distri to um understand sales orders and forecast you don't have to do that which what the main thing is that you have the product date and the quantity and again also here there's the uh monthly grouping to the uh first of the of the month so this is basically your demand this is what's going to trigger everything okay H you can have more than one line per date per per month per item no limitation of course this table can be as long as you want same for inventory okay so uh just before I continue to the engine part I just want to um let you know that uh I'd appreciate if you could subscribe leave a like leave a comment if this or any of the other videos uh help feel and this is a growing Channel and this really helps um and I'd be posting content regularly okay so up until here this was all the information that you need to key in bomb and suppliers once per file probably and inventory in demand that would be constantly whenever you want to update the information MPS um I don't want to get too in technical into the uh formulas itself a lot of formulas here some are more complicated some are less complicated what you need to to understand that this um sheet takes the list of products you see from the bomb takes the onhand data from the inventory the production lead time that's taken from the dashboard we'll go to that in a minute in a few minutes and that depends on the supplier you select the demand itself that's being pulled from the uh demand table the inbound delivery that's being pulled from the inventory data uh basically it's some ifs okay it's not that complicated just don't want to waste too much time you have here the column the row of the dates okay so you see it's the cycle of the demand the cycle of the inbound delivery closing inventory okay so that's um it's not realistic because you see negative but this is just the inventory you see for the first month it's the on hand minus the demand plus the inbound next month it's the closing minus the demand plus the the other inbound etc etc then you have the net demand which here you basically it only shows wherever you're negative and this shows you what you need to order okay so this is the net demand and the planned order this is where um the lead time takes into account sorry so basically since for example right now there's a two Monon lead time so it's going to the planned order is going to p pull two months ahead so that's 670 you need in June it's going to appear that you need to order it already in April okay and this is why you see the Gap because those two because of a two-month lead time basically they they should have been ordered in February and January so you're going to have a gap if you work with this supplier so this is the MPS sheet okay so as you see it has it's has formulas up until about 20 items uh again if you have more you can just drag the formulas more and this will work for the other sheets bomb explosion um so this this um sheet will explode the bill of material uh for each product so you can see here basically there's the um this row will Define how many basically there's a maximum number of materials okay it's looking at this and the concept is for each chunk of raw materials we want to build the explosion per month so you can see that this is basically the main driver for this sheet whenever this number is equal to the max I want to go back to one I want to jump ahead of month so I'm starting over so if I had less for example if I had four raw materials then you'd see that I would jump after four to the next month and basically for each month I have the um the uh uh requirements basically it's pulling the requirements which is this index so it's pulling whatever number you have over here and it's multiplying basically by uh the MPS part the plan finished good order so think of it this way it's taking this number over here one and multiplying by whatever you have here okay and if you do that for all the products eventually you understand that for head you need 200 units okay only for superhero doll in this case in month of April you need 670 for firman 200 and 350 it's assuming it's the same item of course it's not the same item but it's just for the example okay so this is this sheet you don't need to touch anything here or change uh if you add raw materials then this will move automatically okay so if I add another raw material like eyes so you will now see eyes okay with no data because there right now there's no data but if I add eyes and one okay so now you see that it do did explode it so this is pretty easy to um to update um okay MRP which is the last line last part is very similar to MPS from a structure point of view the demands are coming from the bom explosion the onand and inbound is coming from the inventory the lead time is coming from the dashboard based on the supplier selection and you have the closing inventory net demand similar to the MPS and eventually the planned raw material order and again you can see the Gap from the re requirements because of the lead time okay so again you have four sheets talking about data three sheets that are manipulating and basically um translating all the data to um what you really need and here we have the dashboard so the dashboard you can change the date this will change all the dates throughout uh the file in the MPS bomb explosion and MRP um because because for example if now you're in May and you have data for May it's important that you show the date of May it doesn't matter which day because I'm taking the again a first day of the month so the summary table shows you basically what is your Gap in finished goods what is your Gap in raw material you can sum it although sometimes it doesn't make a lot of sense and the total cost of your selection there's a difference between whatever supplier you select because the cost of the uh production it's what we set up here the production cost and also the delivery cost so this is where it's getting interesting so you can see for example I have a gap in the first product so I can select a different supplier okay and you can see I still have a gap and I can switch to another supplier and the Gap is gone the cost went up which makes sense usually if you Outsource to um uh cheaper production areas sometimes you pay with the um larger lead time uh which is what I wanted to simulate here so again I also here see see here I have a gap so I can change to Turkey or to Sweden and here I get the error that that's not it's not able you not you're not able to produce this okay also you can see that I have here Transportation so sometimes even if you're um if you have a gap you can first try maybe air maybe that's a cheaper solution in this case it doesn't help okay so you can play with these and you you best change the this to uh A supplier that can support you and make sure that this is minimum and this is minimum and of course if you have more items like the eyes you just need to copy this this row and change it to the um the um New raw material and um um this will cap catch this automatically so if you key in something that doesn't make sense um okay all right so this is it so you have the data input the engine and the dashboard um if you like the video please hit that like button leave a comment and subscribe to the channel I hope you enjoyed this video and uh let me know what you think take care