Hi, I'm Sergey Seven, 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 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 setup a spreadsheet version of that algebraic model. And we'll use an Excel tool called solver to help us find the best option. We will also see how this best option, and it's 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 optimization tool, so as a reminder, here's an algebraic representation of the advertising problem. 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. Okay, 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. I 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 only apply to this version of Excel. However, the steps you will have to go through will be similar if you're using Excel on Mac or if you're using another spreadsheet program like Google Sheets. But we'll 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 tab Data in the portion called Analysis. If you see the Solver button under this tab, you're ready. If the silver 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 Okay. Now the solver should be visible under the Data tab. In other versions of Excel, such as Excel for Mac, or in a Google Sheets, the steps you need to take to find solver may be 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. Okay, let's set up our model. We need to translate our algebraic model into a spreadsheet form. That is, we need to specify on this spreadsheet. Three main components of an optimization model. Decision variables, objective function, and constraints. First, the decision variables. In our advertising problem, we have decision variables representing the advertising amounts for each of the four product market combinations. On this spreadsheet, I have reserved cells B11, C11, B12, and C12 to hold the values of these variables. For example, the cell B11 will hold then value of the amount expressed in millions of dollars spend advertising, standard product in India. Let us put some initial values in these decision variable cells. Say, let's put 40 which stands for 40 million into each cell. I think it is useful to have a simple color code and 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 call it a decision variable cells in blue, and change the font to bold. Okay, 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 net sales increase. So, we need to instruct Excel and Silver to calculate the net cell's 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 x B6, 40 x 0.05 + C11 x C6, 40 x 0.04 + B12 x B7, 40 x 0.02 + C12 x C7, 40 x 0.03. Presenter and the sale 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 its name suggests, calculates products of values in corresponding cells in two equally sized group of cells, and then, adds those products. In our case, the formula in the cell E6 can be written as SUMPRODUCT 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 we had there before are equivalent. You can find more information about SUMPRODUCT function in the Excel Help. By the way, in the Excel for Windows, I'm using a shortcut F2 to display the formula in any cell. For example, in the objective function cell E6. 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. The formula in cell E6 will 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 Solver to change the values in our decision variable cells, to make the value in 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, who will also instruct it 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 is 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 3 million in the net sales increase in the Indian market. The limiting value, 3 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 constraint on the minimum net sales increase in China. We put the corresponding formula in 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 will calculate the total net sales increase for the enhanced version. As before, we will use a SUMPRODUCT formula. So it's a SUMPRODUCT, Of spending on the enhanced version across the two 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 we'll pass on to the Solver will be that the value in a cell B18 cannot be lower than the value in the cell D18. We can put the corresponding visual cues in those three cells. Okay, we're now ready for the solver. So, we go to Data Click on Solver, the Solver Parameters dialog 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 or 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 the Solver to the cells B11 through C12. Finally, the constraints. Here we use Subject to the Constraints part. We click Add, and we specify the budget constraint first. B15 less or equal than D15. By the way, we can specify constraints by using equals signs, greater equal signs. We can force decision variables to take integer values or binary values, zero, one. Or we can ask Solver 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 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-negativity constraint for all decision variables at the same time. All we need to do is to check the box Make all Unconstrained Variables Non-Negative. Next, we need to select a solving method. Solver offers several choices here. GRG Nonlinear, Simplex 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 position 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 dial-up 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 the message, Solver found a solution. This was 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 become infinitely large. When you see a message like that, it probably means that you forgot 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. So this was a previous solution, let's go here and change the budget to 180 million. When we run 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 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's 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, the advertising channel with a return of 3%. An only minor portion is devoted to advertising channels with higher 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 the 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 5 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 SolverTable that you can download using this link. The results are presented in a 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, $270 million, this rate drops to around 3.9 cents on a dollar. As the advertising budget increases, its 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, its advertising shifts from China to India, as the advertising budget grows. In this session, we have solved our first optimization model in this course, and there's more to come. So far, we have been dealing with a setting where outcome of any potential choice is highly predictable. In the next session, we'll continue working with an optimization toolkit and we'll look at an example where a decision maker uses a simple measure to evaluate future uncertainty. See you soon.
