Hi I'm Sergei Sven. I'm an associate professor in the Department of Operations Information and Decisions at the Wharton's School. Welcome to week one of our modelling risk and realities course. The ultimate goal of our course is to introduce you to the techniques of making data driven managerial decisions using spreadsheets. This week we will begin by analyzing settings with lower levels of uncertainty and looking at the ways of identifying the best decisions in such settings. In particular, we'll go over a set of tools combined under the general name of optimization. This optimization approach will prove very useful in the following weeks when we will be looking at more complex settings, settings with a higher degree of uncertainty, and study how different ways of quantifying uncertainty and risk can be implemented using readily available spreadsheet tools. Let's start. In Week 1, we will start working with optimization tool kit that is designed to analyze decisions in low uncertainty settings. In the first of our three sessions, we will use an advertising example to see how to build an algebraic model of a business decision that ties together three main elements. Decisions, objective, and constraints. In session two, we will create a spreadsheet version of this model and use an optimization tool called Solver to find the best decision. We'll also see how this best decision adapts to alternative data inputs. In session three, we will look at an example that adds a simpler presentation of risk to our modeling. Let's have a look at our first example. In this example, we'll look at a company that designs and sells e-reader devices. The company is designed in it's advertising campaign for a new two-version product. And it is interested in understanding how it should distribute its massive advertizing budget of an equivalent of $195 million of US dollars among two major Asian markets, India and China. The company's decision on how to allocate its advertising budget is influenced among other things by how far its advertising dollars go in terms of generating extra sales. A sort of return on advertising. This return on advertising, depends on the product and the market. For example, $1,000,000 spent advertising the standard product in India, return, $50,000. Hudson Readers assumes that advertising effect, adds up across markets and products. So in order to project the cumulative effect of spending $1 million on the enhanced version in China, and $2 million on the standard version in India, we just need to add two separate contributions to the net sales increase. So the company needs to decide how much to spend on each product market combination to maximize the total advertising impact. There's several requirements that the advertising campaign must satisfy. In India, they must get at least $3 million in the net sales increase. In China, at least $4 million. And the enhanced version must not lag too far behind this standard version in terms of the net sales increase. One important aspect of this problem is that it is deterministic. We only use numbers, not probability distributions. For example advertising effectiveness, that return on companies advertising dollars, is a fixed number for each product market combination that we assume would be realized with a probability 100% as opposed to a distribution of numbers with different chances of being realized. This is a very convenient assumption. In particular, it tells us that the company knows exactly the impact of any decision it contemplates. Imagine that the actual advertising effectiveness for the standard product in India is random and can take two values with equal probabilities. The advantage of replacing the random quantities by their expected values, is that one can then have a good chance of solving very large problems, involving many products and many markets. The downside of using such an approach is that the recommendation that comes from an analysis that relies on removing uncertainty may not be applicable to settings where this uncertainty is significant. We'll have a closer look at such settings in the coming weeks. So this is a short version of our problem formulation. We'll first express this formulation using algebra, and then convert it into a spreadsheet form. The first element of an algebraic optimization model is decisions, or decision variables. In this example, the advertising amounts for each product-market combination is what company needs to decide upon. The next element of the model is the objective or objective function, something the decision maker wants to maximize or minimize. In our problem, the company wants to maximize the total net sales increase, so this is our objective function. The word function reflects the fact that the objective is the function of decision variables. The final element of the model is the constraints. In other words, requirements that a decision-maker must satisfy. The four major requirements in our example, first of all, the total amount of advertising spending cannot exceed advertising budget. Also there are three more requirements. The first two enforce the minimum sales increase in India and in China, while the third one stipulates that the net sales increase for the enhanced model must be at least 80% of the net sales increase for the standard model. So we have four major constraints. We have three main elements of our model. And the overall task is to select the values of the decision variables to maximize the objective while staying within limitations applied by constraints. Let's start with decision variables. Left two products in two markets so we have four decision variables. The amount to spend advertising standard version in India, standard version in China, enhanced version in India, and enhanced version in China for variables. A solution is a particular combination of decision variables such as 70, 50, 50, and 25. Now the objective function. In our example, Hudson Readers wants to select the value of decision variables that will maximize the total net sales increase. In order to understand how to express the subjective as a function of decision variables, let's first calculate the total net sales increase that corresponds to a particular solution. For example: 70, 50, 50, and 25. If the company spends $50 million to advertise the standard version in India, the sum will produce sales effect of 5%. So, the net sales increase resulting from this advertising spending will be 0.05 x $70 millions of dollars. Similarly, the other three spending amounts will be multiplied by their respective sales effect values to yield a total net sales increase of $7.25 million. So what if we look at any potential combination of advertising amounts? How much net sales increase will result from ASI, ASC, AEI, and AEC advertising amounts? Well we need to multiply each variable by its own sales effectiveness value, and then add results. This is an algebraic expression that will allow us to calculate the impact of any decision we may want to explore. Now, the constraints. First is the budget constraint. Whatever the company spends cannot exceed the advertising budget of $195 million. Again, let's check if this constraint is satisfied for the solution we looked at earlier. Yes, it is satisfied that all spending is exactly $195 million. Okay, what about the constraint on the total net sales increase reached on the Indian market? Okay, let's calculate how much net sales increased as achieved in India. The answer is $4.5 million. This constraint is satisfied. Now, what about net sales increase reached on the Chinese market? Again, calculating the net sales increase for China, we got $2.75 million. This is short of the goal of $4 million. Now the third guideline. The net sales increase for the standard version is $5.5 million. And the net sales increase for the enhanced version is $1.75 million. The third guideline is obviously violated. Depending on whether they satisfied constraints or not, all solutions can be separated into two groups. Feasible solutions and infeasible solutions. Even if solution violates only one constraint, it isn't feasible. And the solution we looked at is infeasible. So what kind of conditions should a feasible solution satisfy? Let's start with advertising budget constraint. In algebraic form, this is how we express this constraint. The sum of all spending amounts must not exceed $195 million. Let's write down the constraint for the Indian market. We need to multiply each spending amount on this market by the respective sales effectiveness value and request that the sum of these contributions is at least $3 million. In a similar way, we can write a constraint for the Chinese market. Finally, the constraint on the standard versus the enhanced net sales increase. On the left-hand side of this constraint we've calculated the amount of net sales increase realized for the enhanced version, and on the righthand side, the 80% of the net sales increase realized for the standard version. Now we can put all the algebraic expressions we have created into one model that combines decision variables, objective function, and constraints. Note that we have included non-negativity constraint, which stipulates that no spending amount can be negative. This seems like a rather odd constraint to write down explicitly. After all, we all know that advertising amounts cannot be negative. But remember, we're about to pass this model to Excel and Excel does not really know anything about advertising. So this constraint may turn out to be necessary. In the session, we took a first look at the optimization approach designed to identify the best option in business settings where a decision maker can predict with near certainty the impact of any decision he or she considers. In any optimization model three elements must be present. Decisions, objectives, and constraints. Connecting these three elements using algebra may seem challenging or even unnecessary at first, but is often a very important step that makes it easier in practice to identify flawed assumptions or missing modeling features. Once this step is accomplished, it is time to use spreadsheet tools to find the best options. We'll do that in session two. See you then.
