# Excel Reorder Point - DOC

Document Sample

```					Undergraduate Littlefield Game: Inventory Management
Fixed-order quantity model with safety stock.

***************

PLEASE PRINT

****************
Dear students,

On the responsive game, you need to apply what you have learned in inventory
management, i.e., you need to set up the reorder point (R) and reorder quantity or
economic order quantity (EOQ) properly.

You MUST do the following analysis on the first day of the game and show it to me on
your report. Open your textbook (page 601).

A. HOW TO CALCULATE R (REORDER POINT).

Click the “customer orders” icon on the LT web so that you can calculate:

Average number of jobs arriving each day = customer orders each day
= daily demand = daily inventory usage = d bar
Standard deviation of daily demand = sigma d

How to do it? Go to LT web, then click the “customer orders” icon> “plot job arrivals”>
“download”> Save on desktop After that, open it using Excel and apply the following
formulas (assuming you have 50 data or 50 days and your data starts at cell B1):

=AVERAGE (B1:B50)

=STDEV(B1:B50)

If you do not have 50 data or your data starts at a different cell, then modify the formulas
accordingly. Although your computer has Microsoft Excel, in some cases your computer
does not allow you to open the LT data by clicking “Open Using.” Two ways to
overcome this problem:

1. Easy way: Open it using Microsoft Word and then copy and paste into Microsoft
Excel.
2. More difficult way: Open Microsoft Excel>File>Open>Look in Desktop> File
name: All files or *.*> Click the LT data>Open>Next>Next> Finish.

Purchasing (new inventory/raw material) lead time is L = 4 days (constant).

Sigma L = SQRT (L x (sigma d) ^2)

Page 1 of 2
Therefore, you must set your reorder point (R in batches) at least

= demand during the lead time + safety stock [equation 15.5, page 601].
= 4 x d bar + safety stock

Where safety stock = z x sigma L

Assuming the customer demand follows the normal distribution and using the empirical
rule in statistics, if you set z = 3 then you will cover 99.74% of the demand. Therefore, if
you use z larger than 3 such as 4 or 5, most likely you always have enough raw material
inventory, i.e., no stock out. However, larger z also means that you carry more
inventories that cost your money. I explained this in class.

B. HOW TO CALCULATE EOQ (ECONOMIC ORDER QUANTITY).

To find EOQ, refer to example 15.4 (page 603) where:

S = \$1000

C = \$600 per batch of 60 kits

H = 10% x per year of \$600 per batch of 60 kits = \$60

1 year = 365 days

D = yearly demand = d bar x 365.

Qopt in batches = EOQ in batches = SQRT (2 x d bar x 365 x 1000 / 60)

Good luck!

Regards,

Ahmad Syamil

Page 2 of 2

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 533 posted: 2/3/2011 language: English pages: 2
Description: Excel Reorder Point document sample
How are you planning on using Docstoc?