All right everyone welcome back to our next linear programming or LP problem. We're going to do one that is a slight variation on the first one. A little more complicated but nothing that we can't handle. So pause the video, paste the question inside your spreadsheet. Read it once, read it twice if you've not done so already. As a reminder, when you go through it, when you read these things, you're always looking for what is the objective? What's the point of the problem? What am I trying to do? Usually it's a max or min problem. What are my variables? What are things that I'd like to know the answers to? What my decision variables as a manager. What am I trying to decide? And then of course, what are my constraints? I've seen people, some students, a little more detailed than perhaps I am. They color code, these as they read through. They take out the highlighters, digital or actual physical and they highlight different parts of the problem to correspond to each one. You don't have to be as fancy but just kind of keep track of what they are and maybe jot them down as you go. Alright, pause the video and read the problem if you haven't done so already. Alright, ready? Let's go through it. We will start to build our template. If you already have a template you can open it and just paste it on there. I like in the beginning to kind of build it a couple times so just go through it to recreate it and that helps me sort of internalize what's going on to understand what's going on. If you need it for any other project, you can pull it up but as your first learning just keep building it over and over again. So you understand what's going on. So we have our variables, we're going to color code those green. Of course, we're going to label them very clearly. We will have our objective like what's the point where we want to max and then we have our constraints. Little longer word problems. So it's going to take us a little longer to get through it but let's get started. The PC tech company assembles, Oops assembles, there it is. And then test two models of computers, basic and XP so there's two types of computers. For the coming months the company want to decide how many of each model to assemble and test. Okay, makes sense? No computers are an inventory from the previous month. And because these models are going to be changed after the month, we don't want to hold any inventory after the month. Okay, so we're basically building everything and get rid of it. It believes that the most you can sell this month are 600 basics and 1200 XPS. Now this is interesting. So the most you can sell. So obviously you're not going to produce anymore. You can sell right off the bat. We're getting a constraint. We're getting constraints. I don't want to produce any more than 600 basic computers and then 1200 XPS. So let's start. You can already see what the question is going to be. How many of each one should I produce? So let's create some variables here. And I want the number of basic computers and then the number of XPS. If you just put down basics, I guess it's okay as long as you understand it is a number. If you just say basic computers, that's not a number, a computer is not a number, right? So, I want the number of basic computers, the number of XPS, whatever they are. I'm going to put in some dummy numbers. My four and my five from last video were terribly wrong, but they worked out just fine. So enter dummy numbers. There you go. They worked out just fine to help me make sure the spreadsheet was working. And right away I have constraints sort of buried in line three here. Most 600 basics, so at most 600 basics, and at most 1200 XPS. Notice in one sentence, I got two constraints that's common, so break them up as needed. So, let's build at our table. Remember we always go three past the green cells, the variable cells. So that gets me to see and then 1, 2, 3 gets me to f. I don't know how many constraints are going to be in this long word problem. So, I'll just build out some random number of rows, highlight that, and then we label the headers here. It's LHS for left hand side sign and then RHS for right hand side. Our sign here is going to be less than or equal. The only one that gets a formula is going to be my left hand side and that formula is always going to be the same, which I could drag down. So at most 600 LPS, so hopefully you agree that that's less than equal to and then 600 and less than equal to and that is 1200. I'll center everything and make it look all pretty. The formula for this, I wanted to sort of count how many XPS I have. Remember how we do that? That would just be a one here and one here. If I just want to count if I just want the number again. That would be a one and a one that lets me set up the some product and I grabbed the decision variables comma and then the entire row corresponding to my constraint. I'll pause here so you can see the formula but some product what this is doing, remember a blank is treated like a 0. It does 1 times 4 or 4 times 1, doesn't matter. 1 times 4 plus, there's some part and there's the product part 5 times 0. If I hit Enter, I'll get 4, and hopefully that's okay. So this will count how many computers I have and then I'll check using solver, did I actually stay under 600 and let me move this over. I'll do the formula text, here so you can see it. The only formula here is coming from the left hand side. Everything else is just typed. Alright, so back to the problem. So we got hit with a constraint pretty quickly and I guess that's okay. I'll move the question maybe down here so I can see it. Alright, it believes most sales month of 600 and 1200. Okay, we got that. Each basic sells for $300 and each XP sells for 450. Okay, that sort of information, at the moment I don't know what to do with it yet we'll come back to it. The cost of component parts. Alright, so we have parts now is 150 for an XP is that labor is testing and there are most 10,000 assembly hours. Okay, so all these numbers are hitting me pretty darnn fast. What do I do with this information? So now you have to use your skills from the prior, what do we want to know? Let's fast forward a little bit, where's the actual question? Here, it is. PC tech wants to know how many of each model it should produce, assemble and test to maximize its net profit. I love this problem here because it's going to combine our skills from the other one here. So I want to maximize and what do I want to maximize? Our profit. But now you have to remember how the heck do you actually build, profit? Well, to do that let's push this down a little bit. Remember how to make profit? Profit is equal to what? I need to talk about my revenue produced and then my cost or expenses. And we've done lots of modeling on how to create a profit formula. If you do this right, it's revenue minus or cost. That would be our profit and this is what I want to maximize. That makes sense. Please don't minimize your profit. Again, you won't be having a job for too long. I just realized I see the NA here. I see I didn't drag my formula down. I'd like to just drag the formula down for the other left hand side. Let me fix that right now as I catch this. So drag though, I really want to make sure that my variables are in place. So let me just lock those in. I'll highlight, I'll do function of four in the computer. I'll hit Enter and then I can drag this down again. These dummy numbers here, help me catch any errors along the way. So what I want to maximize is my profit. So now the question is, how do I make money and how do I spend money? This is exactly the same as prior ones before we start doing LP problems. So here we go. Let's go back and get some information from those sentences that kind of hit us pretty quickly. Each basic sells for $300 and each XP sales for 450. So this is how I'm making money. So let's put that in. Just be careful, they have to line up with the correct column. Don't mix up the order. So the number of basic sells for 300, here we go, and the number of XP sells for 450. So let me move my profit over a little bit as well. So really what do I want here? Let's build a little table out here. Alright, so let's figure out how much money I make and that'll be my total column. So if I produce four basic computers and sell them for 300 I would multiply those together. If I sell five XPS and they make 450 I'd multiplying together then I'd add them up. Well, this is a perfect application of our favorite formula, some product. So 4 and 5 comma 300, 400. So with this particular example of 4 and 5 obviously this is the wrong answer. I just threw in some dummy numbers here. I can change it to 6 or 2 or 3 or whatever. My spreadsheets going to calculate the total revenue. Great, that's how I make money and notice I've used the 300 and 450. Now, I have a couple costs here. There are costs for parts. So we've seen this before. What do you do when you have different costs? It's okay, you just break them out. So we have parts, one of my costs for parts. So I'll invent that a little bit, and then what else? We're going to have some I saw some labor. I saw some assembly but we'll figure that out. Alright, so the cost of component parts for basic is 150. What if they gave me more information we would just add more rows. It's very easy to add rows in Excel. For an XP it's 225. Great, labor is required for assembly and testing. There are at most 10,000 assembly hours and 3000 testing hours. Right now we got a pause for a second. There's no dollar signs on these numbers, right? And cost. This isn't going to go to my profit. Notice the information is coming at me in no particular order. That is okay. There are most 10,000 assembly hours. That sounds like a constraint. So assembly hours and testing hours at most less than or equal to, less than or equal to and then we do 10,000 and 3000 For testing. These are all hard coded numbers. No formulas in here at all. Just read the problem, putting my constraint table. constraints go in the constraint table. Hopefully that's makes sense. Each labor hour for assembling costs $11. Alright, so now that's another cost, right? So this is like labor for assembling, labor for assembling. Each labor hour costs $11 and each labor hour for testing costs $15. Boy, okay, so hold on. So I need to put another one in here. This is labor for testing per hour. Well, how do I know what to put in here? What's interesting here, this is a formula. I have to do it by the number of hours. So here's where it comes in. Each XP requires 6 hours for assembly. Alright, so let's put that. So 6 hours for assembly and 2 hours for testing. Basic requires 5 hours for assembly and 1 hour for testing. Alright, so I have to be careful here. These are the hours. All right, these are true costs. So just be careful. I really should let me format these to make things more clear. So there's the parts cost and if it costs me parts for each computer, I need to multiply this by each computer. Some product, the parts by the computer, and what I want to do here is maybe I somehow need to put in the $11 per hour and the 15 per hour. Where can I put that? Maybe I'll put that over here. 11 for assembly and $15. Where you put it doesn't matter. Just be clear about it for labor. These are dollar signs. There's a trap here. It's gotta be tricky. What is hours and what is this? So, alright, so here we go. So for every computer it cost me 5 hours to assemble it if it's a basic it's 6 hours to assemble it if it's XP. The total number of hours, what I want to do here? I want to still want to calculate how many hours I have, the sum product for my labor against the number of computers. But remember this is 50 hours, the hours of time. Building four basics and five XPS requires 50 hours of time. I do not want to add that to my profit calculation because it's not dollars. It's dollars, and what's not, let me use units here to really clarify this. However, you can easily say, okay. Great, if it's 50 hours of time and it costs me $11 per hour. Well, now I can multiply it by the 11 that I put over here. And now it's truly money. Stare at this for a second. So you have to compute the total time and then hit that against the 11 hours. And for all the same reasons you want to do it one more time. And I'll put the formulas down in a second. I want to do the sum product of these two times the $15 needed. Do the formula text and we can show the formulas. So notice that I have just had to be careful, what is time, what is not. So now, I also have to adjust my profit calculation. You can see that it is clearly not using the total cost. So I want to take the money I make, minus the cost for parts minus the labor minus the labor for testing and assembly. So I have three things to subtract off. I could compute the total and just added them up and just do one subtraction. That's fine too. However you cook it, you have to do a net profit. So it is your revenue minus cost. And now if I go through and look at everything, just give this one more read, pause video if you need. Now you have what you need. You have every single piece of information in there and notice the spread you got a little more complicated because we have a net profit model. You can imagine this gets as complicated as you want. When it's really complicated you do it off in another tab and then you come back to your LP calculation. So it's pretty common for real examples. You have your LP calculation and then you can imagine doing a more complicated net profits on some other tab and then linking this cell back, but this is small enough where I can fit inside of a smallish area on my screen and we're good. So I think I'm good to go. I'm ready to go to solve or to get the actual number of computers. What I would do here at this point is just read the question one more time and make sure I didn't miss any numbers. And I think we're all set, so it's time to head over to server, data solver. Here we go. Set the objective function. Now, my objective function is nicely color coded and gray, that's what I want. So I set that to be a max. I want to maximize my profit. I have two variable cells to change. These are my green cells against the other color coding really helps you out here subject to the constraints. Now I have constraints. I didn't finish my thing. Done it, hold on, close, come back. I need to know the assembly hours. How many total assembly hours do I have? I almost did it inside of here to calculate this but we can do it again. So 5, 6, 1, 2. I knew something was missing. My formula for left hand side is just dragged down. You can see that was the number actually we computed beforehand. Less than 10,000, 3000. 10,000 and 3000 looks good. Alright, so I caught a mistake. Let's try one more time. There we go, data solver. We can set the objective. That's the gray cell. Check to max, check changing the variable cells b2 and C2. Alright, here we go, add. Now, last time I added each constraint individually, I want to show you a little bit of a shortcut. You can batch, upload constraints. Remember these three sort of things to be filled in, match up with our left hand side, our sign and our right hand side. So let's since they're all less than or equal, watch this. You can highlight the entire left hand side, select the less than or equal side and then the constraint you can do the entire right hand side. That's a batch upload. So you don't have to do each one individually. Solver knows what you mean on that and it'll compare it just like your table constraint. So that's it. There's we can do a batch upload here since they all match. Hit Okay. And it looks like there's just one in here but you can see it's saying 14 to 17 less than equal to 14, it knows what it means. So that's a nice shortcut instead of having to click under time. We want to make the constrained variables non negative is again it's really weird to come back and say the right answer is going to be negative number computers. We do select the solving method. I want to change that to be simplex LP. Fantastic, and we itself. Here's the results. Solver found a solution. All constraints are satisfied. Fantastic. And we hit Okay. So I get the number of basics to be 560 the number of XPS to be 1200 my net profit that was maximized to $17,600. We would then write a summary sentence, make sure that we could communicate these results. So we would produce 560 basic computers, 1200 XP computers for a maximum net profit of $217600. There's all the assumptions to back it up. You want to check to make sure everything is there. We didn't produce any more than 600 basics. We didn't produce any more than 1200 XPS, assembly hours there Under where they need to be and so are the testing hours. Alright, great job on this one. Go over this one, just another way to complicate an LP problem. All these different pieces are going to start to come together now. Really go over these basic examples. Make sure you understand it. Great job on this one. I'll see you next.