Spreadsheet Analysis of the EOQ at the Neighborhood Pharmacy, Inc.

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.

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-axix and order quantity, Q on the horizonal X-axis, plot the relation between inventory-related total costs and the order quantity.