Spreadsheet Analysis of the EOQ at the Neighborhood Pharmacy, Inc.
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.
1. 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)
2. 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.
3. Placing inventory-related total costs, TC, on the vertical or Y-axix and order quantity, Q on the horizonal X-axis, plot the relation between inventory-related total costs and the order quantity.