Purchase Solution

What inventory levels to carry? Simulation? Optimization?

Not what you're looking for?

Ask Custom Question

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.

Purchase this Solution

Solution Summary

The solution answers the question(s) below.

Solution provided by:
  • BSc , Wuhan Univ. China
  • MA, Shandong Univ.
Recent Feedback
  • "Your solution, looks excellent. I recognize things from previous chapters. I have seen the standard deviation formula you used to get 5.154. I do understand the Central Limit Theorem needs the sample size (n) to be greater than 30, we have 100. I do understand the sample mean(s) of the population will follow a normal distribution, and that CLT states the sample mean of population is the population (mean), we have 143.74. But when and WHY do we use the standard deviation formula where you got 5.154. WHEN & Why use standard deviation of the sample mean. I don't understand, why don't we simply use the "100" I understand that standard deviation is the square root of variance. I do understand that the variance is the square of the differences of each sample data value minus the mean. But somehow, why not use 100, why use standard deviation of sample mean? Please help explain."
  • "excellent work"
  • "Thank you so much for all of your help!!! I will be posting another assignment. Please let me know (once posted), if the credits I'm offering is enough or you ! Thanks again!"
  • "Thank you"
  • "Thank you very much for your valuable time and assistance!"
Purchase this Solution

Free BrainMass Quizzes
Measures of Central Tendency

This quiz evaluates the students understanding of the measures of central tendency seen in statistics. This quiz is specifically designed to incorporate the measures of central tendency as they relate to psychological research.

Measures of Central Tendency

Tests knowledge of the three main measures of central tendency, including some simple calculation questions.

Terms and Definitions for Statistics

This quiz covers basic terms and definitions of statistics.

Know Your Statistical Concepts

Each question is a choice-summary multiple choice question that presents you with a statistical concept and then 4 numbered statements. You must decide which (if any) of the numbered statements is/are true as they relate to the statistical concept.