Question

Hello I need help to figure out the MIN Inventory for my stimulation game. I got the daily ending inventory but how do I get the min inventory and total capactiy needed in excel. For example, I got my available capacity of 20, and the daily ending inventory by taking the beginning inventory-the demand+available capacity. But I'm not sure how to calculate min inventory and total capacity needed in excel. Also can you please help with Order point below. thank you!

There are four critical variables in the game: the reorder point, the order quantity, shipping mode, and capacity. These variables interact with one another. That interaction is described below.

We'll begin by assuming that the reorder point (called order point in the game) is 300, the order quantity (called quantity in the game) is 150, and the capacity is 20. These, by the way, are the actual values that you'll have at the start of the simulation.

The reorder point (ROP) determines when an order will be sent to the factory for production. If your ROP is 300, for instance, and your inventory level is 400, an order to the factory won't be triggered until 100 units or more have been used to fulfill customer orders (demand). When you do reach 300 units on hand or less, then an order for 150 units (the order quantity) will be sent to the factory automatically. Since capacity -- the amount that can be produced in a given period of time -- is 20 units, it will take 7.5 days to make the order (150 units / 20 units of capacity). Note that the order will not ship until all of the units have been produced.

Once produced, it will take 7 days for the order of 150 units to reach the warehouse if you ship by truck. (It would take one day to ship by mail, but it can cost more to do so; which mode to use and when is something you need to figure out).

Technically, an order counts as inventory at the warehouse as it is being transported, but it can't be shipped to a customer until it arrives there. So, if the order quantity is 150 units, the ROP is 300, and if it takes seven days to transport, a series of days in which demand is 25 units per day will cause you to run out of inventory at the factory after 12 days (300/25=12), and material will not arrive at the warehouse until 2.5 days later (7.5 days to produce 150 units and 7 days to transport them to the warehouse = 14.5 days; 12-14.5=-2.5).

That having been said, you need to:

1. Set the ROP to a level that will sustain demand until material can be produced and transported to the warehouse;
2. Set the order quantity to an amount that will minimize transportation costs and will enable you to get material to the warehouse before you run out;
3. Have sufficient capacity to produce orders quickly and to enable you to keep up with demand -- ideally without having far too much capacity.

If, under the scenario given above, you had set the ROP to 500 units, and had kept the order quantity at 150 and capacity at 20 units, then with demand of 25 units per day, you would have had 20 days of supply on hand when the order was sent to the factory for production. Given a constant level of demand of 25 units per day, you would have had roughly 5 days of inventory left on hand when the order from the factory arrived at the warehouse (500 / 25 = 20 days - 14.5 days to produce and transport the order = 5.5 days). Likewise, if you had increased capacity to 75 units per day, and kept the ROP and order quantity constant at 300 and 150 units, respectively, then you would have had 3 days of inventory on hand when the truck arrived at the warehouse (300 / 25 = 12 days; 150 / 75 = 2 days to produce; 7 days to transport; thus, inventory on hand = 12 - 2 - 7 = 3).

One thing that was implied in the description above needs to be stated explicitly: orders are completed in their entirety (all 150 units in an order will be completed at the same time), and all of the units in an order will ship immediately after completion (i.e., if you ship by truck, then the truck will have 150 units on it; if you ship by mail, then all 150 units will ship at the same time.) Consider, too, that if you ship by truckload quantities, the maximum number of units per truck is 200; if your order size is 300, then 200 will ship on the first truck and the second truck will only have 100 units on it. The implications on the per unit shipping costs need to be considered here

Answer 1

We need to keep in mind the following points while deciding Reordering point, Order quantity & production capacity per day.

- Minimum transportation days 7, so during transportation days sale quantity required will be 175 (25*7)

- The truck load is 200, the 200 order quantity will not work because it will require significant increase in production capacity. As a result for lowest transportation cost we need to consider option of 400 order quantity

- When the order quantity is 400 per order, it will fulfill 16 days demand with 25 quantity per day, so we have only 16 days time to produce the order. Out of 16 days 7 days are required for transportation so we have balance 9 days for production. In balance 9 days we need to produce 400 quantities so production capacity will be 44.44 units (400/9).

- The re-order point can be 400 units since order quantity 400 will be delivered in 16 days and 400 inventory will be sufficient to cater demand of 16 days, however it is proposed that Re-order point is be 450 units so as to have 2 days inventory in hand for emergency

In view of the above :

Reorder point is 450 Unit

Order quantity is 400 Unit

Production capacity should be 44.44 units per day. This will ensure that production will run for 9 days and then it will have break for 7 days which is inevitable considering transportation time.     

Hope this is helpful -- let me know if you need further information on this.