Collaborate on Bilty Format in Excel for Logistics with Ease Using airSlate SignNow

Bilty format in excel for Logistics

[Music] hello and welcome to another video in my series on management science where we utilize Microsoft Excel to model and then solve simple everyday business problems now two reminders before we get started into the problem number one if you are watching this video on YouTube please refer to the description below the video and there you will find a link to the corresponding blog post and on the blog post you will see a download link for this very file so you can follow right along with us step by step number two to keep the video a reasonable length I have built in certain features automatically into the spreadsheet primarily the naming of cell regions so I have gone through and named certain cell regions so that when we use formulas and the solver addin we can use those more intuitive names rather than an obscure cell region now there isn't a whole lot of background to this problem basically we have a company that is named delivered to you and they have two factories a distribution center and two warehouses and they need to ship their goods from Factory to Warehouse in a way that minimizes the total cost now one component of this problem that is different or in addition to the previous Logistics problem I did is that the shipping arcs or shipping lanes in the problem some of them have capacity constraints so from Factory to Warehouse goes over a rail system so there's theoretically no limit when we go from Factory to Distribution Center then Distribution Center to to Warehouse we have a capacity of 50 units and we cannot exceed that so we have the added constraint of capacity in some of our shipping lanes or arcs as they're technically called the easiest thing to do before we actually get into working the Excel spreadsheet is to draw a diagram of our problem so we can visualize it we can understand how things are going to flow how it's going to look and then after we draw the diagram we'll come back into Excel and solve it so let's go ahead and do our diagram okay so let's go ahead and sketch out our problem so we can see it visually now in the delivered to you network we have what are called five nodes and nodes are just buildings or offices or whatever where Goods either flow out of flow through or flow two in terms of that's where they terminate so in this problem we have five nodes two factories a distribution center and two warehouses so let's go ahead and label those in the top left we have Factory one so we'll call that F1 down below we have Factory 2 we'll call that F2 in the middle is our distribution center so we'll call that DC the top right we have Warehouse 1 and down below Warehouse 2 and again these are called nodes now connecting our nodes are what are called arcs these can be thought of as shipping lanes but we're going to call them arcs because that's the technical term so from Factory one to Distribution Center we have an arc same thing from Factory 2 distribution center from distribution to Warehouse one distribution to Warehouse House 2 now we also have two more Factory one can ship directly to Warehouse one and Factory 2 can ship directly to Warehouse 2 now one thing about these is that they are directional so we can think of them as vectors they have a Direction so they do have an arrow indicating Direction now if you notice Factory 1 is not going to ship to factory 2 and and Warehouse one will not ship to Warehouse 2 and so forth so the green arrows here are called arcs and it's just a path along which Goods flow and then the direction they flow now we do have some information from our problem that we can go ahead and add to our diagram for example in Factory one we know that it's going to produce 80 units 80 units produced Factory 2 is going to produce 70 units so if you add that up that's a total of 150 units now if we go to Warehouse 1 it needs 60 units in Warehouse 2 needs 90 units if you notice that also equals 150 so important component of these problems is that however number of units are produced at a production facility or factory has to be the same number of units that are demanded by a warehouse so production has to equal the amount demanded now another piece of information we have is the shipping cost per unit for each Arc so from Factory one to Warehouse one we know that that is $700 a unit from Factory one to the Distribution Center is $300 from the Distribution Center to Warehouse one is $200 then we get on the fact Factory 2 Factory 2 to Distribution Center is 400 Distribution Center to Warehouse 2 is 400 and Factory 2 to Warehouse 2 is 900 and again this diagram is not geographically proportionate or correct it's it's simply a model so so far we have our five nodes Factory one and two Distribution Center in the middle and Warehouse one and two on the right hand side for each Factory we have a production or a supply and then on the right hand side we have the demand or what is needed by each warehouse and then for each green Arc we have a unit cost for shipping so again here in a few minutes our goal is going to be to minimize our shipping costs and finally the last bit of information we have for this problem and what makes it different than the previous Logistics problem I did is that each Arc through our diagram does not have infinite shipping possibilities so some of our arcs have a maximum number of units that can be passed through them and it could be just a function of time or the size of the container or whatever it might be but there are limits in some of our arcs two routes that do not have have many limits are direct routes so Factory one to Warehouse one theoretically has sort of an infinite capacity and Factory 2 to Warehouse 2 also has an infinite capacity now in our problem this is described that from Factory one to Warehouse one is a rail system so theoretically they could ship as many as they would want direct from Factory 1 to Warehouse one the same holds true for Factory 2 to Warehouse 2 that's also a rail line that theoretically has an infinite capacity now Factory to distribution and distribution to Warehouse is done by truck so only a certain number of units can fit on a truck there is some limited capacity in each one of those nodes and from Factory one to distribution is 50 maximum from Distribution Center to Warehouse one is also 50 maximum from Factory 2 to distribution is 50 and finally the same for distribution center to Warehouse 2 is 50 so let's write out our Arc pairs so we can see exactly each way Goods can travel through our Network so these will be our arcs okay so we can go F1 directly to Warehouse one we can go Factory one to our distribution center we can go Distribution Center to Warehouse one and that covers our top section we can we can go Factory 2 to distribution center and distribution center to Warehouse 2 then we can also go Factory 2 directly to Warehouse 2 now our goal is to minimize cost now we have two constraints I call it con one and that is that our supply must equal our demand and again up at the Top If we have 80 and 70 units coming out of the factories combined the warehouses require the same amount so it's 150 on the left and 150 on the right our constraint two is the capacity our raar line capacity is theoretically infinite we could load up as many train cars as we like and ship those directly from Factory to Warehouse in a truck our capacity is 50 maximum at a time so we have a constraint depending on which Arc we take through a distribution center and out of it or direct from Factory to Warehouse given our supply demand constraint our capacity constraint and the goal of minimizing our overall cost in this model what we're going to have Excel do is figure out exactly how many units should go through each Arc okay so as you can see from our diagram we're basically trying to minimize our shipping cost with in certain capacity constraints and Supply demand constraints and to put those you know in a simple way is that everything that flows out of a factory has to go somewhere it has to go into the warehouses in this problem it's 150 Supply and 150 demand if we don't if those aren't equal there's there's no other place for our Goods to go so they have to be equal and of course other constraint is the capacity whenever we go through the Distribution Center cuz we're using um a truck so let's go ahead and take a look at our spreadsheet and see how we have modeled it in the left hand side you will see all six of our shipping arcs F1 to W1 F1 to DC DC to W1 Etc these are the same shipping arcs we had in our diagram now on the right hand side of that box you'll see unit cost that's simply the unit cost we were given uh in our problem the thing here to notice though is that the direct shipping method from Factory to Warehouse is relatively expensive you know compared to shipping by truck and that's sort of the gist of the problem is that you know if if shipping directly from Factory to Warehouse were Dirt Cheap we would just throw all of our stuff on the train and ship it but the reality is you shipping by rail is significantly more expensive so our Optimum solution is going to be some mix of the two some over Rail and some by truck and that's really at the heart of this problem there in the purple you'll see our 50 unit capacity when we're going through the distribution center on truck to the left of that you will see the light blue cells for ship that's actually what Excel is going to find for us so it will tell us exactly how many units to ship in each shipping Arc then based on that we're going to tell Excel to you know find that shipping combination in a way that minimizes our total cost which is the redish sell there at the bottom so we're going to minimize cost by changing our shipping cells subject to certain constraints of capacity in the purple and then supply and demand there on the right let's look at that box real quick over on the right if you notice we have a cell for each of our five nodes F1 F2 DC W1 and W2 and on the right hand side in the darker blue we have our supply and demand and if that node is putting product out into the network it's positive so Factory 1 Factory 2 are 80 and 70 respectively if that node is taking product out of the network like the warehouse houses are then that value is negative so you can see Warehouse 1 is -60 Warehouse 2 is 90 now if you notice the Distribution Center is zero and that's because everything that is coming in distribution center is going right back out so the net product moving through Distribution Center is zero everything in goes right back out now like in many of these type of problems we have set this up with an equal sign as a constraint and what we're going to do here in a second is we're going to put a formula in the net flow column that checks to make sure that whatever goes out of factory one in total equals 80 units and then the same thing for factory 2 and so forth and we have to double check to make sure we have to put a constraint on the distribution center for it to be zero and the same for fact for warehouse 1 and two so that is just a equal constraint that is going to put parameters into our Excel solver that is a check on our solution now luckily we don't have to do a whole lot more to go ahead and solve this using Excel we do have to enter a couple formulas and the first one is kind of complex I'm going to do my best to explain it but sometimes it's hard to explain so we're going to go to the the cell next to the F1 node under net flow and that's where that's where we're going to put our first Formula and the formula looks like this it's a conditional sum formula so equals Su if now if you look at the little tool tip with this formula you see it says range criteria and sum range so I'm going to go ahead and type in all that information then I'll go back and explain how it's going to work so this will be from then I4 and ship close parenthesis now what exactly is this going to do it's easiest to look at it from right to left instead of left or right so we'll start with ship first thing it's going to do is go to the ship column which is our light blue and then it's going to look over in cell I4 which in this case is F1 and then in the ship column it's only going to add things together where the value in that cell which is F1 is the same as the value in the from column wherever there's an F1 in from it's only going to add those cells and then put it in this case in j4 so for this formula all it's going to do is add the first two cells in the ship column because over in from it's F1 which is the same as I four now it's only half of it we have to do minus sum if and this one is set up very similar to I4 ship and this part of it is going to do the exact same thing but it's going to look in the two colum over on the left hand side now if you notice there is no F1 in the two column cuz we're not shipping to any factories okay we're only shipping from factories so actually the second half of this formula is going to be zero so we'll go ahead and hit enter now everything here is going to be zero because we don't have any numbers in and of course the great thing about Excel is we can just drag down and it fills in everything automatically and it messes up my border there I'm not going to worry about that so this was this is going to do a sum IF function to make sure that whatever our net flow in or out of a node is the same as the supply and demand we stipulated in our problem really we only have one more thing to do and that's total cost I've used this one uh before in previous problems so equals sum product now again this multiplies two corresponding arrays you know two sell arrays so what's our total cost going to be how are we going to figure that out well it depends on how many we ship in each Arc so it will be dependent on this ship column and of course it will be a function of our cost so this is just going to be ship and unit cost so however many we ship in each Arc times the unit cost for that Arc will be our total cost okay so let's go ahead and open up the solver and solve this problem so we go ahead and open up the solver there we go now remember our objective here is to minimize our total cost so our set objective is total cost and we want to minimize that so we'll select the minimize button there below total cost we want Excel to do that by changing the shipping cells which is the light blue and we named that that region ship now we do have a couple of constraints our first constraint let me go over here if you notice we have four capacity cells in the purple that correspond to four cells in the shipping so we need only the four cells in shipping that are across from the capacity so we're going to highlight those four cells and that has to be less than or equal to capacity just how it looks in our cell region there so the four cells AC cross from capacity must be less than or equal to the actual capacity constraint which is there in the purple so go ahead and click okay now remember we have one more constraint and that has to do with our boxes there our cells there on the right and that is that the net flow has to equal the supply and demand that's why we have an equal sign there so add a constraint and we have net FL flow must equal our supply demand which we named Supply demand so net flow must equal Supply demand and click okay okay so make sure that make unconstrained variables non negative is checked because we're going to be shipping positive amounts of goods which is always good and then select solving method will be Simplex LP for simplex linear programming now if we have everything spelled correctly all the cell regions chosen correctly we'll go ahead and click solve and solver says uh solver found a solution all constraints are satisfied do we want to keep the solver solution and yes we do so we'll click okay so let's take a look at what we have here now remember we asked Excel to find our shipping quantities in each Arc So based on excel's solution we're going to be shipping 30 units from Factory one directly to Warehouse one uh 50 units from Factory 1 to the Distribution Center 30 from Distribution Center to Warehouse one and so forth so in each of our six shipping arcs Excel has found the exact amount that needs to go through each one and everything that comes out of the factories will end up in our warehouses Distribution Center will net to zero and you can tell that by looking over here on the right our net flow equals our supply demand at each node now our total cost is down here in the reddish cell and I'm going to go ahead and format that to Dollars there we go and what it found out is that the absolute minimum shipping cost we are going to incur within the given constraints and the unit cost is $110,000 if we were to change some aspect of the problem you know unit cost or capacity then the answer would be different but given our constraints and given the unit cost the minimum cost we can do this at is $110,000 thanks for watching and I'll see you next [Music] time