42 Chapter 2
CASE STUDY FOR CHAPTER 2
Spreadsheet Analysis of the EOQ at the Neighborhood Pharmacy, Inc.
A spreadsheet is a table of data organized in a logical framework similar to an accounting
income statement or balance sheet. At first, this marriage of computers and accounting
information might seem like a minor innovation. However, it is not. For example, with
computerized spreadsheets it becomes possible to easily reflect the effects on revenue, cost, and
profit of a slight change in demand conditions. Similarly, the effects on the profit-maximizing or
breakeven activity levels can be easily determined. Various “what if?” scenarios can also be
tested to determine the optimal or profit-maximizing activity level under a wide variety of
that can be employed to analyze a variety of typical optimization problems.
To illustrate the use of spreadsheets in economic analysis, consider the hypothetical case
of The Neighborhood Pharmacy, Inc. (NPI), a small but rapidly growing operator of a number of
large-scale discount pharmacies in the greater Boston, Massachusetts, metropolitan area. A key
contributor to the overall success of the company is a system of tight controls over inventory
acquisition and carrying costs. The company’s total annual costs for acquisition and inventory
of pharmaceutical items are composed of the purchase cost of individual products supplied by
wholesalers (purchase costs); the clerical, transportation, and other costs associated with
placing each individual order (order costs); and the interest, insurance, and other expenses
involved with carrying inventory (carrying costs). The company’s total inventory-related costs
During the relevant planning period, the per unit purchase cost for an important
prescribed (ethical) drug is P = $4, the total estimated use for the planning period is X = 5,000,
the cost of placing an order is Θ = $50; and the per unit carrying cost is C = $0.50, calculated
as the current interest rate of 12.5 percent multiplied by the per unit purchase cost of the item.