Hi, I'm Sergei Savin, and this is session 2 of the first week of our course on modeling risk and realities. In session 1, we have looked at an example of decision making in low uncertainty setting. And have formulated an algebraic model designed to guide us to the process of finding the best possible decision. In this session, we'll set up a spreadsheet version of that algebraic model and we'll use an Excel tool called Solver to help us find the best option. But we'll also see how this best option and its impact change with the model inputs. In the first session of week 1, we have looked at the ways to set an optimization model in algebraic form. By identifying decision variables, objective function and constraints. In this session, we will implement this algebraic model in a spreadsheet form and solve it using Excel's optimization tool. So as a reminder, here's an algebraic representation of the advertising problem we're focused on. Next, we will move to set up the elements of this model, decision variables, objective function, and constraints in the spreadsheet format. We have prepared for you an Excel template Hudson Readers_0, which we will use for this purpose. This is our Excel file Hudson Readers_0. As I mentioned, it is posted on our course's site. You can use it to follow the steps I'm going through. In this preliminary Excel file version, I have entered the problem parameters and already outlined the structure of the spreadsheet model we're going to create. In our analysis, I will use Excel 2013 on the Windows platform and all of my comments apply only to this version of Excel. However, the steps you will have to go through will be similar, if you are using Excel on Mac or if you're using another spreadsheet program, like Google Sheets. We will be using Excel add in called Solver to find the best budget allocation. And before we set up a spreadsheet model, let us make sure that we locate the Solver. We're using Excel 2013, and in this version the solver can be found located under the top called Data. In the portion called Analysis. If you see the Solver button under this tab, you're ready. If the Solver button is not there go to File > Options > Add-Ins, here you will see the tab Excel options. Find the manage Excel Add-ins at the bottom of the tab and click Go. You will be able to see the tab called Add Ins, make sure that the Solver Add In is checked and click OK. Now the Solver should be visible under the Data tab. In other versions of Excel, such as Excel for Mac or in the Google Sheets, the steps you need to take to find Solver maybe different, but just remember that the Solver is an add in. Find where the add ins are and make sure that the Solver is activated. Let's set up our model. We need to translate our algebraic model into a spreadsheet form. That is we need to specify on the spreadsheet. Three main components of optimization model. Decision variables, objective function and constraints. First, the decision variables. In an advertising problem we have decision variables representing the advertising amounts for each of the four product market combinations. On this spread sheet I have reserved cells B11, C11, B12 and C12 to hold the values of these variables. For example, the cell B11? The whole value of the amount expressed in million of dollars, spend advertising standard product in India. Let us put some initial values in this decision variable cells. Say, let's put 40 which stand for 40 million into each cell. I think it is useful to have a simple color coding scheme in the optimization spreadsheets to make sure that when the spreadsheet is opened by you or someone else who wants to use your model. It is easy to read and navigate as part of this color coding scheme. Let's color the decision variable cells in blue and change the font to Bo ld. We have our decision variable cells, and the Solver will be instructed to change the values in those cells to find the best decision for us. Now we move to the second element of our model, the objective function. The objective to be maximized in our example, is the total nets sales increase. So we needed to instruct Excel in Solver to calculate the net sales increase for any combination of decision variable values. On our spreadsheet, I have reserved the cell E6 for the value of the objective function. Let's calculate the total net sales increase that will be generated by the advertising plan in the cells B11 through C12. So we go to the cell E6 and type in the formula for the total net sales increase. In order to calculate the impact of advertising spending amounts in the decision variable cells. We need to multiply the value in each decision variable cell by the corresponding advertising effect value from the cells B6 through C7. So the formula we want to place in the cell E6 is B11*B6, 40*0.05 + C11*C6, 40*0.4 + B12*B7, 40*0.02 + C12*C7, 40*0.03. We press Enter, and the cell E6 reflects the Total Net Sales Increase of $5.6 million. Now this is all good but typing in this formula can become impractically cumbersome if the number of decision variables in a model is large. Fortunately, Excel has a convenient shortcut function called SUMPRODUCT. SUMPRODUCT, as it's name suggests calculates products of values in corresponding cells in two equally sized groups of cells and then adds those products. In our case, the formula in the cell E6 can be written as. Some product of cells B11 through C12 and cells B6 through C7. When we click enter, the same result as before is displayed and it's no surprise, because the formula with the SUMPRODUCT form, and the formula that we had before are equivalent. You can find more information about SUMPRODUCT function in the Excel help. By the way, in Excel for Windows, I'm using a shortcut F2 to display the formula in any cell. For example, in the objective function cell B6. As we did for the decision variable cells, let's also use a color coding for the objective function cell. Let us use red color and bold font. To make it stand out. But formula in cell E6, we'll calculate the net sales increase for any possible combination of decision variable values. For example, we can change the value in the cell B11 to 50 million reflecting a potential increase. In advertising for the standard product in India. And we see that the total net sales increase is now 6.1 million. We will later instruct Solva to change the values in our decision variable cells to make the value of the objective function cell as large as possible. Clearly we cannot put just any values in our decision variable cells but only the ones that correspond to feasible solutions. So it is time to specify the constraints for our problem. The first constraint, is the budget constraint. The budget value $195 million is stored in the cell D15. We want to make sure that the sum of our decision variables, that is the sum of spending amounts for two products in two markets, does not exceed this value. My plan is to put the value of the sum into the cell B15. Sum of four decision variables. Currently it is $170 million. Later, when we will ask the solver to find the best advertising policy. We will also instructed to make sure that the value in the cell B15 never exceeds the value in the cell D15. I put a corresponding sign in the cell C15. Just to embellish the spreadsheet. The sign will not play any role in the optimization. It's just a visual cue. So you don't have to use it if you don't want to. Okay, the next constraint is the one that requires Hudson Readers to generate at least three million in the net sales increase in the Indian market. The limiting value three million is stored in the cell D16. So in the cell B16, we calculate the net sales increase in India resulting from the decisions in cells B11 through C12. This increase can be calculated by multiplying the advertising spending on the standard product in the Indian market by the respective advertising effectiveness value. And do the same for the enhanced product in the Indian market. And then add those two products. This sounds like a SUMPRODUCT and that's what we're going to use. SUMPRODUCT of. Spending in India on both types of products times the corresponding effectiveness coefficients. Now let's do the same thing for the constrained on the minimum net sales increase in China. We put the corresponding formula in the cell B17, SUMPRODUCT of spending in China multiplied by respective coefficients of sales effectiveness in this market. Now the last constraint tells us that the total net sales increase for the enhanced version must be at least 80% of that increase for the standard version. So in the cell B18, we we'll calculate the total net cell's increase for the enhanced version. As before, we'll use a SUMPRODUCT formula. So it's a SUMPRODUCT of spending on the enhanced version across the tool markets multiplied by the respective coefficients. And now, in the cell D18 I'm going to put the formula for the 80% of the net sales increase for the standard version. So it's equal to 0.8 times SUMPRODUCT of spending on standard version, across two markets, multiplied by the two coefficients. The constraint that will pass on to the Solver will be that the value in the cell B10 can't be lower than the value in the cell D18. We can put the corresponding visual cues in this three cells. Okay. We're now ready for the Solver. So we go to data. Click on solver. The solver parameters dialogue box pops up. Here in the set objective part, we click on the cell selection tool and choose the cell E6 to represent our objective function. The solver can maximize and minimize the selected objective or it can find a combination of decision variables to produce a required value of the objective function. Here, we're maximizing the total net sales increase, so we select Max. Now, we need to communicate to Solver where our decision variables are. So, we use, by changing variable cells selection tool to point this over to the cells D11 through C12. Find all the constraints. Here we use subject to the constraints part, and we specify the budget constraint first. B15 less or equal than D15. By the way, we could specify constraints by using equal signs, greater equal signs, we can force decision variables to take integer values or binary values 0, 1, or we can ask over to find a solution with all decision variables taking different values. Next, we need to add constraints on sales increase in India, China, and also enhanced versus standard version constraint. So all of those three cells should be greater or equal than the values in these three cells. We click OK, now we see all the constraints have been communicated to Excel. What is left is adding the constraint that tells the solver that our decision variables must take non-negative values. Solver offers a convenient way of introducing non-negative to constraint for all decision variables at the same time. All we need to do is to check the box, make all unconstrained variables none negative. Next, we need to select a solving method. Solver offers several choices here. GRG nonlinear, LP, and even Evolutionary solver. I would set the solver method as GRG nonlinear. This solution method is very general and will allow you to work with many different kinds of models. A couple of things to remember, since GRG nonlinear is a general method of solving optimization problems, it will try to find the best possible solution for any model you formulate. However, it may not always be able to guarantee, that what it finds and gives you as a solution is actually the best possible decision. In addition, the solution that the GRG Nonlinear method produces may depend on the initial values in the decision variable cells. So, when optimizing using this method, run it several times with different starting values of the decision variables to see if you can improve the objective function value. Okay, let's run the Solver. We need to click the Solve button. And also to make sure that Solver found a solution is displayed in the dialog box that appears. Here, we have the optimal solution. We will discuss it in a couple of minutes but first a few words about solver messages. This time the solver came up with a message solver found a solution. This is the message that indicates to us that everything went according to the plan. However it is possible to get other, not so encouraging messages. For example, suppose we forgot to include an important constraint. Let's go to the solver dialogue and remove all the constraints. If we solve our model now, the solver comes back with a complaint, objective cell values do not converge. This means that the net sales increase in this model can becoming infinitely large, when you see a message like that it probably means that you forgotten an important constraint. Okay, let's put back our constraints. Another possibility arises when you over constraint your model. In other words, when there's no feasible solution. To demonstrate how can this happen, let us reduce the advertising budget to $180 million. This was a previous solution. Let's go here and change the budget to $180 million. When we're around the model now. The solver says that it could not find a feasible solution. To summarize, when you miss a constraint or you put constraints that contradict to each other, you will get one of those messages. And then you have to go back to the drawing board to see how your model should be corrected. Here is the solution to the Hudson Readers problem. We have stored the details in the excel file with the same name. As the solution implies, the entire advertising budget is used up. This of course is not surprising. Given the advertising alternatives the company has, the maximum net sales increase it can achieve using the budget of $195 million is around $7.38 million. In other words, the best return on advertising spending that the company can achieve is 7.38 divided by 195, is around 3.8%. The major portion of spending goes to the advertising of the enhanced version in China. They advertise in China with a return of 3%. And only minor portion is devoted to advertising channels with high returns, 4% and 5%. This, of course, is a consequence of the constraints that the company faces. It is important to keep in mind that the solver can be used to investigate impact of changes in the model parameters on the optimal choice and the optimal objective function value, and to answer various what if questions. For example, if the company is interested in how the extra advertising budget can change the way it plans its campaign, solver can help. This is what happens if the problem is resolved, with a slightly higher value of the advertising budget, namely, $200 million. These extra five million, bring the total net sales from. 7.38 to 7.59 million. The allocation of this new budget, in percentage terms, also changes. Shifting away from promoting the standard version in China. It turns out to be increasingly beneficial. To use China market for the promotion of enhanced version, and Indian market for the promotion of the standard version. Of course, we can systematically change the budget value to see how it impacts the Optimal Net Sales Increase Value and the budget allocation. We can do it manually or we can do it in a more automated fashion using an Excel Add-in Solver Table that you can download using this link. The results are presented in the sheet called Analysis in the Hudson Readers spreadsheet. For example, we can tabulate the values of the Optimal Net Sales Increase as a function of the budget value to see how far the extra dollars of the Advertising Budget go. In this case, extra budget brings extra net sales increase at the rate of around 4.2 cents on a dollar. As the budget increases in the range of budget values we looked at from 195 million to 170 million dollars, this rate drops to around 3.9 cents on a dollar. As the advertising budget increases, it's allocation in percentage terms also changes. In particular, the fraction of the advertising of the enhanced version in China remains stable and high. Around 56, 57% across the range of budget values we looked at. While advertising the enhanced version in India remains unattractive. As far as the standard version is concerned It's advertising shifts from China to India as the advertising budget grows. In the session we have solved our first optimization model in this course, and there's more to come. So far? We have been dealing with the setting were outcome of any potential choice is highly predictable. In the next session, we'll continue working with optimization toolkit and we'll look at an example where a decision maker uses a simple measure to evaluate future uncertainty. See you soon.
