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