Explore BrainMass

# Spreadsheet Analysis of the EOQ at the Neighborhood Pharmacy

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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.