Once the problem has been setup in Excel, then we can use the solver tool to solve the problem and prescribe best decision for the production and the inventory levels. So here is our solver tool. And here, you can see that we have the decision variables over here, B1 through B4. So we go to the data tab. And then under the data tab, you'd find the solver. We need to specify all the different parameters of this problem. So the objective function value, the objective function is available in cell B22. So we select that, then we have minimalization problem. The decision variables are in the cells B6 through E6 and then we can add the constraints. There is one set of constraints coming from the production minimums and another set from production maximum. Similarly, there will be constraints coming in from the minimum ending inventory and maximum ending inventory. So let's include those. So first, we can start with the minimum number of units that need to be produced each month. So the left-hand side of that is the number of units that are produced in different months and these have to be at least that is greater than equal to the minimum production level requirements, which is these cells. Cell B11 through E11, which is the minimum requirement. So the amount that is B1 through B4 have to be greater than 400 units every month. So that's this constraint. Similarly, we need to include a maximum capacity constraint for production. So once again, we select the production levels. These have to be less than equal to the maximum production capacity for the different months, which is given here in cell B12 through E12. So we have the maximum, the minimum production level constraints. Next, we turn our attention to the inventory constraints. So we have the requirement that the ending inventory of each month have to be at least 50. So we're going to add that constraint. The ending inventory of month one through month four can be selected on the left-hand side and these have to be at least that is greater than equal to the minimums required. The minimum inventory is 54 to 4 months. So we select that and add it as a constraint. Similarly, we need to add the maximum constraint of depending on the warehouse capacity. We need to specify that the amount that we can stock the end inventory of each month. These have to be less than equal to the capacity of the warehouse, which is 140 units. Sorry, 130 units. So here, we have this at the right inside of the constraint. So we said OK and these are our four constraints. We make these variables non-negative use simplex and we can run the solver to find the solution. So once you click solve, it should try to find a solution. Now here, you would see under solver result, this is an interesting one. Because what it is saying in the report, it's saying solver could not find a feasible solution for this problem and it has created these report for feasibility. So it means that there is no value of these parameters B1 to B4 that can satisfy all the constraints. So this problem doesn't have a feasible solution, unless we change some of the parameters. To better understand why it doesn't have a feasible solution, you might want to look at the feasibility report and you would see it here. In this case, we have the feasibility report two that was generated right now. It is saying that this one C8 constraints C8 greater than C14 is being violated. So let's look at C8 greater than C14. So C8 is this constraint. So the ending inventory month 2, it's turning out to be 40 units only whereas the minimum ending inventory required in C14 is 50. So what it is telling you that there is a requirement of being able to store at least 50 units of the fertilizer in the warehouse every month, but the solver is not being able to keep 50 units in the warehouse. It can only keep at most 40 units, given the rest of the parameters, values in the constraints of this problem. So to understand why that is happening, let's see what happens if this was 50, right? If the solver had been able to set this value to 50, the minimum that is required at the end of month 2. If it accepts to 50, then in month 3, we would be starting with the invention level of 50 units. There is a minimum requirement or production of 400 units in each month. So 50 units plus 400 unit produced would give us 450 units in total. Now, notice that the demand for the third month is pretty low compared to other months. The demand is only 310. We have a total of 450 units available, which is 50 from the previous month and 400. That was the minimum that had to be produced in this month, 415 units in total. I have a demand of only 310 units. Therefore, I would have 140 units left in stock. But I can only stock 130 units at most, because of the maximum invented constraint. Therefore, there is no way I could keep 140 units in the stock in my warehouse. So that's why the solver was not able to set this value to 50. Because if it had set it to 50, then the month three starting out with the inventory of 50 units. The minimum it has to produce is 400. So it would at least have 50 plus 400 for 50 units of fertilizer, but there's only a demand of 310 units. Therefore, 450 minus 310, that's 140 units need to be stopped in the warehouse. But the warehouse can only take up to 130 units and that is why there is no feasible solution from the problem. So what we need to do as a company to make this feasible? Well, the answer is simple. We need to be able to stalk those additional ten units somewhere, at least for that third month, so that we can get your feasible solutions. So now if we had been able to store another ten units, at least in the third month, maybe by subcontracting somewhere or storing it in another warehouse. If we had been able to raise this value to 140, the right-hand side of that constraint, then we should be able to find an optimal solution that is feasible. So let's run the solver again with this change to 140. And you would see that in that case, we would be able to find a feasible solution. We would be able to have 50 over here and we would be able to stock that much of invented 140 units away in the warehouse at the end of the month 3 and find a feasible value. So let's run it again. And there you go, you have a solver solution. Here it is now setting it to 50 and it's producing the minimum for that month 450 units. It's filling in the demand of 310 and the remaining 140 units are going to the warehouse. And so this time is being able to find a feasible solution for the problem and the optimal production levels are going to be 410 units in the first month, 520 units in the second month, 400 units in the third month and 450 units in the fourth month. And with this provision of extra ten units to be stored in the third month, we would be able to find it feasible solution after modifying the original problem. And my minimum cost for doing all this for production and inventory taken together is going to be $83,617.50. This is the minimum cost that the company would incur. If it were able to increase the inventory level in the warehouse for the third month by another 10 years to 140 units, then it could have this as a feasible solution.
