Page 435 -

ECONOMIC ORDER QUANTITY (EOQ) MODEL 415

Figure 10.4 Worksheet for the Cape Cola EOQ Inventory Model

EXCEL file
EOQ

EOQ models in general, which indicates that if we have at least reasonable estimates
of ordering cost and holding cost, we can expect to obtain a good approximation of
the true minimum cost order quantity.

Excel Solution of the EOQ Model

Inventory models such as the EOQ model are easily implemented with the aid of
worksheets. The Excel EOQ worksheet for Cape Cola is shown in Figure 10.4. The
formula worksheet is in the background; the value worksheet is in the foreground.
Data on annual demand, ordering cost, annual inventory holding cost rate, cost per
unit, working days per year and lead time in days are input in cells B3 to B8. The
appropriate EOQ model formulas, which determine the optimal inventory policy,
are placed in cells B13 to B21. The value worksheet in the foreground shows the
optimal economic order quantity 1824.28, the total annual cost E3 648.56 and a
variety of additional information. If sensitivity analysis is desired, one or more of the
The Management input data values can be modified. The impact of any change or changes on the
Scientist software has an optimal inventory policy will then appear in the worksheet.
inventory module that The Excel worksheet in Figure 10.4 is a template that can be used for the EOQ
can also be used to solve
the inventory problems in model. This worksheet and similar Excel worksheets for the other inventory models
this chapter. presented in this chapter are available on the online platform that accompanies this text.

Summary of the EOQ Model Assumptions
To use the optimal order quantity and reorder point model described in this section,
an analyst must make assumptions about how the inventory system operates. The
EOQ model with its economic order quantity formula is based on some specific
assumptions about the CBC inventory system. A summary of the assumptions for

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.