Economic Order Quantity. A large consulting firm orders photocopying
paper by the carton. The firm pays a $30 delivery charge on each
order. The total cost of storing the paper, including forgone
interest, storage space, and deterioration, comes to about $1.50 per
carton per month. The firm uses about 1,000 cartons of paper per
month.

a. Fill in the following table:
                             Order Size
                   100      200      250     500
Orders per month ________ ________ ________ ________
Total order cost ________ ________ ________ ________
Average inventory ________ ________ ________ ________
Total carrying costs ________ ________ ________ ________
Total inventory costs ________ ________ ________ ________

b. Calculate the economic order quantity. Is your answer consistent
with your findings in part (a)?

I would like to have the answers in an excel spreadsheet if possible.  Thank you.

Hi missmallprincess!
Here are the answers to your questions.

a) You'll find the Excel spreadsheet at the following address:

http://www.angelfire.com/alt/elmarto/googleanswers/340643EOQ.xls
(right-click on the link and choose "Save Target as...")

I've written the spreadsheet in a way such that you can alter the
quantity of cartons used per month, the cost per order and the
carrying cost per unit and see how these changes affect the results.
Please note that I have used the standard assumption that the average
inventory is half the order size.

Looking at the spreadsheet, it's clear that the order size that
minimizes the total inventory cost is 200 units, with a total cost of
$300.

b) You can find the formula for the economic order quantity, along
with an explanation of this concept, at the following link:

http://www.inventoryops.com/economic_order_quantity.htm

Using the forumla and plugging your values gives that the economic
order quantity is exactly 200 units, the same result we got by looking
at the spreadsheet.


Google search terms
economic order quantity


I hope this helps! If you have any doubts regarding my answer, please
don't hesitate to request clarification.

Best wishes!
elmarto

Just in time ordering has some advantages especially for computer
salesman. Let's assume a human uses analysis instead of a computer. 
Dec 6 We have 300 cartons in the warehouse, and an estimated 200
cartons at various locations in the offices. We estimate 900 cartons
use over the next 30 days; We order 500 cartons expecting delivery on
or about Dec 19th. We might have waited several more days to place the
order, but a Christmas rush could mean no delivery until early
January. The 500 cartons arrives Dec 19th, increasing the warehouse
stock to 600 cartons. Apparently the employees ripped off more paper
than usual as 153 cartons are estimated in the various offices. The
warehouse has been deliering 1/2 the requested amount for several
days, except for small requests. This increases delivery costs, but
reduces wasteful consumption which is a trade off unless several
offices run out of paper completely. We check again on Dec 21 as we
might not be able to place an order on Dec 23 or Dec 24 and/or our
supplier may be less competent than usual in an other day or two. We
estmate only 400 cartons are on hand, so we order 500. They arrive
January 2nd, two days sooner than expected. Our warehouse has less
stuff than usual, so we estimate our warehouse handling cost at $1.20
per carton short term. Also our delivery staff is some what less busy
than typical, so we deliver 20% more paper than requested to the
average office. This brings us to 400 cartons in the offices and 600
in the warehouse, the largest inventory since last July. We wait to
place the next order until Jan 12, when we expect total inventory to
be 800 and our supplier estimates delivery on Jan 21. Our cost of
storage went back to $1.50 per month per carton on January 12, so we
should figure five cents per day since part of the month was
different. It does not seem prudent to order less than 500 cartons at
a time, unless the delivery schedual by our supplier is shorter than
the 9 day average, and/or our warehouse will have trouble making room
for that much paper. I'm guessing, so please comment, refute and/or
embelish.   Neil