Hi. I'm Noah Ganz, and this is session four of week four of our Operations Analytics course. In the previous session, we used solver to find IDEAS optimal order quantity, Q, for supplier P. We started with a simulated sample of demand. And then optimized the queue. In this session, we'll use the same approach to solve the news vendor problem. We'll start with the demand forecast Senthil's proposed in week one. And we'll use simulation and optimization to select an optimal order quantity that maximizes the estimate expected value. We'll also consider the notion of riskiness that Sergey introduced in week three. For any order quantity we can calculate the standard deviation of ideas profit. And we'll see how to add or solve a constraint that places an upper bound on the standard deviation of the profit. Okay this is session four and we're gonna wrap up the week. The way we're gonna do it is we're gonna go back to the news vendor problem that we introduced at the beginning of the course. Let's remember Senthil's newsvendor problem from week one. You're selling wodgets, the unit cost of a wodget is three talers. The sales price is 12 talers and there's no salvage value. You've got a bunch of historical demand that you can analyze. And you see that the demand's been variable and uncertain. What you'd like to do is use the data that you have, the demand and the cost and revenue data, to figure out how many wodgets, Q you should order to maximize expected profit. We now know how to find a good order quantity. We'll use the historical data to forecast future demand. Senthil had a normally distributed forecast with a mean of 52.81 and a standard deviation of 15.1 We'll use the demand forecast then to drive a simulation. In the simulation, if we order quantity Q and demand is D, then our profit pi is going to be 12 tailors times the quantity sold. And that's just the minimum of demand in the order quantity minus three tailors times the quantity ordered. For a given Q, we can simulate samples of D. And for each sample, we can calculate a profit, pi. Once we've got the profits for each of the samples, we can then calculate the average. Finally, we'll use optimization to find an average profit maximizing Q for the sample. Our objective will be to maximize the average profit that we've calculated. And the decision variable will be the order quantity. If we want we can place constraints on the minimum and maximum order quantity. The optimal q of course maximizes the average profit just for that sample. But if the sample is large, we know it's a good estimate for the optimal Q for the demand forecast. Let's take a look at a spreadsheet where we'll build the simulation and the optimization. We can implement our simulation and optimization in Excel again. I'm going to use a template that's a part of a file newsvendoroptimization.xlsx, that you can download from the Coursera website. Again, the template has all of the labels filled in, and we're going to fill in the numbers and the formulas. At the top, we have the sales price, and that's 12 tailors. We have the unit cost, and that's three tailors and the order quantity is what we want to find. For the moment, I am going to put in an order quantity, lets say, of 40. Notice that I've highlighted the order quantity with red to identify it as the decision variable. In column b, we're going to sample fifty samples from the demand distribution which is normally distributed with a mean of 52.81 and the standard deviation of 15.1. Now, if you look carefully, you can see here the numbers go one through eight. And then 49 and 50 and that's because I've hidden rows 18 to 57. But don't worry, when we use the random number generator to fill in those numbers the hidden cells will be filled in as well. So let's go now and use the random number generator. Remember from week three if we click the data tab. And the data analysis menu item, and then the random number generation menu item. We can choose to sample from the normal distribution. Again, this dialogue box is very small and in a moment I'll show you a larger version of it. We're going to sample one random variable, that's demand. We're going to sample this time, 50 numbers, and we're going to sample from a normal distribution. The normal distribution has two parameters of mean. In this case the forecast mean is 52.81 and the standard deviation. And here the forecast standard deviation is 15.1. We'll use our familiar random seed, one, two, three, four, and we want to tell Excel that the output range starts in cell B10. Having set everything up, we can click OK and it will generate our 50 normally distributed random numbers. Notice again that by definition, a normal distribution will have fractional numbers coming out of it. And I'm just going to reduce the number of digits after the decimal to clean it up a little bit. Before we move on, I want to show you that random number generator dialogue box. Again there's one random variable that's the demand. We took 50 samples of demand. The demands were normally distributed with a mean of 52.81 and a standard deviation of 15.1. We'll use the Random Seed 1234, and the first cell in the output range was cell B10. Okay. Let's go back now to the spreadsheet. So we now have all the data that we need and we can start constructing Excel formulas to calculate the other important statistics. As before, the sales revenue equals the sales price, in this case it's 12, times the minimum of the order quantity, here it's 40, and the demand for that sample. So here you can see the sales revenue is 424.5, and that's driven by the demand, because 35.4 is less than 40. The unit cost is always going to be 3, times, the order quantity, so that should be 120 and total profit is simply the sales revenue minus the unit cost. So in this first sample we can see that the demand sample was 35.4. The sales revenue was about 424, the unit cost was 120, and the profit was about 304 talers. One we have the calculations for one sample, we can copy them down for the rest of the samples. And here's what they look like. It's interesting to see that for the samples that we see, after the first two all of the samples are above the order quantity of 40. And in all of those cases, the profit for each sample is 360. For example, if we change the order quantity, let's say to 60. Then we're gonna see in many cases the demand was less than 60 and we see the total profits changing. Again, once we have the profit for each of the samples, then we can use the Excel formula average to calculate the average profits for the simulation. And notice that I've highlighted that cell in blue, because that's going to be our objective function. And finally, if we're interested, we can also calculate the standard deviation using the Excel function. And here, you can see the standard deviations, about 159. We'll actually come back to that a little bit later. So now we've got the spreadsheet set up and we've seen that each time we change the order quantity, the average profit changes. Just as we did in last session. We're going to define an optimization problem where we're going to maximize average profits by changing the decision variable, which is the order quantity. Recall from week two, that we can access Excel solver by the data tab. And then the solver dialogue box. Again, the dialogue box is very small. And in a moment I'll show you a larger version that's easier to see. We're going to set our objective to be the average profit. And we wanna make sure that we maximize it. Our decision variable is the order quantity. And I'm just going to have one constraint, and that is I want to make sure that the order quantity is greater than or equal to zero. Okay, notice I could I could have just clicked this Make Unconstrained Variables Non-negative. That is the same thing as saying the variable C5, or the order quantity, has to be greater than or equal to zero. Here I've done it explicitly. It doesn't hurt to have both. As we saw last time, this problem's nonlinear because of the min function, which defines the revenue. So I'm going to leave GRG nonlinear. If you're interested in seeing why that is and what you can do to streamline the formulation, you're welcome to see the optional advanced session. But again, it's only optional. There's no requirement to see it. So let's run the optimization problem with the 50 samples. And you can see in this case, the optimal solution is to order 59.5 units and, earn an average profit, across the 50 samples, of around 402 Taylors. We can also include constraints on risk. Recall Sergei's session on risk and reward. That is, often decision makers must trade off risk and reward. A common measure of the reward is the expected value. That's risk neutral. One common measure of risk out of many is the standard deviation. Suppose we wanted to limit the news vendor's risk. That is, suppose we wanted to limit the standard deviation of the profit, so that it's no more than 125 talers. We can update our optimization problem to limit that risk. We'll use Excel to calculate the standard deviation of the profit. And we'll add a constraint that limits that standard deviation for any Q. Note, the standard deviation function is not linear, but it is something called quadratic, it just has square terms in it. And that's a well behaved kind of non linear function. If you want to learn more about non linear optimization and quadratic functions. Look at a book on business analytics for example the one we show here. Here we are again at our Simulation and Optimization problem that we started building up earlier in the session. Remember, originally we maximized the average profit which was 402 tailors by choosing an order quantity, and in this case the optimal order quantity was 59.5. What we'd like to do now is add one additional constraint. And that is, we want to make sure that the standard deviation of the profits, down here you can see below the average profit. It's about 158, we want to make sure that, that does not exceed 125. And that's also very easy to do. Let's go again to the solver dialogue box. Here's our original optimization problem. We're just going to add one more constraint and the constraint is that whatever number is in cell E62, which is the standard deviation should be less than 125. So that will limit the standard deviation of any solution to be less than or equal to 125. We can then resolve the optimization and we see that the optimal order quantity has now reduced from 59.5 to 50.3. By reducing the order quantity, we've been able to bring the standard deviation down from more than 150 to 125. Of course the price that we've paid is we've reduced the average profit from more than 400 to about 382. And what we've done is to reduce our risk, we had to pay a price of reducing the reward. In this case, the reward is the average profit. So that's it for week four, session four. We came back to the newsvendor, that's a fundamental problem in operations. Given the price, cost, and demand history, we need to choose a good order quantity Q. We used the demand forecast from week one, along with the simulation and optimization tools from weeks two and three to find a good Q. With these tools, finding an effective Q was easy. We simulated a large number of demands. We then optimized to find a Q that maximized the average profit for the sample. We used the optimal Q for the sample as an estimate for the optimal Q for the overall problem. Adding a constraint on the standard deviation of the profit was also easy, there are two caveats to remember. First, the "min (D,Q) in the middle of the problem is not linear. And if you're interested to see why and what you can do about it, there is an optional advanced session that you can take a look at. The other caveat is the standard deviation is also not linear, but as I said before, that's not a problem. In this last course session, we return news vendor problem that started the course. Our input data where Senthil's forecast of the demand distribution, along with the newsvendor's revenues and costs. We built a simulation model that calculates average profit for a sample of demands in any order quantity Q. We then use solver to optimize the simulation spreadsheet and find an approximately optimal que. That's the queue that maximizes the average profit of the samples. We also define the news vendor's risk tolerance as limiting the estimated standard deviation of the profit. And we added a constraint to our optimization model to ensure that the optimal solution would have an estimated standard deviation that's less than the limit. More broadly, in week four we've taken a look at decision-making under uncertainty when the outcomes of decisions depend on many factors and can be uncertain. We started by introducing decision trees. As a tool to describe and analyze these problems. And we used simulation and optimization to extend the range of settings that can be tackled using trees. Not bad for a weeks work.
