Welcome to the review session for week two of Operations Analytics Scores. This review session is completely optional. If you feel confident in your knowledge of the optimization tool kit, take a look at the practice problems we've prepared for you. And if you feel ready to take on week two homework questions. On the other hand, if you feel you can benefit from looking at additional examples of resourceful location, and network optimization models before moving to the practice problems. This session is for you. In this review, we will go over two additional examples of optimization problems. A resource allocation, and a network management. Here's the context for the resource allocation example. A UN agency is planning to build housing units for victims of a recent natural disaster. Four types of housing units are considered, A, B, C, and D. Each type of housing unit requires four basic resources, bricks, iron sheets, wooden poles, and labor hours. For example, each unit of type B requires 600 bricks, 10 iron sheets, 32 wooden poles, and 200 labor hours. Different types of housing units provide shelter for different numbers of people. For example, each type of B unit will house six people. Its resource has limited availability. For example, there's only 1.5 million bricks available for this project. The agency is trying to decide how many units of each type to build, to house as many people as possible without exceeding the resource availabilities. So we have a verbal description of the problem. The next step is to convert this verbal description into algebraic formulas. We must identify decision variables and write formulas for the objective function and for the constraints. Let's start with the decision variables. We need to decide how many units of each type to build. So, we let NA designate the number of units of type A to build, and the number of units of type B to build, and C and D, the number of units of types C and D to build respectively. We have four decision variables. Okay, the agency would like to maximize the total number of people receiving shelter. If it builds NA type A units the total number of people sheltered in those units will be seven times NA. In the same way if it builds NB, NC, and ND units. The contributions of those decisions to the objective function will be 6xND+5xNC+7xND, and in this contribution were all unit types, we get the expression for the objective function. Let's move to the constraints. First, resource availabilities. The number of required bricks cannot exceed the availability of bricks. If we build N-A of type A units, they will require 400 times N-A Bricks, if we build N-B of type B units, we will require 600 times N-B bricks. And so on, 400 NC and 300 time ND breaks for NC type C units and ND of type D units. The total number of required breaks will be 400xNA+600xNB+400xNC+300xND. And that should not exceed 1.5 million bricks that the agency has available. What about iron sheets? The constraint on iron sheets looks the same way. The number of required iron sheets cannot exceed the number of iron sheets available. So we add all the requirements for iron sheets across four unit types. 20xNa+10xNb+25xNc=18xNd, and that should not exceed 100,000 iron sheets that the agency has available. In a similar way the number of required wooden poles cannot exceed the wooden pole availability. The number of required poles will be 35*Na + 32*Nb + 26*Nc + 30*Nd. And that total sum cannot exceed 125,000 wooden poles that the agency has available. Finally, a similar requirement must be satisfied for the number of labor hours. The total number of labor hours available is 1.5 million, and that could not be exceeded by the total requirement for the number of labor hours, 300 times na plus 200 times nb + 200*Nc +400*Nd. So, let's put together all the resource availability constraints. We have four of them. One for bricks, one for iron sheets, one for wooden poles, and one for labour hours. Finally, we need to ensure that the numbers of housing units are integer and non-negative. So this is our complete model. We need to identify the numbers of each type of housing unit to build to maximize the total number of sheltered people, subject to limitations on our resource availabilities. Here's the template Housing Relief_0. We have posted it for you. You might find it useful to open and follow my steps. So we have the problem data here. In order to complete our model we need to define decision variables. Objective function and constraints. First, decision variables. In this problem, we need to determine the number of houses of each of four types, A, B, C & D, to build. So, we need four decision variables. An A, and B and C and D. We will use the cells C5 through F5, for those four variables. Let's put some trial values in the cell, say 1,000 into each. Now let's use our convention to distinguish these cells from the rest by making them bold and blue. Next, the objective function. In this problem, the UN agency is trying to maximize the total number of sheltered people. So, H5 will be used to store the value of this objective. The formula is sum product A five decision variables, C5:F5. And of the cells C6:F6. Because those cells reflect the numbers of people sheltered in each type of housing unit. So that's the formula. As we can see the objective function value for the current set of these certain variables, is equal to 25,000. Again following our convention let's change the font in the objective function cell to bold and red. Finally, we need to specify the constraints for our problem. In trying to maximize its objective, the agency is restricted by the amounts of four resources, bricks, wooden poles, iron sheets, and labor hours. So for any set of number of housing units the agency plans to build, it must calculate the corresponding resource requirements and ensure those requirements do not exceed the availabilities of these resources. Let's first write down the formula that calculates the required number of bricks associated with the set of decision variables in the cell C5 through F5. We will put this formula into the cell G10. So in order to calculate the required number of bricks, we write down the formulas SUMPRODUCT of our decision variables, C5, F5, and the respective brick requirements for each type of housing unit, which are located in the cells C10, F10. The resulting requirement is 1.7 million bricks, this is more than the amount of bricks the agency has, but we will let the solver sort this out later. Now in order to calculate the required amounts for the remaining resources we can go to cell G10, use the absolute cell references for the decision variable cells. And then copy and paste this formula into the cells G11 through G13. Just to make sure that we have copied everything correctly, let's go to the cell G13 and have a look at the formula. We're using the shortcut F2 to display the formula. Okay, it looks correct. Okay, now we're ready to call the solver. Data > Solver. Now we need to specify three components of our decision model to the solver. Objective function, decision variables and constraints. For the objective function, we'd point to the cell H5. And we want to maximize this objective, that's the total number of people sheltered. Decision variables are the number of units of each type to build, so that's C5 through F5, and constraints. Well, the main constraints here are the availability of our resources, so we point out the cells G10 through G13 as our resource requirements. And those should not exceed our resource availabilities, which are stored in I10 through I13. We also need to tell the solver that our decision variable is the number of housing units must be integer, so we'll click Add. We select our decision variables, and we select int, integer as an option here, we click OK and we see that our constraint is added here. We wanna make sure that the number of housing units we build is non-negative. We leave the solving method as it is, GRG nonlinear. And in the options, we uncheck Ignore Integer Constraints to make sure that our requirements for the number of housing units to be integer is not ignored. We click OK, we click Solve and we see the solution displayed on the spreadsheet. Here's a picture of the optimized spreadsheet with the formulas put in. As you can see, the optimal portfolio of housing units is dominated by the units of type D and type B with only two units of type C recommended to be built. Units of type A housed seven people each, just like the units of type D, but they apparently take too much resources to build and are not included in the optimal solution. In the second example in this review session, we're looking at the network optimization problem. In particular, we're considering a company, Columbia Parquet, that makes hardwood floors. The company operates two plants in different parts of Germany, plant A and plant B. During the next quarter, each plant can produce only up to a certain amount of hardwood floors. For example, plant A has a quarterly production capacity of 2,500 of thousand square feet of hardwood floors. Company's hardwood floors are sold in three regions. Each region has a minimum required amount of hardwood floors it must receive in next quarter. Colombi may decide to ship more than those minimum values to each region, but it cannot ship less. When shipping floors between plants and marketing regions, the company incurs the following costs. For example, if it ships 1,000 square feet from Plant B to region 2, it will incur costs of 25.5 Euros. So Colombi needs to understand how much it should ship from each plant, to each marketing region, to achieve the lowest possible shipping cost, while ensuring that all demands are satisfied by the available plant capacity. In order to convert this verbal description of the problem into an algebraic one, we must specify decision variables, objective function and constraints. So let's start with the decision variables. First, two variables that count amount ship to region 1 from plant A and from plant B. Now let's add amounts shipped to region 2 from plant A and from plant B and, the amounts shipped to region 3. All and all, we have six decision variables in this network. The objective in this problem is the total shipping cost, and it has six components, one for each decision variable. On the constraints side, we have two constrained types. The first one is the supply constraints. Plant A cannot produce and ship more than its capacity. So all shipments that originate from plant A cannot add up to more than 2,500. In a similar fashion, the total shipments from plant B cannot exceed 3,000. Demand constraints is the second constrained type. For region 1 the total shipment must be at least the minimum demand requirement of 2000. Region 2 must receive at least 930. For region 3 the minimum requirement is 2,200. Finally, we cannot ship negative amounts. Since it is possible to ship fractions of thousands of square feet of hardwood floors, we do not impose a requirement that our decision variables must be an integer. Here's the complete algebraic formulation for the Colombi's Problem. We can now use the Excel template Colombi_0 we have prepared, to set up the spreadsheet model and optimize it using solver. As usual we start with the zero version of the Excel file, Colombi_0. The first task is to define decision variable cells on the spreadsheet. The decision variables in this problem are the quantities expressed in thousand of square feet to be shipped from each plant to each marketing region. We will put the values of those decision variables in the cells B11 through D12. So for example, the cell B11 will hold the value for the number of 1,000 of square feet of hardwood floors to be shipped from Plant A to Region 1. Let us put in some trial values into the decision variable cells. Say, 1,000 for each cell. As before, we use blue color and bold font to distinguish the decision variable cells from other cells. Now the objective function. In the Columbi example, the objective function is the total shipping cost. This is the objective function the company would like to minimize. We'll place the formula for this objective function into the cell F3. We start with the equality sign and put in SUMPRODUCT of our decision variable cells, B11, D12, and the corresponding transportation costs, B6 through D7. The result in the cell F3 is, according to Excel, 124,000 euros. So we have the objective function cell. Let's change the font in this cell to bold and red, for it to stand out. Now it's constraint's turn. In the Columbia example, we have two plan capacity constraints and thee market and region demand constraints. On the capacity side, the total amount shipped from each plant cannot exceed that plant's manufacturing capacity. In a cell e11, let's calculate the total amount shipped from plant A. So we write, SUM of B11, C11, and D11. And we get the result for the trial values of decision variables, 3000. This amount exceeds the Plant A capacity so the solar will have to adjust the values of the decision variables to make sure that we have a feasible shipping plan. Now we can copy and paste the formula in the cell E11 into the cell E12 to calculate the amount shipped from Plant B. 3,000. Now let's move to the demand constraints for each marketing region. We will write the formula for the total amount currently shipped to Region 1 in the cell B13. So the formula is *SUM(B11,B12). In a similar way, we get the amounts shipped to Regions 2 and 3, by copying and pasting the formula from the cell B13 into the cells C13 and D13. Now, if we look into the cell D13, We see that the formula there is the sum of D11 and D12, which is the sum of the quantity shipped to Region 3 from Plants A and B. We have specified the decision variables, the object functions in the constraints. And now we can use the solver to find out optimal solution. So we'll go to data, click on solver and start with the objective function. Objective function cell is F3 and we're minimizing this objective because this is the total shipping cost. The decision variables are the cells B11 through D12 and there are two types of the constraints. First type is to make sure that the amount shipped from each plant, does not exceed the capacity of each plant. Let's click OK. The second type of constraint is to make sure that whatever each region receives, B13 through D13 is at least what this region requested, B15, D15. Now we do not need the integer constraints here because we can ship fractional amounts, but we do need to specify that our shipping quantities are now negative. We leave this solving method as GRG nonlinear, we click Solve, and the optimal solution is displayed on the spreadsheet. Here's the optimised spreadsheet.
