ABC Classification

(only edit green-shaded cells)

Input: Output: SORT
Input unit cost and annual usage, then press SORT to
reorder items and draw graph (located below).
(Reordered from highest to lowest value)
% of % of Cum % of Cum % of
Item Unit Annual % Total Total Total Item Unit Annual % Total Total Total Total Total
No. Cost Usage Quantity Value Value No. Cost Usage Quantity Value Value Value Quantity
1 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
2 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
3 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
4 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
5 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
6 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
7 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
8 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
9 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
10 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
11 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
12 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
13 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
14 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
15 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
16 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
17 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
18 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
19 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
20 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
21 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
22 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
23 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
24 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
25 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
26 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
27 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
28 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
29 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
30 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
31 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
32 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
33 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
34 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
35 #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! 0.00%
Sum 0 #DIV/0! $0 #DIV/0! 0 0.00% $0
Cumulative % of
Drag and drop lines and

Total Value
12

labels on graph to 10

approximate A, B, and C 8

ABC Classification
6
items. Adjust size of B C
4

lines as needed. 2 A
1200.00% 0

0 2 4 6 8 10 12

1000.00%
800.00% C
Note: the y-axis is
based on # of
600.00% different items, not
B total quantity; see
400.00% help file to change
y-axis
200.00%
A
0.00%
0 5 10 15 20 25 30 35 40
Cumulative # of Items
The Economic Order Quantity Model
(only edit green-shaded cells)
Input:
Input costs, demand and days per year. Excel will calculate the optimal order
Carrying cost, Cc =
quantity, total cost, the number of orders per year, and the order cycle time.
Ordering cost, Co = Costs are graphed for various quantities ordered.
Annual Demand =
Days per year =
$12.00
Output:
Qopt = #DIV/0! $10.00
Total Cost, TC = #DIV/0!
Orders per year = #DIV/0! $8.00

Order cycle time = #DIV/0! days

$6.00
Ordering
$4.00 Cost
Graph: Carrying Cost
Total Cost
Q Co * D/Q Cc *Q/2 TC $2.00
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! $0.00
! ! ! ! ! ! ! ! ! ! ! !
#DIV/0! #DIV/0! #DIV/0! #DIV/0! /0 /0 /0 /0 /0 /0 /0 /0 /0 /0 /0 /0
IV IV IV IV IV IV IV IV IV IV IV IV
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #D #D #D #D #D #D #D #D #D # D #D #D
Units
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
The Production Quantity Model
(only edit green-shaded cells)
Input costs, demand, and production information. Use the annual days/months/weeks cell
Input: to convert demand to the same time terms as production .
Carrying cost, Cc =
Setup or Ordering cost, Co =
Annual Demand, D = 0 $12.00
Annual days, months or weeks = $10.00
Demand rate, d =
Production rate, p = $8.00
Setup Cost
$6.00
Carrying
Output: Cost
$4.00
Qopt = #DIV/0! Total Cost
Total Cost, TC = #DIV/0! $2.00
Production run length = #DIV/0!
Number of runs = #DIV/0! $0.00
! ! ! ! ! ! ! ! ! !
Maximum inventory = #DIV/0! /0 /0 /0 /0 /0 /0 /0 /0 /0 /0
IV IV IV IV IV IV IV IV IV IV
Cycle Time = #DIV/0! #D #D #D #D #D #D #D #D #D #D

Graph: Q Co * D/Q Cc *Q/2 * (1-d/p) TC

#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
A Quantity Discount Model with Constant Carrying Cost
(only edit green-shaded cells)
Input:
Input costs, demand, and price break quantities with associated unit
price. Excel will calculate the total cost for the EOQ and price break
Carrying Cost, Cc quantities.
Ordering Cost, Co
Annual Demand
Price Break Qty
Unit Price

Output:
C0 D Cc Q
Price TC= + + PD
Break Qty Unit Price Qopt Discount Q Total Cost Q 2
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Discount Q: If the EOQ appears in the interval, the
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
discount Q is the EOQ. Otherwise, the discount Q is the
0 $0.00 #DIV/0! #DIV/0! #DIV/0! price break quantity.
#DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Total Cost w/Qty Disc

12.00

10.00

8.00
TC

6.00

4.00

2.00

0.00
0 1 2 3 4 5 6 7 8 9
Q
A Quantity Discount Model with Variable Carrying Cost
(only edit green-shaded cells)
Input:
Input carrying cost %, ordering cost , demand, and price break quantities with
associated unit price. Excel will convert to carrying cost $ and calculate total cost
Carrying Cost % for the EOQ and price break quantities. This model does not consider %
Ordering Cost, Co
Annual Demand, D
Price Break Qty
Unit Price, P
Carrying Cost $, Cc $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Output:
C0 D Cc Q
Price TC= + +PD
Break Qty Unit Price Qopt Discount Q Total Cost Q 2
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! Discount Q: If the EOQ appears in the interval, the
#DIV/0!
discount Q is the EOQ. Otherwise, the discount Q is the
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
price break quantity.
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
0 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0!

Total Cost w/Qty Disc

$12.00

$10.00

$8.00
TC

$6.00

$4.00

$2.00

$0.00
0 1 2 3 4 5 6 7 8 9
Q
Reorder Point
Input annual demand, lead time, standard deviation of
demand, service level, and days/year. (only edit green-shaded
cells)
Input: Output:
Annual Demand, D Z-Value #VALUE!
Lead time (days) Reorder point w/ constant demand #DIV/0!
Std. dev. of daily demand Safety Stock #VALUE!
Service level Reorder point w/ variable demand #DIV/0!
Working days per year
Daily Demand, d 0

Enter cost data, if available, to calculate

EOQ and view order cycle graph.
Inventory Order Cycle
Inventory Level

Note: Order cycle graph will not print for problems without Q data
1.00
Ordering cost, Co
Carrying cost, Cc
0.90
Qopt
0.80 Daily demand
0.70 Inventory Level Number of orders per year
0.60 Time between orders (days)
Reorder Point w/
0.50 constant demand Total Cost, TC
0.40 Reorder Point w/
0.30 variable demand

0.20
0.10
0.00
Qopt =
√ 2C 0 D
Cc
C0 D Cc Q
0
11
22
33
44
55

77
88
99
110
121
132

165
176
187
198
209
220
231
242
253
264
275

297
308
319
330
341
352
66

143
154

286

363

Time (days) TC= +

Q 2
Fixed Time Period Model with Variable Demand
(only edit green-shaded cells)
Input:
Average demand rate = per day
Time between orders = days
Lead time = days
Standard deviation of demand =
Inventory in stock =
Service level =

Output:
Z value = ###
Safety stock = ###
Q= ###

Q=d ( t b + L ) + zs d √ t b + L−I

Page 8