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


A.   Set up a table or spreadsheet for NPI's order quantity (Q), inventory-related total cost (TC), the 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).


The 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) appears as follows:


Quantity (Q)
Total Cost (TC)
Price (P)
Use Requirement (X)
Order Cost θ
Carrying Cost (C)
0

4
5,000
$50
$0.50
100
$22,525
4
5,000
50
0.50
200
21,300
4
5,000
50
0.50
300
20,908
4
5,000
50
0.50
400
20,725
4
5,000
50
0.50
500
20,625
4
5,000
50
0.50
600
20,567
4
5,000
50
0.50
700
20,532
4
5,000
50
0.50
800
20,513
4
5,000
50
0.50
900
20,503
4
5,000
50
0.50
1,000
20,500
4
5,000
50
0.50
1,100
20,502
4
5,000
50
0.50
1,200
20,508
4
5,000
50
0.50
1,300
20,517
4
5,000
50
0.50
1,400
20,529
4
5,000
50
0.50
1,500
20,542
4
5,000
50
0.50
1,600
20,556
4
5,000
50
0.50
1,700
20,572
4
5,000
50
0.50
1,800
20,589
4
5,000
50
0.50
1,900
20,607
4
5,000
50
0.50
2,000
20,625
4
5,000
50
0.50






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


Based on the NPI spreadsheet, the order quantity that will minimize the company's inventory-related order costs during the planning period is Q = 1,000, the total cost-minimizing order level.