What are inventory assumptions

Question

Assignment:

What inventory levels to carry? Simulation? Optimization?

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.

BACKGROUND:

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.

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

Q: 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.

The table is shown below:

Lead Time (Months)
1
1
2
2
2
2
2
2
2
2
2
4
2
2
2
2
2
2
2
1
2
5
3
1
1
2
1
1
4
1
2
3
2
1
2
2
2
2
2
2
4
2
2
2
2
2
2
1
1
1
2
2
2
2
1
5
5
5
3
2
2
2
2
2
2
1
2
3
3
1
2
2
2
2
2
2
2
2
3
2
2
2
2
1
3
2
2
2
2
1
1
2
2
2
2
2
2
3
3
3
2
1
2
2
2
4
4
2
2
2
2
4
3
5
3
2
2
2
2
3
2
2
2
1
2
2
2
2
2
2
2
2
1
2
2
2
5
2
2
2
2
2
2
1
2
2
1
1
2
2
2
2
2
2
2
3
3
3
2
2
2
1
1
2
2
2
2
2
5
2
3
1
2
1
2
2
2
1
3
4
2
2
2
1
2
2
2
2
2
2
2
2
2
2
1
2
1
2
2
2
2
2
2
2
2
1
2
2
1
2
1
3
2
1
1
1
4
2
2
2
2
2
2
2
4
2
2
1
2
1
1
3
2
2
2
2
3
2
2
2
2
1
3
2
2
2
2
2
2
2
2
2
5
2
3
1
3
3
1
2
2
2
2
2
2
2
1
3
2
2
2
1
2
5
2
2
2
2
2
3
1
5
1
2
2
1
2
2
2
2
2
2
5
3
1
4
3
2
2
2
2
2
2
2
2
1
2
1
2
2
2
2
2
2
2
1
2
2
5
2
2
2
2
2
4
2
1
2
2
2
1
3
2
3
1
2
1
3
2
1
4
5
2
2
2
2
2
4
2
2
2
2
2
2
5
2
3
2
2
5
2
3
2
2
2
2
4
2
5
2
2
1
2
2
2
2
2
2
1
2
1
5
2
1
2
5
2
3
2
2
2
1
2
1
5
1
3
2
1
2
3
2
2
3
2
2
2
2
2
1
2
1
4
1
2
1
1
3
3
1
1
1
1
1
3
5
3
1
2
2
2
1
2
2
2
3
3
1
1
2
1
2
5
3
2
3
2
1
1
2
3
2
2
3
3
3
1
3
2
2
2
1
2
1
2
2
1
2
2
2
2
2
4
1
2
2
1
2
2
1
2
2
3
2
2
4
2
2
1
2
1
4
2
2
3
2
2
2
1
5