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.
