Explore BrainMass

WalMart simulation re-order point

Please see the following. Calculate re-order point. Format is Excel. (see attached file for data).

Wal-Mart provides lower prices because they optimize their inventory via simulation to reduce their costs.

Simulation is used to determine when and how many units to order from their suppliers, knowing that if they have inventory that is not sold within a period of time they will pay penalties for space and interest which results in holding or carry costs. On the other hand, if they are out of supply and can't meet demand there is a loss of sales cost for each unit not available when needed. They also pay a fixed fee for whenever they place orders on their suppliers called the order cost.

Wal-Mart is expecting a big season for Flat Panel TV sales in 2008 and planners are determining their inventory policy right now.

Part A:
Using the assumptions given below develop an Excel/Crystal Ball simulation and recommend an inventory policy for ordering Flat Panel TVs from their suppliers that would minimize the costs mentioned above. Specifically, determine, when (reorder point) and how many Flat Panel TVs Wal-Mart should order from their suppliers.

Assumption 1: Demand for FLAT Panel TVs is N(100,000, 7,000) TVs per week, except from Thanksgiving Thru Xmas (Week 48thru Week 52) where it is N(150,000, 10,000) TVs per week
Assumption 2: Delivery time from the suppliers is fixed at 2 weeks (available to sell the 3rd week)
Assumption 3: The penalty for missed sales is $100 per unit not available
Assumption 4: The holding cost penalty is $10 per unit held but not sold each week
Assumption 5: The order cost is fixed at $1,000,000 per re-order independent of quantity
Assumption 6: Assume an initial inventory 0f 100,000 TVs

The order point should be determined by comparing the current and outstanding (on order but not yet delivered) inventory to a fixed quantity you determine. The order quantity you determine should also be fixed (constant per reorder).


Base the reorder decision on actual plus outstanding inventory (not arrived yet). Set up the order point and the order quantity as decision variables and use Crystal Ball decision tables to find best result. An example Excel template is included below. Yellow are decision variables and blue are forecasts (only TOTAL COST needs to be forecasted).

Part B:

Modify the model to handle the case where the supplier's lag time is distributed as follows:
1 week .1 (available 2nd week)
2 weeks .4 (available 3rd week)
3 weeks .2 (available 4th week)
4 weeks .1 (available 5th week)
5 weeks .1 (available 6th week)
6 weeks .1 (available 7th week)

The following template should help you set up the problem. Feel free to use, not use, modify, etc. Double click to see all rows and columns.

Extend to Week 52



Solution Summary

Wal-Mart simulations re-order points are provided.