Economic Order Quantity

							Inventory Management using EOQ

                                                                                 EOQ
     Holding Cost                  0.3
                                                                                 66.12
       Order Cost                  160
                                                                            Total Cost
          Unit Cost                 61
                                                                               $16,459.96
          Demand                   250


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
    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
     22             0
     22         17266.5978
   EOQ                        Backorder Option
   66.12

Total Cost
 $16,459.96




                                                                               Holding Cost
                                                                               Order Cost
                                     Costs per Order Quantity
                                                                               Material Cost
                                                                               Total Cost
                                                                               Shortage Cost

              $30,000.00


              $25,000.00


              $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
140.00
Basic 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                  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                      (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