A. Set up a table or spreadsheet for NPI's order quantity (Q), inventory-related total cost (TC), purchase price (P), use requirement (X), order cost (Θ), and carrying cost (C). Establish a range for Q from 0 to 2,000 in increments of 100 (i.e., 0, 100, 200, ..., 2,000).

B. Based on the NPI table or spreadsheet, determine the order quantity that will minimize the company's inventory-related total costs during the planning period.

C. Placing inventory-related total costs, TC, on the vertical or y-axis and the order quantity, Q, on the horizontal or x-axis, plot the relation between inventory-related total costs and the order quantity

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 operating conditions. Thus, it becomes easy to quantify in dollar terms the pluses and minuses (revenues and costs) of alternate decisions. Each operating and planning decision can be easily evaluated in light of available alternatives. Through the use of spreadsheet formulas and so-called "macros," managers are able to locate maximum or minimum values for any objective function based on the relevant marginal relations. Therefore, spreadsheets are a very useful tool 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 are given by the expression:

TC = P × X + Θ × X/Q + C × Q/2

where TC is inventory-related total costs during the planning period, P is the purchase price of the inventory item, X is the total quantity of the inventory item that is to be ordered (used) during the planning period (use requirement), Θ is the cost of placing an individual order for the inventory item (order cost), C is inventory carrying costs expressed on a per unit of inventory basis (carrying cost), and Q is the quantity of inventory ordered at any one point in time (order quantity). Here Q is NPI's decision variable, whereas each other variable contained in the total cost function is beyond control of the firm (exogenous). In analyzing this total cost relation, NPI is concerned with picking the order quantity that will minimize total inventory-related costs. The optimal or total cost minimizing order quantity is typically referred to as the "economic order quantity."

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.