Your supervisor tells you to create a spreadsheet that can analyze the inventory quantity and ordering using the POQ model. Note..you may want to do the EOQ too as a comparison or if you just cannot figure out the POQ. If the latter happens, I may be able to give partial credit though there is no penalty for only doing the POQ. She is going to find data for the last 18 months and wants you to create a spreadsheet that she can paste the data into. Demand varies but averages about 1600 per month.

While she gets the actual data you recognize that you will need to make up some data for your development purposes. Furthermore you plan your spreadsheet to have your demand data in column B and your formulas reference this data. Use the spreadsheet named Inventory Work. One technique I use is to make up a few data points that average what I want and then copy them down to fill the series. When the real data is obtained, it is pasted over these.

Cost, Volume and Calculation Considerations

The lead time from placing an order to receipt is one month and reliable. The order can be shipped gradually as it is produced. You plan for a low probability of stock out of 1.5 percent maximum, though it may change.

Order volume, demand, has some variability with a normal looking distribution and the mean and variance to be determined by the data used. See the hint above.

Ordering and setup costs are approximately $27,500 per order whereas holding costs for a unit of inventory are charged at 3% per month or 36% per year based upon the purchase price of $175. The production rate is 10,000 units per month.

You want to know total inventory costs associated with your order quantity (POQ) and reorder point (ROP) decisions. These include ordering costs and holding costs where the holding costs include average inventory holding costs and safety stock (SS) holding costs.