[MUSIC] In this exercise, we're going to cover decision optimization using the Excel Solver Add-in. Decision optimization is concerned with choosing the best set of decisions such that our objective is maximized or minimized. The decisions are usually subject to constraints such as budget or available labor hours. For example, we love to increase profits by increasing marketing advertisements. However, in reality, we most likely have a maximum amount of resources that we can dedicate to marketing. So we're going to demonstrate how to setup Excel to allow the Solver Add-in to optimize a set of decisions. In the scenario below we've been presented with five business initiatives which come with an estimated total cost, and a net benefit. In this case, we'd like to know which initiatives to execute against, subject to a $25 million budget. In other words, our decision variables are binary, yes or no. And we want to determine which initiatives to say yes to. To accomplish this task, we're going to use Excel Solver, a free add-in. Solver requires a few inputs, such as the objective cell, the type of optimization problem, and any constraints. To increase profits, we've identified five different initiatives that we can implement. The first initiative to consider is adding a new route, which has an estimated net benefit of $275,000. Next we're considering adding more capacity to our existing set of routes. Which carries an estimated net benefit of $2.9 million. Another option is to invest in a new technology solution that is estimated to bring a net benefit of $825,000. The final two options under consideration are increasing the size of the sales force, and a process improvement plan. With estimated net benefit figures of $2.8 million, and $1.2 million respectively. While we'd like to implement each initiative because they all carry a positive net benefit, we're constrained by our $25 million budget, and each initiative requires an investment. In fact, if we sum the cost of each initiative, The total investment exceeds $56 million, twice our budget. In cells C293 through G293, we've included the net benefit of each initiative. And the costs are in cells C296 through G296. In our scenario, we want to maximize net benefit while keeping total costs at or below $25 million. Excel Solver is an add-in that needs to be enabled if it is not already. To check if it's enabled navigate to the DATA tab and look for the Solver icon in the Analysis section. If you see the icon then the add-in is enabled and you can proceed. If not, navigate to the FILE tab and click on the Options button from the menu. In the Excel Options window select the Add-ins option. At the bottom of the window, find the Manager drop-down, and with Excel Add-ins selected, click Go. This brings up the Add-ins window. Check the Solver Add-in box, then click OK. Verify the Solver icon is visible on the DATA tab. As a reminder, want to maximize net benefit, subject to our $25 million budget. Returning to the problem, we've labeled cell I293 as the objective cell, at the moment, this cell is empty. But we need it to be linked to our decision variables in cells C292 through G292. The decision variables are binary, 1 if we decide to pursue the initiative, and 0 otherwise. In the objective cell, we need to sum the net benefit if the corresponding decision variable is 1, and disregard it if the decision variable is 0. One way to accomplish this is a SUMIF formula, however this option will become less efficient if the decision variables are not binary. As a result we will use SUMPRODUCT because it can handle both binary and non-binary decision variables. The SUMPRODUCT formula multiplies corresponding elements of equal length arrays, then sums each product. For example, the first element of the first array is multiplied by the first element of the second array, second element by second element, third by third. Until each element of each array has been accounted for. Then we sum the result of each product. Some viewers might recognize this as the dot product of two vectors. In cell I293, type =SUMPRODUCT and hit the Tab key. The first array will be our decision variables. So select C292 through G292 and type a comma. The next array is the net benefit range. Select cells C293 through G293. Close parentheses and hit Enter. Right now the formula returns 0 because each decision variable is empty. To further demonstrate SUMPRODUCT, place 1 in cell C292, and observe the result. Now we have 1 multiplied by cell C293, with all remaining products totaling 0. The goal is to get Solver to change our decision variables between 1 and 0 until we have the largest possible number in cell I293, while keeping investment expenditures at or below $25 million. The investment costs for each initiative is located in the range C296 through G296. Similar to the objective cell, we need a way to link the total investment cost to our decision cells. That is, if we choose to execute a plan, we add that plan's investment costs to total costs. We'll use the SUMPRODUCT again to accomplish this. In cell I296, Type =SUMPRODUCT and press the Tab key. Similar to the objective cell, the first array is the decision variable range. C292 through G292. So, select those cells with your mouse. Type a comma, the second array is the total cost range, in cells C296 through G296. Once you've selected that array, finalize the formula by closing the parenthesis and hitting the Enter key. Now, since we have a 1 in cell C292, the result of the formula should be 1 times the number in C296. As the decision cells change, the total cost will updated. We've configured the Excel spreadsheet to update when the decision variables change using the SUMPRODUCT formula. Now, we need to setup Solver to change the decision cells. In order to maximize the objective cell, while keeping cell I296 less than or equal to K296. We're going to input all of these information in the Solver window. To open the Solver window, navigate to the DATA tab. And click the Solver button in the Analysis section. The first input is Set Objective. This let's Solver know which cell we want to optimize. Click the reference box and select cell I293. Next is the to option which is determined by the type of problem. We want to maximize net benefit, so click the option button Max, if not already selected. Moving down the window we have the, By Changing Variable Cells, cell reference box. This lets Solver know which cells need to be changed until it finds the optimal solution. We want Solver to choose the initiatives that should be executed to maximize net benefit. Which means choosing the best combinations of 1's and 0's in cells C292 through G292. Click the cell reference box and select cells C292 through G292, and hit the Enter key. We now turn to the constraints section of the window. We're going to enter two constraints. First, our decision variables are binary, so we need to let Solver know this. Second, we need to specify the total cost cell that is calculated for a particular combination of 1's and 0's. This will be our left-hand side. The right-hand side of this constraint is going to be our total budget, which is located in cell K296. To add the constraints, click the Add button. We'll enter the binary constraints first, so select the cells C292 through G292 in the cell reference box. Then change the qualifier drop-down to bin, which stands for binary, click Add. For the budget constraint, click cell I296, then hit the Enter key. We need cell I296 to be less than or equal to $25 million, which we've provided in cell K296. Make sure our qualifier is less than or equal, for the constraint box select K296 and click OK. The last option you configure is the solving method, select Simplex LP. This is the algorithm used to change the cells, but the details of the mechanics are beyond the scope of this exercise. We're now ready to solve. Click Solve and Solver will find the optimal solution that satisfies all constraints, assuming there is a feasible solution. As you can see, the best possible solution is to choose technology investment, adding more sales reps, and the process improvement plan. The result of this is a net benefit of $4.9 million. And the total investment required is $23.4 million, which we require to be less than our budget of $25 million. Why is this such a powerful tool? Couldn't we just change the values of the cells manually until we get the best solution? Well, technically you can, but this is a simple problem and you still have 32 different combinations of initiatives to assess and compare. Imagine if we had more variables to consider. For binary variables each additional variable doubles the number of combinations we'd have to consider. If the decision variables are integers it becomes very difficult to obtain the optimal solution manually. We've now demonstrated how to configure Excel to find an optimal solution using the Solver Add-in. Now it's your turn to practice in the student workbook. [MUSIC]