1
2
3
4
5
6
7
8
9
10
11
12
13
A B C D E F G H I J
Basic EOQ Model
Data Results Range Name Cell
D = 600 (demand/year) Reorder Point 10 AnnualHoldingCost G7
K = $75 (setup cost) AnnualSetupCost G6
h = $4 (unit holding cost) Annual Setup Cost $900 DC4
L = 5 (lead time in days) Annual Holding Cost $100 hC6
WD = 300 (working days/year) Total Variable Cost $1,000 KC5
LC7
Decision Q C11
Q = 50 ReorderPoint G4
TotalVariableCost G8
WD C8
1
2
3
4
5
6
7
8
9
10
11
12
13
A B C D E F G H I J
Basic EOQ Model (Analytical Version)
Data Results Range Name Cell
D = 3,000 (demand/year) Reorder Point 0 AnnualHoldingCost G7
K = $200 (setup cost) AnnualSetupCost G6
h = $20 (unit holding cost) Annual Setup Cost $2,449 DC4
L = 0 (lead time in days) Annual Holding Cost $2,449 hC6
WD = 300 (working days/year) Total Variable Cost $4,899 KC5
LC7
Decision Q C11
Q = 245 (optimal order quantity) ReorderPoint G4
TotalVariableCost G8
WD C8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A B C D E F G H I J
EOQ Model with Planned Shortages (Analytical Version)
Data Results Range Name Cell
D = 6,000 (demand/year) Max Inventory Level 563 AnnualHoldingCost G7
K = $115 (setup cost) AnnualSetupCost G6
h = $4.20 (unit holding cost) Annual Setup Cost $1,183 AnnualShortageCost G8
p = $120 (unit shortage cost) Annual Holding Cost $1,143 DC4
Annual Shortage Cost $40 hC6
Decision Total Variable Cost $2,366 KC5
Q = 583 (optimal order quantity) MaxInventoryLevel G4
S = 20 (optimal maximum shortage) p C7
Q C10
S C11
TotalVariableCost G9
1
2
3
4
5
6
7
8
9
10
11
13
15
16
17
18
19
20
21
A B C D E F G H I J
EOQ Model with Planned Shortages (Solver Version)
Data Results Range Name Cell
D = 676 (demand/year) Max Inventory Level 6 AnnualHoldingCost G7
K = $75 (setup cost) AnnualSetupCost G6
h = $600 (unit holding cost) Annual Setup Cost $1,950 AnnualShortageCost G8
p = $200 (unit shortage cost) Annual Holding Cost $415 DC4
Annual Shortage Cost $1,538 hC6
Decision Total Variable Cost $3,904 KC5
Q = 26 (order quantity) MaxInventoryLevel G4
S = 20 (maximum shortage) p C7
37
38
39
40
41
42
43
44
45
46
A B C D E F G H I J
12 $1,950 $2,262 $554 $4,765
14 $1,950 $1,662 $754 $4,365
16 $1,950 $1,154 $985 $4,088
18 $1,950 $738 $1,246 $3,935
20 $1,950 $415 $1,538 $3,904
22 $1,950 $185 $1,862 $3,996
24 $1,950 $46 $2,215 $4,212
26 $1,950 $0 $2,600 $4,550
28 $1,950 $46 $3,015 $5,012
30 $1,950 $185 $3,462 $5,596
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
A B C D E F G H I J K
EOQ Model with Quantity Discounts (Analytical Version)
Data
D = 5,200 (demand/year)
K = $50 (setup cost)
I = 20% (inventory holding cost rate)
N = 3 (number of discount categories)
Annual Annual Annual Total
Range of order quantities Purchase Setup Holding Variable
Category Price Lower Limit Upper Limit EOQ Q* Cost Cost Cost Cost
1$100 099 161 99 $520,000 $2,626 $990 $523,616
2$95 100 499 165 165 $494,000 $1,572 $1,572 $497,143
3$90 500 10000000 170 500 $468,000 $520 $4,500 $473,020
Results
Optimal Q 500
Total Variable Cost $473,020
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
A B C D E F G H I J K
EOQ Model with Quantity Discounts (Analytical Version)
Data
D = 365 (demand/year)
K = $5 (setup cost)
I = 20% (inventory holding cost rate)
N = 3 (number of discount categories)
Annual Annual Annual Total
Range of order quantities Purchase Setup Holding Variable
Category Price Lower Limit Upper Limit EOQ Q* Cost Cost Cost Cost
1$4.00 149 68 49 $1,460 $37 $20 $1,517
2$3.90 50 99 68 68 $1,424 $27 $27 $1,477
3$3.80 100 10000000 69 100 $1,387 $18 $38 $1,443
Results
Optimal Q 100
Total Variable Cost $1,443
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
A B C D E F G H I J K
EOQ Model with Quantity Discounts (Analytical Version)
Data
D = 400 (demand/year)
K = $80 (setup cost)
I = 20% (inventory holding cost rate)
N = 3 (number of discount categories)
Annual Annual Annual Total
Range of order quantities Purchase Setup Holding Variable
Category Price Lower Limit Upper Limit EOQ Q* Cost Cost Cost Cost
1$8.50 199 194 99 $3,400 $323 $84 $3,807
2$8.00 100 999 200 200 $3,200 $160 $160 $3,520
3$7.50 1000 10000000 207 1000 $3,000 $32 $750 $3,782
Results
Optimal Q 200
Total Variable Cost $3,520
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
A B C D E F G H I J K
EOQ Model with Quantity Discounts (Analytical Version)
Data
D = 2,400 (demand/year)
K = $4 (setup cost)
I = 17% (inventory holding cost rate)
N = 3 (number of discount categories)
Annual Annual Annual Total
Range of order quantities Purchase Setup Holding Variable
Category Price Lower Limit Upper Limit EOQ Q* Cost Cost Cost Cost
1$1.00 1199 336 199 $2,400 $48 $17 $2,465
2$0.95 200 499 345 345 $2,280 $28 $28 $2,336
3$0.90 500 10000000 354 500 $2,160 $19 $38 $2,217
Results
Optimal Q 500
Total Variable Cost $2,217
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G H I J
EOQ Model with Gradual Replenishment (Analytical Version)
Data Results Range Name Cell
D = 6,000 (demand/year) Annual Setup Cost $45,000 AnnualHoldingCost G4
PR = 24,000 (production rate) Annual Holding Cost $45,000 AnnualShortageCost G5
K = $7,500 (unit setup cost) Total Variable Cost $90,000 DC4
h = $120 (unit holding cost) h C7
KC6
Decision PR C5
Q = 1,000 (production lot size) Q C10
TotalVariableCost G6
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G H I J
EOQ Model with Gradual Replenishment (Analytical Version)
Data Results Range Name Cell
D = 52,000 (demand/year) Annual Setup Cost $13,000 AnnualHoldingCost G4
PR = 104,000 (production rate) Annual Holding Cost $13,000 AnnualShortageCost G5
K = $1,000 (unit setup cost) Total Variable Cost $26,000 DC4
h = $13 (unit holding cost) h C7
KC6
Decision PR C5
Q = 4,000 (production lot size) Q C10
TotalVariableCost G6
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G H I J
EOQ Model with Gradual Replenishment (Analytical Version)
Data Results Range Name Cell
D = 250,000 (demand/year) Annual Setup Cost $60,000 AnnualHoldingCost G4
PR = 750,000 (production rate) Annual Holding Cost $60,000 AnnualShortageCost G5
K = $12,000 (unit setup cost) Total Variable Cost $120,000 DC4
h = $3.60 (unit holding cost) h C7
KC6
Decision PR C5
Q = 50,000 (production lot size) Q C10
TotalVariableCost G6
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
23
24
25
26
27
28
A B C D E F G H I J
EOQ Model with Gradual Replenishment (Analytical Version)
Data Results Range Name Cell
D = 250,000 (demand/year) Annual Setup Cost $75,000 AnnualHoldingCost G4
PR = 750,000 (production rate) Annual Holding Cost $75,000 AnnualShortageCost G5
K = $15,000 (unit setup cost) Total Variable Cost $150,000 DC4
h = $4.50 (unit holding cost) h C7
KC6
Decision PR C5
Q = 50,000 (production lot size) Q C10
TotalVariableCost G6
Holding Cost (h)
50,000 $2.70 $3.60 $4.50
Setup $9,000 50,000 43,301 38,730
Cost $12,000 57,735 50,000 44,721
(K) $15,000 64,550 55,902 50,000
Optimal Order Quantity (Q)
Holding Cost (h)
$150,000 $2.70 $3.60 $4.50
Setup $9,000 $90,000 $103,923 $116,190
Cost $12,000 $103,923 $120,000 $134,164
(K) $15,000 $116,190 $134,164 $150,000
Total Variable Cost (when Q = Q*)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
A B C D E F G H I J
EOQ Model with Gradual Replenishment
Data Results Range Name Cell
D = 250,000 (demand/year) Annual Setup Cost $75,000 AnnualHoldingCost G4
PR = 750,000 (production rate) Annual Holding Cost $75,000 AnnualShortageCost G5
K = $15,000 (unit setup cost) Total Variable Cost $150,000 DC4
h = $4.50 (unit holding cost) h C7
KC6
Decision PR C5
Q = 50,000 (production lot size) Q C10
TotalVariableCost G6
Holding Cost (h)
$150,000 $2.70 $3.60 $4.50
Setup $9,000 $90,000 $105,000 $120,000
Cost $12,000 $105,000 $120,000 $135,000
(K) $15,000 $120,000 $135,000 $150,000
Total Variable Cost (when Q = 50,000)
1
2
3
4
5
6
7
8
9
10
11
A B C D E F G H I J
EOQ Model with Gradual Replenishment (Analytical Version)
Data Results Range Name Cell
D = 250,000 (demand/year) Annual Setup Cost $67,082 AnnualHoldingCost G4
PR = 1,500,000 (production rate) Annual Holding Cost $67,082 AnnualShortageCost G5
K = $12,000 (unit setup cost) Total Variable Cost $134,164 DC4
h = $3.60 (unit holding cost) h C7
KC6
Decision PR C5
Q = 44,721 (production lot size) Q C10
TotalVariableCost G6