Hi, I'm Noah Gantz and we're starting session 3 of week 4 of our operations analytics course. In session 1 of this week, we introduced decision trees to analyze decisions to be made under uncertainty. Then, in session 2, we used simulation to evaluate event nodes within a tree that had a complex set of outcomes. In this session, we'll address another complexity. Suppose there are many alternative choices available at a decision node. For example, suppose our retailer IDEA must decide how many units of the Krusbar tent to order. Then there's a useful alternative to drawing out the many branches that can follow from a decision. We can sometimes use simulation together with optimization to analyze this many decisions. In this session, we'll build on this simulation model we built for IDEA in session 2. And, we'll use the solver that's again introduced in week 2, to optimize among the large set of choices that IDEA can make. In session 3, we're going to use optimization together with simulation to help IDEA choose order quantities. To get started let's remember what the demand model is for IDEA's Krusbar tent. There's a 50/50 chance that demand will be strong or weak. If demand is weak, it'll be uniformly distributed between 2,000 and 8,000 units. If demand is strong, it will be uniformly distributed between 6,000 and 14,000 units. In either case, we're going to call the random variable for demand D, and we'll simulate that in Excel. Other elements of IDEA's problem have remained the same from the beginning. The price per tent is 150 Euros. The order quantity, fixed charge, and unit cost varies by supplier. For supplier S, IDEA orders 5,000 units. It's got a fixed charge of 0. And the unit cost is 120 euros per tent. For supplier P, IDEA orders 10,000 units. It has a fixed charge of 50,000 euros and the unit cost is 100 euros per tent. But suppose IDEA could choose the order quantity. In the original problem, IDEA had to use 100% of the supplier's capacity, all 5,000 units from supplier S and all 10,000 units from supplier P. Suppose that supplier P now offers IDEA the following contract. The upfront charge would increase from €50,000-€100,000. IDEA would need to place the first order of at least 4000 units. IDEA would then place a second order which could very anywhere from 0 to 6,000 units. IDEA would place that second order in response to market conditions. The timing of the second order would be shortly after the start of the summer selling season. At that point, IDEA would know with certainty if the market were weak of strong. We're gonna call the total quantity IDEA orders Q, and that total quantity will vary somewhere between 4,000 and 10,000 units. That's the 4,000 unit minimum from the first order plus anywhere from 0 to 6,000 units for the second order. If IDEA chooses supplier P and the market is weak, we can write out all of the equations that we need. IDEA pays a fixed cost of 100,000 Euros now. If IDEA decides on a total order quantity of Q, that's somewhere between 4,000 and 10,000 units, then the order cost would be 100 Euros per unit times Q. Demand D is now uniformly distributed between 2,000 and 8,000 units. And IDEA earns 150 euros of revenue for each unit sold. If D, the demand, is less than Q, the order quantity, then the revenue would be 150 euros times D, that's the number sold. If D, demand, is greater than Q, the order quantity, then the revenue would be 150 euros times Q. Because at that point, Q would be the number of units sold. We can put those two equations together, and write the total revenue as 150 euros, times the total number sold, or the minimum of D and Q. We can now write IDEA's profit function. There's the 100,000 euro up front cost, the 100 Euro times Q order cost, and the 150 Euro times the sales revenue. Notice that this is almost identical to the profit formula from the last session. But last time, the fixed cost was 50,000 rather than 100,000 Euros. And last time we fixed Q at 10,000 units. Here we're going to let Q be a decision variable. Okay, let's look at the Excel spreadsheet. This Excel spreadsheets called IDEA Optimazation.xlsx and you can download it from the Coursera site. The first sheet is a ten sample version of the problem when IDEA of contracts with supplier P, and demand is weak. If you look in cell B3, you can see the order quantity of 10,000. Because that's going to be a decision variable, I've highlighted it in red. Next we have a fixed cost of 100,000, rather than the 50,000 we had before. We've got the unit price of 150 Euros, and a unit cost for supplier P of 100 Euros. The lower rows in column B, show 10 samples of demand when the market is weak, and you should see that they're familiar from last time. The rest of the spreadsheet is calculated just as before. And you can see that when IDEA orders from supply P, the order size is 10,000 units and the market is weak, the average profits are really a loss of nearly 500,000 euros. I've highlighted the average profit in blue because that's going to be our objective function. Now what we'd like to do is we'd like to optimize by choosing a better order quantity. And to do that we're going to use the solver optimization tool that Sergei introduced to you in week 2. Remember that to access the Solver we go up to the Data tab. And then the Solver menu item. The dialog box is quite small, and as before don't worry if you can't see the numbers clearly, just hang on a moment and I'll show you a slightly larger version of it. But let's go ahead and fill it in. We wanna set our objective to be average profit, and we wanna make sure that Solver maximizes it. Our decision variable is Q, the order quantity. And we just have two constraints. The first constraint is that we want to make sure that the order quantity is greater than or equal to, the 4000 unit minimum. The second constraint is that the order quantity should be less than or equal to, the 10,000 unit maximum. I know that Sergei has told you, if you can, you'd wanna use the simplex method to make it a linear model. Unfortunately this is not a linear model and I'll describe in a little more detail later why that is and what we can do about it. Before I move on though, I wanna show you just a slightly bigger version of the dialogue box. We set the objective to be cell F21, that was the average profit. We wanna maximize the average profit. Our decision variable was cell B3, that was the order quantity. And we had two constraints, that the order quantity, B3, should be less than or equal to 10,000 units and the order quantity B3, should be greater than or equal to 4,000 units. Again, we're gonna stick with GRG nonlinear, because unfortunately this optimization problem is nonlinear. And I'll talk about that again in a moment. Here we are, back at the spreadsheet. I'm going to access the Solver dialog box now. And I'm actually gonna solve the problem. And here's the optimal solution. The optimal solution for supplier P, when the market is weak and we have these ten demand samples, is to order the bare minimum, 4,000 units. In this case, IDEA makes an average profit of 18,150 euros. Now remember that the average profit for these ten samples might not be the average profit for a different set of ten samples, and I wanna talk about that as well in a moment. Before I move on, I wanna point out that the problem's not linear, but we could still solve it. And you can see in the Solver dialogue box on the right, we use GRG Nonlinear. The objective, remember, is to maximize the average profits across all the samples. And the profit within each sample depends on revenue that includes that min function. And the min function is not linear in a not very nice way. But we are lucky that Excel's solver can solve it, although it might not always work. And if you're interested, there's an optional advanced session where I'll show you how to clean up the formulation, and eliminate the problem with non-linearity. I wanna repeat that it's an optional session, and we won't be testing you on it. Okay, let's go back to the optimal solution. And here's a screenshot with the ten samples. Remember, for supplier P in a weak market, the optimal solution here is to order 4,000 units. And the average profit is 18,150 Euros. Of course, that optimal solution is only optimal for that set of ten samples. All right? If we were to run the same optimization on a different set of ten samples, then we might get a different optimal Q. Why? Well, as Sergei showed you in Week 3, a set of 10 samples is quite small. We really need more samples for more precise, stable estimates from the simulation. The same principle holds true for the optimal solution. With 1,000 samples, the optimal Q's would not tend to change much across samples. The spreadsheet's optimal solution of course even with 1,000 samples is just an estimate optimal Q. To find the truly optimal Q using simulation, we need to include an infinite number of samples. Here's the optimal solution for 1,000 samples, when IDEA contract with supplier P in a weak market. This screenshot is from Excel. And I've just put it into PowerPoint. You'll see that the optimal order quantity is about 4,138 units. That's a little more than the 4,000 units that we saw when there were only ten samples. And the objective function value, the average profit, is 51,646 Euros. We can also run the same simulation for supplier P in a strong market. You can see in the upper left that the optimal order quantity is about 8,850 units. And the average profit, the objective function value in the lower right, is about 268,862 Euros. The only difference between this spreadsheet and the other is in column B where we've got a new set of demand samples. Remember in a strong market, the uniform distribution stretches from 6,000 to 14,000 units. Now that we've simulated a weak market and a strong market, and run an optimization for each, we have two estimates of the expected profits for IDEA, and we can fill out the rest of IDEA's decisions tree. Here's the decision tree, let me remind you, on the left is the contracting decision. An IDEA has three choices, it can contract with no one, and earn no Euros. It could contract with supplier S, and if the market were weak, its estimate of the expected profit would be 42,405 Euros. If the market were strong, its estimate of the expected profit would be 150,000 Euros. Finally, IDEA could decide to contract with supplier P. With the new contract we've run the optimization with the simulation, and found estimates for the expected profits for a weak and a strong market. For the weak market, it's 51,646 Euros. And for the strong market, it's 268,862 Euros. We've got the whole decision tree, and we can evaluate the expected value maximizing decision just as we normally would. We'll start out with the event nodes and calculate the expected values. For supplier S it remains just as we had before because nothing's changed. The expected value or our estimate of the expected value for supplier S is 96,202 Euros. With the new contract for supplier P our estimate of the expected value is 160,254 Euros. We're going to now replace the event nodes with those expected values. The last step of evaluating the decision tree is to find the decision that maximizes the expected value. And here you can see the optimal decision is to go with supplier P, with an estimated expected value of 160,254 Euros. So we'll eliminate the other two choices. What's happened? The ability of IDEA to change order quantities in response to market conditions has actually added value for supplier P. IDEA's expected profit from contracting with P has increased enormously, from about 6,500 Euros with a fixed order quantity of 10,000, to around 160,000 Euros now. So, the new contract with supplier P is now favored over IDEA's other options. So, that's it for session 3 of week 4. How did Idea's problem change from last session to this one? As before, we simulated the outcomes for the weak and strong markets. The demand model had a 50/50 chance that the market would be weak or strong. For each case, we then simulated uniformly distributed demand. This time, however, the structure of the decision problem became more complex. First, IDEA needed to decide on a supplier, either S or P, or no one. For supplier P, I could then decide on an order quantity. Rather than running a separate simulation for each possible Q, and we could have run a separate simulation for Q equals 4000, Q equals 4001, Q equals 4002, we used a common set of simulated demands for all the possible Q's. And we then optimized to find an "approximately optimal" Q. In fact, we essentially solved Senthil's newsvendor problem from Week 1. And in next session, we'll go back to see how we do it. In this session, we extended our model of decision making to include more complex decisions. We simulated a single set of demands and calculated average profit as a function of them, and of IDEA's order quantity Q. By optimizing the spreadsheet, we estimated the Q that would maximize IDEA's expected profits. So now we've seen how we can use three analytics tools together to evaluate potentially complex decisions to be made under uncertainty, decision trees, simulation and optimization. Not only that, we also know how to crack the news vendor problem that Senhtil introduced in week one of the course. In session 4, we'll go back to the news vendor to see how it's done.
