In this video, we're going to use the linear programming to optimize our resources. We're going to look at how linear programming can be used to optimize resource planning. Here, I have a problem, so that's given on the left hand side. Problem is that we have the forecasted demand for six months, from Month 1 to Month 6. Then I know for my resource standpoint, for my regular work hours, I can produce about 150, 260 units a month, based on my regular workforce. These are in units, but you can also do this if you decide to forecast this in hours or minutes. I also know that from overtime capacity standpoint, I can produce 10 a month, 10 and then no overtime in Month 3, and then 10 for the rest of the month for Month 4 to Month 6. I also know that in a regular time for me to produce in a regular work hours, it cost me $50 to produce one unit. That's adding my labor cost and my other costs that may be in end year. Let's get factored into the cost of goods. This may vary depends on how your company does the counting. This will be good too, if you choose to use this. Do this. It's good to clarify the accounting rules in your company. Same over time is up to 75 is more expensive. Then if I decide to subcontract, so hire a temp worker, that gets more expensive. It's $80 an hour. Then if I have inventory at the end of every period, it will cost me four dollars to hold that inventory. If I have incurred any backlog, then it cost me six dollars per unit. There's some penalty, it's not an incentive for me to have backlogs. There's some constraints. In terms of sub-contracting, I cannot produce more than 10 units a month. I can now hire people to do more than 10 units a month work worth of work. The beginning inventory always be zero. At the very beginning of Month 1, in this analysis is zero. But this may change based on the organization. They may have some inventory already and you can set it as such. You just need to make sure that in formulating the problem, the model in Excel, you account for that accordingly. We're going to assume there's no back orders. But in real life, you may have that scenario. Once you have this information, you are collecting all this information, and now we can formulate the model, the output. The left hand side, I have what's given, what happened. I went and collected for my company. On the right hand side is what I formulated just for clarity to help us to calculate the, to use the optimization in Excel. Demand forecasts, I know that, it was given. I can just copy that over by just saying for Month 1 to Month 6, 168 Month 1, 115 Month 2 and so forth. From the output standpoint, I know regularly, in a regular time, this is also given; 150, 150, up to 160 the last two months, same as here this one was given. Overtime is subcontract. These are not given. These are the things that we need to figure it out. The objective is basically I want to minimize my cost. We'll see that at the end, once we walk with a formulation. I want to basically minimize this cost and trying to figure out how much overtime the subcontract if I need to use if any, based on the capacity that I have. The capacity I'm able to provide based on the manpower I have. How much overtime subcontract resources do I need? These are just arbitrary, but it's right now randomly put it in so we can at least see the changes. Then the output minus forecast is basically what I can produce value for my regular, my outputs, my regular time, overtime, and subcontract, and last the demand. Right now, in Month 1, for now, for discussion sake, that I can produce 160. My demand 160. Hope that my net forecast is 0. But in Month 2, I can produce 160, this is pre-optimization. I can produce 160, but my demand is only 110 so I have 10 surplus. In Month 3, I produce 155, but my requirement 160. For that month, my net is negative 5 and so forth. For my inventory standpoint, remember that it is for this problem, the assumption is that my beginning inventory is 0. At very beginning of the month, my inventory is 0. I set that to 0, set it to same as this assumption or constraints here. In my ending inventory is the net of my output, as my output is 160 in this case, and then my demand is also 160 so I'll have nothing left by the end of this month. My average inventory, you just average the average of beginning and the end. My backlog is basically 0. But the logic here is if the ending inventory is positive, then I don t have backlog excess inventory. But if it's not positive, it's not greater than 0, then I have a backlog. In Month 2, my beginning inventory would actually equal to the ending inventory of the prior period, so equals to the ending inventory of Month 1. In my end, it just the net of my capacity, my output, and my demand, in this case, is I have a surplus of 10. My average inventory from beginning to the end is 5. It is just using the average function to help us calculate the average inventory. In the beginning of Month 3, since I have in Month 2, I have ending inventory of 10, so in Period 3, I just say that the beginning inventory is 10. I have surplus of 5 because my demand is 160 but my output is only 155. Since I have excess inventory from the prior period from the beginning, so I can use that, that is also counted as my inventory. Beginning my output less my demand so that gives me the ending inventory. You can calculate that for the rest of the month. The cost. Now, regular time, irregular hours, my capacity is 150 units, and I know each unit cost $50. I take 150 times 50, that gives me the cost for a regular time. The same for overtime is whatever the overtime values times the overtime costs per unit and the same for sub-contracting. Inventory wise, so we take the cost of the average inventory for that period. We also have a backlog cost so then we will calculate that as well. Here, we're just doing a sum of what our costs are in Month 1, Month 2, Month 3, and Month 4, and so forth. Here, I also have a summation of the total cost for regular times, over times, and subcontract inventory backlog. Here is the cause for the entire period of six periods as you see. Our objective is really to minimize for the six periods of what my cost should be. I want minimize my cost by changing, I don't want to play around with how much overtime is subcontract I need to use so I can satisfy the demand that I'm receiving. Do this, we can still do it through solver. We going to walk through this together. We're going to set the objective to minimize my cost and this is just the same as this. It just say equal. This is the mirror image of my total costs here. I can just set it here or either here is fine. This is linked through the formula. I'm trying to minimize, in this case, I'm trying to minimize my cost and I'm going to change my variables. My variables are overtime, they're subcontract. Now, I have constraints. Let me take this out so we can do one-by-one together. First constraint we want to add is there's no backlog. Since these backlog is using formula to determine whether this backlog or not, solver does not like formula. Then what I should say. Then we're going to use the ending inventory. We're going to say if my ending inventory is greater than equal to 0, then I don't have backlog. That's one constraint I want to add. Then the second constraint I want to add is that my overtime. My overtime hours, they cannot be more than the overtime hours or an hour per unit. The unit I can produce over time cannot be greater than what's the capacity that is given to me. Sometimes this is understand both you can only work a sudden over time. Then the amount of units you can produce is as such or you may have some constraints whether if I'm a labor force or unions or something that you may have this in your organization. We're going to add that as a constraint. The constraint is that I want to have my variables. Over time, they subcontract to be all integers. I don't want decimal place. It doesn't make sense to have that small units like 5.1, 5.2. They had to be all integer. I have one more is my subcontract. I have a constraint here that I stayed at my subcontract. The hours are the units that I can do this now, it's only 10. It's going to be less than 10 a month. That's also maybe there's a limited supply of external attempts. Good, I can work. Let's check it over again. I'm trying to minimize my total cost, that's minimization by changing the variables, the overtime is subcontract and is subject to the first one. They cannot be backlog in my setup here. Then a second one is that the overtime has to be less than what's given. My variables are all integers. Then the last one is that my subcontract has less than the capacity that's given, which is 10 units a month. We're going to select make sure to check this. Make the unconstrained variables non-negative. Then here we're actually going to use the GRC. It will be a nice one to use for this formulation. GRC non-linear. Once you have this setup, we can click on Solve. Just some iterations. My solver came back and told me that I found a solution. All constraints and optimalities are satisfied and I can just keep it. Today, you can see that the values changed. My overtime, I subcontract, it's not as same as before. It's actually for certain months. I only have overtime or period. Then for the others, I actually have both. If the last period, my internal regular time capacity is able to satisfy this need. For this, 50,720 is my answer. If you want to try something else, now you can just say restore the original values and you click Okay, then you actually restore this back. This is where we started from. If you'd like to, you can also try another method if you'd like to see if that will give you a better solution, or maybe it won't. For example, here, it says the linearity condition required that this is not linear. Then this is not a good one to use. Then we just go back and I choose the non-linear option, then you hit Solve. You can use the solver in any programming to solve for resource optimization.
