Description
EOQ model in excel for Operations management.
Inventory Management using EOQ
Holding Cost Order Cost Unit Cost Demand
Order Quantity Holding Cost Q hC(Q/2) 4.41 $40.33 8.82 $80.66 13.22 $121.00 17.63 $161.33 22.04 $201.66 26.45 $241.99 30.86 $282.32 35.26 $322.66 39.67 $362.99 44.08 $403.32 48.49 $443.65 52.89 $483.98 57.30 $524.32 61.71 $564.65 66.12 $604.98 70.53 $645.31 74.93 $685.64 79.34 $725.98 83.75 $766.31 88.16 $806.64 92.57 $846.97 96.97 $887.30 101.38 $927.63 105.79 $967.97 110.20 $1,008.30 114.60 $1,048.63 119.01 $1,088.96 123.42 $1,129.29 127.83 $1,169.63 132.24 $1,209.96 þÿ0.3 þÿ160 þÿ61 þÿ250 Order Cost (R/Q)S $9,074.69 $4,537.35 $3,024.90 $2,268.67 $1,814.94 $1,512.45 $1,296.38 $1,134.34 $1,008.30 $907.47 $824.97 $756.22 $698.05 $648.19 $604.98 $567.17 $533.81 $504.15 $477.62 $453.73 $432.13 $412.49 $394.55 $378.11 $362.99 $349.03 $336.10 $324.10 $312.92 $302.49 Material Cost CR $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 Shortage Cost (B(Q-n)^2)/2Q $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
EOQ
þÿ66.12
Total Cost
þÿ$16,459.96
Total Cost TC $24,365.02 $19,868.01 $18,395.89 $17,680.00 $17,266.60 $17,004.44 $16,828.71 $16,706.99 $16,621.29 $16,560.79 $16,518.62 $16,490.21 $16,472.37 $16,462.84 $16,459.96 $16,462.48 $16,469.45 $16,480.12 $16,493.92 $16,510.37 $16,529.10 $16,549.79 $16,572.19 $16,596.08 $16,621.29 $16,647.66 $16,675.06 $16,703.39 $16,732.55 $16,762.45
Q 22 22
Line 0 17266.6
EOQ
þÿ66.12
Backorder Option
Total Cost
þÿ$16,459.96
Costs per Order Quantity
$30,000.00
$25,000.00
Column Column Column Column Column Column
C D E G C F
$20,000.00
$15,000.00
$10,000.00
$5,000.00
$0.00 0.00
20.00
40.00
60.00
80.00
100.00
120.00
140.00
Order Quantity (Q)
Q
C D E G C F
120.00
140.00
Basic EOQ Model (Analytical Version)
D= K= h= L= WD = Q= Data 6000 $115 $4.20 9 250 Decision 573.21 (demand/year) (setup cost) (unit holding cost) (lead time in days) (working days/year) (optimal order quantity) Reorder Point Annual Setup Cost Annual Holding Cost Total Variable Cost Results 216 $1,203.74 $1,203.74 $2,407.49
Range Name AnnualHoldingCost AnnualSetupCost D h K L Q ReorderPoint TotalVariableCost WD
Cell G7 G6 C4 C6 C5 C7 C11 G4 G8 C8
Spreadsheet Implementation of EOQ Model Annual Demand D 1000 Ordering Cost S $5.00 Holding Cost H $1.25 Lead Time L 5 Item Cost C $12.50 Total annual inventory cost = Ordering cost + Carrying cost Q Order Quantity 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 (D/Q)*S Ordering Cost (Q/2)*H Carrying Cost
Total Cost
doc_383074880.xls
EOQ model in excel for Operations management.
Inventory Management using EOQ
Holding Cost Order Cost Unit Cost Demand
Order Quantity Holding Cost Q hC(Q/2) 4.41 $40.33 8.82 $80.66 13.22 $121.00 17.63 $161.33 22.04 $201.66 26.45 $241.99 30.86 $282.32 35.26 $322.66 39.67 $362.99 44.08 $403.32 48.49 $443.65 52.89 $483.98 57.30 $524.32 61.71 $564.65 66.12 $604.98 70.53 $645.31 74.93 $685.64 79.34 $725.98 83.75 $766.31 88.16 $806.64 92.57 $846.97 96.97 $887.30 101.38 $927.63 105.79 $967.97 110.20 $1,008.30 114.60 $1,048.63 119.01 $1,088.96 123.42 $1,129.29 127.83 $1,169.63 132.24 $1,209.96 þÿ0.3 þÿ160 þÿ61 þÿ250 Order Cost (R/Q)S $9,074.69 $4,537.35 $3,024.90 $2,268.67 $1,814.94 $1,512.45 $1,296.38 $1,134.34 $1,008.30 $907.47 $824.97 $756.22 $698.05 $648.19 $604.98 $567.17 $533.81 $504.15 $477.62 $453.73 $432.13 $412.49 $394.55 $378.11 $362.99 $349.03 $336.10 $324.10 $312.92 $302.49 Material Cost CR $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 $15,250.00 Shortage Cost (B(Q-n)^2)/2Q $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
EOQ
þÿ66.12
Total Cost
þÿ$16,459.96
Total Cost TC $24,365.02 $19,868.01 $18,395.89 $17,680.00 $17,266.60 $17,004.44 $16,828.71 $16,706.99 $16,621.29 $16,560.79 $16,518.62 $16,490.21 $16,472.37 $16,462.84 $16,459.96 $16,462.48 $16,469.45 $16,480.12 $16,493.92 $16,510.37 $16,529.10 $16,549.79 $16,572.19 $16,596.08 $16,621.29 $16,647.66 $16,675.06 $16,703.39 $16,732.55 $16,762.45
Q 22 22
Line 0 17266.6
EOQ
þÿ66.12
Backorder Option
Total Cost
þÿ$16,459.96
Costs per Order Quantity
$30,000.00
$25,000.00
Column Column Column Column Column Column
C D E G C F
$20,000.00
$15,000.00
$10,000.00
$5,000.00
$0.00 0.00
20.00
40.00
60.00
80.00
100.00
120.00
140.00
Order Quantity (Q)
Q
C D E G C F
120.00
140.00
Basic EOQ Model (Analytical Version)
D= K= h= L= WD = Q= Data 6000 $115 $4.20 9 250 Decision 573.21 (demand/year) (setup cost) (unit holding cost) (lead time in days) (working days/year) (optimal order quantity) Reorder Point Annual Setup Cost Annual Holding Cost Total Variable Cost Results 216 $1,203.74 $1,203.74 $2,407.49
Range Name AnnualHoldingCost AnnualSetupCost D h K L Q ReorderPoint TotalVariableCost WD
Cell G7 G6 C4 C6 C5 C7 C11 G4 G8 C8
Spreadsheet Implementation of EOQ Model Annual Demand D 1000 Ordering Cost S $5.00 Holding Cost H $1.25 Lead Time L 5 Item Cost C $12.50 Total annual inventory cost = Ordering cost + Carrying cost Q Order Quantity 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 (D/Q)*S Ordering Cost (Q/2)*H Carrying Cost
Total Cost
doc_383074880.xls