 Hello supply trainers and welcome to our third life event of SC2X. First of all, sorry about the delay Okay, so this is our third life event and here I'm with me. It's Ahmed Bilal. Hi, guys Okay, so the plan is the following. What we like to cover in this session is I'm gonna be giving a brief overview of Dexam Let me share my screen to do Just one second, okay So what I would like to cover is what we would like to cover is is the following We're gonna start just giving a brief overview of the final exam just final remarks that are important for you then the main focus of the life event is gonna be a Solving a couple of work problems. So I'm gonna be in charge of the first one about a transportation procurement a problem And then a Bilal will help us to solve one a problem related to discounted cost loss And at the end we'll open to any questions that you might have about the exam itself Or maybe about the contents that will happen there in the exam without further ado, let's say get it started Let's start talking about the final exam So as you know, the final exam is an open book book exam So this means that you can use the KCD So you can use the key concept document to solve the problems in the exam You can also use the slides that we shared with shared with you. You can also use your own note So any a material that you have related to them to the course you can you can use it you can use it The main goal of the of the exam is to assess your knowledge. So it's it's all about assessment and non-learning. So that's why We won't be providing any feedback or solutions During or after the test. Okay, so I'm gonna be clear with it with this So no feedback or solution will be provided and then at any time. So this is not new We have this for the we had this for the for the meeting and this is a policy that is Consisting across the different SCX courses Okay, please keep that that in mind So regarding the timing so the final exam will open next Wednesday. So that's tomorrow July 3rd Usually will open at 15 15 hundred a UTC and it will close one week after so we'll be closing on July 10 Again at fifteen hundred UTC. So the exam will be available for one week However, please remember, please keep in mind that this is a time exam So this me this means that once you start the exam when you click on the start exam You will have only four hours to complete the test You can select that the specific day when you want to want to take the exam However, once you start you have only four hours to complete the test Okay, so let's talk about and the content so the exam and Will will have a four problems So he will be covering the Basically all content weeks so the first block of the of the course which was from weeks one to four One two three and four and the second half of the course which are the week the content of weeks seven eight nine and ten Okay So basically all the content that was all the greater content that was available for you is going to be part of this final exam Please keep in mind that the final exam is worth a total of a 45 percent of your final grade So you are a play in an important piece of your final grade in the final exam So Let's talk about some technical fields that are very important So please remember that you cannot use your the edx mobile app to take time exams So you have to use web browser. That's a constraint that we have and additionally we Extremely recommend you to use your computer so you can use your desk for or you can use the laptop But please to use a computer to solve the exam The exam is designed to be sold in excel so you can use excel But also you can use different Optimization software that you are familiar familiar with so you can use sass or ample Finally the the owner code so remember that this is an individual exam So the work must be on your own so no Collaboration in in the final exam so the staff you can get it getting touch with us, but please just Ask us about clarification questions or no questions about the content itself But maybe if you are not understanding the question, please send out an email to s at S2x at MIT that edu and we'll be happy to help you But keep in mind that this is just for clarity Clarification question is something in the question is not clear for you If you are not getting what the question is asking please reach us To ask Additional, please don't post any questions or any comments in in our discussion forums or any other website So we're gonna be vigilant regarding this phone and we'll be kept keep some eyes in the discussion forum But also we'll be searching in different web web web sites Okay, so with that I will open to I will continue with a procurement but before that let me see if you have any any questions I'm going Okay No, we don't have any any question What the only question that you have is if the event will be available after After this live event. Okay, so we'll make it available So we'll post all the content that will show today and also will be will be a Putting the link of the big the video in week 11 Okay, so all the material will be available for you after After and we close this live event. Okay, so let's get started. So let's solve our first problem So it's gonna be it's it's a problem that is related to transportation procurement and Let me talk to you about the problem. Let me Let me read this paragraph. So we have this company a golden house Which is a largest distribution of wine in the US and the company is currently a Opening a full fulfillment center in Columbus And the idea of this fulfillment center is to attend the new e-commerce channel of the of the company and You have been tasked with securing the tracking services to handle all drug laws from this fulfillment center to four regional distribution center that are located in Atlanta, Kansas City, Minneapolis and New York Okay, so basically the company is opening a new fulfillment center and is looking for a tracking services to connect This fulfillment center to a four regional distribution centers So these are the the lanes that we are looking for a to to hire for tracking services All of the lanes are a departing from From columbus. So columbus is going to be the origin And the final destination of these lanes are Atlanta, Kansas City, Minneapolis and New York So you are responsible for hiring these tracking services that are going to be serving these these lanes What information is available to solve this problem? So we Were given with the the distances the distance that connect the origin and the different destinations and these Distances are expressed in in miles. So for example the lane that connects Columbus with with Atlanta it the distance is 567 miles And we have the same information for the order for the other lanes So the you decided to to run an auctions and you received some bits from three main Carriers and these carriers are a b c a a b and c And what they provide is just the bits meaning what what they will charge you if they If they get the the lane for example carrier a carrier a will charge you 10 Point three a dollars per mile. So this information is measuring dollars per per mile. Okay, so What you need to do is you need to to decide How you're going to be Allocating the different lanes to Atlanta, Kansas City, Minneapolis and New York to the different carriers So basically the allocation of the loads that you have to send through these lanes So we have a for this for the first part of this problem. We have only two constraints So the first one, of course, we need to satisfy satisfy a demand So associated to each of the lanes we have a demand and this demand is measured in the number of loads That should be a transported for example for the first lane Columbus, Atlanta There's a demand of 180 loads that should be a fulfilled Similarly, we have that information demand information for all the lanes and that's what we have in the column that is named demand So that's the first constraint that we need to satisfy the second one is related to to capacity So the carriers express express their capacity in terms of the number of tracks that they can That they can they can provide you for example Carried a it has a capacity of 150 tracks career b of 250 and finally carried c Has a capacity of nine nine nine hundred different tracks So this is something that we need to also keep in mind when we are Allocating the the tracks of the different to the different carriers So these are the only two constraints that we need to satisfy demand and and capacity And of course we would like to minimize minimize costs. Okay, so then Let's solve the problem So let's start with question number one and the question number one is asking How many track track logs will you award to each carrier in order to minimize transportation costs? Okay, so basically it's asking how many tracks or how many track logs will Will award to the different the different cars and to solve this problem. Let's let me go to excel And I think I have to share stopping. Uh, stop sharing this I'm gonna be Uh Sharing my nice cream. So Let me see if you can see Okay, we have it. Okay. I'm gonna make this this bigger Okay, I already put some of the information that is available for the for the problem An idea some initial initial steps So for example, what information is available that was saying we have the distance to the different lanes First lane is columbus atlanta second cancer city and so on and so forth So these are the distance and these distances are measured in in mass Then we have the bits The bits For the different carriers the different lanes and these bits are measuring dollars per mile per mile So the first thing that I did is just to convert these Uh bits that are in Dollars per mile to just dollars and this is just simply multiplying The bit the dollars per mile by the by the distance So this is just a multiplication of 10.3 times 560 65 and I did the same for the different for all the carriers and for the different lanes So now we have the bits we have the rates but and these bait these rates are just measuring in dollars So this is what a carry a for example We charge us if we transport one truck log from from columbus to to atlanta. So these are Per the per truck locked. Okay so, uh, let's After that what I have is an space so different cells that's going to serve as decision variables. So this Here i'm going to be a or the optimization the optimization engine will be replacing the number of truck load that will be assigned into the different living carriers. Okay, so let's A compute the the total cost. So remember that we are trying to minimize this this total cost And this is just going to be the sum product of my rates That are dollars per truck or per load and the number of loads Okay, I'm using just the sum product a function of Affix it Okay, so I already have my object in function Then I need to add my constraints. Remember that for this question. We have only two constraints First one that we need to fulfill the demand So we need to make sure that all the loads that are assigned it to the carriers so the sum of this row should be Equal to the to the demand. Okay, so just i'm assuming All the loads are going to be assigned to each of the of the lines Okay, I'm going to be doing something similar but now with the with the capacity Okay, I'm going to be summing all the loads that are going to be assigned to carrier a And that value should be less or equal to the capacity or to the number of truck that is available for carrier a Okay, I'm going to be repeating this And we are all set Okay So we are trying to minimize cost. We have our decision variables and we already have in place our constraints So let me go now to solve it Okay, I have it solved it with me I'm going to be just adding a few things I'm going to be selecting cell c2 As the object in function Then I'm going to Minimizing the the object in function. I'm going to be selecting the decision variables Decision variables. I saw them a bit. My mouse is lost My decision variables is all the cells of our color yellow then I'm going to be adding my constraints. So only two constraints. The first one is That sales From f 25 to a f 29 should be equal to the demand equal to sales run h 25 to h 20 28 Okay, and finally The capacity constraints similarly so the sum this sum So sales from sales 29 to e 29 So that should be less to the to the capacity I'm trying to do that Okay, I added that constraint Capacity constraint. I'm going to be selecting simple simplex LP as a solving method and just solve it Okay, so we find the solution Okay, just to make sure that we are fulfilling all all the constraints. So we take a look to the sum Of what of the loads assigned to each of the loads So we can see that we are fulfilling fulfilling the the demand And also we can take a look to the capacity To make make sure that we are not exceeding the number of trucks available in each of the of the lengths. Okay So that's that's done And the question was was asking how many Loads should assign to each of the of the credit. So the answer to this question will be 150 loads assigned to carry Carried a 200 to carry b and 310 to carry c. Okay So that will be the solution or the answer that we should be you should be a provided. Okay So now let's move on And go to the next question Question number number two. Okay. I'm gonna stop in sharing this excel And let me Share with you again the My screen. Okay. So question one is done So let's focus on question question two And question two is asking the following So To avoid handling a split assignments You want to make sure that each lane is awarded to only one carrier In other words only one carrier can be awarded all the volume of a lane For example of the all the volume shipped to atlanta 180 trust log must be assigned to only one carrier And the question is saying based on this new information how many truck loads Will you award to each carrier in order to minimize transportation costs? Okay, so this is is different because in our solution we were maybe using Two or maybe three carriers for each of the lanes, but this question is saying that we can We we have to award Each lane to one single carrier. Okay, so let's try to do it that on on excel Okay, when I'm sharing my excel So let's go now to problem two Again, I have I populated my spreadsheet with relevant information So you will see that now So besides having the the number of loads that we will be assigning To the different carriers for the different for the different lanes We need to add Additional decision variables and that this And those decision variables are going to be binary variables Because there is a constraint that is saying that the number of carriers assigned to each Lane should be only only one. So that's why in this part We are having these binary binary binary variables Okay, so the cost is going to be exactly the same as before So it's going to be just the sum product of the rates per load Multiplied by the loads that will be assigned to the to the carriers. So that's exactly the same We do have a demand constraint as before And we also have a capacity constraints are before so that's exactly the same as in In question number one What we need to add now is a the the new constraint. So we need to make sure That the sum of these binary variables is equal to one Okay, that only out of these free sales only one Cell could take the value of of of one So in this sense, we'll be making sure that each lane is awarded by only It's awarded to only one single carrier Okay, so we added this is a binary variables But now we also have to link somehow the flow variables that are the the the the number of loads with the binary variables So this is Similar to what we observe in the network design problem when we add the linking constraint So basically i'm going to be Taking the flow the x Minus the the y which is going to be my binary variable And that should be multiplying by a big number and that big number for me Is going to be just the sum of the of the demand Okay, i'm going to fix in this this value and that will be all Let me see i'm going to be Dragging the content and same thing for the other cells I'm going to be just check for one of the values. So here i'm saying that multiplying the integer variable minus The binary value multiplied by this big number. Okay, the the sum of the of the demand So we are all set and remember that all of these values should be less or equal to to zero Okay, this is a linking linking constraint Okay, so let's go to solver I'm going to be adding the object in function again. The object in function is Cell c2 and we are trying to minimize the value of this cell And Whenever choose a minimization. Okay, my decision variables now are different The loads assigned to the lanes but also a but also the The binary variables So basically all the cells that are color in in yellow I'm going to be adding constraints. I'm going to be adding with a constraint that that we had before A first one is demand So these cells should be equal To the demand that we observe for the different lanes Okay, that's done Then i'm going to be adding the capacity constraints again selecting the columns Sorry, the this this the row And that should be less Or equal to the capacity of the curves the number of tracks Okay, so that's similar to what we had in for problem Question number one, but now we need to add Three additional constraints the first one is that All my binary variables should be binary actually, right? So I'm going to be selecting all of the cells And I have to make sure that that's taking a binary a binary value then I'm going to be making sure that the That the sum that this sum Is exactly equal to two one That's exactly equal to what we have in rows of cells h 34 to 37 And finally we need to add The linking constraints and the linking constraints is All of of this And that should be less or equal to to zero. Okay, so my right right right hand side is going to be just zero Okay, I think we have all the constraints going to be solving the problem We have a solution Okay, again, let's see if we are fulfilling the demand. Yes Let's see if we're not exceeding the capacity. We are okay with the capacity Our third constraint is now that each Lane is a water to one single carrier. Yes, we can see for example Atlanta is Assigned to carrier carrier c Kansas city in a similar way The Minneapolis is assigned to carrier b And finally Columbus New York is assigned to carrier carrier one. Okay, so this is what what we want so the problem was the the question was Was asking the number of carriers The sort of the number of loads and again, that's what we have in this in this sense So 80 loads assigned to carrier a to 100 to carrier b and finally 380 to carrier c Okay, and with that I will I will stop so let's see if you have any questions. So before going or moving to the next To the next question Okay, so let me see if you have any any any any any questions Okay Okay, there's a question from a He's say the youp So he's saying when do you set the demand equal to the amount and when do you set the demand greater than the than the amount So I think it's I would say that it's safer if you set as an equal constraint Especially when you are designing a network when you are dealing with a network design problem in those cases when you set A demand constraint and using the greater or equal and you're you have involved some A label of separate constraints. So this label of separate constraints make a might make you to To fulfill more demand that that is needed And that's reflected in sending for example in this example sending more flow that are required Because the constraint in saying that you have to at least fulfill a specific value of the demand Okay, so in this particular problem, I think you are safe. So you can use greater greater or equal But as a general recommendation, I will encourage you to use the use the the equal equal sign Okay Another question Okay, should we use an equal restriction with demand always okay again my recommendation you used to to use the equal sign Again Will the exam be harder that this or similar to this one? So this problem this particular problem was taken from large one of c2x So you can expect a similar level of difficulty for this upcoming upcoming exam as well I'm going to be showing a with you the solver box I think was that was not visible for you But you will have after we finish this life event So you will have the the excel file and you will have also the solver settings with with it Okay, so with that without that let's let me um Add it to to bilal Cool. Thank you. Thank you sir. Gio Yeah, so let's switch back to their presentation mode But that's the I think how to switch to to the power point Let's stay with this. Can you help me with it? Okay, yeah great okay, so This is a problem that now we will be talking about so I'll give you like Few seconds so that we can actually read through this question Okay, so what is this question about so this question is about pet feed And they are a chain retailer that sells animal food And they have to make a they have to decision and they have to make a decision And they're considering two options option one is that they can They can rent a store an option two is actually Is they can actually Purchase the store So these are the two options that they they have to decide and then there's a lot of data That's actually provided in this question to help us figure out which is the best decision to make Okay So and as you would have like guess to reading through this question is that This is a question that's heading towards NPV so what we need to do is really is to figure out the the values Are both these options and then identify which which option has the largest value? That's a very traditional and standard Finance question and a very important one indeed Okay, so you see there are a lot of numbers there and we'll we'll talk about those numbers But I mean that's really the the punchline is that There are two options and we need to make a decision on which option to go for Okay So we also have been provided with this table and generally in the exam you we would provide you this sort of table So you could always like copy this table and paste it in your excel and then take this as a starting point Okay, so and here you see The sort of questions that you can get in this exam Now like I said before like this question is really asking you to determine the net present value of two options buying and renting But as you would see in the exam, we would also ask you like some intermediate questions But they all lead to Sort of the ultimate goal, which is to figure out the NPV So why don't we actually figure out the NPV and you would see like all the questions That have been listed here would automatically get answered But in general you see like three big questions here. We need to figure out a depreciation We need to figure out the free cash flow and then finally we need to figure out the net present value Okay, so now i'm gonna open an excel sheet Yeah, switch to excel Then switch to excel And then share with index Okay You're ready to go. Okay. Awesome. Thank you sir. Okay So let me zoom in here Okay, let me zoom out. Okay So really like I said, there are two options buying renting. So let's do it for The option of like buying the store and then we'll do it the for renting The good thing is that the process is the same, you know, the numbers would change So here we would see like sort of a template that I've already created. So we have the discount rate and text rate mentioned here This is really the table that has been given to you as a starting point And then here I have Sort of a framework I like to use for NPV calculation And we'll talk about it once we reach to this point But let's start by first like populating this table So the first item that we need to enter Is the revenue and then we this is the time period year one two three So the revenue as you can see is basically is 50 million. So In this table Everything is a million And the revenue remain continuous throughout the next three years Now let's talk about operating expenses. Now if we are Buying the store we also are responsible for maintaining the store And so there's like a maintenance charge and if you read through it that is around 45 million And that again remains the same throughout the years And obviously if we are buying the rent is zero, we don't have to I mean it's our store. We don't have to pay any rent Okay, so Then we have the earning before interest taxes depreciation Also known as EBITDA. So in this case, this is very simple. You just take the revenue and subtract the Expense 50 minus 45 this comes out to be 5 million And again, you can just scale it to all the years So the next thing is depreciation and this is really a tricky thing But it's again very easy to calculate if you know how to go about it So the question says like we're using straight line depreciation and the formula for the straight line depreciation is basically you Take the original value, which is the 18 million you subtract the salvage value the salvage value is the value of the asset at the end of its Lifetime so which here in this case is mentioned 20 million Okay, and then you divide it by the total Total number of errors this machine will be active, which is three in this case This comes out to be 20 And again, this value remains the same in year two As well as in year three and in year four This goes down to zero Okay, so then what we need to do is to calculate the earning before interest in texas. This is Really we take this Value And we subtract Sorry, so I think I Made some mistake here. Yes, I can let me see if I'm using the right numbers here Oh, sorry. So yeah, I put in the wrong value for the upgrade. This is supposed to be 10 million But this was a good sanity check. So I was getting a earning before interest in texas of Minus 20 and something like that. So while this is possible, this could definitely happen but this was definitely raised a red flag for me and So and when I reviewed that you could see that the store upgrading expense was 10 million But I had incorrectly Actually used the value for the rent which was 45 million Okay, so now we have the a bit here The next thing that we have to do is to calculate the texas. This is very important. The texas is applied on The abit and not the abit. So that's something you need to be really careful about The tax rate is 0.1 The tax comes out to be two millions per year And we could then again just scale it across all the three years Finally we have the the income after tex, which is really the abit and we subtract the Tex from here It's good to know that some students are really following you because we receive at least five people that Commended that we're using the right value for the for rent for for buying Okay, so so this is basically the so we have reached to a point where we have the now net operating income after tex Okay, the next thing that now we have to do is to figure out the NPV. So I like to use this framework It's a very comprehensive framework and let me just quickly go through the framework and then we'll fill in the numbers So cash flow is basically equal to these seven steps. The first is the investment which in this case basically is the value of the asset which is 80 million So minus 80 Then this is after tex abit. So this thing is basically the same as net operating income after tex So you could like, you know plugging the numbers, but you would get the same value as this so I'm going to just copy the value from the above cells Then we need to add depreciation again This is a very common mistake that people make so depreciation is not a cash flow It is an accounting procedure And while it impacts the earning as you saw here But basically It needs to be added back because it's not a cash flow So that's what we will be doing here. So we will now add back the depreciation While we are calculating the cash flow Then we have the salvage value. So i'm not sure if we have used this same terminology in the In the course, but salvage value is really the market price of the asset after It has completed its lifetime period So it has been again provided here in this question if you see it is I just want to make sure you use the right number here Um, so I think it's 20 million Yeah, 30 million after three years. Yeah, so you add it After three years Now five in six is something that you don't need to be concerned about and we will not test you on these three two things Uh, but just for the sake of completing the framework. I provided these two things but text on book gain just to A little bit tell you about it. So sometimes once you sell your um Asset of and the end of let's say in this case three years You could you might have to pay some texas. So that texas needs to be deducted and similarly if there's any opportunity cost That also needs to be deducted from your cash flow of calculation again These are something that you don't have to worry about In this course and in the exams, but I've added it just for the sake of completeness Um net working capital again. It's in this case has been specified. Nothing would happen So again, this is something that you'll not need to be concerned about So we're done with our numbers now. What we need to do is to just add those things up And we'll have the cash flow For all the years so Here you go. So we have the cash flow From year zero to year three Now we need to sort of discount those cash flows And so again the you guys probably already know the formula, but the way I like to do the thing is that you take the You take the free crash flow you and then divided by one plus the discount rate which in this case is um 0.09 okay and then raise to power The year that we're talking about which is In this cell is zero And then again, you could apply it to all the years. So if you look at again this formula But we're taking taking this value divided it by one plus 0.09 raise to power three Okay, so we have now the present values And NPV is just about summing those values So we just sum these values And here we get our NPV. So again a very good framework framework that you can use to Calculate your NPV Now you can do the same thing with renting really nothing changes. It's the same The only I think the important thing is that number one we When we are renting there's no maintenance charges But we do have to pay rent obviously which is 45 million in this case And then the important thing is that there's no depreciation And that makes a lot of sense because you're not purchasing anything. There's no asset that comes on your Book so there's no nothing to depreciate but that remains zero throughout rest Everything remains the same. It's really just copying and pasting the above formulas into into this Sheet as well, and if you calculate the NPV After this should be P and this should be NPV It comes out to be 11.4 million. So really now we have the two numbers if we buy The value is 31.6 if we rent it's 11.6. So what decision would you make? Obviously we would go With the first option There is an opportunity to create 20 million more Value while going with the first option So this is the end of the question and if there's any Let's let's see if there are any questions on If you guys have any questions, so so before going to the question. So Villana, have you used the NPV for a formula that it's built in in excel? Yeah, absolutely. You can use that I wanted to show it The formula so that you can understand what happens under the hood But feel free to use that formula it should give you the same answer Okay, so that will replace instead of computing the the pbs the present values Yes, you don't need to calculate the present values It will give you the net present values straight away. Okay. Awesome. So that's also alternative, but you can you can use So let's say let's stop this and I think I have a couple of questions for you Villa okay so The question from from from uni. So He or she saying do you always subtract salvage value to get a depreciation? Yes, you always do that. So sometimes you would say that the salvage value would be zero in many cases Like the the statement would be like, you know, it will depreciate and at the end of its Lifetime it would have no value in that case salvage value is zero. So you just take the original price And divided by the number of fewer that machine or asset will be active but the the formula like I said for straight line depreciation is the The price the paying price minus your salvage Value and divided by the number of fewer that That asset or machine is active. Okay. Awesome. So we have just a final final questions so Let's go back So, uh, Taiyou is asking about so going back to the first problem and the procurement the transportation procurement problem So asking about what was the purpose of the linking constraints? So remember that you have to you use these linking constraints as the as the name says to link integer variables with decision variables and that's that's very important in this problem because if you are opening a lane If the binary value for a lane is one You have to make sure that you are sending some some or you are allocating some loads to that to the lane Same thing with the neighbor neighbor design the design problem Okay, so if you are not using for example the facility if If the flow that is coming out of facility zero is because that facility is is is close So that's the the user of this linking con constraint constraints and another question is Saying that what's What's is there any Special function enter in the excel in setting up the binary cells or you only have to indicate the binary function in solver So there is no other function So the only thing that you have to do is you have in solver you have to select All the binary variables and you have to set them up as a binary binary variables So this is done in in solver not in in the spreadsheet itself So final questions how many numerical questions and multiple choice questions will be will be there So we'll have four problems in the in the final example Three of three of them is going to be mostly in numerical And one of them is going to be more about a quality So it's going to be a series of check boxes. I think it's a similar format to what you had in in in the meter example Okay, thank you will art for for taking the time and for solving this this is problem any final a Comment today to decide All the best for the final and guys have done a great job throughout the course And I wish you all the best and I hope hope you like you'll do amazing in the final Okay, please keep in mind that the material that we we show today is going to be available for you the video and also the the excel files And please take exam the exam as soon as you can So don't wait until the the last day and do your best and the best of luck in the exam See you the next opportunity. See you. Bye. Bye