Explore BrainMass

What inventory levels to carry? Simulation? Optimization?

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

We are asked (ideally) to use Crystal Ball (CB), an Excel add-in, or Excel itself to do this inventory problem. CB would be ideal, but we can use just plain-old Excel if it can do what needs to be done.

I see this as a simulation or maybe optimization problem, but not sure how to tackle it.

Given historical data on demand and lead time, we have to determine what inventory plannign decisions to make....maybe change future re-order point, new order sizes perhaps? In order to achieve the ideal service rate of 90%..which means 90% of customer demand is satisfied. We don't want to stock too much, or too little.


Mike's Metal Detectors in Memphis is the main supplier of the most popular, high-end metal detector to amateur treasure hunters in the US - the SureShot1000. They buy the SureShot 1000's directly from the SureShot factory in Sacramento, and sell them to the consumers. They also supply accessories like headphones and such, and are a tremendous resource for hobbyists and professional treasure seekers alike.

Over the past few years, Mike Moolah (the proprietor) has noticed a great deal of growth in sales of metal detectors. Part of this is due to the fact that detectors like the SureShot1000 are closing the gap between professional and hobby grade scopes. However, Mike has noticed another development along with increased sales. He has noticed that monthly inventory has increased proportionally, which increases his tax liability. He would like to correct this inventory dilemma. He feels there are basically two decisions influencing inventory: reorder point and order size. The reorder point is the inventory point that determines whether or not an order should be made. For example, if the reorder point is 1000 units and month end inventory is 950, and order is made. If the inventory is 1250, no order is needed. The order size can be thought of as the lot-size in an order. The lot size is 500, then 500 units will be ordered whenever an order is placed.

Mike Moolah thinks if he can intelligently manage the order point and order quantity, he can better control the month end inventory. But with inventory...there are two uncontrollable forces at play, too. They are level of demand and lead-time. Demand is easy: that's how many SureShots are demanded monthly. Lead-time is the amount of time elapsed between the placement of the order and the receipts of this order. Both times vary monthly. Mike has supplied us with historical data in an attached spreadsheet. (**see attachment)

In addition to minimizing the average month's end inventory, Mike wishes to maintain an average service level of at least 90%. 90% service level means that 90% of demand was actually satisfied. This is a key measure, because if we specify a very high service level (say 98%), we end up carrying high inventories in order to supply demand, which is the problem Mike currently has. But, if we prescribe a low service level, we get inventory down but have unmet demand, alienating our customers.

Inventory Assumptions:

1) 500 Units in inventory at start of first planning period
2) Beginning inventory for a period is the ending inventory for prior period. No gaps.
3) Ending inventory for a period is the beginning inventory plus units received, minus demand met.
4) We analyze 12 continuous months of inventory dynamics when we make our recommendations

QUESTION: Use Excel (or Excel's Crystal Ball add-in, but no other software allowed) to forecast optimum inventory levels to meet demand (and considering other factors)...what inventory planning decisions should Mike make????

ASSUMPTIONS: We can make any necessary assumptions (as long as we document them). Order size is 500. Ordering can be done only at the end of month.

© BrainMass Inc. brainmass.com October 24, 2018, 5:34 pm ad1c9bdddf


Solution Summary

The solution answers the question(s) below.

See Also This Related BrainMass Solution

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


View Full Posting Details