0:00

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.

Â 0:41

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.

Â 1:18

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.

Â 1:40

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.

Â 2:15

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.

Â 2:50

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.

Â 3:22

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.

Â 3:56

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.

Â 4:06

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.

Â 4:45

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.

Â 5:42

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.

Â 6:06

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.

Â 6:33

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.

Â 7:24

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.

Â 8:26

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.

Â 9:08

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.

Â 9:43

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.

Â 10:16

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.

Â 11:20

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.

Â 12:38

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.

Â 12:57

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.

Â 14:08

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.

Â 14:26

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.

Â