Monte Carlo Simulation Outline: Inventory Mgt Examples Monte Carlo Simulation Concepts {Ragsdale, Chapter 12}
Figure 12.30 Worksheet: Data
Figure 12.30 Worksheet: Model
1
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
2
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
5,000 Replications at Reorder Point of 28 and Order Quantity of 50:
3
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples Can also run a Decision Table with Two Decision Variables, but monitoring only Service Level (limited to one target cell): To illustrate I’ve increased the step size for each decision variable and reduced the number of trials:
4
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
Using OptQuest To determine the order quantity and reorder point that provides an average service level of say, 98%, while keeping the average inventory as low as possible involves “optimizing” the model using OptQuest. I reset the decision variable step size to 1, then Run | OptQuest:
5
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
Follow the Wizard:
Select Decision Variables:
No relevant Constraints for this problem:
Select Forecast cells – note there can be only one objective, but there can be multiple requirements:
Select Preferences:
(note: I reduced the trials in run preferences to 1,000)
6
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
Results:
Copy results to EXCEL
Re-run at optimal solution (with 5,000 trials) to understand performance:
7
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
See 12.15.4 for details:
8
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
Second Inventory Example Problem1
Home Repair is a large hardware retail store that often has to place orders for hammers. The fixed cost for placing an order is $500, independent of the size of the order. The unit cost per hammer is $20. Home Repair estimates that the cost of holding a hammer in inventory for 1 week is $3. The company defines its inventory position at the beginning of any week as the number of hammers in inventory plus any that have already been ordered but have not yet arrived, minus any backorders. The company’s ordering policy is an (s, S) policy, a common periodic review policy used by many companies. This policy, defined by two numbers s and S, where s"&A45,$E$44:E44) =SUMIF($F$44:F45,">"&A46,$E$44:E45)
Inventory position (amount on hand + amount due in) D44 =SUM(B44:C44)
Order (follow logic of the (s,S) ordering policy) E44 =IF(D44<=Reorder_point_s,Order_up_to_level_S-D44,0)
Week Order Arrives (see when order arrives, if there is an order) F44 =IF(E44>0,A44+CB.Custom($B$20:$B$22,$C$20:$C$22),"NA")
Demand (generate random demands) G44 =ROUND(CB.Normal(Mean_weekly_demand,Stdev_weekly_demand),0)
11
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples
End Inventory (if customer demand is less than on-hand inventory, then ending inventory is the difference, otherwise it is 0) H44 =MAX(B44-G44,0)
Emergency Orders (only if customer demand is greater than on-hand inventory) I44 =MAX(G44-B44,0)
Weekly Costs J44 K44 L44 M44 =IF(E44>0,Fixed_order_cost,0) =Variable_order_cost*E44 =Inventory_holding_cost*(B44+H44)/2 =Emergency_shipment_cost*I44
12
Spreadsheet Modeling
Dr. Grayson
Monte Carlo Simulation Outline: Inventory Mgt Examples Problem 12.12 Logic
Logic
End Inv
ROP
Demand Satisfied prior
Order ?
prior
Begin
Recvd
Qty Demand
OQ
Key relationships: Demand Satisfied = min (demand, beg + recvd) End Inv = Beg + Recvd – Demand Satisfied Order = If (End Inv < ROP, 1,0) Recvd = Prior Order Period Decision * OQ
13
Spreadsheet Modeling
Dr. Grayson