EOQ Analysis with Excel and Excel QM

[Page 746 ( continued )]

Exhibit 16.2 shows an Excel spreadsheet set up to perform EOQ analysis for our noninstantaneous receipt model example. The parameters of the model have been input in cells D3:D8 , and all the formulas for optimal Q , total cost, and so on have been embedded in cells D10:D14 . Notice that the formula for computing optimal Q in cell D10 is shown on the formula bar at the top of the screen.

Exhibit 16.2.

In Chapter 1 we introduced Excel QM, a set of spreadsheet macros that we have also used in several other chapters. Excel QM includes a set of spreadsheet macros for "Inventory" that includes EOQ analysis. After Excel QM is activated, the "Excel QM" menu is accessed by clicking on "QM" on the menu bar at the top of the spreadsheet. Clicking on "Inventory" from this menu results in a Spreadsheet Initialization window, in which you enter the problem title and the form of holding (or carrying) cost. Clicking on "OK" will result in the spreadsheet shown in Exhibit 16.3. Initially, this spreadsheet will have example values in the data cells B8:B13 . Thus, the first step in using this macro is to type in cells B8:B13 the data for the noninstantaneous receipt model for our I-75 Carpet Discount Store problem. The model results are computed automatically in cells B16:B26 from formulas already embedded in the spreadsheet.

---

[Page 747]

Exhibit 16.3.